Using the SQLCommand Object - Executing Stored Procedures with Return Values
(Page 4 of 5 )
Stored procedures are the core of most database applications—and for good reason. In addition to their performance benefits, stored procedures can also be a mechanism for restricting data access to the predefined interfaces that are exposed by the stored procedures. Similar to prepared SQL statements, stored procedures get significant performance benefits from the fact that they are compiled before they are used. This allows the database to forego the typical parsing steps that are required, skipping the need to create an access plan. Stored procedures are the true workhorse of most database applications, and they are almost always used for database insert, update, and delete operations, as well as for retrieving single values and result sets. In the following examples, you see how to execute SQL Server stored procedures using the SqlCommand object. In the first example that follows, you’ll see how to execute a stored procedure that accepts a single input parameter and returns a scalar value.
The following listing presents the T-SQL source code required to create the StockValue stored procedure that will be added to the sample Northwind database. You can create this stored procedure by executing this code using Query Analyzer.
CREATE PROCEDURE StockValue
@ProductID int
AS
DECLARE @StockValue money
SELECT StockValue = (UnitsInStock * UnitPrice)
FROM Products WHERE ProductID = @ProductID
RETURN @StockValue
In the preceding listing, you can see that the StockValue stored procedure accepts a single input parameter. That parameter is an Integer value that’s used to identify the ProductID. The StockValue stored procedure returns the stock value of that ProductID from the Products table in the Northwind database. The stock value is calculated by retrieving the UnitsInStock number and multiplying it by the value in the UnitPrice column. The results are then assigned to the @StockValue variable, which is returned as a scalar value by the stored procedure. After the sample stored procedure has been created in the Northwind database, it can be called by your ADO.NET applications. The following example shows how to use the SQLCommand class form VB.NET to execute the StockValue stored procedure and retrieve the scalar value that it returns:
Private Sub SQLCommandSPScalar(ByVal sServer As String, _
ByVal sDB As String)
Dim cn As New SqlConnection("SERVER=" & sServer _
& ";INTEGRATED SECURITY=True;DATABASE=" & sDB)
' Create the command object and set the SQL statement
Dim cmd As New SqlCommand("StockValue", cn)
cmd.CommandType = CommandType.StoredProcedure
'Create the parameter
cmd.Parameters.Add("@ProductID", SqlDbType.Int)
cmd.Parameters("@ProductID").Direction = _
ParameterDirection.Input
cmd.Parameters("@ProductID").Value = 1
Try
Dim nStockValue As Decimal
' Open the connection and execute the command
cn.Open()
nStockValue = cmd.ExecuteScalar()
txtMid.Text = nStockValue
Catch e As Exception
MsgBox(e.Message)
End Try
' Close the connection
cn.Close()
End Sub
Following is the C# version of the SQLCommandSPScalar subroutine that calls a SQL stored procedure and returns a scalar value:
private void SQLCommandPSScalar(string sServer, string sDB)
{
SqlConnection cn = new SqlConnection("SERVER=" + sServer
+ ";INTEGRATED SECURITY=True;DATABASE=" + sDB);
// Create the command object and set the SQL statement
SqlCommand cmd = new SqlCommand("StockValue", cn);
cmd.CommandType = CommandType.StoredProcedure;
// Create the parameter
cmd.Parameters.Add("@ProductID", SqlDbType.Int);
cmd.Parameters["@ProductID"].Direction =
ParameterDirection.Input;
cmd.Parameters["@ProductID"].Value = 1;
try
{
decimal nStockValue;
// Open the connection and execute the command
cn.Open();
nStockValue = (decimal)cmd.ExecuteScalar();
txtMid.Text = nStockValue.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// Close the connection
cn.Close();
}
In the beginning of this routine you can see where the cn SqlConnection object is created, followed by the creation of the SqlCommand object named cmd. In this example, the constructor for the SqlCommand object uses two parameters. The first parameter is a string that accepts the command that will be executed. This can be either a SQL statement or the name of the stored procedure. In this example, you can see that the name of the StockValue stored procedure is used. The second parameter is used for the name of the SqlConnection object that will be used to connect to the target database. After the cmd SqlCommand object has been created, its CommandType property is set to CommandType.StoredProcedure indicating that a stored procedure will be executed. The CommandType property can accept any of the values shown in the following table:
| CommandType Values | Description |
| CommandType.StoredProcedure | The command is a stored procedure. |
| CommandType.TableDirect | The command is the name of a database table. |
| CommandType.Text | The command is a SQL statement. |
After the SqlCommand object’s CommandType property is set to CommandType.StoredProcedure, the SqlParameter object used to supply the input value to the StockValue stored procedure is created. SqlParameter objects can be created either by using the SqlParameter class constructor or by executing the SqlCommand object’s Parameters collection Add method. In this example, the parameter is created using the Add method of the SqlCommand object’s Parameters collection. The first parameter supplied to the Add method is a string containing the name of the parameter. In this case "@ProductID". Again, note that replaceable parameters used by the SQLParameter object must begin with the at symbol (@). The second parameter uses the SqlDbType.Int enumeration to indicate that the parameter will contain an Integer value. The next line sets the Direction property to the value ParameterDirection.Input to indicate that this is an input parameter. Last, the SqlParameter object’s Value property is set to 1—storing a value of 1 to pass to the StockValue stored procedure.
The next section of code sets up a Try-Catch block to open the connection to the SQL Server system and then executes the StockValue stored procedure. The important point to note in the Try-Catch block is that the cmd SqlCommand object’s ExecuteScalar method is used to execute the StockValue stored procedure and the return value is assigned to the nStockValue variable. The contents of the nStockValue variable are then assigned to a text box. Like the earlier examples, if the connection or the stored procedure fails, a message box showing the error text will be displayed to the end user. Then the connection will be closed.
Next: Executing Transactions >>
More Database Articles
More By McGraw-Hill/Osborne
|
 This article is taken from chapter 8 of ADO.NET: The Complete Reference, written by Michael Otey and Denielle Otey (McGraw-Hill/Osborne, 2004; ISBN: 0072228989). Check it out at your favorite bookstore. Buy this book now.
|
|
