Summary: Billy Hollis explains the benefits of using the stored procedure in complex systems, making the storage process beyond the category of demo software, providing a practical example of how to access stored procedures and starting using these stored procedures in your own application .
Our author usually divides the software into two categories - real software and demo software. Real software is software used in a real environment. Demonstration software is used to interpret programming concepts.
Most of the code you see in article and books are demo software. It must be simpler than the real software, otherwise the reader will give God for details that are unrelated to the concept of interpretation. But sometimes demonstration software is too extreme. Pursuit of simplicity will ignore the details needed to write real software.
Recently, I have encountered such a problem in data access. In fact, each data access example I have ever read using the SQL statement from the relational database (eg, Microsoft SQL Server?) Or writes in it. However, in a real environment, this is an uncomfortable programming method except for a limited small system. Structure rationally N-layer application uses a stored procedure to replace the SQL statement for data access.
The stored procedure is conceptually similar to a function in the program. They get input parameters to run in black box mode and return to the corresponding information. Unlike the function, the stored procedure is performed by the database engine, not in the program. That is, it is necessary to input information to a stored procedure or from the output information. In Microsoft Visual Basic? 6.0, this technology is traditional ADO. In Visual Basic .NET, we can use ADO.NET to complete this task.
For many programming tasks, Visual Basic .NET makes it easy to access data through storage procedure than using Visual Basic 6.0. Some of them are used to help the process of guidance, once you learn how to avoid some errors, even if you use ADO.NET, you are not complicated.
This article describes some basic techniques for using the stored procedures in ADO.NET and starts from read-only operations until how to use stored procedures to perform data insertion, delete, and update.
You can also benefit from this article without the writing of the preparation of the stored procedure. Many large-scale programming group developers need to use stored procedures written by others. One of our examples needs to insert the stored procedure into the sample database, but we will gradually introduce this task.
Introduction to ADO.NET
This article assumes that you have learned the basics of ADO.NET. If you have never used the DataAdapter, Dataset, and Command objects in ADO.NET in your work, you should read some articles on Ado.Net, including Rocky's named ADO.NET written by this column.
Briefly, DataSet is used as a data container in ADO.NET and is used when disconnecting with the database. DataSet contains one or more DataTable, each of which contains a set of rows. For those familiar with the traditional ADO environment, DataTable can be seen as a discordset that is disconnected.
DataAdapter works when connecting to the database. The role of a single DataAdapter is to populate a DataTable using data in the database, or write the changes in DataTable back to the database, or both.
DataAdapter requires the Command object to perform various database operations. The Command object stores the SQL statement or the stored procedure name of the specified data access implementation method. Each DataAdapter has four properties that specify a command object for one of the four data access types.
SELECTCOMMAND: This Command object is used to select data from the database. UpdateCommand: This Command object is used to update existing records in the database. INSERTCOMMAND: This Command object is used to insert new records into the database. DELETECOMMAND: This Command object is used to delete existing records in the database. Figure 1 illustrates these objects and their relationships.
Figure 1: The main ADO.NET class used to access the stored procedure and the relationship between them
So far, the presentation software examples you have seen may configure their COMMAND object to use the SQL statement to perform data access. In fact, some examples may completely skip the creation of the Command object because a constructor of DataAdapter allows the Command object to select the data created in the background. Let us run such an example to compare before using the stored procedure.
All examples in this article use the Northwind sample database included with SQL Server. We also use the ADO.NET classes created specifically for SQL Server, not ordinary OLE DB classes. For ease of accessing these SQL Server classes, all examples need to add the following code lines at the top of the application code:
Imports system.data.sqlclient
Now let's take a look at the first example of not using the stored procedure to perform data access. In this example, we will retrieve all products in the Northwind Database Products table. Create a new Windows application, on the blank Form1 that appears, places a button and a DataGrid. Set the DataGrid's Anchor property to all four sides, expand it with the form of the form. Place the following code in the click Click event:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim sSQL As String = "SELECT * FROM Products" Dim daGetProducts As New SqlDataAdapter (sSQL, sConnectionString) Dim dsProducts As New DataSet () DagetProducts.Fill (DSProducts, "Products") DataGrid1.datasource = DSPRODUCTS.TABLES ("Products")
Depending on the computer configuration, you may need to change the connection string. After establishing a database connection, the remaining code should be able to run normally. This demo software illustrates the easiest way to fill in and use DataSet.
Note that the code does not create a Connection object or a Command object. In fact, there is no such object, and ADO.NET will not work, but they are created and used in the background. Instantiate the code line of SqlDataAdapter into the SQL string (for configuring the background Command object) and the connection string (for configuring the background Connection object).
We can change this code to use explicit connection and command objects to stay away from demonstration software. Place a button on the form and place the following code in the Click event:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim sSQL As String = "SELECT * FROM Products" Dim cnNorthwind As New SqlConnection (sConnectionString) Dim cmdProducts As New SqlCommand (sSQL, cnNorthwind ) Dim daGetProducts As New SqlDataAdapter (cmdProducts) Dim dsProducts As New DataSet () daGetProducts.Fill (dsProducts, "Products") DataGrid1.DataSource = dsProducts.Tables ( "Products") code by explicitly creating Connection and Command objects, and Attach these objects to DataAdapter, which illustrates the commonality of DataAdapters. By incurring CMDPRODUCTS when instantiation DataAdapter, DataAdapter's SELECTCOMMAND will be set. Then you can use the DataAdapter to access the database.
The result of this code is the same as the result in the previous example. Although it is a bit close to the real software, since the data access is implemented through the SQL statement, it is still a demo software.
Acquire data using simple stored procedures
How to change this demo software to use stored procedures? Simply change a few lines of code. Place a button on the form and place the following code in the Click event:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim cnNorthwind As New SqlConnection (sConnectionString) Dim cmdProducts As New _ SqlCommand ( "ten most expensive products", cnNorthwind) cmdProducts. CommandType = CommandType.StoredProcedure Dim daGetProducts As New SqlDataAdapter (cmdProducts) Dim dsProducts As New DataSet () daGetProducts.Fill (dsProducts, "Products") DataGrid1.DataSource = dsProducts.Tables ( "Products")
When instantiate the Command object, this code does not use the SQL statement and replaced the stored procedure name to use. In addition, the CommandType property of the Command object must be set to StoredProcedure.
Since then the code is very similar to the previous example, but it returns different data. The stored procedure looks for ten most expensive products and only returns the name and price of each product.
Stored procedure with input parameters
This example is simple because the stored procedure does not require any input parameters. That is to say, finding ten most expensive products do not require any external information. No need to help, the stored procedure can do this. However, most stored procedures require input parameters to perform their functions. In the next example, let's see how to pass the input parameters to the stored procedure. We will use CustomerID to get all orders for the relevant customers and use the stored procedure called CustOrdRhist (already existing in the Northwind database). Create a button on the list of used, and place the following code line to the button's Click event:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim cnNorthwind As New SqlConnection (sConnectionString) Dim cmdOrders As New SqlCommand ( "CustOrderHist", cnNorthwind) cmdOrders.CommandType = CommandType.StoredProcedure ' setting parameters Dim prmCustomerID as New SqlParameter () is a stored procedure prmCustomerID.ParameterName = "@CustomerID" prmCustomerID.SqlDbType = SqlDbType.VarChar prmCustomerID.Size = 5 prmCustomerID.Value = "ALFKI" cmdOrders.Parameters.Add (prmCustomerID) Dim daGetOrders as New SqldataAdapter (cmdorders) Dim dsorders as new dataset () DagetRDERS.Fill (DsORDERS, "ORDERS") DataGrid1.DataSource = DSORDERS.TABLES ("Orders")
This code is very similar to the code in the previous example, and the difference is to create a Command object, configure the Parameter object and add this object to the parameter collection of Command. In this example (closer to presentation software), the client ID is hard coded, and the value attribute of the parameter is typically set to some user input data. However, other attributes of the parameters can be fully set in this example.
All parameter settings in this example are explicit settings. Some developers like this style because it makes it easy. But some developers like to use less lines with less lines:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim cnNorthwind As New SqlConnection (sConnectionString) Dim cmdOrders As New SqlCommand ( "CustOrderHist", cnNorthwind) cmdOrders.CommandType = CommandType.StoredProcedure cmdOrders .Parameters.Add (New _ SqlParameter ( "@ CustomerID", SqlDbType.VarChar, 5)) cmdOrders.Parameters ( "@ CustomerID"). Value = "ALFKI" Dim daGetOrders As New SqlDataAdapter (cmdOrders) Dim dsOrders As New DataSet ( DataRDERS.FILL (DsORDERS, "Orders") DataGrid1.datasource = dsORDERS.TABLES ("Orders") This code is identical to the previous example. But each parameter only needs two lines of code, not six lines. If the stored procedure contains a large number of parameters (as shown in some examples later), there will be significant differences in the required code line, so in the back section, we will use this form.
Use stored procedures in Visual Basic .NET (2)
Update the database using the stored procedure
The above examples use the stored procedure to extract information from the database. It is also common to use stored procedure updates in complex applications. Let's see how to do this using ADO.NET.
In the first example, we will use the wizard in Visual Studio .NET to write a stored procedure collection and create code that uses these procedures. Although we only need to write a minimum of code in this example, the code that checks the wizard creation helps us understand the process of interacting with the stored procedure in addition to obtaining data.
In this example, we will use the Customers table in the Northwind sample database. The installed Northwind database does not contain stored procedures for updates, insert, or delete customers, but the DataAdapter Configuration Wizard in Visual Studio .NET is easy to write some stored procedures for us.
Start a new Windows Application project. Place a DataGrid and two buttons on blank Form1. As previously, change the ANCHOR attribute of DataGrid to all four sides. Name the button btnfill and btNupdate and change its text attribute to Fill and Update.
Go to the Toolbox's DATA (Data) tab, drag the SqlDataAdapter control onto the form and release the mouse. This will launch the DataAdapter Configuration Wizard (Data Adapter Configuration Wizard). Click the Next button to start entering the wizard.
First, you need to select a connection to the NorthWind database; if you do not display the connection in the list, click the New Connection button to create a connection. Then click the Next button. There will be three data access methods on the next screen. Its appearance is similar to Figure 2.
Figure 2: Select Data Access Type for DataAdapter
At this point, most demo software examples choose the first option to use the SQL statement. However, we will use the second option and let the wizard generate some stored procedures for us. Select CREATE New Stored Procedures Options, and then click the Next button.
The next screen will request the SQL statement to indicate the data originally extracted from the database. But does not use this SQL statement directly. The information in the SQL statement will be used to construct the stored procedure to perform actual data access. In order to make the example simply, enter the SQL statement select * from customers, then press the Next button.
At this point, the wizard requests the name of the stored procedure to be created. There are four operations in operation - SELECT, UPDATE, INSERT, and DELETE operations. Name the following:
SELECT: MSDNSELECTCUSTOMERS UPDATE: MSDNUPDATECUSER INSERT: MSDNINSERTCUSTOMER DELETE: MSDNDLETECUSTOMER
Select Yes, Create The Database for ME (Yes, create them in the database.) Options. At this point, the wizard screen should be shown in Figure 3.
Figure 3: Naming the stored procedure to be created by the DataAdapter wizard
Click the Next button. The wizard will create a stored procedure and indicate its progress on the status bar screen. Once you have finished, click the Finish button to exit the wizard.
The wizard creates a complete DataAdapter, but does not create DataSet to store data. This is our next step. Drag the DataSet control in the DATA (Data) tab of the Toolbox (Toolbox). When the configuration screen appears, select the untyped dataset.
Now we are ready to use the DataAdapter populated data set. In BTNFill's Click event, add the following two lines of code:
SqlDataAdapter1.fill (DataSet1, "Customers") DataGrid1.datasource = DataSet1.tables ("Customers")
In the Click event of btnupdate, put the following code line:
SqldataAdapter1.Update (Dataset1, "Customers")
Now we have a valid demo software that uses stored procedures for data access. You can run the program and click the Fill button to get the list of customers in the grid. Then, you can edit the customer record in the pane and select the Update button to return the change to the database.
Note: Edit the first column (ie
An exception will appear when CustomerID is because the primary key in the database record cannot be updated in SQL Server.
Viewing the code generated by the wizard will help, all of which are hidden in the Windows Form Designer Generated Code (the code generated by the Windows Form Designer) area. Click the plus number of the area to expand the code. Note the following code that instantiates the desired SqlDataAdapter and four command objects: Me.SqlDataAdapter1 = new system.data.sqlclient.sqlDataAdapter () me.sqlselectcommand1 = new system.data.sqlclient.sqlCommand () me. SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand () Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand () Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand ()
Since then the code configures each command object and creates a collection of parameters. This code is similar to the previous example, which uses a stored procedure with parameters. However, the code-creatored code uses some additional properties of the parameters to work with the stored procedure for changing the data. For example, the code for creating the companyName parameter of SQLInsertCommand1:
Me.sqlinsertCommand1.Parameters.add (new _ system.data.sqlclient.sqlparameter ("@ companyName", _ system.data.sqldbtype.nvarchar, 40, "companyname"))
In the previous example, we only set attributes for parameter names, data types, and lengths.
This code will also set the SourceColumn property of the parameter to GroupName. This attribute indicates the field corresponding to this parameter in the Customers DataTable of the DataSet. This allows the value in the DataTable to automatically insert into the Value property of the parameter during the insert operation. Let us introduce it in detail.
This method updates a single DataTable in the DataSet when calling the Update method of the SqlDataAdapter. When checking DataTable by line, you will find rows that need to be updated, inserted, or deleted. When you find the row you need to insert into the database, SqlDataAdapter will use the Command object set by its INSERTCOMMAND property. In this case, the Command object will access the MSDNISERTCUSTOMER stored procedure.
Before the stored procedure is run, the value attribute of each parameter must be imported from the inserted row. The code for configuring SqlDataAdapter1 associates each parameter of the stored procedure to the corresponding fields in the DataTable. Thus, the data in the new DataTable line will automatically transfer to the parameters of the stored procedure.
The configuration method of other stored procedure parameters is similar. But there is a difference worth noting. Other stored procedures are incorporated into the original value of the data in the data, which is used to check if the data has changed without knowing. That is, if you have extracted some data, others change the data before you try to update, you will receive concurrent anomalies. To start the above programs, extract customers, then use tools (such as SQL Enterprise Manager to change the contents of the records, you can see this situation. If you change the same record in the sample program and try to update, it will receive concurrent anomalies.
Use stored procedures in Visual Basic .NET (3)
Return value from stored procedure
The above example has a shortcomings. The Northwind Customers table uses the primary key in the form of a digital alphabet, and must be generated by an application that is inserted into the data. That is, if you are inserted into a new record using the above program, you must create a value consisting of five characters yourself for CustomerID. In real software, automatically generate primary keys for new records is more common. The primary key is usually allocated in order.
There are two basic technologies for the new record setting primary key. The application can call the generated stored procedure to generate the next available ID and then put this ID directly in the new row of DataSet. Alternatively, the stored procedure for inserting records can be derived for a new ID, and then transfer it to the application as a return value.
The first technique requires a little additional logic to get the new ID and put it in the corresponding position of the new record. Execution of the insertion operation using the stored procedure is similar to the above example.
But the second technical requirement uses a new type of parameters during the stored procedure. All parameters we have seen so far are the default type, that is, the input parameters. In fact, the parameters are divided into four types:
INPUT This parameter is only used to transfer information from an application to the stored procedure. InputOutput This parameter can transfer information from the application to the stored procedure and transmit information from the stored procedure back to the application. Output This parameter is only used to transfer information from the stored procedure back to the application. ReturnValue This parameter indicates the return value of the stored procedure. This parameter is not displayed in the stored procedure list of SQL Server. It only associates only values in the Return statement of the stored procedure.
After the stored procedure generates a new value for the primary key, the RETURN statement in the stored procedure is usually returned to this value, so the parameter type used to access the value is the returnValue parameter.
ReturnValue parameters have an important difference from other types of parameters. Typically, the order in which parameters configured for the Command object in ADO.NET is not important. The parameter name is only used to match the corresponding parameters during the stored procedure. However, for the ReturnValue parameter, it must be the first parameter in the list.
That is, when the returnValue parameter is configured for the Command object, the parameter must be configured in the code so that it can get the first digital index in the collection. If any other parameters are configured first, the returnValue parameter will not work properly.
To illustrate the usage of the stored procedure with the return value, we write an example of inserting records in the Northwind Products table. This table is set to automatically create new product IDs using the Identity column. Unfortunately, the Northwind sample database does not include the stored procedure to perform the required operation, so we need to insert a stored procedure to the database before completing the remainder of the sample.
Go to the Server Explorer in Visual Studio .NET (Server Explorer). Open the node of SQL Server, open the node of the SQL Server instance, and open the node of the Northwind database.
Right-click the Stored Procedures node, select New Stored Procedure. In the editing window that appears, replace all of the text with the following text:
ALTER PROCEDURE dbo.MSDNInsertProduct (@ProductName nvarchar (40), @SupplierID int, @CategoryID int, @QuantityPerUnit nvarchar (20), @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint, @ReorderLevel smallint, @Discontinued bit) AS declare @ProductID int SET NOCOUNT OFF; INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @ Unitsonorder, @reorderlevel, @discontinued; select @ProductId = @@ identity return @ProductID Now close the editing window, when you ask if you want to save your changes, click Yes. Now the stored procedure is saved to the database and is named MSDNINSERTPRODUCT.
You can now write code to use this stored procedure. Create a new Windows application, put on blank Form1, place anchored to all four side DataGrid, you also need to add two buttons called BTNFILL and BTNISERTPRODUCT. Setting BTNFILL's text property to Fill, set the TEXT property of btnInsertProduct to INSERT Produc.
Place the following code in the Click event of BTNFill:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim sSQL As String = "SELECT * FROM Products" Dim daGetProducts As New SqlDataAdapter (sSQL, sConnectionString) Dim dsProducts As New DataSet () DAGETPRODUCTS.FILL (DSPRoducts, "Products") DataGrid1.datasource = DSPRODUCTS
It is roughly the same as the code mentioned in the foregoing, so we will not have to repeat it. Don't forget to change the connection string if necessary, and place an Imports statement at the top of the project code to sqlclient namespace. The following code is then placed in the Click event of btnInsertProduct:
Dim sConnectionString As String = _ "server = localhost; uid = sa; pwd =; database = Northwind" Dim cnNorthwind As New SqlConnection (sConnectionString) Dim cmdInsertProduct As New SqlCommand ( "MSDNInsertProduct", cnNorthwind) cmdInsertProduct.CommandType = CommandType.StoredProcedure ' cmdInsertProduct.Parameters.Add setting parameters for the stored procedure (New SqlParameter ( "@ RETURN_VALUE", SqlDbType.Int, 4, "ProductID")) cmdInsertProduct.Parameters ( "@ RETURN_VALUE"). Direction = ParameterDirection.ReturnValue cmdInsertProduct.Parameters.Add (New SqlParameter ( "@ ProductName", _ SqlDbType.NVarChar, 40, "ProductName")) cmdInsertProduct.Parameters.Add (New SqlParameter ( "@ SupplierID", _ SqlDbType.Int, 4, "SupplierID")) cmdInsertProduct.Parameters .Add (New SqlParameter ( "@ CategoryID", _ SqlDbType.Int, 4, "CategoryID")) cmdInsertProduct.Parameters.Add (New SqlParameter ( "@ QuantityPerUnit", _ SqlDbType.NVarChar, 20, "QuantityPerUnit")) cmdInsertProduct .Parameters.add (New Sqlparameter ("@ Unitprice", _ SqldbType.money, 8, "Unitprice")) cmdInsert Product.Parameters.Add (New SqlParameter ( "@ UnitsInStock", _ SqlDbType.SmallInt, 2, "UnitsInStock")) cmdInsertProduct.Parameters.Add (New SqlParameter ( "@ UnitsOnOrder", _ SqlDbType.SmallInt, 2, "UnitsOnOrder" )) cmdInsertProduct.Parameters.Add (New SqlParameter ( "@ ReorderLevel", _ SqlDbType.SmallInt, 2, "ReorderLevel")) cmdInsertProduct.Parameters.Add (New SqlParameter ( "@ Discontinued", _ SqlDbType.Bit, 1, " Discontinued "DISCONTINUED"
)) Dim daInsertProduct As New SqlDataAdapter () daInsertProduct.InsertCommand = cmdInsertProduct Dim dsProducts As DataSet = CType (DataGrid1.DataSource, DataSet) Dim drNewProduct As DataRow drNewProduct = dsProducts.Tables ( "Products"). NewRow drNewProduct.Item ( "ProductName" ) = "Billy's Sesame Oil" drNewProduct.Item ( "SupplierID") = 4 drNewProduct.Item ( "CategoryID") = 7 drNewProduct.Item ( "QuantityPerUnit") = "6 10oz bottles" drNewProduct.Item ( "UnitPrice") = 69 drNewProduct.Item ( "UnitsInStock") = 12 drNewProduct.Item ( "UnitsOnOrder") = 0 drNewProduct.Item ( "ReorderLevel") = 6 drNewProduct.Item ( "Discontinued") = False dsProducts.Tables ( "Products"). Rows.add (DRNEWPRODUCT) DAINSERTPRODUCT.UPDATE (DSProducts ")) MsgBox (" ProductID ") This code is similar to the code as shown above, just different lines of return value configuration parameters. . Note that it is the first parameter and is set to place the return value back into the ProductID field.
The code used to insert a new row to the data set is a standard ADO.NET code, so we will not repeat it. It creates a line of appropriate structures for product records (using product DataTable newrow methods), then puts the data into the row, and finally add rows to the product DataTable ROWS collection.
Now run the program for testing. Click the Fill button, but do not make any changes to the data in the grid. Then press the INSERT Producuct button. The new product records that will be inserted into the Billy's Sesame Oil, and the message box that appears will notify you of the ProductID returned. You can also open the Products table in the grid, scroll to the bottom, and see the new row has been added.
Write parameter code using Server Explorer (Server Explorer)
The above code is written until both lengthy and cumbersome. However, the DataAdapter Configuration Wizard prompts you can write this code for us using Visual Studio. DataAdapter Configuration Wizard generates code for the four stored procedures required for full configuration (SELECT, UPDATE, INSERT, and DELETE). Suppose you only need a memory code as the above example, you can short it. To get a pre-written code with a stored procedure, simply expand the Server Explorer to display the stored procedures you need to access, then drag the stored procedure to the design interface. The DataAPter and Command objects created for this stored procedure are seen, and the designer section of the code contains all the code required for the stored procedure configuration parameter. This code can be used as needed, or it can be replicated and adjusted as needed. summary
The examples in this article are still demo software, but at least enough to explain how to access stored procedures so that you start writing your own real software. Of course, you need to understand the stored procedures you want to access, and you may need to consult with the database administrator (DBA) or other group members to get this information.
For complex systems, there are many advantages in the stored procedure. I hope that you have learned enough knowledge in this article, you can do not worry about how to start using them. When you try to write a code for the first time, you may want to use the DataAdapter Wizard or Server Explorer (Server Explorer). But if you can write access code yourself when necessary, you can use the stored procedure more efficiently.
