Примечание
Для доступа к этой странице требуется авторизация. Вы можете попробовать войти или изменить каталоги.
Для доступа к этой странице требуется авторизация. Вы можете попробовать изменить каталоги.
Объекты команды используют параметры для передачи значений в выражения 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 определяется из типа Value
объекта Parameter
или из DbType
объекта Parameter
. Следующая таблица показывает тип Parameter
, выводимый из объекта, переданного как значение Parameter
, или указанного значения DbType
.
Тип .NET Framework | DbType | SqlDbType | OleDbType | OdbcType | OracleType |
---|---|---|---|---|---|
Boolean | Логический | бит | Логический | бит | Байт |
Byte | Байт | TinyInt | UnsignedTinyInt | TinyInt | Байт |
байт[] | Бинарный | VarBinary. Это неявное преобразование завершится ошибкой, если массив байтов больше максимального размера VarBinary, который составляет 8000 байт. Для массивов байтов больше 8000 байт явно задается SqlDbType. | VarBinary | Бинарный | Необработанные |
Char | Вывод SqlDbType из символа не поддерживается. | Уголь | Уголь | Байт | |
DateTime | Дата и время | Дата и время | DBTimeStamp | Дата и время | Дата и время |
DateTimeOffset | DateTimeOffset (смещение даты и времени) | Тип DateTimeOffset в SQL Server 2008. Вывод значения SqlDbType из значения DateTimeOffset не поддерживается в версиях SQL Server, предшествующих SQL Server 2008. | Дата и время | ||
Decimal | Десятичный | Десятичный | Десятичный | Числовое | Число |
Double | Двойной | Тип с плавающей запятой | Двойной | Двойной | Двойной |
Single | Один | Реальный | Один | Реальный | Тип с плавающей запятой |
Guid | GUID | УникальныйИдентификатор | GUID | УникальныйИдентификатор | Необработанные |
Int16 | Int16 | SmallInt | SmallInt | SmallInt | Int16 |
Int32 | Int32 | int | int | int | Int32 |
Int64 | Int64 | БигИнт | БигИнт | БигИнт | Число |
Object | Объект | Вариант | Вариант | Вывод типа OdbcType из типа Object не поддерживается. | BLOB-объект |
String | Строка | NVarChar. Это неявное преобразование завершится ошибкой, если строка превышает максимальный размер для типа NVarChar (4000 символов). Для строк длиннее 4000 символов явно установите значение SqlDbType. | VarWChar | NVarChar | NVarChar |
TimeSpan | Время | Время в SQL Server 2008. Вывод типа SqlDbType из типа TimeSpan не поддерживается в версиях SQL Server, предшествующих SQL Server 2008. | DBTime | Время | Дата и время |
UInt16 | UInt16 | Вывод типа SqlDbType из типа UInt16 не поддерживается. | UnsignedSmallInt | int | UInt16 |
UInt32 | UInt32 | Определение типа SqlDbType по UInt32 не поддерживается. | Неотрицательное целое число | БигИнт | UInt32 |
UInt64 | UInt64 | Вывод типа SqlDbType из типа UInt64 не поддерживается. | UnsignedBigInt | Числовое | Число |
AnsiString | VarChar | VarChar | VarChar | VarChar | |
AnsiStringFixedLength | Уголь | Уголь | Уголь | Уголь | |
Валюта | Деньги | Валюта | Вывод типа OdbcType из типа Currency не поддерживается. |
Число | |
Дата | Дата в SQL Server 2008. Определение типа SqlDbType на основе типа Date не поддерживается в версиях SQL Server, предшествующих SQL Server 2008 года. | Дата Базы Данных | Дата | Дата и время | |
SByte | Вывод типа SqlDbType из типа SByte не поддерживается. | TinyInt | Вывод типа OdbcType из типа SByte не поддерживается. |
SByte | |
СтрокаФиксированнойДлины | NChar | WChar | NChar | NChar | |
Время | Время в SQL Server 2008. Определение SqlDbType из Time не поддерживается в версиях SQL Server до SQL Server 2008. | DBTime | Время | Дата и время | |
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 в системе является пустой объект, который не имеет значений. Объект DBNull используется для представления значений NULL. Дополнительные сведения о значении NULL базы данных см. в разделе Handling Null Values.
Выведение сведений о параметрах
Информацию о параметрах можно вывести из хранимой процедуры с помощью класса 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(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new()
{
Connection = connection,
CommandText = "SalesByCategory",
CommandType = CommandType.StoredProcedure
};
// Add the input parameter and set its properties.
SqlParameter parameter = new()
{
ParameterName = "@CategoryName",
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input,
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($"{reader[0]}: {reader[1]:C}");
}
}
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
Если с объектами OleDbCommand или OdbcCommand используются параметры, порядок параметров, добавляемых в коллекцию Parameters
, должен соответствовать порядку параметров, определённых в вашей хранимой процедуре. Поставщики данных на платформе .NET Framework для OLE DB и 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;