Lesson 07: Using Stored Procedures(使用存储过程)

本文档由李欣蔚(nirvana_li)翻译自 http://www.csharp-station.com/,转载请注名出处!
更新日期2006-2-14

Lesson 07: Using Stored Procedures

第 7 课，使用存储过程

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. 
• 学习如何通过使用存储过程修改SqlCommand对象
• 学习如何使用带参数的存储过程

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.

一个存储过程是一个存储在数据库中的预先定义号的，可重用的程序。 SQL Server 编译存储过程，它使其更有效的使用。因此，作为动态的在代码中创建查询的替代，你能够得到存储过程的重用和性能优势。下面的章节将告诉你如何修改 SqlCommand 对象来使用存储过程。另外，你将看到为什么参数支持是 ADO.NET 库中一个另外重要部分的原因

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:

除了使用字符串创建命令 ,SqlCommand 类型能够被存储过程所使用 . 在这里需要有两件事情发生 : 让 SqlCommand 对象知道哪一个存储过程执行 , 并告诉 SqlCommand 对象它执行的是存储过程 . 下面是说明了这两个步骤 :

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

在上面声明 SqlCommand 对象声明时 , 第一个参数设置为 ”Ten Most Expensive Products”. 这是在 Northwind 数据库中的存储过程的名字 . 第二个参数是连接对象 , 它和用来执行查询字符串的 SqlCommand 构造函数一样 .

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.

第二个命令通过设置 CommandType 属性为 CommandType 枚举中的存储过程的值的方式告诉 SqlCommand 对象它将执行何种命令 .SqlCommand 构造函数中的第一个参数被默认地作为查询字符串解析（而不是将它解释为命令字符串）。其它的代码能够像前面课程中一样使用 SqlCommand 对象。

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:

对存储过程使用的参数与对 command 使用的查询字符串相同。如下代码所示：

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

上面的 SqlCommand 构造函数指明了存储过程的名字为 CustOrderHist 作为它的第一个参数。这个特殊的存储过程使用一个单独的参数，名为 @CustomerID. 因此，必须使用 SqlParameter 对象增加此参数。作为第一个参数传递给 SqlParameter 构造函数的参数名字必须与存储过程参数名字一致。然后与你执行任何其它 SqlCommand 对象一样执行命令。

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.

Listing1 中的代码包括完整能运行的如何使用存储过程的示例。对于有参数的存储过程和没有参数的存储过程分别有不同的方法。

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.

在 Listing 1 中的 RunStoredProc 方法简单地运行存储过程并打印结果到控制台。在 RunStoredProcParams 方法中，存储过程使用单一参数。它说明使用查询字符串和存储过程没有两样。其它的代码应该和本指南前面章节的差不多。

Summary

To execute stored procedures, you specify the name of the stored procedure in the first parameter of a SqlCommand constructor and then set the CommandType of the SqlCommand to StoredProcedure.  You can also send parameters to a stored procedure by using SqlParameter objects, the same way it is done with SqlCommand objects that execute query strings.  Once the SqlCommand object is constructed, you can use it just like any other SqlCommand object as described in previous lessons.

为了执行存储过程，在 SqlCommand 构造函数的第一个参数中指定存储过程的名字，然后将 SqlCommand 的 CommandType 设置为 StoredProcedure 。

同样能够使用 SqlParameter 对象将参数传递给存储过程，它与使用 SqlCommand 对象执行查询字符串一样。一旦 SqlCommand 对象被构造，你就能像使用前面课程中的 SqlCommand 对象一样使用它。

For further questions, you are welcome to participate in the C# Station Discussion Forums.

Your feedback is very important and I appreciate any constructive contributions you have.  Please feel free to contact me for feedback or comments you may have about this lesson.