配置参数和参数数据类型Configuring parameters and parameter data types

通过提供类型检查和验证,命令对象可使用参数来将值传递给 SQL 语句或存储过程。Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. 与命令文本不同,参数输入被视为文本值,而不是可执行代码。Unlike command text, parameter input is treated as a literal value, not as executable code. 这样可帮助抵御“SQL 注入”攻击,这种攻击的攻击者会将命令插入 SQL 语句,从而危及服务器的安全。This helps guard against "SQL injection" attacks, in which an attacker inserts a command that compromises security on the server into an SQL statement.

参数化命令还可提高查询执行性能,因为它们可帮助数据库服务器将传入命令与适当的缓存查询计划进行准确匹配。Parameterized commands can also improve query execution performance, because they help the database server accurately match the incoming command with a proper cached query plan. 有关详细信息,请参阅执行计划的缓存和重用参数和执行计划重用For more information, see Execution Plan Caching and Reuse and Parameters and Execution Plan Reuse. 除具备安全和性能优势外,参数化命令还提供一种用于组织传递到数据源的值的便捷方法。In addition to the security and performance benefits, parameterized commands provide a convenient method for organizing values passed to a data source.

DbParameter 对象可以通过使用其构造函数来创建,或者也可以通过调用 DbParameterCollection 集合的 Add 方法以将该对象添加到 DbParameterCollection 来创建。A DbParameter object can be created by using its constructor, or by adding it to the DbParameterCollection by calling the Add method of the DbParameterCollection collection. Add 方法将构造函数实参或现有形参对象用作输入,具体取决于数据提供程序。The Add method will take as input either constructor arguments or an existing parameter object, depending on the data provider.

提供 ParameterDirection 属性Supplying the ParameterDirection property

在添加参数时,您必须为输入参数以外的参数提供一个 ParameterDirection 属性。When adding parameters, you must supply a ParameterDirection property for parameters other than input parameters. 下表显示了可用于 ParameterDirection 枚举的 ParameterDirection 值。The following table shows the ParameterDirection values that you can use with the ParameterDirection enumeration.

成员名称Member name 描述Description
Input 该参数为输入参数。The parameter is an input parameter. 这是默认设置。This is the default.
InputOutput 该参数可执行输入和输出。The parameter can perform both input and output.
Output 该参数为输出参数。The parameter is an output parameter.
ReturnValue 该参数表示从某操作(如存储过程、内置函数或用户定义的函数)返回的值。The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

使用参数占位符Working with parameter placeholders

参数占位符的语法取决于数据源。The syntax for parameter placeholders depends on the data source. .NET Framework 数据提供程序以不同方式处理命名和指定参数和参数占位符。The .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently. 此语法是针对某个特定的数据源自定义的,如下表所述。This syntax is customized to a specific data source, as described in the following table.

数据提供程序Data provider 参数命名语法Parameter naming syntax
System.Data.SqlClient @参数名格式使用命名参数。Uses named parameters in the format @parametername.
System.Data.OleDb 使用由问号 (?) 指示的位置参数标记。Uses positional parameter markers indicated by a question mark (?).
System.Data.Odbc 使用由问号 (?) 指示的位置参数标记。Uses positional parameter markers indicated by a question mark (?).
System.Data.OracleClient :参数名 (或 参数名)格式使用命名参数。Uses named parameters in the format :parmname (or parmname).

指定参数数据类型Specifying parameter data types

参数的数据类型是特定于.NET Framework 数据提供程序。The data type of a parameter is specific to the .NET Framework data provider. 指定类型的值转换Parameter到.NET Framework 数据提供程序类型,然后再将值传递到数据源。Specifying the type converts the value of the Parameter to the .NET Framework data provider type before passing the value to the data source. 也可以通过通用的方式指定 Parameter 的类型,方法是将 DbType 对象的 Parameter 属性设置为特定的 DbTypeYou may also specify the type of a Parameter in a generic manner by setting the DbType property of the Parameter object to a particular DbType.

.NET Framework 数据提供程序类型的Parameter从.NET Framework 类型的对象进行推断ValueParameter对象,或从DbTypeParameter对象。The .NET Framework data provider type of a Parameter object is inferred from the .NET Framework type of the Value of the Parameter object, or from the DbType of the Parameter object. 下表显示了根据作为 Parameter 值传递的对象或指定的 Parameter 推断出的 DbType类型。The following table shows the inferred Parameter type based on the object passed as the Parameter value or the specified DbType.

.NET Framework 类型.NET Framework type DbTypeDbType SqlDbTypeSqlDbType OleDbTypeOleDbType OdbcTypeOdbcType OracleTypeOracleType
Boolean BooleanBoolean Bit BooleanBoolean Bit ByteByte
Byte ByteByte TinyIntTinyInt UnsignedTinyIntUnsignedTinyInt TinyIntTinyInt ByteByte
byte[]byte[] 二进制Binary VarBinary。VarBinary. 如果字节数组大于 VarBinary 的最大大小即 8000 个字节,此隐式转换将失败。对于大于 8000 个字节的字节数组,显式设置SqlDbTypeThis implicit conversion will fail if the byte array is larger than the maximum size of a VarBinary, which is 8000 bytes.For byte arrays larger than 8000 bytes, explicitly set the SqlDbType. VarBinaryVarBinary 二进制Binary RawRaw
Char 不支持从 char 推断 SqlDbTypeInferring a SqlDbType from char is not supported. CharChar CharChar ByteByte
DateTime DateTimeDateTime DateTimeDateTime DBTimeStampDBTimeStamp DateTimeDateTime DateTimeDateTime
DateTimeOffset DateTimeOffsetDateTimeOffset SQL Server 2008 中的 DateTimeOffset。DateTimeOffset in SQL Server 2008. SQL Server 2008 以前的 SQL Server 版本不支持从 DateTimeOffset 推断 SqlDbTypeInferring a SqlDbType from DateTimeOffset is not supported in versions of SQL Server earlier than SQL Server 2008. DateTimeDateTime
Decimal 十进制Decimal 十进制Decimal 十进制Decimal NumericNumeric 数字Number
Double DoubleDouble FloatFloat DoubleDouble DoubleDouble DoubleDouble
Single SingleSingle RealReal SingleSingle RealReal FloatFloat
Guid GUIDGuid UniqueIdentifierUniqueIdentifier GUIDGuid UniqueIdentifierUniqueIdentifier RawRaw
Int16 Int16Int16 SmallIntSmallInt SmallIntSmallInt SmallIntSmallInt Int16Int16
Int32 Int32Int32 IntInt IntInt IntInt Int32Int32
Int64 Int64Int64 BigIntBigInt BigIntBigInt BigIntBigInt 数字Number
Object 对象Object 变体Variant 变体Variant 不支持从 Object 推断 OdbcType。Inferring an OdbcType from Object is not supported. BlobBlob
String StringString NVarChar。NVarChar. 如果字符串大于 NVarChar 的最大大小(4000 个字符),此隐式转换将失败。This implicit conversion will fail if the string is larger than the maximum size of an NVarChar, which is 4000 characters. 对于大于 4000 个字符的字符串,请显式设置 SqlDbTypeFor strings larger than 4000 characters, explicitly set the SqlDbType. VarWCharVarWChar NVarCharNVarChar NVarCharNVarChar
TimeSpan 时间Time SQL Server 2008 中的 Time。Time in SQL Server 2008. SQL Server 2008 以前的 SQL Server 版本不支持从 TimeSpan 推断 SqlDbTypeInferring a SqlDbType from TimeSpan is not supported in versions of SQL Server earlier than SQL Server 2008. DBTimeDBTime 时间Time DateTimeDateTime
UInt16 UInt16UInt16 不支持从 UInt16 推断 SqlDbTypeInferring a SqlDbType from UInt16 is not supported. UnsignedSmallIntUnsignedSmallInt IntInt UInt16UInt16
UInt32 UInt32UInt32 不支持从 UInt32 推断 SqlDbTypeInferring a SqlDbType from UInt32 is not supported. UnsignedIntUnsignedInt BigIntBigInt UInt32UInt32
UInt64 UInt64UInt64 不支持从 UInt64 推断 SqlDbTypeInferring a SqlDbType from UInt64 is not supported. UnsignedBigIntUnsignedBigInt NumericNumeric 数字Number
AnsiStringAnsiString VarCharVarChar VarCharVarChar VarCharVarChar VarCharVarChar
AnsiStringFixedLengthAnsiStringFixedLength CharChar CharChar CharChar CharChar
货币Currency MoneyMoney 货币Currency 不支持从 OdbcType 推断 CurrencyInferring an OdbcType from Currency is not supported. 数字Number
日期Date SQL Server 2008 中的 Date。Date in SQL Server 2008. SQL Server 2008 以前的 SQL Server 版本不支持从 Date 推断 SqlDbTypeInferring a SqlDbType from Date is not supported in versions of SQL Server earlier than SQL Server 2008. DBDateDBDate 日期Date DateTimeDateTime
SByteSByte 不支持从 SByte 推断 SqlDbTypeInferring a SqlDbType from SByte is not supported. TinyIntTinyInt 不支持从 SByte 推断 OdbcTypeInferring an OdbcType from SByte is not supported. SByteSByte
StringFixedLengthStringFixedLength NCharNChar WCharWChar NCharNChar NCharNChar
时间Time SQL Server 2008 中的 Time。Time in SQL Server 2008. SQL Server 2008 以前的 SQL Server 版本不支持从 Time 推断 SqlDbTypeInferring a SqlDbType from Time is not supported in versions of SQL Server earlier than SQL Server 2008. DBTimeDBTime 时间Time DateTimeDateTime
VarNumericVarNumeric 不支持从 VarNumeric 推断 SqlDbTypeInferring a SqlDbType from VarNumeric is not supported. VarNumericVarNumeric 不支持从 VarNumeric 推断 OdbcTypeInferring an OdbcType from VarNumeric is not supported. 数字Number
用户定义类型(带有 SqlUserDefinedAggregateAttribute的对象)user-defined type (an object with SqlUserDefinedAggregateAttribute 对象或字符串,具体取决于提供程序(SqlClient 始终返回对象,Odbc 始终返回字符串,而 OleDb 托管数据提供程序可查看两者中的任何一个Object or String, depending the provider (SqlClient always returns an Object, Odbc always returns a String, and the OleDb managed data provider can see either 如果存在 SqlUserDefinedTypeAttribute ,则为 SqlDbType.Udt;否则为 VariantSqlDbType.Udt if SqlUserDefinedTypeAttribute is present, otherwise Variant OleDbType.VarWChar(如果值为 null),否则为 OleDbType.Variant。OleDbType.VarWChar (if value is null) otherwise OleDbType.Variant. OdbcType.NVarCharOdbcType.NVarChar 不受支持not supported

备注

从小数转换到其他类型是缩窄转换,这种转换会将小数值舍入到最近的接近零的整数值。Conversions from decimal to other types are narrowing conversions that round the decimal value to the nearest integer value toward zero. 如果无法以目标类型表示转换结果,则会引发 OverflowExceptionIf the result of the conversion is not representable in the destination type, an OverflowException is thrown.

备注

当你向服务器发送 null 参数值时,则必须指定DBNull,而非null(Nothing在 Visual Basic 中)。When you send a null parameter value to the server, you must specify DBNull, not null (Nothing in Visual Basic). 系统中的 null 值是一个不具有任何值的空对象。The null value in the system is an empty object that has no value. DBNull 用于表示 null 值。DBNull is used to represent null values. 有关数据库 null 值的详细信息,请参阅 Handling Null ValuesFor more information about database nulls, see Handling Null Values.

派生参数信息Deriving parameter information

还可以使用 DbCommandBuilder 类从存储过程派生参数。Parameters can also be derived from a stored procedure using the DbCommandBuilder class. SqlCommandBuilderOleDbCommandBuilder 类都提供了静态方法 DeriveParameters,该静态方法将自动使用存储过程中的参数信息填充 Command 对象的 Parameters 集合。Both the SqlCommandBuilder and OleDbCommandBuilder classes provide a static method, DeriveParameters, which automatically populates the parameters collection of a command object that uses parameter information from a stored procedure. 请注意, DeriveParameters 会覆盖此命令的任何现有参数信息。Note that DeriveParameters overwrites any existing parameter information for the command.

备注

派生参数信息会影响性能,因为它需要对数据源进行额外的往返访问,以检索信息。Deriving parameter information incurs a performance penalty because it requires an additional round trip to the data source to retrieve the information. 如果参数信息在设计时是已知的,则可以通过显式设置参数来提高应用程序的性能。If parameter information is known at design time, you can improve the performance of your application by setting the parameters explicitly.

有关详细信息,请参阅使用 Commandbuilder 生成命令For more information, see Generating Commands with CommandBuilders.

对 SqlCommand 和存储的过程使用参数Using parameters with a SqlCommand and a stored procedure

在数据驱动的应用程序中,存储过程具有许多优势。Stored procedures offer many advantages in data-driven applications. 通过利用存储过程,数据库操作可以包装在单个命令中,为获取最佳性能而进行优化并通过附加的安全性得到增强。By using stored procedures, database operations can be encapsulated in a single command, optimized for best performance, and enhanced with additional security. 虽然可以通过传递存储的过程的名称后接参数自变量的 SQL 语句,通过调用存储的过程ParametersADO.NET 集合DbCommand对象使您更为明确地定义存储的过程参数,还可以访问输出参数和返回值。Although a stored procedure can be called by passing the stored procedure name followed by parameter arguments as an SQL statement, by using the Parameters collection of the ADO.NET DbCommand object enables you to more explicitly define stored procedure parameters, and to access output parameters and return values.

备注

参数化语句在服务器上通过使用 sp_executesql, 执行,sp_executesql 允许重复使用查询计划。Parameterized statements are executed on the server by using sp_executesql, which allows for query plan reuse. sp_executesql 批处理命令中的本地光标或变量对于调用 sp_executesql的批处理命令是不可见的。Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. 数据库上下文中的更改只持续到 sp_executesql 语句的结尾。Changes in database context last only to the end of the sp_executesql statement. 有关详细信息,请参阅sp_executesql (TRANSACT-SQL)For more information, see sp_executesql (Transact-SQL).

SqlCommand 使用参数以执行 SQL Server 存储过程时,添加到 Parameters 集合中的参数的名称必须与存储过程中参数标记的名称相匹配。When using parameters with a SqlCommand to execute a SQL Server stored procedure, the names of the parameters added to the Parameters collection must match the names of the parameter markers in the stored procedure. SQL Server 的.NET Framework 数据提供程序不支持问号 (?) 占位符向 SQL 语句或存储的过程传递参数。The .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to an SQL statement or a stored procedure. 它将存储过程中的参数视为命名参数,并搜索匹配的参数标记。It treats parameters in the stored procedure as named parameters and searches for matching parameter markers. 例如,通过使用名为 CustOrderHist 的参数定义 @CustomerID存储过程。For example, the CustOrderHist stored procedure is defined by using a parameter named @CustomerID. 您的代码在执行该存储过程时,它也必须使用名为 @CustomerID的参数。When your code executes the stored procedure, it must also use a parameter named @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

示例Example

此示例演示了如何调用 Northwind 示例数据库中的 SQL Server 存储过程。This example demonstrates how to call a SQL Server stored procedure in the Northwind sample database. 存储过程的名称为 dbo.SalesByCategory ,它具有名为 @CategoryName 的输入参数,其数据类型为 nvarchar(15)The name of the stored procedure is dbo.SalesByCategory and it has an input parameter named @CategoryName with a data type of nvarchar(15). 该代码在 using 代码块内创建一个新 SqlConnection ,以便在过程结束时释放连接。The code creates a new SqlConnection inside a using block so that the connection is disposed when the procedure ends. 会创建 SqlCommandSqlParameter 对象,并设置其属性。The SqlCommand and SqlParameter objects are created, and their properties set. SqlDataReader 会执行 SqlCommand 并从存储过程返回结果集,以在控制台窗口中显示相关输出。A SqlDataReader executes the SqlCommand and returns the result set from the stored procedure, displaying the output in the console window.

备注

您可以选择使用任一重载构造函数在一个语句中设置多个属性,而不是创建 SqlCommandSqlParameter 对象,然后在各个语句中设置属性。Instead of creating SqlCommand and SqlParameter objects and then setting properties in separate statements, you can instead elect to use one of the overloaded constructors to set multiple properties in a single statement.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = 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 (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 使用参数Using parameters with an OleDbCommand or OdbcCommand

OleDbCommandOdbcCommand使用参数时,添加到 Parameters 集合中的参数的顺序必须与在存储过程中定义的参数的顺序相匹配。When using parameters with an OleDbCommand or OdbcCommand, the order of the parameters added to the Parameters collection must match the order of the parameters defined in your stored procedure. OLE DB 的.NET Framework 数据提供程序和 ODBC 的.NET Framework 数据提供程序中的存储过程的参数视为占位符并应用顺序中的参数值。The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC treat parameters in a stored procedure as placeholders and apply parameter values in order. 此外,返回值参数必须为添加到 Parameters 集合中的第一批参数。In addition, return value parameters must be the first parameters added to the Parameters collection.

OLE DB 的.NET Framework 数据提供程序和 ODBC 的.NET Framework 数据提供程序不支持命名的参数,将参数传递给 SQL 语句或存储的过程。The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement or a stored procedure. 在此情况下,必须使用问号 (?) 占位符,如以下示例所示。In this case, you must use the question mark (?) placeholder, as in the following example.

SELECT * FROM Customers WHERE CustomerID = ?

因此,将 Parameter 对象添加到 Parameters 集合的顺序必须直接与参数的问号 (?)As a result, the order in which Parameter objects are added to the Parameters collection must directly correspond to the position of the ? 占位符的位置相对应。placeholder for the parameter.

OleDb 示例OleDb Example

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 示例Odbc Example

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;

请参阅See also