Возвращающие табличные значения параметры

  • Статья

Скачать ADO.NET

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

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

Примечание.

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

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

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

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

Примечание.

Если для столбца идентификаторов задается значение в возвращающем табличное значение параметре, необходимо выполнить инструкцию 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();

Следующие шаги