使用 Microsoft.Data.SqlClient 配置参数

参数将值与其 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)}");
}

参数名称必须与存储过程声明的名称一致。

配置表值参数

表值参数会向存储过程或参数化语句发送多行。 要配置其中一个:

  1. 设置 SqlDbType.Structured。
  2. 将TypeName设置为数据库用户定义的表类型。
  3. 指定 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 每次调用都需要额外一次与数据库的往返通信。 必要时在开发阶段推导元数据,然后在性能敏感路径中明确定义参数。