Parametry typu tabelarycznego

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 o wartościach tabelarycznych można uzyskać przy użyciu standardowych instrukcji SELECT Transact-SQL. 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 / Notatka

Nie można zwracać danych w parametrze wartości tabeli. Parametry tabelaryczne 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żyj parametrów o wartości tabelarycznej (silnik bazy danych)	Opisuje sposób tworzenia i używania parametrów wartości tabeli.
typy tabelUser-Defined	Opisuje typy tabel zdefiniowane przez użytkownika, które są używane do deklarowania parametrów tabelarycznych.

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 Update metodę SqlDataAdapter. Przesyłaj zmiany indywidualnie lub w partiach. Nawet w ramach partii serwer wykonuje każdą instrukcję oddzielnie.

• 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 Table-Valued

Parametry wartości tabeli są oparte na silnie typiowanych strukturach tabel, które są definiowane przy użyciu instrukcji CREATE TYPE Transact-SQL. 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 User-Defined Typy tabel.

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 Transact-SQL pokazuje, jak zadeklarować parametr o wartości tabeli w definicji procedury składowanej. Należy pamiętać, że do deklarowania parametru o wartościach tabelarycznych wymagane jest użycie słowa kluczowego READONLY.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modyfikowanie danych przy użyciu parametrów Table-Valued (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 wartości tabeli przez dołączenie go do tabeli Categories. Jeśli używasz parametru wartości tabelarycznej z operatorem JOIN w klauzuli FROM, musisz również nadać mu alias, jak pokazano tutaj, gdzie parametr wartości tabelarycznej 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 Transact-SQL pokazano, jak wybrać wiersze z parametru o wartości tabeli, aby wykonać operację 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 Table-Valued

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 o wartościach tabelarycznych można indeksować tylko w celu obsługi ograniczeń UNIQUE lub PRIMARY KEY. 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 tabelarycznym, należy wstawić dane do tabeli tymczasowej lub do zmiennej tabelarycznej.

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

Konfigurowanie przykładu parametru SqlParameter

Zainstaluj pakiet NuGet Microsoft.Data.SqlClient i zaimportuj Microsoft.Data.SqlClient i System.Data. W przypadku sekwencji rekordów po stronie klienta użyj Microsoft.Data.SqlClient.Server.SqlDataRecord i Microsoft.Data.SqlClient.Server.SqlMetaData. Nie należy zastępować typów SQL CLR po stronie serwera za pomocą Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient obsługuje wartości parametrów tabelarycznych z obiektów IEnumerable<T> typu SqlDataRecord, DataTable lub DbDataReader. Ustaw parametr TypeName na nazwę zgodnego typu tabeli utworzonego wcześniej na serwerze. Poniższy fragment konfiguruje parametr w celu wstawienia 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 Table-Valued 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 Table-Valued do procedury składowanej

W tym przykładzie dane parametrów o wartościach tabelarycznych są przekazywane do procedury składowanej. Kod wywołuje GetChanges, aby wyodrębnić dodane wiersze do nowego elementu DataTable. Tworzy SqlCommand z ustawieniem CommandType na StoredProcedure. Dodaje parametr z AddWithValue i ustawia SqlDbType na Structured. Na koniec kod wywołuje metodę 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

Przekazywanie parametru Table-Valued do sparametryzowanej instrukcji SQL

Poniższy przykład wstawia dane do dbo.Categories za pomocą instrukcji INSERT i podzapytania SELECT, które odczytuje parametr tabelaryczny. W przypadku sparametryzowanej instrukcji SQL ustaw dla parametru TypeName nazwę zgodnego typu tabeli utworzonego wcześniej na serwerze. W tym przykładzie użyto dbo.CategoryTableType.

Uwaga / Notatka

Jeśli podasz wartość dla kolumny identyfikacyjnej w parametrze tabelarycznym, musisz wykonać instrukcję SET IDENTITY_INSERT w tej 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 za pomocą DataReader

Można użyć dowolnego obiektu pochodzącego z DbDataReader, aby przesyłać strumieniowo wiersze do parametru o wartości tabelarycznej. Poniższy fragment pobiera dane z bazy danych Oracle przy użyciu OracleCommand i OracleDataReader. Kod konfiguruje element SqlCommand , aby wywołać procedurę składowaną z jednym parametrem wejściowym. Ustawia SqlDbType na OracleDataReader i przekazuje AddWithValue zestaw wyników przez Structured jako parametr table-valued.

// 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 także

• Konfigurowanie parametrów i typów danych parametrów
• Komendy i parametry
• Parametry elementu DataAdapter
• Operacje danych w SQL Server z wykorzystaniem ADO.NET
• Omówienie ADO.NET