Capturing Values from Stored Procedures
You can retrieve values from a stored procedure in three ways: through output parameters, return status values, and results sets. In SqlMagProcFuncProject.zip, which you can download at InstantDoc ID 45478, you'll find the sample SSMProcFunc Visual Studio .NET project we'll use for this article's examples. The .zip file includes the code behind Form1, which Figure 1 shows.
In Form1, Button 1 combines with a text box, a label, and a list box to display the values that the upDiscontinuedProducts stored procedure returns. Figure 1 shows the values the stored procedure returns for an @CategoryID parameter value of 6. The user enters the @CategoryID value in the text box and clicks Button1. Button1's Click event procedure populates the label above the list box and adds to the list box the name of each discontinued product in the designated category.
Form1's code demonstrates how to retrieve all types of values from a stored procedure in Visual Basic .NET. To capture stored-procedure values, you need to start by creating a connection to the SQL Server database that contains the stored procedure. You then need to retrieve and display the stored procedure's values by creating an instance of the SqlCommand object that represents the stored procedure, configuring the SqlCommand object's parameters, and using one of two methods to retrieve the data set. Let's take a closer look at each of these tasks.
Connecting to the database. Before you capture values from a stored procedure, you must connect to the database that holds the stored procedure. You can create this connection in two ways: with Windows integrated security or with SQL Server security. Form1's Button1_Click procedure shows the code to use for Windows integrated security.
With integrated security, you have to make sure that all users have Windows accounts that map to logins for a SQL Server instance. In turn, those logins must map to user accounts that have permission to invoke database objects your application relies on (e.g., the upDiscontinuedProducts stored procedure). Listing 4 shows the code from the Button1_Click procedure that instantiates a SqlConnection object. Then, the code invokes the Open method to connect with the SSMProcFunc database.
The code in the Button1_Click procedure uses an Imports statement for the System.Data.SqlClient namespace:
Imports System.Data.SqlClient
An Imports statement for a namespace can simplify references to namespace types, such as the SqlConnection and Sql.Command classes in the System.Data.SqlClient namespace.
Instantiating the SqlCommand object. Listing 5 shows an excerpt from the Button1_Click code behind Form1. The code at callout A in Listing 5 creates an instance of the SqlCommand object (cmd1), which points to the upDiscontinuedProducts stored procedure by referencing the procedure's name in the SqlCommand object's constructor with the NEW keyword. You must also assign a value to a SqlCommand object's CommandType property that indicates you're using a stored procedure. In addition to the stored procedure's name, the SqlCommand object's constructor references cnn1, the connection the SqlCommand object uses to the SSMProcFunc database.
Configuring the SqlCommand object's parameters. Configuring the SqlCommand object's parameters (i.e., the parameters for the upDiscontinuedProducts stored procedure) varies slightly, depending on the type of input or output scalar value you're referencing. For an input parameter such as @CategoryID, you can invoke the Add method of the SqlCommand object's Parameters collection. You need to specify the parameter's name and data type. You should also assign a value for an input parameter, such as an Integer value based on TextBox1's Text property. Callout B in Listing 5 highlights the code that references the stored procedure's @CategoryID parameter.
As callout C in Listing 5 shows, the code that references the output parameter @DiscontinuedCount is similar to the code that references an input parameter. In this case, you don't need to assign a parameter value, but you must specify an output direction.
You also need a SqlCommand object parameter to reference a stored procedure's return status value, as the code at callout D shows. For this task, the name you use to reference a parameter doesn't matter. For example, foo would do just as well as @NumberOfProducts. However, it is imperative that you designate a Return Value argument for the parameter's Direction property.
Retrieving the result set. After you instantiate and configure a SqlCommand object for a stored procedure, you can retrieve the result set. You can use two methods: one based on the SqlDataReader object and the other based on the DataSet object. These two approaches apply to both stored procedures and table-valued UDFs. For this example, we'll use the SqlDataReader object.
As the code at callout E shows, you use the SqlCommand object's ExecuteReader method to create and populate a SqlDataReader object. You can then iterate through the procedure's result set with a Do loop statement that reads in values as long as there are rows to recover from the result set. The statement inside the Do loop adds items to a list box from successive result set rows.
As the code at callout F shows, you must close the SqlDataReader object before attempting to access the output parameter return status values from a stored procedure. Only then can you combine the output parameter and return status values with text to assign the Text property of Label2. In addition, the code makes ListBox1 visible only when there are discontinued products to show. Button1's Click event procedure concludes by closing the connection to the SSMProcFunc database.
Capturing Values from UDFs
Form2 in the SSMProcFunc project taps the three UDFs in the SSMProcFunc database. The code behind the form uses a DataSet object instead of a SqlDataReader object to access a result set of discontinued products in a product category. Figure 2 shows Form2 with the results matching an @CategoryID value of 7. Instead of displaying the discontinued products in a list box, this form uses a DataGrid control, which the DataSet object populates. You can cause Form2 to open when you run the project by choosing Form2 as the Startup object from the project's Property Pages. Capturing the UDFs' result set involves a process that's similar to capturing the stored procedure's result set.
Connecting to the database. Listing 6 shows the code for connecting to the SSMProcFunc database. Users initiate this connection when they click Button1 in Form2. This connection string differs from the one behind Form1 in that it designates a SQL Server login (SSMProcFuncU1) and password (SSMProcFunc). Before running the code behind Form2, make sure that you run the T-SQL code in SSMProcFunc.sql to create the login and grant the login appropriate permissions for the UDFs in the SSMProcFunc database.
Retrieving and displaying UDF values. Listing 7 shows an excerpt from the Button1_Click code behind Form2, which retrieves and displays data from the three UDFs in the SSMProcFunc database code library. The combination of using UDFs as a source for values and populating a DataGrid with a DataSet object helps make the code simpler to write and provides flexibility in how you can write the code. For example, this approach makes it easy to use one SqlCommand object for retrieving multiple return values, regardless of whether the return values are scalar or table-valued.
The code at callout A in Listing 7 instantiates and configures a SqlCommand object that represents the ufProductsInCategory UDF. Recall that this UDF returns a scalar value. The code to configure @CategoryID is similar to the code you used to configure the stored procedure's input parameter. By invoking the ExecuteScalar method, the code copies the value of the UDF to the int1 variable, which a preceding Dim statement declares with an integer data type.
The code at callout B in Listing 7 shows how to reuse the same SqlCommand object to retrieve a value from the ufDiscontinuedProductsInCategory UDF. This task involves two steps. First, you assign a new CommandText property to the SqlCommand, which points at the new UDF. Second, you save in a new variable the value that the ExecuteScalar method returns. The new variable, int2, also has an integer data type.
The code at callout C that populates the Text property of Label2 is generally the same as in Form1. However, Form2 uses int1 and int2 instead of prm1 and prm2.
The code at callout D shows how to reuse the SqlCommand object again; you use the SqlCommand object as an argument for a SqlDataAdapter object's constructor. Then, you invoke the SqlDataAdapter object's Fill method to populate a DataSet (das1). The table in das1—Tables(0)—which contains the result from the UDF, serves as the data source for a DataGrid control.
Why Bother with These Techniques?
ADO.NET developers can gain important security and ease-of-use benefits by using stored procedures and UDFs instead of T-SQL strings. By using this article's techniques for recovering and displaying values from stored procedures and UDFs, you'll be able to create data-access solutions that are not only easier to use but also more secure than those created with T-SQL strings.