DOTNET CSHARP ADODOTNET DAY SEVEN - K MANOHAR GOUD FACULTY TRAINER DEVELOPER

K MANOHAR GOUD

9866550012

Using Stored Procedures

Objectives:

• 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).

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 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.

 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();
   }
  } 
 }
}