Поделиться через


Настройка параметров и типов данных параметров (ADO.NET)

Обновлен: November 2007

Объекты команды используют параметры для передачи значений в выражения SQL или хранимые процедуры, обеспечивая проверку типов и правильности. В отличие от текста команд, входные параметры обрабатываются как буквенные значения, а не как исполняемый код. Это помогает защищаться от атак путем внедрения кода SQL, при которых злоумышленник вставляет в инструкцию SQL команду, ставящую под угрозу безопасность сервера.

Параметризованные команды также позволяют повысить производительность при выполнении запроса, поскольку при их использовании сервер баз данных может точно сопоставить входящей команде правильный кэшированный план запроса. Дополнительные сведения см. в разделах Кэширование и повторное использование плана выполнения и Повторное использование параметров и плана выполнения электронной документации по SQL Server. Помимо повышения безопасности и производительности параметризованные команды обеспечивают удобный метод организации значений, передающихся в источник данных.

Объект 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.

Тип поставщика данных .NET Framework объекта Parameter выводится из типа .NET Framework значения Value объекта Parameter или из DbType объекта Parameter. Следующая таблица показывает тип Parameter, выводимый из объекта, переданного как значение Parameter, или указанного значения DbType.

Тип .NET Framework

DbType

SqlDbType

OleDbType

OdbcType

OracleType

bool

Boolean

Bit

Boolean

Bit

Byte

byte

Byte

TinyInt

UnsignedTinyInt

TinyInt

Byte

byte[]

Binary

VarBinary. Это неявное преобразование не будет выполнено, если размер массива байтов превышает максимальный размер VarBinary, равный 8000 байт. Для массивов байтов, размер которых превышает 8000 байт, необходимо явно указать тип SqlDbType.

VarBinary

Binary

Raw

char

 

Вывод типа SqlDbType из типа char не поддерживается.

Char

Char

Byte

DateTime

DateTime

DateTime

DBTimeStamp

DateTime

DateTime

DateTimeOffset

DateTimeOffset

Тип DateTimeOffset в SQL Server 2008. Вывод типа SqlDbType из типа DateTimeOffset не поддерживается в версиях SQL Server до SQL Server 2008.

DateTime

Decimal

Decimal

Decimal

Decimal

Numeric

Number

double

Double

Float

Double

Double

Double

float

Single

Real

Single

Real

Float

Guid

Guid

UniqueIdentifier

Guid

UniqueIdentifier

Raw

Int16

Int16

SmallInt

SmallInt

SmallInt

Int16

Int32

Int32

int

int

int

Int32

Int64

Int64

BigInt

BigInt

BigInt

Number

object

Object

Variant

Variant

Вывод типа OdbcType из типа Object не поддерживается.

Blob

string

String

NVarChar. Это неявное преобразование завершится ошибкой, если строка превышает максимальный размер для типа NVarChar (4000 символов). Для строк длиннее 4000 символов явно установите значение SqlDbType.

VarWChar

NVarChar

NVarChar

TimeSpan

Time

Тип Time в SQL Server 2008. Вывод типа SqlDbType из типа TimeSpan не поддерживается в версиях SQL Server до SQL Server 2008.

DBTime

Time

DateTime

UInt16

UInt16

Вывод типа SqlDbType из типа UInt16 не поддерживается.

UnsignedSmallInt

int

UInt16

UInt32

UInt32

Вывод типа SqlDbType из типа UInt32 не поддерживается.

UnsignedInt

BigInt

UInt32

UInt64

UInt64

Вывод типа SqlDbType из типа UInt64 не поддерживается.

UnsignedBigInt

Numeric

Number

 

AnsiString

VarChar

VarChar

VarChar

VarChar

 

AnsiStringFixedLength

Char

Char

Char

Char

 

Currency

Money

Currency

Вывод типа OdbcType из типа Currency не поддерживается.

Number

 

Date

Тип Date в SQL Server 2008. Вывод типа SqlDbType из типа Date не поддерживается в версиях SQL Server до SQL Server 2008.

DBDate

Date

DateTime

 

SByte

Вывод типа SqlDbType из типа SByte не поддерживается.

TinyInt

Вывод типа OdbcType из типа SByte не поддерживается.

SByte

 

StringFixedLength

NChar

WChar

NChar

NChar

 

Time

Тип Time в SQL Server 2008. Вывод типа SqlDbType из типа Time не поддерживается в версиях SQL Server до SQL Server 2008.

DBTime

Time

DateTime

 

VarNumeric

Вывод типа SqlDbType из типа VarNumeric не поддерживается.

VarNumeric

Вывод типа OdbcType из типа VarNumeric не поддерживается.

Number

yy6y35y8.alert_note(ru-ru,VS.90).gifПримечание.

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

yy6y35y8.alert_note(ru-ru,VS.90).gifПримечание.

При передаче на сервер значения null в качестве параметра необходимо указать значение DBNull, а не null (Nothing в Visual Basic). Значение null в системе — это пустой объект, не имеющий значения. Для представления значений null используется тип DBNull. Дополнительные сведения о значении null базы данных см. в разделе Обработка значений Null (ADO.NET)

Выведение информации о параметрах

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

yy6y35y8.alert_note(ru-ru,VS.90).gifПримечание.

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

Дополнительные сведения см. в разделе Создание команд с помощью построителей команд (ADO.NET).

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

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

yy6y35y8.alert_note(ru-ru,VS.90).gifПримечание.

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

Если параметры используются с объектом 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 и возвращает результирующий набор из хранимой процедуры, отображая выходные данные в окне консоли.

yy6y35y8.alert_note(ru-ru,VS.90).gifПримечание.

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

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()
        Dim 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 Sub
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();
        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();
    }
}

Использование параметров с OleDbCommand или OdbcCommand

Если с объектами 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;

См. также

Основные понятия

Параметры DataAdapter (ADO.NET)

Другие ресурсы

Команды и параметры (ADO.NET)

Сопоставления типов данных в ADO.NET