Using Stored Procedures in Conjuction with DataAdapter

Introduction

A well-designed application that uses a relational database management system in the backend should make extensive use of stored procedures.  A stored procedure is a named collection of SQL statements that are stored in the database. To the client a stored procedure acts similar to a function. The stored procedure is called by name, can accept parameter values passed in, and can return parameter values back to the client. There are many advantages to incorporating stored procedures into your application logic including:

• Shared application logic among various client applications
• Faster execution
• Reduced network traffic
• Improved database security

The purpose of this article is to demonstrate how stored procedures can be used in conjunction with the SqlDataAdapter in order to fill and update data contained in a DataSet.

Note: In order to complete the activities outlined in this article you must have Visual Studio .NET installed and access to SQL Server 2000 with the Pubs database installed.

Creating a Stored Procedure

Creating a stored procedure is a straightforward process and can be completed inside the Visual Studio IDE. Open Visual Studio, navigate to the Pubs database node in the Server Explorer window, and expand the node. You should see a stored procedure node (see Figure 1). By right clicking on the stored procedure node, a popup menu will give you the option to create a new stored procedure. When you choose to create a new stored procedure, the following code template is presented in the Code Editor window.

CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN

Figure 1.

The create procedure statement is used to create a new stored procedure and is followed by the procedure name. After declaring the procedure name, the parameters used (if any) by the stored procedure are declared. The AS key word follows the parameter declarations and is followed by the SQL code that makes up the body of the stored procedure. The RETURN keyword is used to exit from the stored procedure and can be used to send an integer status value back to the caller. The following code creates a simple stored procedure that takes no parameters and returns a result set back to the caller.

CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN

Once you have entered the code into the Code Editor window, save the stored procedure. After saving the stored procedure, it should show up under the Stored Procedure node in the Server Explorer window. Notice that the CREATE key word has been changed to the ALTER key word in the code editor window. The ALTER key word is used to make any changes to existing stored procedures. To test the stored procedure right click the procedures node in the Server Explorer window and choose Run Stored Procedure. The output from the stored procedure is written to the Output window. It should contain a list of the publisher information and a return value of zero as shown in figure 2.

Figure 2.

Creating a Stored Procedure with Parameters

Now that you know how to create a basic stored procedure, let us look at creating a more advanced stored procedure that includes parameters. Navigate to and expand the Pubs database node in the Server Explorer window of Visual Studio. Right click on the stored procedure node and select Create a New Stored Procedure from the popup menu. Change the name of the stored procedure to up_UpdatePubInfo and add the following code to the body of the stored procedure.

CREATE PROCEDURE dbo.up_UpdatePubInfo
(
@pub_id char (4),
@pub_name varchar (40),
@city varchar (20),
@state char (2),
@country varchar (30)
)
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state, country = @country WHERE (pub_id = @pub_id)
RETURN

The difference between this stored procedure and the previous one is the use of the parameter values. The parameters of the stored procedure are declared as local variables by preceding the name with an @ sign. In addition to the name, you define the data type and the direction of the parameter. The caller of the stored procedure passes in the necessary Input parameters and is the default type. Although not used in this stored procedure, you can use output parameters to return singleton values (such as a count or sum) back to the caller. The OUTPUT keyword designates the parameter as an output parameter. This stored procedure uses input parameters to update the publisher information in the database. Once you have entered the code into the Code Editor window, save the stored procedure.

Note: Stored procedures can also use a Return parameter and an InputOutput parameter type. For more information on these types, consult SqlServer Books Online.

To test the stored procedure right click the procedures node in the Server Explorer window and choose Run Stored Procedure. A parameter input window displays as shown in Figure 3. Enter some test data making sure you use a pub_id that corresponds to an existing publisher and click OK to run the stored procedure. After executing the stored procedure, right click publisher table node in the Server Explorer window and choose Retrieve Data from Table. Verify that the changes updated to the database.

Using the SQLDataAdapter Object in Conjunction with a Stored Procedure to fill a DataSet

In order to fill a DataSet with the publisher information a SqlDataAdapter is used as a broker between the SQL Server and the DataSet. The fill method of the SqlDataAdapter retrieves the data from the database and populates the DataSet. Before the Fill method of the SqlDataAdapter can be executed its SelectCommand property must be set to a valid SqlCommand object. This SelectCommand is responsible for executing the T-SQL statement that returns the result set, which in turn fills the DataSet object. 

In order to demonstrate the process of executing a stored procedure to fill a DataSet, create a new Windows Application project in Visual Studio. Add a class to the project and rename it Publishers. Add an Imports statement above the class definition to import the SqlClient namespace. Declare a private class level instance of the SqlConnection class, SqlDataAdapter class, the SqlCommand class and the DataSet class.

Imports System.Data.SqlClient
Public Class Publishers
Private cnPubs As SqlConnection
Private daPubs As SqlDataAdapter
Private cmdSelPubInfo As SqlCommand
Private dsPubs As DataSet
End Class

Create a class constructor (Sub New). In the body of the constructor instantiate the connection object passing in the connection string information.  Instantiate the select command and set the Connection, CommandType, and CommandText properties. Instantiate the DataAdapter and set its SelectComand property to the cmdSelPubInfo object. The final step is to instantiate the DataSet object, which will hold the result set returned from the database.

Public Sub New()
'Connection
cnPubs = New SqlConnection _("server=localhost;integrated security=true;database=pubs")
'select command
cmdSelPubInfo = New SqlCommand
cmdSelPubInfo.Connection = cnPubs
cmdSelPubInfo.CommandType = CommandType.StoredProcedure
cmdSelPubInfo.CommandText = "up_GetPubInfo"
'DataApapter
daPubs = New SqlDataAdapter
daPubs.SelectCommand = cmdSelPubInfo
'Dataset
dsPubs = New DataSet
End Sub

Note: This assumes you have a local instance of SQL Server and are logged on with a trusted connection.

Create a function procedure called GetPubInfo in the class that takes no input parameters and returns a DataSet to the caller. In the body of the function, use the Fill method of the DataAdapter to fill the DataSet. After filling the DataSet, return it to the caller.

Public Function GetPubInfo() As DataSet
daPubs.Fill(dsPubs)
Return dsPubs
End Function

Note: Error handling has been omitted for clarity.

It is interesting to note that the fill method of the DataAdapter will implicitly open the connection and close it after retrieving the data. If you explicitly open the connection, however, the DataAdapter will not close it.

To test the method, place a DataGrid on Form1. Switch to the Code Editor Window and declare a private class level instance of the Publishers class and the DataSet class after the Inherits statement.

Public Class Form1
Inherits System.Windows.Forms.Form
Private objPublishers As Publishers
Private dsPubInfo As DataSet

In the Forms constructor code instantiate a new instance of the Publishers class. Set dsPubInfo to the DataSet returned by calling the GetPubInfo method of the Publishers class. Set the DataSource property of the DataGrid to the first (and only) table in the dsPubInfo tables collection.  

Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
objPublishers = New Publishers
dsPubInfo = objPublishers.GetPubInfo
Me.DataGrid1.DataSource = dsPubInfo.Tables(0)
End Sub

Run the application in the debugger and verify that the results are displayed in the grid.

Updating Data Using the SQLDataAdapter in Conjunction with a Stored Procedure

In addition to the SelectCommand property, the SQLDataAdapter object includes the UpdateCommand, InsertCommand, and the DeleteCommand properties. The Update method of the SQLDataAdapter will call the appropriate SQLCommand object for each updated, inserted, or deleted DataRow in the DataTable passed with the update call. When updating data, input parameters pass the values of the updated fields to the stored procedure.

The SQLParameter class encapsulates properties and methods for working with parameters. The SQLParameter class includes properties such as the ParameterName, SqlDBType, Direction, Size, Value, SourceColumn, and SourceVersion. The ParameterName, SqlDBType, Direction and Size properties are set to match the parameter definition in the stored procedure. For example, the following code creates a parameter object that matches the @pub_id parameter in the up_UpdatePubInfo stored procedure created previously.

Dim UpdParam As New SqlParameter("@pub_id", SqlDbType.Char, 4)

In this case, an overloaded constructor of the SqlParameter class sets the appropriate properties. Although the Direction property has not been explicitly set, Input is the default. If the parameter direction is InputOutput, Output, or a ReturnValue, the direction must be explicitly set. For example, the following code is used to explicitly set the Direction property of a SqlParameter object.

oParam.Direction = ParameterDirection.Output

The SourceColumn property maps a DataColumn from the DataTable passed in when you call the Update method of the SqlDataAdapter object. This mapping allows the implicit loading of the SqlParameters Value property from the DataTable during updates. If you do not set the SourceColumn, the Value property of the SqlParameter object must be explicitly set in code.

The SourceVersion is a useful property that defaults to a value of Current. This value is the current value of the field in the DataRow and is the value that you intend to update to the database. The SourceVersion of a SqlParameter object can also be set to a value of Original. This is the value of the field when the DataTable was originally loaded from the database. Passing both of these values to a stored procedure allows for concurrency checking before the data update takes place. The following stored procedure uses the original value to check if another user has changed the data before updating.

CREATE PROCEDURE dbo.up_updPubName
( @pub_id char(4),
@pub_name varchar(40),
@Original_pub_name varchar(40)
)
AS
if exists(select pub_id from publishers
where (pub_id = @pub_id) AND
(pub_name = @Original_pub_name))
Begin
UPDATE publishers SET pub_name = @pub_name
WHERE (pub_id = @pub_id)
End

In order to execute this stored procedure, you define an @Original_pub_name SqlParameter object that uses the Original value of the field as its SourceVersion.

Dim UpdPubParam As New SqlParameter("@Original_pub_name", _SqlDbType.VarChar, 40)
UpdAuthParam.SourceColumn = "pub_name"
UpdAuthParam.SourceVersion = DataRowVersion.Original

Note: Concurrency issues in disconnected data scenarios are an important topic. Although only briefly mentioned in this article, a future article will explore these issues.

After defining a SqlParameter object for each parameter identified in the stored procedure, the SqlParameter objects are added to the Parameters collection of the SqlCommand object responsible for executing the stored procedure. The following code demonstrates adding a SqlParameter object to a Parameter collection.

cmdUpdPubInfo.Parameters.Add(UpdParam)

As an alternative, the overloaded Add method allows the creation of the SqlParameter object and its addition to the collection in one-step.

cmdUpdPubInfo.Parameters.Add("@pub_id", SqlDbType.Char, 4,"pub_id")

In order to demonstrate the process of using a SqlDataAdapter object in conjunction with a stored procedure to update data, open the Windows Application project you created earlier. Using the Server Explorer window create the up_UpdPubName stored procedure in the Pubs database. Open the Publishers class in the Code Editor and add the following class level declaration to create a SqlCommand object.

Private cmdUpdPubInfo As SqlCommand

In the body of the class constructor, add code to instantiate the update command and set the Connection, CommandType, and CommandText properties.

'Update command
cmdUpdPubInfo = New SqlCommand()
cmdUpdPubInfo.Connection = cnPubs
cmdUpdPubInfo.CommandType = CommandType.StoredProcedure
cmdUpdPubInfo.CommandText = "up_UpdPubName"

Next, define and add the necessary update parameters to the parameters collection.

'Update command parameters
cmdUpdPubInfo.Parameters.Add("@pub_id", SqlDbType.Char, 4, "pub_id")cmdUpdPubInfo.Parameters.Add("@pub_name", SqlDbType.VarChar, 40, pub_name")Dim UpdPubParam As New SqlParameter _("@Original_pub_name", SqlDbType.VarChar, 40, "pub_name")
UpdPubParam.SourceVersion = DataRowVersion.Original
cmdUpdPubInfo.Parameters.Add(UpdPubParam)

Add code after the DataAdapter instantiation to set the UpdateCommand property of the DataAdapter to the cmdUpdPubInfo object.

daPubs.UpdateCommand = cmdUpdPubInfo

Create a sub procedure called UpdatePubInfo that takes a DataSet object as an input parameter. In the body of the function, call the update method of the DataAdapter object passing in the DataSet.

Public Sub UpdatePubInfo(ByVal dsChanges As DataSet)
daPubs.Update(dsChanges)
End Sub

Add a button to Form1 and add code to the buttons update event that calls the UpdatePubInfo method of the objPublisher. First, check if the DataSet has changes by calling the HasChanges method. If there are changes, filter the dsPublisher and pass only the changed rows by calling the GetChanges method of the DataSet.

If dsPubInfo.HasChanges Then
objPublishers.UpdatePubInfo(dsPubInfo.GetChanges)
dsPubInfo.Clear()
dsPubInfo = objPublishers.GetPubInfo
End If

Run the application in debug mode. Make changes to the pub_name and click the update button. Stop the application. Right click publisher table node in the Server Explorer window and choose Retrieve Data from Table. Verify that the changes updated to the database.

Now that you are familiar with using a stored procedure to implement updating data through the SqlDataApapter, You can implement data inserts and deletes in a similar fashion. This will be an exercise left for the reader. The solution code accompanying this article will contain sample code demonstrating inserts and updates using the SqlDataAdapter.

Summary

This article demonstrated using the SqlDataAdapter class and its use of parameters when updating data back to the database. You also saw how to use the SqlDataAdapter in conjunction with stored procedures to propagate data changes in a disconnected DataSet back to the database. Encapsulating data update logic in stored procedures is an excellent way to improve the manageability, scalability, and security of your database driven applications. One area this article does not address is the issue of data concurrency. Dealing with data concurrency is vital in a disconnected scenario. Although only hinted at in this article, look for a future article that will discuss concurrency issues in detail.

Note: The code presented in this article is for demonstration purposes only. Proper error handling has been omitted for clarity.