Using stored procedures in Visual Basic. NET (2)

visual| stored procedures return values from stored procedures
The example above has one disadvantage. The Northwind Customers table uses a primary key in alphanumeric form and must be generated by the application that inserts the data. In other words, if you use the above program to insert a new record, you must create a five-character value for CustomerID.

In real software, it is more common to automatically generate primary keys for new records. Primary keys are usually long integers that are assigned sequentially.

There are two basic techniques for setting a primary key for a new record. The application can call a stored procedure that generates the next available ID, and then place the ID directly on a new row of the DataSet. Alternatively, the stored procedure used to insert the record can derive a new ID for the record and then pass it back to the application as the return value.

The first technique requires a little extra logic to get the new ID and put it in the appropriate location for the new record. Using a stored procedure to perform an insert operation is similar to the preceding example.

But the second technique requires a new type of parameter to be used in the stored procedure. All the parameters we've seen so far are the default type, which is the input parameter. There are actually four types of arguments:

Input This parameter is used only to transfer information from the application to the stored procedure.
InputOutput This parameter transfers information from an application to a stored procedure and transmits information from the stored procedure back to the application.
Output This parameter is used only to transfer information from the stored procedure back to the application.
ReturnValue This parameter represents the return value of the stored procedure. This parameter is not displayed in the list of stored procedure parameters for SQL Server. It is only associated with the value in the return statement of the stored procedure.

When a stored procedure generates a new value for the primary key, the value is usually returned using the return statement in the stored procedure, so the parameter type used to access the value is the returnvalue parameter.

The returnvalue parameter has an important difference from other types of arguments. In general, the order of the parameters configured for the Command object in Ado.net is not important. The parameter name is used only to match the corresponding parameter in the stored procedure. However, for the returnvalue parameter, it must be the first parameter in the list.

In other words, when you configure the returnvalue parameter for the Command object, you must first configure the parameter in your code so that it can get the first numeric index in the collection. If you configure any other parameters first, the returnvalue parameter will not work correctly.

To illustrate the use of stored procedures with return values, we write an example of inserting records in the Northwind Products table. This table is set to automatically create a new product ID using the Identity column. Unfortunately, the Northwind sample database does not contain stored procedures to perform the required actions, so before we complete the remainder of the example, we need to insert one of these stored procedures into the database.

Go to Server Explorer (Server Explorer) in Visual Studio. NET. Open the node for SQL Server, open the node of the SQL Server instance, and open the node for the Northwind database.

Right-click the Stored procedures (stored procedure) node and select New Stored Procedure (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 the 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 edit window and when asked if you want to save your changes, click Yes. The stored procedure is now saved to the database and named MSDNInsertProduct.

You can now write code to use this stored procedure. To create a new Windows application, on a blank Form1, place a DataGrid anchored to all four sides, and add two buttons named Btnfill and btnInsertProduct. Set the Btnfill Text property to Fill, and set the btnInsertProduct Text property to Insert Product.

In the Btnfill Click event, place the following code:

Dim sConnectionString as String = _
"Server=localhost;uid=sa;pwd=;d Atabase=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's roughly the same as the code described earlier in this article, so let's not repeat it. Do not forget to change the connection string when necessary, and place an Imports statement for the SQLClient namespace at the top of the project code. Then place the following code in the btnInsertProduct Click event:

Dim sConnectionString as String = _
"Server=localhost;uid=sa;pwd=;d Atabase=northwind"
Dim Cnnorthwind as New SqlConnection (sConnectionString)
Dim cmdinsertproduct as New SqlCommand ("MSDNInsertProduct", Cnnorthwind)
Cmdinsertproduct.commandtype = CommandType.StoredProcedure
' Set parameters for stored procedures
CMDINSERTPRODUCT.PARAMETERS.ADD (New SqlParameter ("@RETURN_VALUE", SqlDbType.Int, 4, "ProductID"))
Cmdinsertproduct.parameters ("@RETURN_VALUE"). Direction = ParameterDirection.ReturnValue

CMDINSERTPRODUCT.PARAMETERS.ADD (New SqlParameter ("@ProductName", _
SqlDbType.NVarChar, "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, "QuantityPerUnit")
CMDINSERTPRODUCT.PARAMETERS.ADD (New SqlParameter ("@UnitPrice", _
Sqldbtype.money, 8, "UnitPrice")
CMDINSERTPRODUCT.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")

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.tables ("Products"))

MsgBox (Drnewproduct.item ("ProductID"))

This code is similar to the code shown above, except that the line of code that configures the parameter for the return value is different. Note that it is the first parameter and is set to put the return value back into the ProductID field.

The code used to insert new rows into the dataset is standard ado.net code, so we don't repeat them. It creates a new, appropriate structure for the product record (using the NewRow method of the product DataTable), puts the data in a row, and finally adds rows to the rows collection of the product DataTable.

Run the program now to test. Click the Fill button, but do not make any changes to the data in the grid. Then press the Insert Product button. A new product record for Billy ' s Sesame oil will be inserted, and a message box will notify you of the ProductID returned for it. You can also open the Products table in the grid, scroll to the bottom, and see that new rows have been added.

Writing parameter codes using Server Explorer (Server Explorer)
The code above is tedious and tedious to write. However, the DataAdapter Configuration Wizard (Data Adapter Configuration Wizard) prompts you to write this code for us using Visual Studio. The DataAdapter Configuration Wizard (Data Adapter Configuration Wizard) generates code for the four stored procedures (Select, Update, Insert, and Delete) required for a full configuration. Let's say you need only one stored procedure code, just like the example above, you can truncate it. To get the prepared code that communicates with only one stored procedure, simply expand Server Explorer to show the stored procedures that you need to access, and then drag the stored procedure to the design interface. You will see the DataAdapter and Command objects created for the stored procedure, and the designer portion of the code contains all the code needed to configure the parameters for the stored procedure. You can use the code as is, or you can copy and adjust it as needed.

Summary
The examples in this article are still demo software, but at least enough to show you how to access stored procedures so that you can start writing your own real software. Of course, you need to know the stored procedures you want to access, and you may need to consult your database administrator (DBA) or other team member to get that information.

For complex systems, stored procedures have many advantages. I hope you learned enough in this article that you don't have to worry about how to start using them. The first time you try to write code, you may want to use the DataAdapter Wizard (DataAdapter Wizard) or Server Explorer. But if you can write your own access code if necessary, you can use stored procedures more efficiently.