Browse DevX
Sign up for e-mail newsletters from DevX


Using Stored Procedures in Conjunction with the SqlDataAdapter : Page 2

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

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 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.

Public Sub New() 'Connection Dim strCon As String strCon = "server=localhost;" strCon += _ "integrated security=true;" strCon += "database=pubs" cnPubs = New SqlConnection(strCon) '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, then 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.

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.

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.

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