本文档由李欣蔚(nirvana_li)翻译自http://www.csharp-station.com/,转载请注名出处!
更新日期2006-2-14
Lesson 06: Adding Parameters to Commands
第6课:向命令中添加参数
This lesson shows you how to use parameters in your commands. Here are the objectives of this lesson:
这节课介绍了如何在命令中使用参数,下面是本节课的目标:
Understand what a parameter is.
Be informed about the benefits of using parameters.
Learn how to create a parameter.
Learn how to assign parameters to commands.
了解parameter参数是什么
理解使用parameter的好处
学习如何创建parameter
学习如何将parameter赋值给命令
Introduction
介绍
When working with data, you'll often want to filter results based on some criteria. Typically, this is done by accepting input from a user and using that input to form a SQL query. For example, a sales person may need to see all orders between specific dates. Another query might be to filter customers by city.
当操作数据的时候,你通常需要基于某些标准来过滤结果.通常,这些都由从用户处得到的输入和使用输入构成的SQL查询语句实现的.比如,一个商人需要查看在特定的日期之间的所有订单.另外的查询可能通过城市来过滤用户.
As you know, the SQL query assigned to a SqlCommand object is simply a string. So, if you want to filter a query, you could build the string dynamically, but you wouldn't want to. Here is a bad example of filtering a query.
正如你所知道的,SQL查询语句赋值给一个SqlCommand对象只是一个简单的字符串.所以你可能想要过滤一个查询,可以动态的绑定字符串,但是你本来不想这样做,下面是一个过滤查询的坏的示例:
// don't ever do this!
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = '" + inputCity + "'";
Don't ever build a query this way! The input variable, inputCity, is typically retrieved from a TextBox control on either a Windows Form or a Web Page. Anything placed into that TextBox control will be put into inputCity and added to your SQL string. This situation invites a hacker to replace that string with something malicious. In the worst case, you could give full control of your computer away.
千万不要以这种方式创建查询!输入变量inputCity通常都是从一个Windows Form上或者Web页面上的TextBox控件得到输入.任何在TextBox控件中的东西将直接存入inputCity并添加到你的SQL字符串中.黑客可以使用恶意的代码来替换这串字符串,更糟糕的是,他能够进而控制你的计算机.
Instead of dynamically building a string, as shown in the bad example above, use parameters. Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.
作为对上面糟糕的例子使用动态创建字符串的替代，使用parameters。任何放置在parameter中的东西都将被作为字段数据对待，而不是SQL语句的一部分，这样就让你的应用程序更加安全。
Using parameterized queries is a three step process:
使用参数化查询是下面三步过程：
Construct the SqlCommand command string with parameters.
Declare a SqlParameter object, assigning values as appropriate.
Assign the SqlParameter object to the SqlCommand object's Parameters property.
1． 使用parameters构建SqlCommand命令字符串
2． 声明SqlParameter对象，将适当的值赋给它
3． 将SqlParameter对象赋值给SqlCommand对象的Parameters属性
The following sections take you step-by-step through this process.
下面的章节将一步一步介绍这个过程
Preparing a SqlCommand Object for Parameters
为Parameters准备SqlCommand对象
The first step in using parameters in SQL queries is to build a command string containing parameter placeholders. These placeholders are filled in with actual parameter values when the SqlCommand executes. Proper syntax of a parameter is to use an '@' symbol prefix on the parameter name as shown below:
在SQL查询中使用Parameters的第一步是创建包含参数占位符的对象字符串。这些占位符在SqlCommand执行的时候填充实际的参数值。Parameter的正确的语法是使用一个’@’符号作为参数名的前缀，如下所示：
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);
In the SqlCommand constructor above, the first argument contains a parameter declaration, @City. This example used one parameter, but you can have as many parameters as needed to customize the query. Each parameter will match a SqlParameter object that must be assigned to this SqlCommand object.
在上面的SqlCommand构造函数中，第一个参数包含一个参数声明，@City。这个例子使用一个参数，但是你能够根据需要为查询定制需要的参数。每一个参数匹配一个SqlParameter对象，它必须被分配给此SqlCommand对象
Declaring a SqlParameter Object
声明一个SqlParameter对象
Each parameter in a SQL statement must be defined. This is the purpose of the SqlParameter type. Your code must define a SqlParameter instance for each parameter in a SqlCommand object's SQL command. The following code defines a parameter for the @City parameter from the previous section:
在SQL语句中的每一个参数必须被定义。这是SqlParameter类型的需要。你的代码必须为每一个在SqlCommand对象的SQL命令中的参数定义一个SqlParameter实体。下面的代码为前面一节中的@City参数定义了参数。
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;
Notice that the ParameterName property of the SqlParameter instance must be spelled exactly as the parameter that is used in the SqlCommand SQL command string. You must also specify a value for the command. When the SqlCommand object executes, the parameter will be replaced with this value.
注意SqlParameter实体的ParameterName属性必须和SqlCommand SQL命令字符串中的使用的参数一致。你必须同样为此值赋值。当SqlCommand对象执行的时候，此参数将被被它的值替换
Associate a SqlParameter Object with a SqlCommand Object
将SqlParameter对象和SqlCommand对象关联
For each parameter defined in the SQL command string argument to a SqlCommand object, you must define a SqlParameter. You must also let the SqlCommand object know about the SqlParameter by assigning the SqlParameter instance to the Parameters property of the SqlCommand object. The following code shows how to do this:
对于每一个定义在SqlCommand对象中的SQL命令字符串参数，你必须定义一个SqlParameter。你必须同样将SqlParameter实体赋值给SqlComamd对象的Parameters属性的方式让SqlCommand对象知道SqlParameter。下面的代码展示了如何做：
// 3. add new parameter to command object
cmd.Parameters.Add(param);
The SqlParameter instance is the argument to the Add method of the Parameters property for the SqlCommand object above. You must add a unique SqlParameter for each parameter defined in the SqlCommand object's SQL command string.
SqlParameter实体是作为SqlCommand对象的Parameters属性的Add方法中的参数的。你必须为每一个定义在SqlCommand对象的SQL命令字符串中的参数添加一个单独的SqlParameter
Putting it All Together
组合
You already know how to use SqlCommand and SqlDataReader objects. The following code demonstrates a working program that uses SqlParameter objects. So, everything should be familiar by now, except for the new parts presented in this article:
你已经知道了如何使用SqlCommand和SqlDataReader对象。下面的代码说明了一个使用SqlParameter对象的可运行程序。这样，现在对每一件事情都很熟悉了，除了这篇文章中新的部分：
Listing 1: Adding Parameters to Queries
using System;
using System.Data;
using System.Data.SqlClient;
class ParamDemo
{
static void Main()
{
// conn and reader declared outside try
// block for visibility in finally block
SqlConnection conn = null;
SqlDataReader reader = null;
string inputCity = "London";
try
{
// instantiate and open connection
conn = new
SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();
// don't ever do this!
// SqlCommand cmd = new SqlCommand(
// "select * from Customers where city = '" + inputCity + "'";
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;
// 3. add new parameter to command object
cmd.Parameters.Add(param);
// get data stream
reader = cmd.ExecuteReader();
// write each record
while(reader.Read())
{
Console.WriteLine("{0}, {1}",
reader["CompanyName"],
reader["ContactName"]);
}
}
finally
{
// close reader
if (reader != null)
{
reader.Close();
}
// close connection
if (conn != null)
{
conn.Close();
}
}
}
}
The code in Listing 1 simply retrieves records for each customer that lives in London. This was made more secure through the use of parameters. Besides using parameters, all of the other code contains techniques you've learned in previous lessons.
Listing1中的代码简单的取出每一个在伦敦生活的顾客的名字。使用parameters让它更加安全。除了使用parameters，其它所有代码包含的技术都是我们在前面课中学习过的。
Summary
总结
You should use parameters to filter queries in a secure manner. The process of using parameter contains three steps: define the parameter in the SqlCommand command string, declare the SqlParameter object with applicable properties, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.
你应该使用parameters以一种安全的方式过滤查询。使用parameter的过程包含下面三个步骤：在SqlCommand命令字符串中定义parameter，使用适当的属性声明SqlParameter对象，并将SqlParameter对象赋值给SqlCommand对象。当SqlCommand执行的时候，parameters将被SqlParameter对象中的值替换
