Настройка параметров и типов данных параметров

Объекты команды используют параметры для передачи значений в выражения 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 объекта выводится из платформа .NET Framework типа ValueParameter объекта или из DbTypeParameter объекта служб. Следующая таблица показывает тип 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 Вывод типа SqlDbType из типа char не поддерживается. Char Char Byte
DateTime Дата и время Дата и время DBTimeStamp Дата и время Дата/время
DateTimeOffset DateTimeOffset Тип DateTimeOffset в SQL Server 2008. Вывод типа SqlDbType из типа DateTimeOffset не поддерживается в версиях SQL Server до SQL Server 2008. Дата и время
Decimal Decimal Decimal Decimal Числовой Число
Double Double Float Double Double Double
Single Single Real Один Real Float
Guid Guid UniqueIdentifier Guid UniqueIdentifier Необработанное
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BigInt BigInt BigInt Число
Object Объект Variant Variant Вывод типа OdbcType из типа Object не поддерживается. BLOB-объект
String Строка NVarChar. Это неявное преобразование завершится ошибкой, если строка превышает максимальный размер для типа NVarChar (4000 символов). Для строк длиннее 4000 символов явно установите значение SqlDbType. VarWChar NVarChar NVarChar
TimeSpan Время Тип Time в SQL Server 2008. Вывод типа SqlDbType из типа TimeSpan не поддерживается в версиях SQL Server до SQL Server 2008. DBTime Time Дата и время
UInt16 UInt16 Вывод типа SqlDbType из типа UInt16 не поддерживается. UnsignedSmallInt Int UInt16
UInt32 UInt32 Вывод типа SqlDbType из типа UInt32 не поддерживается. UnsignedInt BigInt UInt32
UInt64 UInt64 Вывод типа SqlDbType из типа UInt64 не поддерживается. UnsignedBigInt Числовой Число
AnsiString VarChar VarChar VarChar VarChar
AnsiStringFixedLength Char Char Char Char
Валюта Money Валюта Вывод типа OdbcType из типа Currency не поддерживается. Число
Дата Тип Date в SQL Server 2008. Вывод типа SqlDbType из типа Date не поддерживается в версиях SQL Server до SQL Server 2008. DBDate Дата Дата и время
SByte Вывод типа SqlDbType из типа SByte не поддерживается. TinyInt Вывод типа OdbcType из типа SByte не поддерживается. SByte
StringFixedLength NCHAR WChar NCHAR NCHAR
Время Тип Time в SQL Server 2008. Вывод типа SqlDbType из типа Time не поддерживается в версиях SQL Server до SQL Server 2008. DBTime Time Дата и время
VarNumeric Вывод типа SqlDbType из типа VarNumeric не поддерживается. VarNumeric Вывод типа OdbcType из типа VarNumeric не поддерживается. Число
определяемый пользователем тип (объект с SqlUserDefinedAggregateAttribute Объект или строка в зависимости от поставщика (SqlClient всегда возвращает объект, ODBC всегда возвращает строку, а поставщик данных, управляемый OleDb, может вернуть и то и другое). SqlDbType.Udt, если присутствует SqlUserDefinedTypeAttribute , в противном случае Variant OleDbType.VarWChar (при значении NULL), в противном случае OleDbType.Variant. OdbcType.NVarChar Не поддерживается

Примечание

Преобразования из типа decimal в другие типы являются сужающими. Они округляют десятичное значение до ближайшего целого в направлении нуля. Если результат преобразования нельзя представить в целевом типе, возникает исключение OverflowException .

Примечание

Во время отправки значения параметра NULL на сервер нужно указать DBNull, а не null (Nothing в Visual Basic). В системе значение null - это пустой объект, не имеющий значения. Для представления значений null используется типDBNull . Дополнительные сведения об значениях NULL базы данных см. в разделе Обработка значений NULL.

Выведение сведений о параметрах

Информацию о параметрах можно вывести из хранимой процедуры с помощью класса DbCommandBuilder . Оба класса, SqlCommandBuilder и OleDbCommandBuilder , обеспечивают статический метод DeriveParameters, который автоматически заполняет коллекцию параметров объекта команд, использующего информацию о параметрах от хранимой процедуры. Обратите внимание, что метод DeriveParameters перезаписывает существующую информацию о параметрах для команды.

Примечание

Выведение информации о параметрах снижает производительность, так как для этого требуется дополнительный обмен данных с источником данных. Если информация о параметрах известна во время разработки, можно увеличить производительность приложения, задав параметры явным образом.

Дополнительные сведения см. в статье Создание команд с помощью классов CommandBuilder.

Использование параметров с SqlCommand и хранимой процедурой

Хранимые процедуры дают множество преимуществ в приложениях, управляемых данными. С помощью хранимых процедур операции с базой данных можно инкапсулировать в одну команду, оптимизированную для производительности и обладающую повышенной безопасностью. Хотя хранимые процедуры можно вызывать и с помощью инструкции SQL, указывая в ней имя процедуры и ее аргументы, использование коллекции Parameters объекта ADO.NET DbCommand позволяет более явно задавать параметры процедуры, а также обращаться к выходным параметрам и возвращаемым значениям.

Примечание

Параметризованные инструкции выполняются на сервере с помощью хранимой процедуры sp_executesql, которая позволяет повторно использовать планы запросов. Локальные курсоры или переменные в пакете sp_executesql недоступны пакету, вызвавшему sp_executesql. Изменения в контексте базы данных длятся только до завершения выполнения инструкции sp_executesql . Дополнительные сведения см. в статье sp_executesql (Transact-SQL).

Если параметры используются с объектом SqlCommand для выполнения хранимой процедуры SQL Server, то имена параметров, добавляемых в коллекцию Parameters , должны соответствовать именам маркеров параметров в хранимой процедуре. поставщик данных платформа .NET Framework для SQL Server не поддерживает заполнитель вопросительного знака (?) для передачи параметров в инструкцию SQL или хранимую процедуру. Он обрабатывает параметры в хранимой процедуре как именованные параметры и ищет соответствующие маркеры параметров. Например, хранимая процедура CustOrderHist определяется с использованием параметра @CustomerID. Когда программа выполняет эта хранимую процедуру, она также должна использовать параметр @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Пример

Этот пример показывает, как вызвать хранимую процедуру SQL Server в образце базы данных Northwind . Имя хранимой процедуры – dbo.SalesByCategory . Она имеет входной параметр @CategoryName с типом данных nvarchar(15). Код создает создает новый объект класса SqlConnection в блоке Using, чтобы в конце процедуры соединение удалялось. Создаются объекты SqlCommand и SqlParameter устанавливаются их свойства. Объект класса SqlDataReader выполняет SqlCommand и возвращает результирующий набор из хранимой процедуры, отображая выходные данные в окне консоли.

Примечание

Вместо того, чтобы создавать объекты SqlCommand и SqlParameter и затем задавать их свойства в отдельных инструкциях, можно использовать один из перегруженных конструкторов и задать свойства в одной инструкции.

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 или Одбккомманд

Если с объектами OleDbCommand или OdbcCommandиспользуются параметры, порядок параметров, добавляемых в коллекцию Parameters , должен соответствовать порядку параметров, заданных в хранимой процедуре. поставщик данных платформа .NET Framework для OLE DB и платформа .NET Framework поставщика данных для ODBC рассматривает параметры в хранимой процедуре как местозаполнители и применяет значения параметров по порядку. Кроме того, параметры возвращаемых значений должны быть первыми параметрами, добавляемыми в коллекцию Parameters .

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

См. также