本文档由李欣蔚(nirvana_li)翻译自
http://www.csharp-station.com/,转载请注名出处!
更新日期2006-2-14
更新日期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
对象中的值替换