Настройка параметров и типы данных параметров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.

Указание свойства ParameterDirectionSupplying 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 определенное значение DbType.You 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 Value из Parameter объекта, или из DbType из Parameter объекта.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 байт, необходимо явно указать SqlDbType.This 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 Вывод типа SqlDbType из типа char не поддерживается.Inferring a SqlDbType from char is not supported. CharChar CharChar ByteByte
DateTime DateTimeDateTime DateTimeDateTime DBTimeStampDBTimeStamp DateTimeDateTime DateTimeDateTime
DateTimeOffset DateTimeOffsetDateTimeOffset Тип DateTimeOffset в SQL Server 2008.DateTimeOffset in SQL Server 2008. Вывод типа SqlDbType из типа DateTimeOffset не поддерживается в версиях SQL Server до SQL Server 2008.Inferring 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 ObjectObject ВариантVariant ВариантVariant Вывод типа OdbcType из типа Object не поддерживается.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 символов явно установите значение SqlDbType.For strings larger than 4000 characters, explicitly set the SqlDbType. VarWCharVarWChar NVarCharNVarChar NVarCharNVarChar
TimeSpan ВремяTime Тип Time в SQL Server 2008.Time in SQL Server 2008. Вывод типа SqlDbType из типа TimeSpan не поддерживается в версиях SQL Server до SQL Server 2008.Inferring a SqlDbType from TimeSpan is not supported in versions of SQL Server earlier than SQL Server 2008. DBTimeDBTime ВремяTime DateTimeDateTime
UInt16 UInt16UInt16 Вывод типа SqlDbType из типа UInt16 не поддерживается.Inferring a SqlDbType from UInt16 is not supported. UnsignedSmallIntUnsignedSmallInt IntInt UInt16UInt16
UInt32 UInt32UInt32 Вывод типа SqlDbType из типа UInt32 не поддерживается.Inferring a SqlDbType from UInt32 is not supported. UnsignedIntUnsignedInt BigIntBigInt UInt32UInt32
UInt64 UInt64UInt64 Вывод типа SqlDbType из типа UInt64 не поддерживается.Inferring a SqlDbType from UInt64 is not supported. UnsignedBigIntUnsignedBigInt NumericNumeric ЧислоNumber
AnsiStringAnsiString VarCharVarChar VarCharVarChar VarCharVarChar VarCharVarChar
AnsiStringFixedLengthAnsiStringFixedLength CharChar CharChar CharChar CharChar
ВалютаCurrency MoneyMoney ВалютаCurrency Вывод типа OdbcType из типа Currency не поддерживается.Inferring an OdbcType from Currency is not supported. ЧислоNumber
ДатаDate Тип Date в SQL Server 2008.Date in SQL Server 2008. Вывод типа SqlDbType из типа Date не поддерживается в версиях SQL Server до SQL Server 2008.Inferring a SqlDbType from Date is not supported in versions of SQL Server earlier than SQL Server 2008. DBDateDBDate ДатаDate DateTimeDateTime
SByteSByte Вывод типа SqlDbType из типа SByte не поддерживается.Inferring a SqlDbType from SByte is not supported. TinyIntTinyInt Вывод типа OdbcType из типа SByte не поддерживается.Inferring an OdbcType from SByte is not supported. SByteSByte
StringFixedLengthStringFixedLength NCharNChar WCharWChar NCharNChar NCharNChar
ВремяTime Тип Time в SQL Server 2008.Time in SQL Server 2008. Вывод типа SqlDbType из типа Time не поддерживается в версиях SQL Server до SQL Server 2008.Inferring a SqlDbType from Time is not supported in versions of SQL Server earlier than SQL Server 2008. DBTimeDBTime ВремяTime DateTimeDateTime
VarNumericVarNumeric Вывод типа SqlDbType из типа VarNumeric не поддерживается.Inferring a SqlDbType from VarNumeric is not supported. VarNumericVarNumeric Вывод типа OdbcType из типа VarNumeric не поддерживается.Inferring an OdbcType from VarNumeric is not supported. ЧислоNumber
определяемый пользователем тип (объект с SqlUserDefinedAggregateAttributeuser-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 SqlDbType.Udt, если присутствует SqlUserDefinedTypeAttribute , в противном случае 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

Примечание

Преобразования из типа decimal в другие типы являются сужающими. Они округляют десятичное значение до ближайшего целого в направлении нуля.Conversions from decimal to other types are narrowing conversions that round the decimal value to the nearest integer value toward zero. Если результат преобразования нельзя представить в целевом типе, возникает исключение OverflowException .If 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. Для представления значений null используется типDBNull .DBNull is used to represent null values. Дополнительные сведения о значении NULL базы данных см. в разделе Handling Null Values.For 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. Оба класса, SqlCommandBuilder и OleDbCommandBuilder , обеспечивают статический метод DeriveParameters, который автоматически заполняет коллекцию параметров объекта команд, использующего информацию о параметрах от хранимой процедуры.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 с помощью Parameters коллекцию ADO.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, которая позволяет повторно использовать планы запросов.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. Поставщик данных .NET Framework для SQL Server не поддерживает местозаполнитель вопросительный знак (?) для передачи параметров в инструкции 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

Этот пример показывает, как вызвать хранимую процедуру SQL Server в образце базы данных Northwind .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). Код создает создает новый объект класса SqlConnection в блоке Using, чтобы в конце процедуры соединение удалялось.The code creates a new SqlConnection inside a using block so that the connection is disposed when the procedure ends. Создаются объекты SqlCommand и SqlParameter устанавливаются их свойства.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.

Примечание

Вместо того, чтобы создавать объекты SqlCommand и SqlParameter и затем задавать их свойства в отдельных инструкциях, можно использовать один из перегруженных конструкторов и задать свойства в одной инструкции.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 или OdbcCommandUsing parameters with an OleDbCommand or OdbcCommand

Если с объектами OleDbCommand или OdbcCommandиспользуются параметры, порядок параметров, добавляемых в коллекцию 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. Поставщик данных .NET Framework для OLE DB и поставщик данных .NET Framework для ODBC обрабатывают параметры в хранимой процедуре как местозаполнители и применяют значения параметров в порядке.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.

Поставщик данных .NET Framework для OLE DB и поставщик данных .NET Framework для ODBC не поддерживают именованные параметры для передачи параметров в инструкции 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.

Пример OleDbOleDb 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;

Пример OdbcOdbc 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