Executing SQL Server Stored Procedures

In Chapter 4, you saw how to create and execute SQL Server stored procedures using T-SQL. You execute a stored procedure using the T-SQL EXECUTE statement. In this section, you'll see how to execute SQL Server procedures using ADO.NET.

In Table 8.1, shown earlier in this chapter, I mentioned the CommandType of StoredProcedure. Although you can use this CommandType to indicate that a command is to execute a stored procedure, you're actually better off using the T-SQL EXECUTE command to execute a stored procedure. This is because you can read values that are returned from a stored procedure through a RETURN statement, which you can't do when setting the CommandType to StoredProcedure. Also, it's a lot easier to understand your code when you use the EXECUTE command.

There are a couple of ways you can execute a stored procedure depending on whether your procedure returns a result set (a result set is one or more rows retrieved from a table by a SELECT statement). You'll learn these two ways to execute a stored procedure next.

Executing a Stored Procedure That Does Not Return a Result Set

If your procedure does not return a result set, then you use the following steps to execute it:

  1. Create a Command object and set its CommandText property to an EXECUTE statement containing your procedure call.

  2. Add any required parameters for the procedure call to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection.Output. These output parameters can be defined using the T-SQL OUTPUT keyword in your procedure call, or returned using a RETURN statement in your actual procedure.

  3. Execute your Command object using the ExecuteNonQuery() method. You use this method because the procedure doesn't return a result set.

  4. Read the values of any output parameters.

You'll see how to use these four steps to call the following two SQL Server stored procedures:

  • The first procedure, AddProduct(), will return an output parameter defined using the OUTPUT keyword.

  • The second procedure, AddProduct2(), will return an output parameter using the RETURN statement.

These examples will show you the possible ways to execute a stored procedure using ADO.NET and read the output parameters.

Executing the AddProduct() Stored Procedure

In Chapter 4, you saw how to create a stored procedure in the SQL Server Northwind database. The procedure you saw was named AddProduct(), and Listing 8.11 shows the AddProduct.sql script that creates the AddProduct() procedure. You saw how to run this script in Chapter 4. If you didn't already run this script when reading Chapter 4, and you want to run the example C# program shown later, you'll need to run this script. AddProduct() adds a row to the Products table and returns the ProductID of the new row as an OUTPUT parameter.

Listing 8.11: ADDPRODUCT.SQL

start example
 /*   AddProduct.sql creates a procedure that adds a row to the   Products table using values passed as parameters to the   procedure. The procedure returns the ProductID of the new row   in an OUTPUT parameter named @MyProductID */ CREATE PROCEDURE AddProduct   @MyProductID int OUTPUT,   @MyProductName nvarchar(40),   @MySupplierID int,   @MyCategoryID int,   @MyQuantityPerUnit nvarchar(20),   @MyUnitPrice money,   @MyUnitsInStock smallint,   @MyUnitsOnOrder smallint,   @MyReorderLevel smallint,   @MyDiscontinued bit AS   - insert a row into the Products table   INSERT INTO Products (     ProductName, SupplierID, CategoryID, QuantityPerUnit,     UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,     Discontinued   ) VALUES (     @MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit,     @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel,     @MyDiscontinued   )   - use the SCOPE_IDENTITY() function to get the last   - identity value inserted into a table performed within   - the current database session and stored procedure,   - so SCOPE_IDENTITY returns the ProductID for the new row   - in the Products table in this case   SELECT @MyProductID = SCOPE_IDENTITY() 
end example

Notice the OUTPUT parameter named @MyProductID returned by AddProduct(). Because AddProduct() doesn't return a result set, you use the first set of steps outlined earlier. Let's examine the details of these four steps to execute this stored procedure.

Step 1: Create a Command Object and set its CommandText Property to an EXECUTE Statement

Your first step is to create a Command object and set its CommandText property to an EXECUTE statement containing the call to AddProduct(); notice the parameter placeholders used to mark the position where the parameter values will be substituted in step 2:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " +   "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +   "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +   "@MyReorderLevel, @MyDiscontinued"; 

Notice the OUTPUT parameter placeholder named @MyProductID. This is used to store the OUTPUT parameter returned by AddProduct(). The other parameter placeholders are used to pass values to AddProduct(), which then uses those values in its INSERT statement.

Step 2: Add Any Required Parameters to the Command Object

Your second step is to add any parameters to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection.Output.

In this example, AddProduct() expects an output parameter to store the ProductID for the new row, and you therefore need to add an output parameter to your Command object. You do this by setting the Direction property of your parameter to ParameterDirection.Output. For example:

 mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); mySqlCommand.Parameters["@MyProductID"].Direction =   ParameterDirection.Output; 

The other parameters required to call AddProduct() are:

 mySqlCommand.Parameters.Add(   "@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; mySqlCommand.Parameters.Add(   "@MySupplierID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add(   "@MyCategoryID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add(   "@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; mySqlCommand.Parameters.Add(   "@MyUnitPrice", SqlDbType.Money).Value = 5.99; mySqlCommand.Parameters.Add(   "@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; mySqlCommand.Parameters.Add(   "@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add(   "@MyReorderLevel", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add(   "@MyDiscontinued", SqlDbType.Bit).Value = 1; 

Notice that the SqlDbType parameter types correspond to the types expected by the AddProduct() stored procedure. The values the parameters are set to are then substituted for the placeholders in the EXECUTE statement shown in step 1.

Step 3: Execute the Command Object Using the ExecuteNonQuery() Method

Your third step is to execute your Command object using the ExecuteNonQuery() method. You use ExecuteNonQuery() because the AddProduct() procedure doesn't return a result set. For example:

 mySqlCommand.ExecuteNonQuery(); 

Step 4: Read the Values of any Output Parameters

Your last step is to read the values of any output parameters. AddProduct() used one output parameter named @MyProductID. You read this returned value from the Value property of @MyProductID:

 Console.WriteLine("New ProductID = " +   mySqlCommand.Parameters["@MyProductID"].Value); 

This displays the values of the ProductID generated by SQL Server for the new row in the Products table.

Listing 8.12 illustrates these steps to call the AddProduct() procedure.

Listing 8.12: EXECUTEADDPRODUCT.CS

start example
 /*   ExecuteAddProduct.cs illustrates how to call the SQL Server   AddProduct() stored procedure */ using System; using System.Data; using System.Data.SqlClient; class ExecuteAddProduct {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     mySqlConnection.Open();     // step 1: create a Command object and set its CommandText     // property to an EXECUTE statement containing the stored     // procedure call     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " +       "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +       "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +       "@MyReorderLevel, @MyDiscontinued";     // step 2: add the required parameters to the Command object     mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);     mySqlCommand.Parameters["@MyProductID"].Direction =       ParameterDirection.Output;     mySqlCommand.Parameters.Add(       "@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";     mySqlCommand.Parameters.Add(       "@MySupplierID", SqlDbType.Int).Value = 1;     mySqlCommand.Parameters.Add(       "@MyCategoryID", SqlDbType.Int).Value = 1;     mySqlCommand.Parameters.Add(       "@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";     mySqlCommand.Parameters.Add(       "@MyUnitPrice", SqlDbType.Money).Value = 5.99;     mySqlCommand.Parameters.Add(       "@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;     mySqlCommand.Parameters.Add(       "@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;     mySqlCommand.Parameters.Add(       "@MyReorderLevel", SqlDbType.SmallInt).Value = 5;     mySqlCommand.Parameters.Add(       "@MyDiscontinued", SqlDbType.Bit).Value = 1;     // step 3: execute the Command object using the     // ExecuteNonQuery() method     mySqlCommand.ExecuteNonQuery();     // step 4: read the value of the output parameter     Console.WriteLine("New ProductID = " +       mySqlCommand.Parameters["@MyProductID"].Value);     mySqlConnection.Close();   } } 
end example

The output from this program is as follows:

 New ProductID = 81 

Of course, depending on the existing rows in your Products table, you'll get a different result.

Executing the AddProduct2() Stored Procedure

As you'll see, the AddProduct2() procedure is similar to AddProduct(), except that it uses a RETURN statement instead of an OUTPUT parameter to return the ProductID for the new row. Listing 8.13 shows the AddProduct2.sql script that creates the AddProduct2() procedure. You'll need to run this script before running the C# program.

Listing 8.13: ADDPRODUCT2.SQL

start example
 /*   AddProduct2.sql creates a procedure that adds a row to the   Products table using values passed as parameters to the   procedure. The procedure returns the ProductID of the new row   using a RETURN statement */ CREATE PROCEDURE AddProduct2   @MyProductName nvarchar(40),   @MySupplierID int,   @MyCategoryID int,   @MyQuantityPerUnit nvarchar(20),   @MyUnitPrice money,   @MyUnitsInStock smallint,   @MyUnitsOnOrder smallint,   @MyReorderLevel smallint,   @MyDiscontinued bit AS   - declare the @MyProductID variable   DECLARE @MyProductID int   - insert a row into the Products table   INSERT INTO Products (     ProductName, SupplierID, CategoryID, QuantityPerUnit,     UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,     Discontinued   ) VALUES (     @MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit,     @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel,     @MyDiscontinued   )   - use the SCOPE_IDENTITY() function to get the last   - identity value inserted into a table performed within   - the current database session and stored procedure,   - so SCOPE_IDENTITY returns the ProductID for the new row   - in the Products table in this case   SET @MyProductID = SCOPE_IDENTITY()   RETURN @MyProductID 
end example

Notice the RETURN statement at the end to return @MyProductID. Because AddProduct2() doesn't return a result set of rows, you use the same four steps shown in the previous section to execute the procedure using ADO.NET. The only difference is in the construction of your EXECUTE command when setting the CommandText property in step 1. To call AddProduct2() you set the CommandText property of your Command object as follows:

 mySqlCommand.CommandText =   "EXECUTE @MyProductID = AddProduct2 @MyProductName, " +   "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +   "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +   "@MyReorderLevel, @MyDiscontinued"; 

Notice the change in the position of the @MyProductID parameter: it is shifted to just after the EXECUTE and set equal to the value returned by AddProduct2(). This change is made because Add-Product2() uses a RETURN statement to output the @MyProductID value. The rest of the C# code required to call AddProduct2() is the same as that shown earlier in Listing 8.12.

Note 

Because only the EXECUTE is different, I've omitted the program that calls AddProduct2() from this book. You can see this program in the ExecuteAddProduct2.cs file I've provided. Feel free to examine and run it.

Executing a Stored Procedure that Does Return a Result Set

If your procedure does return a result set, then you use the following steps to execute it:

  1. Create a Command object and set its CommandText property to an EXECUTE statement containing your procedure call.

  2. Add any required parameters to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection.Output.

  3. Execute your command using the ExecuteReader() method, storing the returned DataReader object.

  4. Read the rows in the result set using your DataReader object.

  5. Close your DataReader object. You must do this before you can read any output parameters.

  6. Read the values of any output parameters.

In the following example, you'll see a stored procedure named AddProduct3() that will return a result set along with an output parameter using a RETURN statement.

The AddProduct3() procedure is similar to AddProduct2(), except that it also returns a result set using a SELECT statement. This SELECT contains the ProductName and UnitPrice columns for the new row added to the Products table. This result set is returned in addition to the ProductID of the new row, which is returned using the RETURN statement. Listing 8.14 shows the AddProduct3.sql script that creates the AddProduct3() procedure. You'll need to run this script before running the C# program.

Listing 8.14: ADDPRODUCT3.SQL

start example
 /*   AddProduct3.sql creates a procedure that adds a row to the   Products table using values passed as parameters to the   procedure. The procedure returns the ProductID of the new row   using a RETURN statement and returns a result set containing   the new row */ CREATE PROCEDURE AddProduct3   @MyProductName nvarchar(40),   @MySupplierID int,   @MyCategoryID int,   @MyQuantityPerUnit nvarchar(20),   @MyUnitPrice money,   @MyUnitsInStock smallint,   @MyUnitsOnOrder smallint,   @MyReorderLevel smallint,   @MyDiscontinued bit AS   - declare the @MyProductID variable   DECLARE @MyProductID int   - insert a row into the Products table   INSERT INTO Products (     ProductName, SupplierID, CategoryID, QuantityPerUnit,     UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,     Discontinued   ) VALUES (     @MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit,     @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel,     @MyDiscontinued   )   - use the SCOPE_IDENTITY() function to get the last   - identity value inserted into a table performed within   - the current database session and stored procedure,   - so SCOPE_IDENTITY returns the ProductID for the new row   - in the Products table in this case   SET @MyProductID = SCOPE_IDENTITY()   - return the result set   SELECT ProductName, UnitPrice   FROM Products   WHERE ProductID = @MyProductID   - return @MyProductID   RETURN @MyProductID 
end example

Since you've already seen the basics for the code that execute the six steps shown earlier in this section, I'll go straight to the code with minimal explanation. Listing 8.15 shows the program that calls AddProduct3(). The important things to notice are:

Listing 8.15: EXECUTEADDPRODUCT3.CS

start example
 /*   ExecuteAddProduct3.cs illustrates how to call the SQL Server   AddProduct3() stored procedure */ using System; using System.Data; using System.Data.SqlClient; class ExecuteAddProduct3 {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     mySqlConnection.Open();     // step 1: create a Command object and set its CommandText     // property to an EXECUTE statement containing the stored     // procedure call     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "EXECUTE @MyProductID = AddProduct3 @MyProductName, " +       "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +       "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +       "@MyReorderLevel, @MyDiscontinued";     // step 2: add the required parameters to the Command object     mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);     mySqlCommand.Parameters["@MyProductID"].Direction =       ParameterDirection.Output;     mySqlCommand.Parameters.Add(       "@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";     mySqlCommand.Parameters.Add(       "@MySupplierID", SqlDbType.Int).Value = 1;     mySqlCommand.Parameters.Add(       "@MyCategoryID", SqlDbType.Int).Value = 1;     mySqlCommand.Parameters.Add(       "@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";     mySqlCommand.Parameters.Add(       "@MyUnitPrice", SqlDbType.Money).Value = 5.99;     mySqlCommand.Parameters.Add(       "@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;     mySqlCommand.Parameters.Add(       "@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;     mySqlCommand.Parameters.Add(       "@MyReorderLevel", SqlDbType.SmallInt).Value = 5;     mySqlCommand.Parameters.Add(       "@MyDiscontinued", SqlDbType.Bit).Value = 1;     // step 3: execute the Command object using the ExecuteReader()     // method     SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();     // step 4: read the rows using the DataReader object     while (mySqlDataReader.Read())     {       Console.WriteLine("mySqlDataReader[\" ProductName\"] = " +         mySqlDataReader["ProductName"]);       Console.WriteLine("mySqlDataReader[\" UnitPrice\"] = " +         mySqlDataReader["UnitPrice"]);     }     // step 5: close the DataReader object     mySqlDataReader.Close();     // step 6: read the value of the output parameter     Console.WriteLine("New ProductID = " +       mySqlCommand.Parameters["@MyProductID"].Value);     mySqlConnection.Close();   } } 
end example

The ExecuteReader() method is used to return the result set containing the ProductName and UnitPrice columns for the new row.

The result set is then read using a SqlDataReader object.

The SqlDataReader object is closed before the output parameter is read.

The output from this program is as follows:

 mySqlDataReader["ProductName"] = Widget mySqlDataReader["UnitPrice"] = 5.99 New ProductID = 83 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net