ncapsulating data update logic in stored procedures is an excellent way to improve the manageability, scalability, and security of your database-driven applications. 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
This article will show you how to use stored procedures in conjunction with the SqlDataAdapter in order to fill and update data contained in a DataSet.
Creating a Stored Procedure
Creating a stored procedure is a fairly straightforward process which you can complete inside the Visual Studio .NET IDE. Open Visual Studio .NET and navigate to the Pubs database node in the Server Explorer window and expand the node. You should see a Stored Procedures node (see Figure 1). Right-click on the Stored Procedures node to launch a popup menu. Select the option to create a new stored procedure.
Fill a DataSet using a Stored Procedure In order to fill a DataSet with the publisher information, you can use a SqlDataAdapter 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 you can execute the Fill method of the SqlDataAdapter, you must execute its SelectCommand property 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 how to execute 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 each of the following classes: SqlConnection, SqlDataAdapter, SqlCommand, and DataSet.
Create a class constructor (Sub New) In the body of the constructor, instantiate the Connection object and pass in the connection string information. Instantiate the SELECT command and set the Connection, CommandType, and CommandText properties. Instantiate the DataAdapter and set its SelectCommand property to the cmdSelPubInfo object. Finally, instantiate the DataSet object, which will hold the result set returned from the database.
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, then return it to the caller.
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. 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.
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.
Run the application in the debugger and verify that the results are displayed in the grid. Update Data with a 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.
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.
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.
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.
In the body of the class constructor, add code to instantiate the update command and set the Connection, CommandType, and CommandText properties.
Next, define and add the necessary update parameters to the Parameters collection.
Add code after the DataAdapter instantiation to set the UpdateCommand property of the DataAdapter to the cmdUpdPubInfo object.
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.
About Our Editorial ProcessAt DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere. See our full editorial policy. About Our JournalistCharlie Frank Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience. View Author Walmart’s fintech startup to disrupt BNPL market Cameron Wiggins April 26, 2024 5:38 PM From humble cart to thriving business: The Nitro Bar’s success story Rashan Dixon April 26, 2024 3:50 PM Adapting business strategies amidst unpredictable economies April Isaacs April 26, 2024 3:11 PM Singaporean entrepreneur’s journey to grocery chain success Cameron Wiggins April 26, 2024 3:06 PM Rhode Island coffee startup finds success April Isaacs April 26, 2024 1:39 PM Erel Margalit’s enduring impact on Israeli tech industry Noah Nguyen April 26, 2024 1:12 PM Bilt Rewards transform US rental sector Cameron Wiggins April 26, 2024 8:58 AM Chinese e-commerce platform upsets US market Noah Nguyen April 26, 2024 8:54 AM Stripe refines business model for fintech competitiveness Johannah Lopez April 26, 2024 7:13 AM Addressing challenges in Israel’s tech sector Rashan Dixon April 25, 2024 5:58 PM Gender disparity challenged by women-centric coworking spaces April Isaacs April 25, 2024 5:56 PM From cart to empire: The Nitro Bar’s rise Cameron Wiggins April 25, 2024 5:23 PM FlexAI nets $30 million to simplify AI computing infrastructure Noah Nguyen April 25, 2024 3:36 PM Google fires 28 protesting employees, maintains professional ethics Johannah Lopez April 25, 2024 1:49 PM Growing interest in small business ownership among millennials Johannah Lopez April 25, 2024 1:31 PM India’s rise in space exploration with private corporations Johannah Lopez April 25, 2024 8:42 AM Walmart partners with fintech One for installment payment service Johannah Lopez April 25, 2024 7:59 AM Biden administration bans noncompete agreements nationwide April Isaacs April 25, 2024 7:01 AM Media mogul poised for $1 billion boost if shares surge Cameron Wiggins April 24, 2024 5:19 PM Bitcoin miners’ strategy stabilizes market pre-halving Noah Nguyen April 24, 2024 5:12 PM Digital banking tackles fraud with advanced methods Johannah Lopez April 24, 2024 1:55 PM Wealth accumulation: Path to personal freedom and societal impact Cameron Wiggins April 24, 2024 1:25 PM Japan’s finance minister warns of currency market intervention Rashan Dixon April 24, 2024 1:14 PM TabaPay set to acquire Synapse’s assets April Isaacs April 24, 2024 11:58 AM Baton Rouge Entrepreneurship Week 2024 announced Cameron Wiggins April 24, 2024 11:36 AM |