Parametry o wartościach tabelowych

Parametry wartości tabeli umożliwiają łatwe przeprowadzanie marshalingu wielu wierszy danych z aplikacji klienckiej do programu SQL Server bez konieczności stosowania wielu rund lub specjalnej logiki po stronie serwera do przetwarzania danych. Za pomocą parametrów wartości tabeli można hermetyzować wiersze danych w aplikacji klienckiej i wysłać dane do serwera w jednym sparametryzowanym poleceniu. Przychodzące wiersze danych są przechowywane w zmiennej tabeli, która może być następnie obsługiwana przy użyciu języka Transact-SQL.

Dostęp do wartości kolumn w parametrach z wartościami tabeli można uzyskać przy użyciu standardowych instrukcji Transact-SQL SELECT. Parametry wartości tabeli są silnie typizowane, a ich struktura jest automatycznie weryfikowana. Rozmiar parametrów o wartości tabeli jest ograniczony tylko przez pamięć serwera.

Uwaga

Nie można zwracać danych w parametrze wartości tabeli. Parametry wartości tabeli są tylko danymi wejściowymi; słowo kluczowe OUTPUT nie jest obsługiwane.

Aby uzyskać więcej informacji na temat parametrów wartości tabeli, zobacz następujące zasoby.

Zasób	opis
Używanie parametrów wartości tabeli (aparat bazy danych)	Opisuje sposób tworzenia i używania parametrów wartości tabeli.
Typy tabel zdefiniowanych przez użytkownika	Opisuje typy tabel zdefiniowane przez użytkownika, które są używane do deklarowania parametrów z wartością tabeli.

Przekazywanie wielu wierszy w poprzednich wersjach programu SQL Server

Przed wprowadzeniem parametrów wartości tabeli do programu SQL Server 2008 opcje przekazywania wielu wierszy danych do procedury składowanej lub sparametryzowanego polecenia SQL były ograniczone. Deweloper może wybrać spośród następujących opcji przekazywania wielu wierszy do serwera:

• Użyj serii pojedynczych parametrów, aby reprezentować wartości w wielu kolumnach i wierszach danych. Ilość danych, które można przekazać przy użyciu tej metody, jest ograniczona przez dozwoloną liczbę parametrów. Procedury programu SQL Server mogą mieć co najwyżej 2100 parametrów. Logika po stronie serwera jest wymagana do złożenia tych indywidualnych wartości w zmiennej tabeli lub tabeli tymczasowej do przetwarzania.

• Łączenie wielu wartości danych z rozdzielanymi ciągami lub dokumentami XML, a następnie przekazywanie tych wartości tekstowych do procedury lub instrukcji. Wymaga to, aby procedura lub instrukcja zawierała logikę niezbędną do weryfikacji struktur danych i rozdzielenia wartości.

• Utwórz serię poszczególnych instrukcji SQL dla modyfikacji danych, które mają wpływ na wiele wierszy, takich jak te utworzone przez wywołanie Update metody .SqlDataAdapter Zmiany można przesyłać do serwera pojedynczo lub wsadowo do grup. Jednak nawet w przypadku przesłania w partiach zawierających wiele instrukcji każda instrukcja jest wykonywana oddzielnie na serwerze.

• Użyj programu narzędziowego bcp lub SqlBulkCopy obiektu, aby załadować wiele wierszy danych do tabeli. Chociaż ta technika jest bardzo wydajna, nie obsługuje przetwarzania po stronie serwera, chyba że dane są ładowane do tymczasowej tabeli lub zmiennej tabeli.

Tworzenie typów parametrów wartości tabeli

Parametry wartości tabeli są oparte na silnie typiowanych strukturach tabel, które są definiowane przy użyciu instrukcji Transact-SQL CREATE TYPE. Musisz utworzyć typ tabeli i zdefiniować strukturę w programie SQL Server, zanim będzie można używać parametrów wartości tabeli w aplikacjach klienckich. Aby uzyskać więcej informacji na temat tworzenia typów tabel, zobacz Typy tabel zdefiniowanych przez użytkownika.

Poniższa instrukcja tworzy typ tabeli o nazwie CategoryTableType, który składa się z kolumn CategoryID i CategoryName:

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

Po utworzeniu typu tabeli można zadeklarować parametry wartości tabeli na podstawie tego typu. Poniższy fragment języka Transact-SQL pokazuje, jak zadeklarować parametr o wartości tabeli w definicji procedury składowanej. Należy pamiętać, że słowo kluczowe READONLY jest wymagane do deklarowania parametru wartości tabeli.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Modyfikowanie danych przy użyciu parametrów o wartości tabeli (Transact-SQL)

Parametry wartości tabeli mogą być używane w modyfikacjach danych opartych na zestawie, które wpływają na wiele wierszy, wykonując jedną instrukcję. Można na przykład wybrać wszystkie wiersze w parametrze z wartością tabeli i wstawić je do tabeli bazy danych lub utworzyć instrukcję aktualizacji, łącząc parametr z wartością tabeli do tabeli, którą chcesz zaktualizować.

Poniższa instrukcja Transact-SQL UPDATE pokazuje, jak używać parametru o wartości tabeli, łącząc go z tabelą Categories. W przypadku używania parametru typu table-valued z klauzulą JOIN w klauzuli FROM należy również aliasować go, jak pokazano tutaj, gdzie parametr wartości tabeli ma alias "ec":

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

W tym przykładzie języka Transact-SQL pokazano, jak wybrać wiersze z parametru wartości tabeli w celu wykonania operacji INSERT w ramach jednej operacji opartej na zestawie.

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

Ograniczenia parametrów wartości tabeli

Istnieje kilka ograniczeń dotyczących parametrów wartości tabeli:

• Nie można przekazać parametrów wartości tabeli do funkcji zdefiniowanych przez użytkownika clR.

• Parametry wartości tabeli można indeksować tylko w celu obsługi ograniczeń UNIKATOWE lub KLUCZ PODSTAWOWY. Program SQL Server nie obsługuje statystyk dotyczących parametrów wartości tabeli.

• Parametry wartości tabeli są tylko do odczytu w kodzie Transact-SQL. Nie można zaktualizować wartości kolumn w wierszach parametru wartości tabeli i nie można wstawiać ani usuwać wierszy. Aby zmodyfikować dane przekazywane do procedury składowanej lub sparametryzowanej instrukcji w parametrze wartości tabeli, należy wstawić dane do tabeli tymczasowej lub do zmiennej tabeli.

• Nie można używać instrukcji ALTER TABLE do modyfikowania projektu parametrów wartości tabeli.

Konfigurowanie przykładu parametru SqlParameter

System.Data.SqlClient Obsługuje wypełnianie parametrów z wartości tabeli z DataTableobiektów lub DbDataReaderIEnumerable<T> \ SqlDataRecord . Należy określić nazwę typu dla parametru wartości tabeli przy użyciu TypeName właściwości SqlParameter. Wartość TypeName musi być zgodna z nazwą zgodnego typu utworzonego wcześniej na serwerze. Poniższy fragment kodu przedstawia sposób konfigurowania SqlParameter w celu wstawiania danych.

W poniższym przykładzie zmienna addedCategories zawiera zmienną DataTable. Aby zobaczyć, jak zmienna jest wypełniana, zobacz przykłady w następnej sekcji, przekazując parametr z wartością tabeli do procedury składowanej.

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

Można również użyć dowolnego obiektu pochodzącego z DbDataReader , aby przesyłać strumieniowo wiersze danych do parametru wartości tabeli, jak pokazano w tym fragmentcie:

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

Przekazywanie parametru o wartości tabeli do procedury składowanej

W tym przykładzie pokazano, jak przekazać dane parametrów o wartości tabeli do procedury składowanej. Kod wyodrębnia wiersze do nowego DataTable przy użyciu GetChanges metody . Następnie kod definiuje właściwość SqlCommand, ustawiając CommandType właściwość na StoredProcedure. Obiekt SqlParameter jest wypełniany przy użyciu AddWithValue metody , a SqlDbType parametr ma wartość Structured. Metoda SqlCommand jest następnie wykonywana ExecuteNonQuery przy użyciu metody .

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

Przekazywanie parametru wartości tabeli do sparametryzowanej instrukcji SQL

W poniższym przykładzie pokazano, jak wstawić dane do bazy danych. Tabela kategorii przy użyciu instrukcji INSERT z podzapytaniem SELECT, który ma parametr o wartości tabeli jako źródło danych. Podczas przekazywania parametru wartości tabeli do sparametryzowanej instrukcji SQL należy określić nazwę typu dla parametru wartości tabeli przy użyciu nowej TypeName właściwości .SqlParameter Musi to TypeName być zgodne z nazwą zgodnego typu utworzonego wcześniej na serwerze. Kod w tym przykładzie TypeName używa właściwości do odwołowania się do struktury typów zdefiniowanej w bazie danych. CategoryTableType.

Uwaga

Jeśli podasz wartość dla kolumny tożsamości w parametrze wartości tabeli, musisz wydać instrukcję SET IDENTITY_INSERT dla sesji.

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

Przesyłanie strumieniowe wierszy z elementem DataReader

Można również użyć dowolnego obiektu pochodzącego z DbDataReader , aby przesyłać strumieniowo wiersze danych do parametru wartości tabeli. Poniższy fragment kodu przedstawia pobieranie danych z bazy danych Oracle przy użyciu elementu OracleCommand i OracleDataReader. Następnie kod konfiguruje element SqlCommand , aby wywołać procedurę składowaną z pojedynczym parametrem wejściowym. Właściwość SqlDbType właściwości SqlParameter jest ustawiona na Structured. OracleDataReader Parametr AddWithValue przekazuje zestaw wyników do procedury składowanej jako parametr z wartością tabeli.

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

Zobacz też

• Konfigurowanie parametrów i typów danych parametrów
• Polecenia i parametry
• Parametry elementu DataAdapter
• Operacje danych serwera SQL w ADO.NET
• Omówienie ADO.NET