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