Using Stored Procedures - Ado.net Tutorials
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 procedureSqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);// 2. set the command object so it knows// to execute a stored procedurecmd.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 theStoredProcedure 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 procedureSqlCommand cmd = new SqlCommand("CustOrderHist", conn);// 2. set the command object so it knows// to execute a stored procedurecmd.CommandType = CommandType.StoredProcedure;// 3. add parameter to command, which// will be passed to the stored procedurecmd.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 StoredProcDemostatic void Main(){{StoredProcDemo spd = new StoredProcDemo();// run a simple stored procedurespd.RunStoredProc();// run a stored procedure that takes a parameterspd.RunStoredProcParams();}// run a simple stored procedurepublic void RunStoredProc(){SqlConnection conn = null;SqlDataReader rdr = null;Console.WriteLine("\nTop 10 Most Expensive Products:\n");try{// create and open a connection objectconn = newSqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");conn.Open();// 1. create a command object identifying// the stored procedureSqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);// 2. set the command object so it knows// to execute a stored procedurecmd.CommandType = CommandType.StoredProcedure;// execute the commandrdr = cmd.ExecuteReader();// iterate through results, printing each to consolewhile (rdr.Read()){Console.WriteLine("Product: {0,-25} Price: ${1,6:####.00}",rdr["TenMostExpensiveProducts"],finallyrdr["UnitPrice"]); } }{if (conn != null){conn.Close();}if (rdr != null){rdr.Close();} } }// run a stored procedure that takes a parameterpublic void RunStoredProcParams(){SqlConnection conn = null;SqlDataReader rdr = null;// typically obtained from user// input, but we take a short cutstring custId = "FURIB";Console.WriteLine("\nCustomer Order History:\n");try{// create and open a connection objectconn = newSqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");conn.Open();// 1. create a command object identifying// the stored procedureSqlCommand cmd = new SqlCommand("CustOrderHist", conn);// 2. set the command object so it knows// to execute a stored procedurecmd.CommandType = CommandType.StoredProcedure;// 3. add parameter to command, which// will be passed to the stored procedurecmd.Parameters.Add(new SqlParameter("@CustomerID", custId));// execute the commandrdr = cmd.ExecuteReader();// iterate through results, printing each to consolewhile (rdr.Read()){Console.WriteLine("Product: {0,-35} Total: {1,2}",rdr["ProductName"],finallyrdr["Total"]); } }{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 RunStoredProcParamsmethod, 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.
