Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Using Stored Procedures in Conjunction with the SqlDataAdapter : Page 3

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 you store in a database. A stored procedure is similar to a function. You call the stored procedure by name, you can pass it parameter values, and it can return parameter values back to the calling code.

Update Data 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 value. If the parameter direction is InputOutput, Output, or ReturnValue, you must explicitly set the direction. For example, the following code explicitly sets the Direction property of a SqlParameter object.

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 SqlParameter's Value property from the DataTable during updates. If you do not set the SourceColumn parameter, you must explicitly set the Value property of the SqlParameter object.

The SourceVersion property defaults to a value of Current, which is the current value of the field in the DataRow and is the value that you intend to update in your database. You can also set the SourceVersion of a SqlParameter object to a value of Original, which 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 it performs an update.

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.

Note: Concurrency issues in disconnected data scenarios are an important topic. You should read more about this issue.

Once you've defined a SqlParameter object for each parameter identified in the stored procedure, you can add the SqlParameter objects to the Parameters collection of the SqlCommand object responsible for executing the stored procedure. The following code demonstrates adding a SqlParameter object to the Parameters collection.


As an alternative, the overloaded Add method allows you to create the SqlParameter object and add it to the collection in one step.

The Fill method of the DataAdapter will implicitly open the connection if it is closed and close it after the data is retrieved. If you explicitly open the connection, however, the DataAdapter will not close it.
In order to demonstrate how to use 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

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

daPubs.UpdateCommand = cmdUpdPubInfo

Create a subprocedure 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 and pass in the DataSet.

Add a button to Form1 and add code to the button's Update event that calls the UpdatePubInfo method of 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.

Run the application in Debug mode. Make changes to the pub_name and click the Update button. Stop the application. Right-click on the publisher table node in the Server Explorer window and choose Retrieve Data from Table. Verify that the changes updated 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 manner. This will be an exercise left for the reader. The solution code for this article contains sample code that demonstrates inserts and updates using the SqlDataAdapter.

This article demonstrated how to use the SqlDataAdapter class and its parameters to update a database. You also read 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 data concurrency. Dealing with data concurrency is vital in a disconnected scenario.

CREATE PROCEDURE dbo.up_GetPublisherInfo AS SELECT pub_id, pub_name, city, state, country FROM publishers RETURN 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 oParam.Direction = ParameterDirection.Output 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 Dim UpdPubParam As New SqlParameter("@Original_pub_name", _ SqlDbType.VarChar, 40) UpdAuthParam.SourceColumn = "pub_name" UpdAuthParam.SourceVersion = DataRowVersion.Original cmdUpdPubInfo.Parameters.Add( _ "@pub_id", SqlDbType.Char,4,"pub_id") 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) Public Sub UpdatePubInfo(ByVal dsChanges As DataSet) daPubs.Update(dsChanges) End Sub If dsPubInfo.HasChanges Then objPublishers.UpdatePubInfo(dsPubInfo.GetChanges) dsPubInfo.Clear() dsPubInfo = objPublishers.GetPubInfo End If

Dan Clark is a Microsoft Certified Trainer, Microsoft Certified Solution Developer, and a Microsoft Certified Database Administrator. For the past seven years he has developed applications and trained others how to develop applications using Microsoft technologies. Dan has been developing and training Microsoft's .NET technologies since the early betas. He recently wrote "An Introduction to Object-Oriented Programming with Visual Basic .NET" published by Apress. Reach him at drc_books@yahoo.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date