Табличные параметры

Скачать ADO.NET

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

Доступ к значениям столбца в возвращающих табличное значение параметрах обеспечивается с помощью стандартных инструкций Transact-SQL SELECT. Параметры табличного типа строго типизированы, и их структура автоматически проверяется. Размер возвращающих табличное значение параметров ограничен только объемом памяти сервера.

Примечание.

В параметре, возвращающем табличное значение, невозможно вернуть данные. Параметры, имеющие табличное значение, используются только для ввода; ключевое слово OUTPUT не поддерживается.

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

Ресурс	Описание
Использование параметров, возвращающих табличные значения (ядро СУБД)	Описывается создание и использование табличных параметров.
Создание определяемого пользователем типа таблицы	Описывает определяемые пользователем табличные типы, которые используются для объявления параметров табличного типа.
Определяемые пользователем типы таблиц	Описывает определяемые пользователем табличные типы, которые используются для объявления параметров табличного типа.

Передача нескольких строк данных в предыдущих версиях SQL Server

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

Использовать ряд отдельных параметров, чтобы представить значения в нескольких столбцах и строках данных. Объем данных, которые можно передать с помощью этого метода, ограничен количеством допустимых параметров. В процедурах SQL Server можно использовать не более 2100 параметров. Для сборки этих отдельных значений в табличную переменную или временную таблицу для обработки требуется логика на стороне сервера.
Объединить несколько значений данных в строки с разделителями или документы XML, а затем передать эти текстовые значения в процедуру или инструкцию. Для этого метода требуется, чтобы процедура или инструкция содержали логику для проверки структур данных и разъединения значений.
Создать ряд отдельных инструкций SQL для изменений данных, затрагивающих несколько строк, например, созданных путем вызова метода Update объекта SqlDataAdapter. Изменения можно отправлять на сервер по отдельности или объединять в группы. Тем не менее даже при отправке в пакетах, содержащих несколько инструкций, каждая из них выполняется на сервере отдельно.
Использовать программу bcp или объект SqlBulkCopy, чтобы загрузить в таблицу множество строк данных. Хотя этот метод эффективен, он не поддерживает обработку на стороне сервера, если данные не загружены во временную таблицу или табличную переменную.

Создание типов табличных параметров

Параметры табличного типа основаны на строго типизированных табличных структурах, которые определяются с помощью инструкций Transact-SQL CREATE TYPE. Прежде чем использовать в клиентских приложениях параметры, принимающие табличные значения, необходимо создать в SQL Server тип таблицы и определить его структуру. Дополнительные сведения о создании табличных типов см. в статье Использование параметров, возвращающих табличное значение (ядро СУБД).

Следующая инструкция создает табличный тип с именем CategoryTableType, состоящий из столбцов CategoryID и CategoryName:

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

После создания типа таблицы можно объявлять параметры табличного типа на основе этого типа. В приведенном ниже фрагменте кода Transact-SQL демонстрируется объявление возвращающего табличное значение параметра в определении хранимой процедуры. Для объявления параметра табличного типа требуется ключевое слово READONLY.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Изменение данных с использованием табличных параметров (Transact-SQL)

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

В следующей Transact-SQL UPDATE инструкции показано, как использовать табличный параметр, присоединив его к таблице "Категории". При использовании возвращающего табличное значение параметра со значением JOIN в предложении FROM необходимо также присвоить ему псевдоним, как показано здесь, где параметр с табличным значением имеет псевдоним "ec":

UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;

В этом примере Transact-SQL показано, как выбрать строки из табличного параметра для выполнения INSERT в рамках одной операции над набором.

INSERT INTO dbo.Categories (CategoryID, CategoryName)
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Ограничения табличных параметров

У табличных параметров есть несколько ограничений:

Нельзя передавать табличные параметры в определяемые пользователем функции CLR.
Для табличных параметров можно создавать индексы только для поддержки ограничений UNIQUE или PRIMARY KEY. SQL Server не поддерживает статистику для параметров табличного типа.
В коде Transact-SQL возвращающие табличное значение параметры предназначены только для чтения. Нельзя обновлять значения столбцов в строках табличного параметра, а также вставлять или удалять строки. Чтобы изменить данные, передаваемые в хранимую процедуру или параметризованную инструкцию в возвращающем табличное значение параметре, необходимо вставить данные во временную таблицу или в табличную переменную.
Нельзя использовать инструкции ALTER TABLE для изменения структуры параметров с табличным значением.

Пример настройки объекта SqlParameter

Microsoft.Data.SqlClient поддерживает заполнение параметров табличного типа с помощью DataTable, DbDataReader или объектов IEnumerable<T> \ SqlDataRecord. Укажите имя типа для параметра, принимающего табличное значение, с помощью свойства TypeName объекта SqlParameter. TypeName должно совпадать с именем совместимого типа, ранее созданного на сервере. В приведенном ниже фрагменте кода демонстрируется, как настроить SqlParameter для вставки данных.

В следующем примере переменная addedCategories содержит DataTable. Чтобы увидеть, как заполняется переменная, просмотрите примеры в следующем разделе: Передача возвращающего табличное значение параметра в хранимую процедуру.

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

Также для передачи строк в табличный параметр можно использовать любой объект, производный от DbDataReader, как показано в этом фрагменте.

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);
tvpParam.SqlDbType = SqlDbType.Structured;

Передача табличного параметра хранимой процедуре

В этом примере показано, как передать данные параметра табличного типа в хранимую процедуру. Код извлекает добавленные строки в новый объект DataTable с помощью метода GetChanges. Затем код определяет SqlCommand, устанавливая для свойства CommandType значение StoredProcedure. SqlParameter заполняется с помощью метода AddWithValue, а параметру SqlDbType задано значение Structured. Затем SqlCommand выполняется с помощью метода ExecuteNonQuery.

// Assumes connection is an open SqlConnection object.
using (connection)
{
    // Create a DataTable with the modified rows.
    DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

    // Configure the SqlCommand and SqlParameter.
    SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
    tvpParam.SqlDbType = SqlDbType.Structured;

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

Передача параметра с табличным значением параметризованному SQL-запросу

В следующем примере показано, как вставить данные в таблицу dbo.Categories с помощью оператора INSERT с подзапросом SELECT, в котором в качестве источника данных используется параметр со значением в виде таблицы. При передаче возвращающего табличное значение параметра в параметризованную инструкцию SQL необходимо указать имя типа этого параметра с помощью нового свойства TypeName объекта SqlParameter. TypeName должно совпадать с именем совместимого типа, ранее созданного на сервере. Код в этом примере использует свойство TypeName для ссылки на структуру типа, определенную в dbo.CategoryTableType.

Примечание.

Если для столбца IDENTITY в табличном параметре указано значение, необходимо выполнить инструкцию SET IDENTITY_INSERT для текущего сеанса.

// Assumes connection is an open SqlConnection.
using (connection)
{
    // Create a DataTable with the modified rows.
    DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

    // Define the INSERT-SELECT statement.
    string sqlInsert =
        "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
        + " SELECT nc.CategoryID, nc.CategoryName"
        + " FROM @tvpNewCategories AS nc;"

    // Configure the command and parameter.
    SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.CategoryTableType";

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

Потоковое чтение строк с помощью DataReader

Также для передачи строк данных в табличный параметр можно использовать любой объект, производный от DbDataReader. В следующем фрагменте кода демонстрируется получение данных из базы данных Oracle с помощью OracleCommand и OracleDataReader. Затем код настраивает SqlCommand для вызова хранимой процедуры с одним входным параметром. Свойство SqlDbType объекта SqlParameter имеет значение Structured. AddWithValue передает результирующий набор OracleDataReader в хранимую процедуру как табличнозначный параметр.

// Assumes connection is an open SqlConnection.
// Retrieve data from Oracle.
OracleCommand selectCommand = new OracleCommand(
    "Select CategoryID, CategoryName FROM Categories;",
    oracleConnection);
OracleDataReader oracleReader = selectCommand.ExecuteReader(
    CommandBehavior.CloseConnection);

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam =
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", oracleReader);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();

Операции с данными SQL Server в ADO.NET

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2026-07-21