本文档由李欣蔚(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.
