Wednesday 31 August 2011

Lesson 07: Using Stored Procedures


This lesson shows how to use stored procedures in your data access code.  Here are the objectives of this lesson:
  • Learn how to modify the SqlCommand object to use a stored procedure.
  • Understand how to use parameters with stored procedures. 
Introduction
A stored procedures is a pre-defined, reusable routine that is stored in a database.  SQL Server compiles stored procedures, which makes them more efficient to use.  Therefore, rather than dynamically building queries in your code, you can take advantage of the reuse and performance benefits of stored procedures.  The following sections will show you how to modify the SqlCommand object to use stored procedures.  Additionally, you'll see another reason why parameter support is an important part of the ADO.NET libraries.
Executing a Stored Procedure
In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures.  There are two tasks require to make this happen: let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure.  These two steps are shown below:
        // 1.  create a command object identifying
        //     the stored procedure
        SqlCommand cmd  = new SqlCommand(
               "Ten Most Expensive Products", conn);

        // 2. set the command object so it knows
        //    to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;
While declaring the SqlCommand object above, the first parameter is set to "Ten Most Expensive Products".  This is the name of a stored procedure in the Northwind database.  The second parameter is the connection object, which is the same as the SqlCommand constructor used for executing query strings.
The second command tells the SqlCommand object what type of command it will execute by setting its CommandType property to the StoredProcedure value of the CommandType enum.  The default interpretation of the first parameter to the SqlCommand constructor is to treat it as a query string.  By setting the CommandType to StoredProcedure, the first parameter to the SqlCommand constructor will be interpreted as the name of a stored procedure (instead of interpreting it as a command string).  The rest of the code can use the SqlCommand object the same as it is used in previous lessons.
Sending Parameters to Stored Procedures
Using parameters for stored procedures is the same as using parameters for query string commands.  The following code shows this:
        // 1.  create a command object identifying
        //     the stored procedure
        SqlCommand cmd  = new SqlCommand(
               "CustOrderHist", conn);

        // 2. set the command object so it knows
        //    to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which
        //    will be passed to the stored procedure
        cmd.Parameters.Add(
               new SqlParameter("@CustomerID", custId));
The SqlCommand constructor above specifies the name of a stored procedure, CustOrderHist, as its first parameter.  This particular stored procedure takes a single parameter, named @CustomerID.  Therefore, we must populate this parameter using a SqlParameter object.  The name of the parameter passed as the first parameter to the SqlParameter constructor must be spelled exactly the same as the stored procedure parameter.  Then execute the command the same as you would with any other SqlCommand object.
A Full Example
The code in Listing 1 contains a full working example of how to use stored procedures.  There are separate methods for a stored procedure without parameters and a stored procedure with parameters.
Listing 1: Executing Stored Procedures
using System;
using System.Data;
using System.Data.SqlClient;

class StoredProcDemo
{
        static void Main()
        {
               StoredProcDemo spd = new StoredProcDemo();

               // run a simple stored procedure
               spd.RunStoredProc();

               // run a stored procedure that takes a parameter
               spd.RunStoredProcParams();
        }

        // run a simple stored procedure
        public void RunStoredProc()
        {
               SqlConnection conn = null;
               SqlDataReader rdr  = null;

               Console.WriteLine("\nTop 10 Most Expensive Products:\n");

               try
               {
                       // create and open a connection object
                       conn = new
                               SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
                       conn.Open();

                       // 1.  create a command object identifying
                       //     the stored procedure
                       SqlCommand cmd  = new SqlCommand(
                               "Ten Most Expensive Products", conn);

                       // 2. set the command object so it knows
                       //    to execute a stored procedure
                       cmd.CommandType = CommandType.StoredProcedure;

                       // execute the command
                       rdr = cmd.ExecuteReader();

                       // iterate through results, printing each to console
                       while (rdr.Read())
                       {
                               Console.WriteLine(
                                      "Product: {0,-25} Price: ${1,6:####.00}",
                                      rdr["TenMostExpensiveProducts"],
                                      rdr["UnitPrice"]);
                       }
               }
               finally
               {
                       if (conn != null)
                       {
                               conn.Close();
                       }
                       if (rdr != null)
                       {
                               rdr.Close();
                       }
               }
        }

        // run a stored procedure that takes a parameter
        public void RunStoredProcParams()
        {
               SqlConnection conn = null;
               SqlDataReader rdr  = null;

               // typically obtained from user
               // input, but we take a short cut
               string custId = "FURIB";

               Console.WriteLine("\nCustomer Order History:\n");

               try
               {
                       // create and open a connection object
                       conn = new
                               SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
                       conn.Open();

                       // 1.  create a command object identifying
                       //     the stored procedure
                       SqlCommand cmd  = new SqlCommand(
                               "CustOrderHist", conn);

                       // 2. set the command object so it knows
                       //    to execute a stored procedure
                       cmd.CommandType = CommandType.StoredProcedure;

                       // 3. add parameter to command, which
                       //    will be passed to the stored procedure
                       cmd.Parameters.Add(
                               new SqlParameter("@CustomerID", custId));

                       // execute the command
                       rdr = cmd.ExecuteReader();

                       // iterate through results, printing each to console
                       while (rdr.Read())
                       {
                               Console.WriteLine(
                                      "Product: {0,-35} Total: {1,2}",
                                      rdr["ProductName"],
                                      rdr["Total"]);
                       }
               }
               finally
               {
                       if (conn != null)
                       {
                               conn.Close();
                       }
                       if (rdr != null)
                       {
                               rdr.Close();
                       }
               }      
        }
}
The RunStoredProc method in Listing 1 simply runs a stored procedure and prints the results to the console.  In the RunStoredProcParams method, the stored procedure used takes a single parameter.  This demonstrates that there is no difference between using parameters with query strings and stored procedures.  The rest of the code should be familiar to those who have read previous lessons in this tutorial.

No comments:

Post a Comment