参数将值与其 Transact-SQL (T-SQL) 文本分开发送给命令。 使用 SqlParameter 对象,而不是将值串接或内插到 SQL 语句中。 参数有助于防止SQL注入并保持值的数据库类型。
本文中的示例假定一个名为 connection 的已打开 SqlConnection 指向 AdventureWorks 示例数据库。
C# 示例要求 using Microsoft.Data.SqlClient;。 使用 SqlDbType 的例子也要求 using System.Data;。
添加输入参数
在命令文本中为每个值占位符添加参数。 指定参数的 SQL Server 类型并赋值:
const string sql = """
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = @subcategoryId
AND ListPrice >= @minimumPrice
ORDER BY ListPrice;
""";
using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(
$"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}
参数名称在命令文本和 SqlParameter 中的大小写形式应保持一致。 SQL Server 使用服务器排序规则来匹配变量名,因此在采用区分大小写排序规则的服务器上,名称不匹配会导致“必须声明标量变量”错误。
使用参数占位符
Microsoft.Data.SqlClient 使用采用 @parameterName 形式的命名参数。 不支持位置性问号(?)占位符。
参数占位符表示值,而非SQL标识符或语法。 你不能用参数来表示表名、列名、排序方向或整个SQL片段。 如果应用程序动态选择标识符,对照允许列表验证该标识符后,才将其添加到命令文本中。 继续将所有数据值作为参数传递。
对于文本命令,Microsoft.Data.SqlClient 通过 sp_executesql 向 SQL Server 发送参数化语句。 当语句文本和参数定义保持不变时,SQL Server 可以重复使用执行计划。
指定参数数据类型
当应用程序知道目标 SQL Server 类型时,显式设置SqlDbType。 还可设置类型特定的元数据,如 Size、 Precision和 Scale (如适用)。
该示例与 中的 nvarchar(50) 和 Person.Person 列相匹配:
const string sql = """
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE LastName = @lastName
AND ModifiedDate >= @modifiedSince
ORDER BY BusinessEntityID;
""";
using var command = new SqlCommand(sql, connection);
SqlParameter lastName = command.Parameters.Add(
"@lastName",
SqlDbType.NVarChar,
50);
lastName.Value = "O'Brien";
SqlParameter modifiedSince = command.Parameters.Add(
"@modifiedSince",
SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);
将参数元数据与目标列匹配,避免隐式转换,并防止字符串长度或十进制刻度的变化改变参数定义。
对于小数值,显式设置精度和小数位数。
Weight该Production.Product列使用decimal(8, 2):
const string sql = """
SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight >= @minimumWeight
ORDER BY Weight;
""";
using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
"@minimumWeight",
SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;
传递数据库 NULL 值
分配 Value 以发送数据库 NULL。 对于普通标量参数,C# null 不表示 SQL ServerNULL:
const string sql = """
SELECT SalesOrderID, OrderDate, SalesPersonID
FROM Sales.SalesOrderHeader
WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
ORDER BY SalesOrderID;
""";
using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
"@salesPersonId",
SqlDbType.Int);
int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
? selectedSalesPersonId.Value
: DBNull.Value;
选择性地使用推断元数据
该 AddWithValue 方法从运行时值推断参数元数据。 当推断的元数据稳定且与数据库类型匹配时,推断非常方便。 当字符串长度、十进制精度或其他值特性发生变化时,它可以生成不同的参数定义。
对于目标模式已知的生产命令,优先使用带有显式 SqlDbType 的 Add。 不要把 C# null 传给 AddWithValue;指定类型并赋值 DBNull.Value 。
重用命令参数
只需创建一次参数,在使用不同值运行同一命令时仅更改其 Value:
const string sql = """
SELECT TOP (1) Name, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = @subcategoryId
ORDER BY ListPrice DESC;
""";
using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
"@subcategoryId",
SqlDbType.Int);
foreach (int id in new[] { 1, 2, 3 })
{
subcategoryId.Value = id;
using SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
Console.WriteLine($"{id}: {reader.GetString(0)}");
}
}
不要在每次执行中添加同名参数。 在重建之前,先重用现有参数或清除集合。
设置参数方向
该 Direction 属性标识命令如何使用参数。 将其设置为值 ParameterDirection :
| 方向 | Use |
|---|---|
| Input | 向语句或存储过程发送一个值。 此值为默认值。 |
| Output | 从存储过程输出参数接收一个值。 |
| InputOutput | 发送初始值并接收更新后的值。 |
| ReturnValue | 从T-SQL RETURN 语句中接收整数值。 |
对于输出参数、输入/输出参数和返回值参数,仅在命令完成后读取 Value。 如果命令返回行,先关闭或释放 SqlDataReader,然后再读取这些值。
关于完整的存储过程示例,请参见“用存储过程修改数据”。
在存储过程中使用参数
设置 StoredProcedure 并使用过程的参数名称。 这个示例调用 AdventureWorks 中包含的 dbo.uspGetEmployeeManagers 过程:
using var command = new SqlCommand(
"dbo.uspGetEmployeeManagers",
connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
"@BusinessEntityID",
SqlDbType.Int).Value = 6;
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(
$"{reader.GetString(2)} {reader.GetString(3)}");
}
参数名称必须与存储过程声明的名称一致。
配置表值参数
表值参数会向存储过程或参数化语句发送多行。 要配置其中一个:
- 设置
SqlDbType.Structured。 - 将TypeName设置为数据库用户定义的表类型。
- 指定 DataTable、DbDataReader 或
IEnumerable<SqlDataRecord>行源。
一个已定义列但没有行的 DataTable 会发送空表。 空的 IEnumerable<SqlDataRecord> 会抛出 ArgumentException;分配 C# null 以便为该输入表单发送零行。 不要将 DBNull.Value 赋给表值参数。
关于类型创建、行映射和完整示例,请参见 表值参数。
配置流式值
将 Stream、TextReader 或 XmlReader 分配给 Value,以增量方式提供大值。 对于有界内存传输,可以使用 (max)、 XML 或 JSON 参数类型。 对于非 (max) 字符类型和二进制类型以及旧版大对象类型,Microsoft.Data.SqlClient 会在执行前缓冲源。
参数类型必须与以下值相匹配:
| Value | 兼容 SqlDbType 的数值 |
|---|---|
| Stream |
Binary, VarBinary, 或遗留 Image 类型 |
| TextReader |
Char,NChar,NVarChar,VarChar,旧版Text和NText类型,Xml,或Json |
| XmlReader |
Char,NChar,NVarChar,VarChar,旧版Text和NText类型,Xml,或Json |
当对象类型 ArgumentException 或声明的大小不兼容时,Microsoft.Data.SqlClient 会引发 InvalidCastException、SqlTypeException 或 SqlDbType。 有关完整的上传和下载示例,请参见 SqlClient 流式处理支持。
获取参数信息
DeriveParameters查询 SQL Server 以获取存储过程的参数元数据,并替换命令现有的参数集合:
using var command = new SqlCommand(
"dbo.uspGetEmployeeManagers",
connection);
command.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;
DeriveParameters 每次调用都需要额外一次与数据库的往返通信。 必要时在开发阶段推导元数据,然后在性能敏感路径中明确定义参数。