Create Your Own Code Library

ADO.NET database developers frequently use T-SQL strings to retrieve data for their applications. When ADO.NET developers have a poor understanding of a database's design, the T-SQL language, or both, the process of creating T-SQL strings for their applications can lead to inefficient data-access designs or even result sets with improperly computed values. In such situations, reusing T-SQL code in database objects such as stored procedures and user-defined functions (UDFs) offers distinct advantages. Reusing code in database objects can help simplify data-access tasks and secure a database's schema. A senior developer or DBA can prepare a T-SQL code library that intermediate- and junior-level developers can reuse for many common data-access tasks. And the developer or DBA can require client applications to connect through security accounts that have permission to invoke the library's stored procedures and UDFs—but not necessarily the underlying objects.

Let's learn the techniques for processing stored procedures and UDFs with ADO.NET and Visual Basic .NET. We'll walk through two sample applications the Categories and Products tables from the sample Northwind database. You can join these two tables by their CategoryID column values. You can adapt this article's code samples to apply to any pair of tables that join by one or more pairs of column values. The T-SQL code samples are also helpful in another sense: They demonstrate the techniques you can use to improve the reusability of T-SQL code. These techniques include the use of input parameters, output parameters, and return status values with stored procedures as well as the use of scalar and table-valued returned values from UDFs. By learning and applying these techniques in your applications, you can dramatically increase the reusability of the T-SQL code in your stored procedures and UDFs. (You can, though, apply the Visual Basic .NET techniques we use in this article to any .NET language that implements ADO.NET programming support, such as C#.)

Loading the Library

The T-SQL code library for this article's example consists of a stored procedure and three UDFs, all of which reside in the SSMProcFunc database. At the top of the page via the "Download the Code" icon, you can download the SqlMagProcFuncSqlScript.zip file, which contains the complete T-SQL code for creating the SSMProcFunc database and its objects. The code also creates a login that has access to the SSMProcFunc database and grants permission for the login's user account to invoke database objects containing T-SQL code.

Let's take a closer look at the code you'll use to create the database objects that the application will use. The stored procedure and three UDFs represent complementary techniques for achieving the same result, so you'll typically use one or the other, but not both. As you apply the techniques, you'll discover cases in which one approach or the other is more optimal for a particular application context.

The stored procedure. lt set, an output parameter, and a return status value. The stored procedure accepts an input parameter, @categoryID, and works well for calling applications that need one or more scalar values, such as aggregate values, along with a result set.

Listing 1 includes two comma-delimited parameter declarations. The @CategoryID input parameter can accept an int value from a calling application, such as an ADO.NET application. The upDiscontinuedProducts stored procedure returns the current number of discontinued products through the @DiscontinuedCount output parameter.

The SELECT statement returns three columns (CategoryID, CategoryName, and ProductName) from the Northwind Categories and Products tables. These columns belong to a result set that an ADO.NET application can access by invoking the stored procedure. The WHERE clause specifies three criteria that perform an inner join between the Categories and Products tables.

The SET and RETURN statements conclude the upDiscontinuedProducts stored procedure. The SET statement assigns the @@ROWCOUNT value to the @DiscontinuedCount output parameter. The variable @@ROWCOUNT indicates the number of rows the last statement affected, which in this stored procedure is the number of discontinued products in a category. The RETURN statement assigns to the stored procedure's return status the total number of rows in the Products table in a category.

Three UDFs. A UDF can return either a scalar value or a table value. A scalar return value corresponds generally to a stored procedure's output parameter. But a UDF requires the declaration of its data type in its RETURNS clause. The UDF uses a RETURN clause to pass back a value to its calling application. You can optionally pass one or more parameters to a UDF.

Because one UDF can return just one value, you have to use three UDFs to perform the role of the upDiscontinuedProducts stored procedure. In the context of this application, the stored procedure is more robust, but its code is more complex than the code of any of the three UDFs. In addition, you can reference the return from a table-valued UDF in a FROM clause. T-SQL doesn't support referencing a stored procedure's result set in a FROM clause.

Listing 2 shows the first UDF, ufProductsInCategory, which returns the total number of rows from the Products table matching the @CategoryID parameter value that a calling application passes to the UDF. Listing 2's code starts by removing any previous version of the UDF. Because both stored procedures and UDFs can apply identical code for removing a previously existing object, a good practice is to use distinct names for stored procedures and UDFs.

The code for the second UDF, ufDiscontinuedProductsInCategory, is nearly identical to the code in Listing 2, but this UDF returns a count for just the discontinued products in a category. To create the ufDiscontinuedProductsInCategory UDF, simply change the name references in the DROP and CREATE statements and replace the SELECT statement in Listing 2 with the following code:

SELECT COUNT(*)FROM Northwind.dbo.Products AS np  WHERE np.CategoryID = @CategoryID  AND Discontinued = 1

Listing 3 shows the third UDF, ufDiscontinuedProductRowsInCategory, which is a table-valued UDF. This type of UDF returns a set of rows that you can reference through the FROM clause of a SELECT statement. Notice that the UDF's RETURNS clause specifies a table data type. The RETURN clause includes a SELECT statement that populates the table variable. This SELECT statement is the same one in the upDiscontinuedProducts stored procedure.

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.