параметры 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

Табличные параметры основаны на строго типизированных табличных структурах, определенных с помощью инструкций CREATE TYPE Transact-SQL. Необходимо создать тип таблицы и определить структуру в 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 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

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

В следующем примере 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 в хранимую процедуру

В этом примере показано, как передать данные параметров с табличным значением в хранимую процедуру. Код извлекает добавленные строки в новое 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();
}

' 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 свойства a 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();
}

' 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 свойства 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();

' 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