Настройте параметры с помощью Microsoft. Data.SqlClient

Параметры передают значения команде отдельно от её Transact-SQL (T-SQL) текста. Используйте объекты SqlParameter вместо конкатенации или интерполяции значений в SQL-операторы. Параметры помогают защититься от SQL-инъекций и сохраняют тип значения в базе данных.

Примеры в этой статье предполагают, что открытый SqlConnection с именем connection нацелен на образцовую базу данных AdventureWorks.

Примеры C# требуют using Microsoft.Data.SqlClient;. Примеры, которые используют SqlDbType также требуют using System.Data;.

Добавить входные параметры

Добавьте параметр для каждого заполнителя значения в тексте команды. Укажите тип параметра SQL Server и присвоите ему значение:

const string sql = """
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
      AND ListPrice >= @minimumPrice
    ORDER BY ListPrice;
    """;

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}

Используйте тот же регистр для имени параметра в тексте команды и в SqlParameter. SQL Server сопоставляет имена переменных, используя серверную колляцию, поэтому несоответствие приводит к ошибке «Must declare the scalar variable» на сервере с чувствительной к регистру колляцией.

Работа с заполнителями параметров

Microsoft. Data.SqlClient использует именованные параметры в виде @parameterName. Позиционные заполнители со знаком вопроса (?) не поддерживаются.

Заполнители параметров представляют значения, а не идентификаторы SQL или синтаксис. Нельзя использовать параметр для имени таблицы, имени столбца, направления сортировки или целого SQL-фрагмента. Если приложение динамически выбирает идентификатор, проверьте его по списку разрешений и добавьте его в текст команды только после проверки. Продолжайте передавать все значения данных как параметры.

Для текстовых команд Microsoft. Data.SqlClient отправляет параметризованные операторы на SQL Server через sp_executesql. SQL Server может повторно использовать план выполнения, если текст оператора и определения параметров остаются прежними.

Указание типов данных параметров

Устанавливайте SqlDbType явно, когда приложение знает целевой тип SQL Server. Также устанавливайте метаданные, специфичные для типа, такие Sizeкак , Precision, и Scale при необходимости.

Этот пример соответствует столбцам nvarchar(50) и datetime в Person.Person:

const string sql = """
    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE LastName = @lastName
      AND ModifiedDate >= @modifiedSince
    ORDER BY BusinessEntityID;
    """;

using var command = new SqlCommand(sql, connection);

SqlParameter lastName = command.Parameters.Add(
    "@lastName",
    SqlDbType.NVarChar,
    50);
lastName.Value = "O'Brien";

SqlParameter modifiedSince = command.Parameters.Add(
    "@modifiedSince",
    SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);

Сопоставление метаданных параметров с целевым столбцом позволяет избежать неявных преобразований и предотвращает изменение определения параметра из-за изменения длины строки или десятичного масштаба.

Для десятичных значений установите точность и масштаб явно. Столбец Weight в Production.Product использует decimal(8, 2):

const string sql = """
    SELECT ProductID, Name, Weight
    FROM Production.Product
    WHERE Weight >= @minimumWeight
    ORDER BY Weight;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
    "@minimumWeight",
    SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;

Передавать значения NULL базы данных

Назначьте Value для отправки базы данных NULL. C# null не соответствует SQL Server NULL для обычных скалярных параметров:

const string sql = """
    SELECT SalesOrderID, OrderDate, SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
    ORDER BY SalesOrderID;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
    "@salesPersonId",
    SqlDbType.Int);

int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
    ? selectedSalesPersonId.Value
    : DBNull.Value;

Выборочно используйте выведенные метаданные

Метод AddWithValue выводит метаданные параметров из значения во время выполнения. Инференс удобен, когда предполагаемые метаданные стабильны и соответствуют типу базы данных. Он может создавать различные определения параметров при изменении длины строк, десятичной точности или других характеристик значения.

Предпочтительно использовать Add с явным SqlDbType для производственных команд, целевая схема которых известна. Не передавайте C# null в AddWithValue; указывайте тип и вместо этого назначьте DBNull.Value.

Повторно использовать параметры команды

Создайте параметр один раз и измените только его Value при выполнении одной и той же команды с разными значениями:

const string sql = """
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
    "@subcategoryId",
    SqlDbType.Int);

foreach (int id in new[] { 1, 2, 3 })
{
    subcategoryId.Value = id;

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine($"{id}: {reader.GetString(0)}");
    }
}

Не добавляйте другой параметр с таким же именем при каждом выполнении. Используй существующий параметр или очисти коллекцию перед её повторной сборкой.

Установить направление параметра

Свойство Direction определяет, как команда использует параметр. Установите значение ParameterDirection:

Direction Использовать
Input Отправляет значение в инструкцию или сохранённую процедуру. Это значение по умолчанию.
Output Получает значение из выходного параметра сохранённой процедуры.
InputOutput Отправляет начальное значение и получает обновлённое значение.
ReturnValue Получает целочисленное значение из оператора T-SQL RETURN .

Для параметров вывода, входа/вывода и возвратных значений читайте Value только после завершения выполнения команды. Если команда возвращает строки, закройте или освободите SqlDataReader перед чтением этих значений.

Для полных примеров хранимых процедур см. Изменение данных с помощью хранимых процедур.

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

Установите StoredProcedure и используйте имена параметров процедуры. В этом примере вызывается процедура dbo.uspGetEmployeeManagers, включённая в AdventureWorks:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

Имя параметра должно совпадать с именем, объявленным в хранимой процедуре.

Настройка табличнозначных параметров

Параметр с табличным значением отправляет несколько строк в сохранённую процедуру или параметризованную инструкцию. Для настройки одной:

  1. Задайте SqlDbType.Structured.
  2. Установите для TypeName значение "определяемый пользователем тип таблицы базы данных".
  3. Назначьте источник строки DataTable, DbDataReader или IEnumerable<SqlDataRecord>.

DataTable с определёнными столбцами и без строк отправляет пустую таблицу. Пустой IEnumerable<SqlDataRecord> вызывает ArgumentException; укажите C# null для отправки нулевых строк для этой формы ввода. Не назначайте DBNull.Value параметру с табличным значением.

Для создания типов, отображения строк и полных примеров см. параметры с табличными значениями.

Настроить потоковые значения

Присваивайте Stream, TextReader или XmlReader к Value, чтобы постепенно увеличивать значение на большую величину. Для передачи с ограничением памяти используйте тип параметра (max), xml или json. Microsoft.Data.SqlClient буферизирует источник перед выполнением для несимвольных(max) и бинарных типов, а также для устаревших типов больших объектов.

Тип параметра должен соответствовать значению:

Value Совместимые SqlDbType значения
Stream Binary, VarBinary, или устаревший тип Image
TextReader Char, NChar, NVarChar, VarChar, устаревшие типы Text и NText, Xml, или Json
XmlReader Char, NChar, NVarChar, VarChar, устаревшие типы Text и NText, Xml, или Json

Microsoft.Data.SqlClient выдает ArgumentException, InvalidCastException или SqlTypeException, когда тип объекта SqlDbType или заявленный размер несовместимы. Полные примеры загрузки и скачивания смотрите в разделе поддержка потоковой передачи SqlClient.

Получение сведений о параметрах

DeriveParametersзапрашивает в SQL Server метаданные параметров хранимой процедуры и заменяет существующий набор параметров команды:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;

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

Связанные материалы

  • Последнее обновление: