Настройка параметров
Область применения:
платформа .NET Framework .NET Standard
Объекты команды используют параметры для передачи значений в выражения SQL или хранимые процедуры, обеспечивая проверку типов и правильности. В отличие от текста команд, входные параметры обрабатываются как буквенные значения, а не как исполняемый код. Это поведение помогает защититься от атак путем внедрения кода SQL, в которых злоумышленник вставляет команду, ставящую под угрозу безопасность сервера, в инструкцию SQL.
Параметризованные команды также позволяют повысить производительность при выполнении запроса, поскольку при их использовании сервер баз данных может точно сопоставить входящей команде правильный кэшированных план запроса. Дополнительные сведения см. в статьях Кэширование и повторное использование плана выполнения и Повторное использование параметров и плана выполнения. Помимо повышения безопасности и производительности параметризованные команды обеспечивают удобный метод организации значений, передающихся в источник данных.
Объект DbParameter можно создать при помощи конструктора или путем добавления его в коллекцию DbParameterCollection с помощью метода Add коллекции DbParameterCollection . Метод Add принимает в качестве входных данных либо аргументы конструктора, либо существующий объект параметра - в зависимости от поставщика данных.
Указание свойства ParameterDirection
При добавлении параметров необходимо указать свойство ParameterDirection для параметров, не являющихся входными. В следующей таблице показаны значения ParameterDirection , которые можно использовать с перечислением ParameterDirection .
| Имя участника | Description |
|---|---|
| Input | Параметр является входным. Это значение по умолчанию. |
| InputOutput | Параметр можно использовать как для ввода, так и для вывода. |
| Output | Параметр является выходным. |
| ReturnValue | Параметр представляет значение, возвращаемое как результат операции, например хранимой процедуры, встроенной функции или определяемой пользователем функции. |
Работа с заполнителями параметров
Синтаксис местозаполнителей параметров зависит от источника данных. Поставщик данных Microsoft SqlClient для SQL Server обрабатывает именование, а также установку параметров и заполнителей параметров по-разному. Поставщик данных SqlClient использует именованные параметры в формате @parametername.
Указание типов данных параметров
Тип данных параметра зависит от поставщика данных Microsoft SqlClient для SQL Server. При указании типа значение Parameter преобразуется в поставщик данных типа Microsoft SqlClient для SQL Server перед передачей значения в источник данных. Можно также указать тип Parameter универсальным способом, задав свойству DbType объекта Parameter определенное значение DbType.
Тип поставщика данных Microsoft SqlClient для SQL Server для объекта Parameter выводится из типа .NET Framework Value объекта Parameter или из DbType объекта Parameter. Следующая таблица показывает тип Parameter , выводимый из объекта, переданного как значение Parameter , или указанного значения DbType.
| Тип .NET | DbType | SqlDbType |
|---|---|---|
| Boolean | Boolean |
Bit |
| Byte | Byte |
TinyInt |
byte[] |
Binary |
VarBinary. Это неявное преобразование не будет выполнено, если размер массива байтов превышает максимальный размер VarBinary, равный 8000 байт. Для массивов байтов размером более 8000 байт необходимо явно указать тип SqlDbType. |
| Char | Вывод типа SqlDbType из типа char не поддерживается. | |
| DateTime | DateTime |
DateTime |
| DateTimeOffset | DateTimeOffset |
DateTimeOffset в SQL Server 2008. Вывод типа SqlDbType из типа DateTimeOffset не поддерживается в версиях до SQL Server 2008. |
| Decimal | Decimal |
Decimal |
| Double | Double |
Float |
| Single | Single |
Real |
| Guid | Guid |
UniqueIdentifier |
| Int16 | Int16 |
SmallInt |
| Int32 | Int32 |
Int |
| Int64 | Int64 |
BigInt |
| Object | Object |
Variant |
| String | String |
NVarChar. Это неявное преобразование не будет выполнено, если размер строки превышает максимальный размер NVarChar, равный 4000 байт. Для строк длиннее 4000 символов явно установите значение SqlDbType. |
| TimeSpan | Time |
Time в SQL Server 2008. Вывод типа SqlDbType из типа TimeSpan не поддерживается в версиях до SQL Server 2008. |
| UInt16 | UInt16 |
Вывод типа SqlDbType из типа UInt16 не поддерживается. |
| UInt32 | UInt32 |
Вывод типа SqlDbType из типа UInt32 не поддерживается. |
| UInt64 | UInt64 |
Вывод типа SqlDbType из типа UInt64 не поддерживается. |
AnsiString |
VarChar |
|
AnsiStringFixedLength |
Char |
|
Currency |
Money |
|
Date |
Date в SQL Server 2008. Вывод типа SqlDbType из типа Date не поддерживается в версиях до SQL Server 2008. |
|
SByte |
Вывод типа SqlDbType из типа SByte не поддерживается. |
|
StringFixedLength |
NChar |
|
Time |
Time в SQL Server 2008. Вывод типа SqlDbType из типа Time не поддерживается в версиях до SQL Server 2008. |
|
VarNumeric |
Вывод типа SqlDbType из типа VarNumeric не поддерживается. |
|
| определяемый пользователем тип (объект с SqlUserDefinedAggregateAttribute | SqlClient всегда возвращает объект | SqlDbType.Udt при наличии SqlUserDefinedTypeAttribute. В противном случае — Variant. |
Примечание.
Преобразования из типа decimal в другие типы являются сужающими. Они округляют десятичное значение до ближайшего целого в направлении нуля. Если результат преобразования не может быть представлен в целевом типе, создается исключение OverflowException.
Примечание.
Во время отправки значения параметра NULL на сервер нужно указать DBNull, а не null (Nothing в Visual Basic). Значением NULL в системе является пустой объект, который не имеет значений. Объект DBNull используется для представления значений NULL.
Получение сведений о параметрах
Информацию о параметрах можно вывести из хранимой процедуры с помощью класса DbCommandBuilder . Класс SqlCommandBuilder предоставляет статический метод 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 , должны соответствовать именам маркеров параметров в хранимой процедуре. Поставщик данных Microsoft SqlClient для 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();
}
}
}