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).
End of Article
Prev. page
1
2
[3]
next page -->
