Using stored procedures in Visual Basic. NET (1)

visual| stored procedures using stored procedures in Visual Basic. NET
Billy Hollis.
September 14, 2002

Download the StoredProcVB.NET.exe sample file (English) from MSDN Code Center. (Http://msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/026/002/872/msdncompositedoc.xml)

Summary: Billy Hollis explains the benefits of using stored procedures in complex systems that extend beyond the scope of the demo software and provide practical examples of how to access stored procedures and start using them in your own applications.

Our authors usually divide the software into two categories-real software and demo software. Real software is software that is used in real-world environments. Demo software is used to illustrate programming concepts.

Most of the code you see in articles and books is demo software. It must be simpler than real software, or the reader will be bothered by details that are irrelevant to the concept of interpretation. But sometimes the demo software is too extreme. The pursuit of simplicity ignores the details that developers need to write real software.

I've had this problem recently in terms of data access. In fact, every data access example I've seen uses SQL statements to read from or write to a relational database (for example, Microsoft SQL Server™). However, in a real-world environment, this is not a desirable programming method except for a limited number of small systems. A reasonably structured n-tier application uses stored procedures instead of SQL statements for data access.

A stored procedure is conceptually similar to a function in a program. They get the input parameters, run in black box mode, and return the appropriate information. Unlike functions, stored procedures are performed by the database engine, not in a program. That is, entering information into a stored procedure, or outputting information from it, must be done through the technology that interacts with the database. In Microsoft Visual basic®6.0, this technique is the traditional ADO. In Visual Basic. NET, we can use Ado.net to accomplish this task.

For many programming tasks, Visual Basic. NET makes it much easier to access data through stored procedures than to use Visual Basic 6.0. There are some wizards to help this process, and once you learn how to avoid some errors, it's not complicated to write those logic from scratch even with ado.net.

This article describes some of the basics of using stored procedures in Ado.net, starting with read-only operations, and how to use stored procedures for data insertions, deletions, and updates.

You do not need to be proficient in the writing of stored procedures to benefit from this article. Many developers of large programming teams need to use stored procedures written by others. One of our examples needs to insert a stored procedure into the sample database, but we'll step through the task.

Ado.net Introduction
This article assumes you already know the basics of ado.net. If you have never used DataAdapter, DataSet, and Command objects in Ado.net in your work, you should read some articles about ado.net, including Rocky for this column named Ado.net and you.

In short, the dataset acts as a data container in ado.net and is used when disconnected from the database. The DataSet contains one or more DataTable, and each DataTable contains a rowset. For those who are familiar with the traditional ADO environment, a DataTable can be considered a disconnected Recordset.

DataAdapter works when connecting to a database. The role of a single DataAdapter is to populate a DataTable with data from the database, or to rewrite the DataTable back to the database, or both.

DataAdapter requires Command objects to perform various database operations. The Command object holds the SQL statement or the name of the stored procedure that specifies the data access implementation method. Each DataAdapter has four properties, specifying 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 classes used to access stored procedures and the relationships between them

So far, the demo software sample you've seen might have configured its Command object to use SQL statements for data access. In fact, some examples may have completely skipped the creation of a command object because a constructor of DataAdapter allows the command object to select data that is created in the background. Before using the stored procedure, let's run an example to compare.

All the examples in this article use the Northwind sample database that is included with SQL Server. We also use the Ado.net class created specifically for SQL Server, rather than the normal OLE DB class. For ease of access to these SQL Server classes, all examples need to add the following line of code at the top of the application's code:

Imports System.Data.SQLClient

Now let's look at the first example of performing data access without using a stored procedure. In this example, we will retrieve all the products in the Northwind Database product table. Create a new Windows application, place a button and a DataGrid on the blank Form1 that appears. Set the Anchor property of the DataGrid to all four sides so that it expands with the form's expansion. In the Click event of the button, 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.tables ("Products")

Depending on the configuration of your computer, you may need to change the connection string. After you establish a database connection, the rest of the code should work correctly. This demo software illustrates the easiest way to fill in and use a DataSet.

Note that the code does not create a Connection object or a Command object. In fact, without these objects, ado.net cannot work, but they are created and used in the background. Instantiate the SqlDataAdapter line of code into the SQL string (used to configure the background Command object) and the connection string (used to configure the background Connection object).

We can change this code to use an explicit Connection and Command object to stay away from the demo software slightly. Place a button on the form and place the following code in the Click event:

Dim sConnectionString as String = _
"Server=localhost;uid=sa;pwd=;d Atabase=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")

This code illustrates the common nature of dataadapters by explicitly creating Connection and Command objects and attaching them to DataAdapter. The SelectCommand that is passed in Cmdproducts,dataadapter when the DataAdapter is instantiated is automatically set. You can then use DataAdapter to access the database immediately.

The results of this code are the same as those in the previous example. Although it is a bit close to real software, because data access is implemented through SQL statements, it still belongs to the demo software.

Using simple stored procedures to get data
How do I change this demo software to use a stored procedure? Just 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=;d Atabase=northwind"
Dim Cnnorthwind as New SqlConnection (sConnectionString)
Dim Cmdproducts as New _
SqlCommand ("10 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 the Command object is instantiated, the code does not use an SQL statement and replaces it with the name of the stored procedure to use. In addition, the Command object's CommandType property must be set to StoredProcedure.

The code that followed is very similar to the previous example, but it returns different data. The stored procedure finds 10 of the most expensive products and returns only the name and price of each product.

Stored procedures with input parameters
This example is simple because the stored procedure does not require any input parameters. In other words, finding 10 of the most expensive products does not require any external information. Stored procedures can do this without outside help. However, most stored procedures require input parameters to perform their functions. In the next example, let's look at how to pass input parameters to a stored procedure. We will use CustomerID to obtain all orders from the relevant customer and use a stored procedure named CustOrderHist (already in the Northwind database).

Create a second button on the used form and place the following line of code after the button's Click event:

Dim sConnectionString as String = _
"Server=localhost;uid=sa;pwd=;d Atabase=northwind"
Dim Cnnorthwind as New SqlConnection (sConnectionString)
Dim cmdorders as New SqlCommand ("CustOrderHist", Cnnorthwind)
Cmdorders.commandtype = CommandType.StoredProcedure
' Set parameters for stored procedures
Dim prmCustomerID as New SqlParameter ()
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 ()
Dagetorders.fill (dsorders, "Orders")
DataGrid1.DataSource = Dsorders.tables ("Orders")

This code is very similar to the code in the previous example, except that after the command object is created, it is configured with a Parameter object and added to the Parameters collection of the command. In this example (closer to the demo software) the customer ID is hard-coded, and the Value property of the parameter is usually set to some user input data. However, other properties of the parameter can be set exactly as in this example.

All parameter settings in this example are set explicitly. Some developers like this style because it's easy to explain. However, some developers prefer to use the equivalent method with fewer lines of code:

Dim sConnectionString as String = _
"Server=localhost;uid=sa;pwd=;d Atabase=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 ()
Dagetorders.fill (dsorders, "Orders")
DataGrid1.DataSource = Dsorders.tables ("Orders")

This code works exactly the same as the previous example. However, each parameter requires only two lines of code, not six lines. If the stored procedure contains a large number of parameters (as shown in some of the following examples), the number of lines required will be significantly different, so we will use this form later in this section.

Updating a database using stored procedures
The above example uses stored procedures to extract information from the database. It is also common to use stored procedures to update, insert, and delete records in complex applications. Let's see how to do this with ado.net.

In the first example, we will use the wizards in Visual Studio®.net to write a collection of stored procedures and create code that uses these procedures. Although we only need to write the smallest amount of code in this example, examining the code created by the wizard helps us understand the process of interacting with stored procedures in addition to fetching 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 updating, inserting, or deleting customers, but DataAdapter Configuration in Visual Studio. NET The Wizard (Data Adapter Configuration Wizard) makes it easy to write some stored procedures for us.

Start a new Windows application (Windows application) project. On the blank Form1, place a DataGrid and two buttons. As before, change the anchor property of the DataGrid so that it is anchored to all four sides. Name the button Btnfill and btnupdate, and change its Text property to Fill and Update, respectively.

Go to the Data tab of the Toolbox (Toolbox), drag the SqlDataAdapter control onto the form, and then release the mouse. This will start the DataAdapter Configuration Wizard (Data Adapter Configuration Wizard). Click the Next button to begin entering information into the wizard.

First, you need to select a connection to the Northwind database, or if the required connection is not displayed in the list, click the new Connection button to create a connection. Then click the Next button.

There are three methods of data access on the next screen. The appearance is similar to Figure 2.



Figure 2: Choosing the type of data access 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 have the wizard generate some stored procedures for us. Select the Create new stored Procedures option, and then click the Next button.

The next screen will request a SQL statement that indicates the data that was originally extracted from the database. However, this SQL statement is not used directly. The information in the SQL statement is used to construct the stored procedure to perform the actual data access. For the sake of simplicity, enter the SQL statement SELECT * FROM Customers and press the Next button.

At this point, the wizard requests the name of the stored procedure that you want to create. There are four types of operations-Select, Update, Insert, and Delete operations. Name it in the following ways:

Select:msdnselectcustomers
Update:msdnupdatecustomer
Insert:msdninsertcustomer
Delete:msdndeletecustomer
Select Yes, create them in the database for me (yes, creating them in the databases.) Options At this point, the wizard screen should look like Figure 3.



Figure 3: Naming the stored procedures to be created by the DataAdapter Wizard

Click the next (Next) button. The wizard creates the stored procedure and indicates its progress on the status bar screen. When you are finished, click the Finish button to exit the wizard.

The wizard created a fully configured DataAdapter, but did not create a dataset to hold the data. That's what we're going to do next. In the Data tab of the Toolbox (tool box), drag the DataSet control. When the configuration screen appears, select the untyped dataset (a dataset of no type).

Now we are ready to populate the dataset with DataAdapter. In the Btnfill Click event, put the following two lines of code:

SqlDataAdapter1.Fill (DataSet1, "Customers")
DataGrid1.DataSource = Dataset1.tables ("Customers")

In the Btnupdate Click event, put the following line of code:

Sqldataadapter1.update (DataSet1, "Customers")

Now we have an effective demo software that uses stored procedures for data access. You can run the program and click the Fill button to get a list of customers in the grid. You can then edit the customer record in the pane and select the Update button to put the changes back in the database.

Note: An exception will occur when editing the first column (that is, CustomerID), because the primary key in the database record cannot be updated in SQL Server.
It is helpful to view the code generated by the wizard, all of which are initially hidden in the Windows Form Designer generated code (Windows Forms Designer generated) zone. Click the plus sign of the range to expand the code. Note the following code, which instantiates the required 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 ()

The code thereafter configures each command object and creates a parameter collection for it. This code is similar to the previous example, and they all use stored procedures with parameters. However, the code generated by the wizard uses some of the additional properties of the parameter to work in conjunction with the stored procedures that change the data. For example, the code used to create the SQLInsertCommand1 CompanyName parameter:

Me.SqlInsertCommand1.Parameters.Add (New _
System.Data.SqlClient.SqlParameter ("@CompanyName", _
System.Data.SqlDbType.NVarChar, "CompanyName")

In the previous example, we only set properties for parameter names, data types, and lengths. This code also sets the SourceColumn property of the parameter to the value CompanyName. This property indicates the field corresponding to this parameter in the Customers DataTable of the DataSet. This causes the values in the DataTable to be automatically inserted into the Value property of the parameter during the insert operation. Let's give you a detailed introduction.

When you call the SqlDataAdapter Update method, the method updates a single DataTable in the DataSet. When the DataTable is checked line by row, the rows that need to be updated, inserted, or deleted are found. When a row that needs to be inserted into the database is found, SqlDataAdapter uses the Command object set by its InsertCommand property. In this case, the Command object accesses the Msdninsertcustomer stored procedure.

Before the stored procedure runs, the Value property of each parameter must be imported from the inserted row. The code that configures SQLDataAdapter1 associates each parameter of the stored procedure with the corresponding field in the DataTable. In this way, the data in the new DataTable row is automatically transferred to the parameters of the stored procedure.

Other stored procedure parameters are configured in a similar way. But there is one difference that deserves attention. Other stored procedures pass in the original values of the data in the DataTable, which are used to check that the data has changed without your knowledge. That is, if you extract some data and others change it before you try to update it, you will receive a concurrency exception. This occurs when you start the above program, extract the customer, and then use a tool, such as SQL Enterprise Manager, to change the contents of the record. If you change the same record in the sample program and try to update it, you receive a concurrency exception.