ADO.NET 101: SqlCommand

Database applications commonly need to execute dynamic SQL statements and stored procedures. Dynamic SQL statements are useful for executing Data Definition Language (DDL) operations such as creating tables or for data-access operations such as performing ad hoc queries. And stored procedures, which form the backbone of most database applications, typically perform predefined queries and database-update operations. SqlCommand is the ADO.NET object that executes dynamic SQL statements and stored procedures for a SQL Server database. Knowing how to use the SqlCommand object is essential for developing applications with ADO.NET right now. And understanding Microsoft .NET coding and ADO.NET is an important step in getting ready for Yukon, the next release of SQL Server, which will let you use the .NET languages to build database objects. In this article, I provide an introductory guide to using the SqlCommand object, showing you how to use SqlCommand to execute dynamic SQL DDL statements and pass parameters to a stored procedure. I also demonstrate how to use SqlCommand to execute a stored procedure and return values. Note that although you don't absolutely need to have Visual Studio .NET for these examples, using it is much easier than the alternative of creating these programs manually by using a text editor and the .NET Framework Software Development Kit (SDK).

Before you can use the SqlCommand class (the source code to create a SqlCommand object), you need to add an import directive for the System.Data.SqlClient namespace in your project. The Import directive lets you refer to classes in System.Data.SqlClient in "shorthand" so that you don't always have to prefix each class with the name System
.Data.SqlClient. For a Visual Basic .NET (VB.NET) project, add the following line at the top of your source file:

Imports System.Data.SqlClient

After adding the import directive, you're ready to use ADO.NET SqlCommand objects in your projects.

Executing Dynamic SQL Statements

Dynamic SQL provides a highly flexible mechanism for working with a relational database. For example, dynamic SQL lets you execute ad hoc queries and action queries; it also lets you execute SQL DDL statements to create database objects. The example SQLCommandNonQuery subroutine in Listing 1 illustrates how you can use dynamic SQL with the ADO.NET SqlCommand object to check for the existence of a table and conditionally create the table if it doesn't exist.

The first line of the subroutine passes the sServer and sDB variables as parameters. The values of these two variables specify the SQL Server database server and database that the application will use. Next, the subroutine creates a new ADO.NET SqlConnection object named cn to connect your VB .NET application to the SQL Server database, then passes in the connection string as the first parameter of the SqlConnection object's constructor. The connection string uses Integrated Security to connect to the server because Integrated Security is easier and more secure.

After creating the Connection object, the subroutine instantiates the sSQL variable, which will contain the dynamic SQL statements, and an instance of the SqlCommand object named cmd. In this example, the cmd SqlCommand object's constructor uses two parameters. The first parameter is a string containing the SQL statement that the subroutine will execute; the second is the SqlConnection object that will connect to the target database server. Here, the sSQL string is initially empty.

Next, the subroutine sets up a Try-Catch structure to execute the SQL commands. The first action in the Try-Catch block at callout A in Listing 1 uses the cn SqlConnection object's Open method to open a connection to the SQL Server database that you specified in the connection string. Then, the subroutine assigns the sSQL variable an SQL statement that checks for the existence of the Department table. In this SQL statement, a SELECT statement queries the SQL Server sysobjects table to determine whether a user table named Department exists. If the query finds the Department table, the subroutine executes a DROP TABLE statement to remove the table from the target database. Otherwise, it takes no further action.

So that the subroutine can execute the SQL statement, it assigns the value in the sSQL variable to the CommandText property of the cmd SqlCommand object, then uses the cmd object's ExecuteNonQuery method to send the command to the SQL Server system. The ExecuteNonQuery method executes an SQL statement that doesn't return a result set or a specific value; in this case, it creates a table. Table 1 lists all the SQL command execution methods that the SqlCommand object supports for executing SQL commands on the target database.

After the first DROP TABLE SQL command, the section of code at callout B follows the same sequence to execute a CREATE TABLE command. First, the subroutine assigns the sSQL variable a T-SQL CREATE TABLE statement that creates a two-column table named Department. The first column is an integer data type named DepartmentID, which is also the primary key; the second column is a 25-character type named DepartmentName. Next, the subroutine copies the value in the sSQL variable to the cmd object's CommandText property and calls the ExecuteNonQuery method to execute the CREATE TABLE statement. After the successful completion of the ExecuteNonQuery method, the Department table will exist in the database named in the sDB variable. If an error occurs during any of the preceding steps, during the SqlConnection object's Open method, or during either instance of the SqlCommand object's ExecuteNonQuery method, the code in the Catch block executes and a message box shows the text of the exception condition. At the end of the CommandNonQuery subroutine, the application executes the SqlConnection object's Close method to end the connection to the SQL Server database.

In your applications that execute dynamic SQL statements, be certain to validate all user input to protect your system from SQL injection attacks, which can let intruders gain access to your databases. Although this example generates all the SQL code internally, a common application practice is to build SQL execution strings based on user input. If your application does this, you need to test all input strings for valid input data and the presence of any unwanted SQL keywords and meaningful characters such as semicolons.

Executing Parameterized SQL Statements

In addition to executing dynamic SQL statements, you can use the SqlCommand object to execute parameterized SQL statements and stored procedures. The primary difference between dynamic SQL and parameterized SQL is that SQL Server must parse dynamic SQL statements and create an access plan for them before it runs them. SQL Server handles the execution of dynamic SQL statements intelligently. It stores dynamic statements in its procedure cache for a certain period of time, then when an application executes the statement, SQL Server uses the existing access plan. Even so, the statement's availability in cache depends on the database activity, and dynamic SQL carries no guarantee that the plan will be available the next time the statement is executed. However, parameterized SQL stays in the procedure cache until the application's connection closes.

You can think of parameterized SQL statements as sort of a cross between stored procedures and dynamic SQL. Like stored procedures, they can accept different parameter values at runtime. Like dynamic SQL, they're not persistent in the database. However, unlike with dynamic SQL, SQL Server parses parameterized SQL and creates the access plan only once—when it first prepares the statement. Subsequent statement execution takes advantage of the existing access plan.

The example code in Listing 2 shows how to use the SqlCommand object to create and execute a prepared SQL statement. At the top of the SQLCommandPreparedSQL subroutine, the application uses the sServer and sDB variables to pass the target database server name and the database name to the subroutine. Next, the subroutine creates a new SqlConnection object named cn, followed by a new SqlCommand object named cmd. In this example, the SqlCommand object's constructor takes two parameters. The first parameter assigns an SQL statement to the cmd object. This statement can be either an SQL statement or the name of a stored procedure. Here, the SQL statement is an INSERT statement that inserts values into two columns in the Department table. The second parameter supplies the name of the SqlConnection object.

The important point in this example is the format of the parameter markers in the SQL statement. Parameter markers denote the replaceable characters in a prepared SQL statement. At runtime, the subroutine replaces these parameters with the values that the SqlCommand object's Parameters collection supplied. Unlike ADO or the ADO.NET OleDbCommand object, which uses a question mark (?) to indicate replaceable parameters, the SqlCommand object requires that all parameter markers begin with the at (@) symbol. This example shows two parameter markers: @DepartmentID and @DepartmentName. The SqlCommand constructor's second argument associates the cmd SqlCommand object with the cn SqlConnection object you created earlier.

Next, the code at callout A in Listing 2 creates two SqlParameter objects. The first parameter object, parmDepartmentID, supplies values to the first parameter marker (@DepartmentID). Likewise, the second parameter object, parmDepartmentName, supplies the values that the second replaceable parameter (@DepartmentName) uses. The example code in this subroutine passes two arguments to the SqlParameter object's constructor. The first parameter supplies the name of the SqlParameter object. Here, you need to make sure that the name you supply to the SqlParameter object's constructor matches the name that you used in the parameter marker of the prepared SQL statement. The second parameter that the subroutine passes to the SqlParameter constructor specifies the parameter's data type. Next, the subroutine uses the ParameterDirection.Input enumeration to set the SqlParameter object's Direction property to input. Table 2 lists the valid enumerations for the SqlParameter Direction property.

After you create the SqlParameter objects, the next step is to add them to the SqlCommand object's Parameters collection. You use the Add method of the SqlCommand object's Parameters collection to add both the parmDepartmentID and parmDepartmentName SqlParameter objects to the cmd SqlCommand object. The order in which you add the SqlParameter objects isn't important. Next, in the Try-Catch block at callout B in Listing 2, the subroutine uses the cn SqlConnection object's Open method to open a connection to SQL Server, then uses the Prepare statement to prepare the SQL statement. Note that the subroutine executes the Prepare method after all the parameter attributes have been described. Next, a For-Next loop adds 10 rows to the newly created Department table. Within the For-Next loop, the subroutine assigns the Value property of each parameter object a new data value. For simplicity, the code uses a loop and just adds 1 to the starting value. The subroutine assigns the parmDepartmentID parameter the value of the loop counter contained in the variable i and assigns the parmDepartmentName parameter a string containing the literal "New Department" along with the current value of the loop counter.

Finally, the SqlCommand object's ExecuteNonQuery method executes the SQL statement. I used ExecuteNonQuery because this example uses an SQL action query that doesn't return any values. From a SQL Server perspective, running the ExecuteNonQuery method causes the application to issue an sp_execute command to perform the insertion. Running SQL Server Profiler can reveal exactly which SQL commands the application sends to SQL Server. If an error occurs during any operations in the Try block, the code in the Catch block executes and a message box shows the text of the exception condition. The code at the end of the subroutine executes the SqlConnection object's Close method to disconnect from the SQL Server database.

Executing Stored Procedures That Have Return Values

Stored procedures are the core of most database applications—and for good reason. Besides giving performance benefits, stored procedures can also restrict data access to the predefined interfaces that these stored procedures expose. Like parameterized SQL statements, stored procedures have significant performance benefits because SQL Server compiles them before using them. Early compiling lets the database forego the typically required parsing steps and the need to create an access plan. Stored procedures are the true workhorse of most database applications; application developers almost always use them for database INSERT, UPDATE, and DELETE operations and for retrieving single values and result sets.

The following example shows how to use the SqlCommand object to execute a SQL Server stored procedure that accepts one input parameter and returns a scalar value. In Query Analyzer, execute the T-SQL code that Listing 3 shows to create the StockValue stored procedure in the sample Northwind database. The StockValue stored procedure's input parameter is an integer value that identifies the ProductID. This stored procedure returns the stock value of that ProductID from the Products table in the Northwind database. The StockValue procedure calculates the stock value by retrieving the UnitsInStock number and multiplying it by the value in the UnitPrice column. The procedure then assigns the results to the @StockValue variable, which the procedure returns as a scalar value.

After you create the sample stored procedure in the Northwind database, your ADO.NET applications can call it. The example in Listing 4 shows how to use the SqlCommand object to execute the StockValue stored procedure and retrieve the scalar value that it returns. The code at the beginning of this SQLCommandSPScalar subroutine creates the cn SqlConnection object, then a SqlCommand object named cmd. In this example, the SqlCommand object's constructor uses two parameters. The first parameter is a string that accepts the command that the SqlCommand object will execute. This string can contain either an SQL statement or the name of a stored procedure. This example uses the name of the StockValue stored procedure. The second parameter is the name of the SqlConnection object that the subroutine will use to connect to the target database. After creating the cmd SqlCommand object, the next section of code in the subroutine sets the object's CommandType property to CommandType.StoredProcedure, meaning that the SqlCommand object will execute a stored procedure. The CommandType property can accept any of the values that Table 3 shows.

The next section of code, at callout A in Listing 4, creates the SqlParameter object that will hold the scalar value containing the stock amount value that the StockValue stored procedure returns. The code then sets the ParameterDirection property of the return value parameter, @StockValue, to the value of Parameter.ReturnValue. Next, the subroutine creates the SqlParameter object that will supply the input value to the StockValue stored procedure. You can create SqlParameter objects either by using the SqlParameter class constructor or by executing the SqlCommand object's Parameters collection Add method. In this example, I use the Add method of the SqlCommand object's Parameters collection to create the parameter. The first parameter I supplied to the Add method is a string containing the parameter name—in this case, @ProductID. Remember that replaceable parameters that the SQLParameter object uses must begin with @. The second parameter uses the SqlDbType.Int enumeration to denote that the parameter will contain an integer value. The next line sets the Direction property to ParameterDirection.Input to indicate that this is an input parameter.

Finally, the subroutine sets the SqlParameter object's Value property to 1, storing a value of 1 to pass to the StockValue stored procedure. At callout B, the code sets up a Try-Catch block to open the connection to the SQL Server system, then executes the StockValue stored procedure. Note in the Try-Catch block that the cmd SqlCommand object's ExecuteScalar method executes the StockValue stored procedure, and the subroutine assigns the return value to the TextBox1 variable. As in the earlier examples, if the connection or the stored procedure fails, a message box will display the error text to the end user and the connection will close.

Taking Command

The SqlCommand object is one of the pillars of ADO.NET database programming because it lets you execute ad hoc queries, parameterized queries, and stored procedures from your .NET applications. Knowing how to use SqlCommand is a vital skill for both database developers and DBAs who need to create and troubleshoot .NET applications that connect to SQL Server. And building up your .NET knowledge will put you a step ahead of the game as you prepare for Yukon.

This article is adapted from the book

ADO.NET: The Complete Reference (Osborne/McGraw-Hill, 2003).