配置参数和参数数据类型
通过提供类型检查和验证,命令对象可使用参数来将值传递给 SQL 语句或存储过程。 与命令文本不同,参数输入被视为文本值,而不是可执行代码。 这样可帮助抵御“SQL 注入”攻击,这种攻击的攻击者会将命令插入 SQL 语句,从而危及服务器的安全。
参数化命令还可提高查询执行性能,因为它们可帮助数据库服务器将传入命令与适当的缓存查询计划进行准确匹配。 有关详细信息,请参阅执行计划的缓存和重用和重用参数和执行计划。 除具备安全和性能优势外,参数化命令还提供一种用于组织传递到数据源的值的便捷方法。
DbParameter 对象可以通过使用其构造函数来创建,或者也可以通过调用 DbParameterCollection 集合的 Add
方法以将该对象添加到 DbParameterCollection 来创建。 Add
方法将构造函数实参或现有形参对象用作输入,具体取决于数据提供程序。
提供 ParameterDirection 属性
在添加参数时,您必须为输入参数以外的参数提供一个 ParameterDirection 属性。 下表显示了可用于 ParameterDirection
枚举的 ParameterDirection 值。
成员名称 | 描述 |
---|---|
Input | 该参数为输入参数。 这是默认值。 |
InputOutput | 该参数可执行输入和输出。 |
Output | 该参数为输出参数。 |
ReturnValue | 该参数表示从某操作(如存储过程、内置函数或用户定义的函数)返回的值。 |
处理参数占位符
参数占位符的语法取决于数据源。 .NET Framework 数据提供程序以不同方式处理命名和指定参数和参数占位符。 此语法是针对某个特定的数据源自定义的,如下表所述。
数据提供程序 | 参数命名语法 |
---|---|
System.Data.SqlClient | 以 @ 参数名格式使用命名参数。 |
System.Data.OleDb | 使用由问号 (? ) 指示的位置参数标记。 |
System.Data.Odbc | 使用由问号 (? ) 指示的位置参数标记。 |
System.Data.OracleClient | 以 : 参数名 (或 参数名)格式使用命名参数。 |
指定参数数据类型
参数的数据类型特定于 .NET Framework 数据提供程序。 如果指定类型,则在向数据源传递 Parameter
的值之前,该值将转换为 .NET Framework 数据提供程序类型。 也可以通过通用的方式指定 Parameter
的类型,方法是将 DbType
对象的 Parameter
属性设置为特定的 DbType。
Parameter
对象的 .NET Framework 数据提供程序类型是从 Parameter
对象的 Value
的 .NET Framework 类型或从 Parameter
对象的 DbType
推断而来的。 下表显示了根据作为 Parameter
值传递的对象或指定的 Parameter
推断出的 DbType
类型。
.NET Framework 类型 | DbType | SqlDbType | OleDbType | OdbcType | OracleType |
---|---|---|---|---|---|
Boolean | 布尔 | bit | 布尔 | bit | Byte |
Byte | Byte | TinyInt | UnsignedTinyInt | TinyInt | Byte |
byte[] | 二进制 | Varbinary。 如果字节数组大于 VarBinary 的最大大小(8000 字节),此隐式转换将失败。对于大于 8000 字节的字节数组,请显式设置 SqlDbType。 | VarBinary | 二进制 | 原始 |
Char | 不支持从 char 推断 SqlDbType 。 | Char | Char | Byte | |
DateTime | DateTime | DateTime | DBTimeStamp | DateTime | DateTime |
DateTimeOffset | DateTimeOffset | SQL Server 2008 中的 DateTimeOffset。 SQL Server 2008 以前的 SQL Server 版本不支持从 DateTimeOffset 推断 SqlDbType 。 | DateTime | ||
Decimal | Decimal | 小数 | Decimal | Numeric | Number |
Double | Double | Float | Double | 双精度 | Double |
Single | Single | Real | Single | Real | Float |
Guid | Guid | UniqueIdentifier | Guid | UniqueIdentifier | 原始 |
Int16 | Int16 | SmallInt | SmallInt | SmallInt | Int16 |
Int32 | Int32 | int | int | int | Int32 |
Int64 | Int64 | BigInt | BigInt | BigInt | Number |
Object | 对象 | 变量 | 变量 | 不支持从 Object 推断 OdbcType。 | Blob |
String | String | NVarChar。 如果字符串大于 NVarChar 的最大大小(4000 个字符),此隐式转换将失败。 对于大于 4000 个字符的字符串,请显式设置 SqlDbType。 | VarWChar | NVarChar | NVarChar |
TimeSpan | 时间 | SQL Server 2008 中的 Time。 SQL Server 2008 以前的 SQL Server 版本不支持从 TimeSpan 推断 SqlDbType 。 | DBTime | 时间 | DateTime |
UInt16 | UInt16 | 不支持从 UInt16 推断 SqlDbType 。 | UnsignedSmallInt | int | UInt16 |
UInt32 | UInt32 | 不支持从 UInt32 推断 SqlDbType 。 | UnsignedInt | BigInt | UInt32 |
UInt64 | UInt64 | 不支持从 UInt64 推断 SqlDbType 。 | UnsignedBigInt | Numeric | Number |
AnsiString | VarChar | VarChar | VarChar | VarChar | |
AnsiStringFixedLength | Char | Char | Char | Char | |
货币 | Money | 货币 | 不支持从 OdbcType 推断 Currency 。 |
Number | |
Date | SQL Server 2008 中的 Date。 SQL Server 2008 以前的 SQL Server 版本不支持从 Date 推断 SqlDbType 。 | DBDate | Date | DateTime | |
SByte | 不支持从 SByte 推断 SqlDbType 。 | TinyInt | 不支持从 SByte 推断 OdbcType 。 |
SByte | |
StringFixedLength | NChar | WChar | NChar | NChar | |
时间 | SQL Server 2008 中的 Time。 SQL Server 2008 以前的 SQL Server 版本不支持从 Time 推断 SqlDbType 。 | DBTime | 时间 | DateTime | |
VarNumeric | 不支持从 VarNumeric 推断 SqlDbType 。 | VarNumeric | 不支持从 VarNumeric 推断 OdbcType 。 |
Number | |
用户定义类型(带有 SqlUserDefinedAggregateAttribute的对象) | 对象或字符串,具体取决于提供程序(SqlClient 始终返回对象,Odbc 始终返回字符串,而 OleDb 托管数据提供程序可查看两者中的任何一个 | 如果存在 SqlUserDefinedTypeAttribute ,则为 SqlDbType.Udt;否则为 Variant | OleDbType.VarWChar(如果值为 null),否则为 OleDbType.Variant。 | OdbcType.NVarChar | 不支持 |
注意
从小数转换到其他类型是缩窄转换,这种转换会将小数值舍入到最近的接近零的整数值。 如果无法以目标类型表示转换结果,则会引发 OverflowException 。
注意
将空参数值发送到服务器时,必须指定 DBNull,而不是 null
(在 Visual Basic 中为 Nothing
)。 系统中的 null 值是一个不具有任何值的空对象。 DBNull 用于表示 null 值。 有关数据库 null 值的详细信息,请参阅 Handling Null Values。
派生参数信息
还可以使用 DbCommandBuilder
类从存储过程派生参数。 SqlCommandBuilder
和 OleDbCommandBuilder
类都提供了静态方法 DeriveParameters
,该静态方法将自动使用存储过程中的参数信息填充 Command 对象的 Parameters 集合。 请注意, DeriveParameters
会覆盖此命令的任何现有参数信息。
注意
派生参数信息会影响性能,因为它需要对数据源进行额外的往返访问,以检索信息。 如果参数信息在设计时是已知的,则可以通过显式设置参数来提高应用程序的性能。
有关详细信息,请参阅使用 CommandBuilders 生成命令。
对 SqlCommand 和存储过程使用参数
在数据驱动的应用程序中,存储过程具有许多优势。 通过利用存储过程,数据库操作可以包装在单个命令中,为获取最佳性能而进行优化并通过附加的安全性得到增强。 尽管可以通过在 SQL 语句中传递后接参数自变量的存储过程名称来调用相应的存储过程,但如果使用 ADO.NET DbCommand 对象的 Parameters 集合,则可以让你更为明确地定义存储过程参数,并访问输出参数和返回值。
注意
参数化语句通过使用 sp_executesql
在服务器上执行,从而允许重复使用查询计划。 sp_executesql
批处理命令中的本地光标或变量对于调用 sp_executesql
的批处理命令是不可见的。 数据库上下文中的更改只持续到 sp_executesql
语句的结尾。 有关详细信息,请参阅 sp_executesql (Transact-SQL)。
对 SqlCommand 使用参数以执行 SQL Server 存储过程时,添加到 Parameters 集合中的参数的名称必须与存储过程中参数标记的名称相匹配。 适用于 SQL Server 的 .NET Framework 数据提供程序不支持使用问号 (?) 占位符向 SQL 语句或存储过程传递参数。 它将存储过程中的参数视为命名参数,并搜索匹配的参数标记。 例如,通过使用名为 CustOrderHist
的参数定义 @CustomerID
存储过程。 您的代码在执行该存储过程时,它也必须使用名为 @CustomerID
的参数。
CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)
示例
此示例演示了如何调用 Northwind
示例数据库中的 SQL Server 存储过程。 存储过程的名称为 dbo.SalesByCategory
,它具有名为 @CategoryName
的输入参数,其数据类型为 nvarchar(15)
。 该代码在 using 代码块内创建一个新 SqlConnection ,以便在过程结束时释放连接。 会创建 SqlCommand 和 SqlParameter 对象,并设置其属性。 SqlDataReader 会执行 SqlCommand
并从存储过程返回结果集,以在控制台窗口中显示相关输出。
注意
您可以选择使用任一重载构造函数在一个语句中设置多个属性,而不是创建 SqlCommand
和 SqlParameter
对象,然后在各个语句中设置属性。
static void GetSalesByCategory(string connectionString,
string categoryName)
{
using (SqlConnection connection = new(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new()
{
Connection = connection,
CommandText = "SalesByCategory",
CommandType = CommandType.StoredProcedure
};
// Add the input parameter and set its properties.
SqlParameter parameter = new()
{
ParameterName = "@CategoryName",
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input,
Value = categoryName
};
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
// Open the connection and execute the reader.
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();
}
}
}
Shared Sub GetSalesByCategory(ByVal connectionString As String, _
ByVal categoryName As String)
Using connection As New SqlConnection(connectionString)
' Create the command and set its properties.
Dim command As SqlCommand = New SqlCommand()
command.Connection = connection
command.CommandText = "SalesByCategory"
command.CommandType = CommandType.StoredProcedure
' Add the input parameter and set its properties.
Dim parameter As New SqlParameter()
parameter.ParameterName = "@CategoryName"
parameter.SqlDbType = SqlDbType.NVarChar
parameter.Direction = ParameterDirection.Input
parameter.Value = categoryName
' Add the parameter to the Parameters collection.
command.Parameters.Add(parameter)
' Open the connection and execute the reader.
connection.Open()
Using reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
Do While reader.Read()
Console.WriteLine("{0}: {1:C}", _
reader(0), reader(1))
Loop
Else
Console.WriteLine("No rows returned.")
End If
End Using
End Using
End Sub
对 OleDbCommand 或 OdbcCommand 使用参数
对 OleDbCommand 或 OdbcCommand使用参数时,添加到 Parameters
集合中的参数的顺序必须与在存储过程中定义的参数的顺序相匹配。 适用于 OLE DB 的 .NET Framework 数据提供程序和适用于 ODBC 的 .NET Framework 数据提供程序将存储过程中的参数视为占位符,并按顺序应用参数值。 此外,返回值参数必须为添加到 Parameters
集合中的第一批参数。
适用于 OLE DB 的 .NET Framework 数据提供程序和适用于 ODBC 的 .NET Framework 数据提供程序不支持使用命名参数向 SQL 语句或存储过程传递参数。 在此情况下,必须使用问号 (?) 占位符,如以下示例所示。
SELECT * FROM Customers WHERE CustomerID = ?
因此,将 Parameter
对象添加到 Parameters
集合的顺序必须直接与参数的问号 (?) 占位符的位置相对应。
OleDb 示例
Dim command As OleDbCommand = New OleDbCommand( _
"SampleProc", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As OleDbParameter = command.Parameters.Add( _
"RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue
parameter = command.Parameters.Add( _
"@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"
parameter = command.Parameters.Add( _
"@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output
OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;
OleDbParameter parameter = command.Parameters.Add(
"RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add(
"@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";
parameter = command.Parameters.Add(
"@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;
Odbc 示例
Dim command As OdbcCommand = New OdbcCommand( _
"{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = command.Parameters.Add( _
"@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"
parameter = command.Parameters.Add( _
"@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output
OdbcCommand command = new OdbcCommand( _
"{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;
OdbcParameter parameter = command.Parameters.Add( _
"RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add( _
"@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";
parameter = command.Parameters.Add( _
"@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;