параметры Table-Valued

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

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

Замечание

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

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

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

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

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

• Используйте ряд отдельных параметров для представления значений в нескольких столбцах и строках данных. Объем данных, которые можно передать с помощью этого метода, ограничен количеством параметров, разрешенных. Процедуры SQL Server могут иметь не более 2100 параметров. Логика на стороне сервера необходима для сборки этих отдельных значений в табличную переменную или временную таблицу для обработки.

• Объедините несколько значений данных в разделенные строки или XML-документы, а затем передайте эти текстовые значения в процедуру или инструкцию. Для этого требуется процедура или оператор, чтобы включить логику, необходимую для проверки структур данных и разделения значений.

• Создайте ряд отдельных инструкций SQL для изменений данных, влияющих на несколько строк, например, создаваемых методом Update объекта SqlDataAdapter. Отправляйте изменения по отдельности или пакетами. Даже в пакете сервер выполняет каждую инструкцию отдельно.

• Используйте служебную программу bcp или объект SqlBulkCopy, чтобы загрузить множество строк данных в таблицу. Хотя этот метод очень эффективен, он не поддерживает обработку на стороне сервера, если данные не загружаются во временную таблицу или переменную таблицы.

Создание типов параметров Table-Valued

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

Следующая инструкция создает тип таблицы с именем 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)

Изменение данных с помощью параметров Table-Valued (Transact-SQL)

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

В следующей 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;

Ограничения параметров Table-Valued

Существует несколько ограничений для табличных параметров:

• Нельзя передавать табличное значение в определяемые пользователем функции CLR.

• Параметры табличного типа можно индексировать только для поддержки ограничений UNIQUE или PRIMARY KEY. SQL Server не поддерживает статистику по табличным параметрам.

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

• Нельзя использовать инструкции ALTER TABLE для изменения структуры параметров с табличным значением.

Настройка примера SqlParameter

Установите пакет NuGet Microsoft.Data.SqlClient и импортируйте System.Data и Microsoft.Data.SqlClient. Для последовательностей записей на стороне клиента используйте Microsoft.Data.SqlClient.Server.SqlDataRecord и Microsoft.Data.SqlClient.Server.SqlMetaData. Не заменяйте серверные типы SQL CLR из Microsoft.SqlServer.Server.

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

В следующем примере addedCategories переменная содержит объект DataTable. Чтобы узнать, как заполняется переменная, см. примеры в следующем разделе, передавая параметр Table-Valued в хранимую процедуру.

// 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";

' Configure the command and parameter.
Dim insertCommand As New SqlCommand(sqlInsert, connection)
Dim tvpParam As SqlParameter = _
   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;

' Configure the SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
insertCommand.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
  dataReader)
tvpParam.SqlDbType = SqlDbType.Structured

Передача параметра Table-Valued в хранимую процедуру

В этом примере данные параметра с табличным значением передаются в хранимую процедуру. Код вызывает GetChanges для извлечения добавленных строк в новый DataTable. Он создает объект CommandType с SqlCommand, установленным в StoredProcedure. Он добавляет параметр с помощью AddWithValue и задает для SqlDbType значение Structured. Наконец, код вызывает 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();
}

' Assumes connection is an open SqlConnection object.
Using connection
   '  Create a DataTable with the modified rows.
   Dim addedCategories As DataTable = _
     CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Configure the SqlCommand and SqlParameter.
   Dim insertCommand As New SqlCommand( _
     "usp_InsertCategories", connection)
   insertCommand.CommandType = CommandType.StoredProcedure
   Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
     "@tvpNewCategories", addedCategories)
   tvpParam.SqlDbType = SqlDbType.Structured

   '  Execute the command.
   insertCommand.ExecuteNonQuery()
End Using

Передача параметра Table-Valued в параметризованную инструкцию SQL

В следующем примере данные вставляются в dbo.Categories с помощью инструкции INSERT и SELECT вложенного запроса, который считывает табличный параметр. Для параметризованной инструкции SQL задайте для 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();
}

' Assumes connection is an open SqlConnection.
Using connection
  ' Create a DataTable with the modified rows.
  Dim addedCategories As DataTable = _
    CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Define the INSERT-SELECT statement.
  Dim sqlInsert As String = _
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _
  & " SELECT nc.CategoryID, nc.CategoryName" _
  & " FROM @tvpNewCategories AS nc;"

  ' Configure the command and parameter.
  Dim insertCommand As New SqlCommand(sqlInsert, connection)
  Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
    "@tvpNewCategories", addedCategories)
  tvpParam.SqlDbType = SqlDbType.Structured
  tvpParam.TypeName = "dbo.CategoryTableType"

  ' Execute the query
  insertCommand.ExecuteNonQuery()
End Using

Потоковая передача строк с DataReader

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

// 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();

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

' Configure SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
insertCommand.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
  oracleReader)
tvpParam.SqlDbType = SqlDbType.Structured

' Execute the command.
insertCommand.ExecuteNonQuery()

См. также

• Настройка параметров и типов данных параметров
• Команды и параметры
• Параметры DataAdapter
• Операции с данными SQL Server в ADO.NET
• Общие сведения об ADO.NET