parametry s hodnotami tabulky

Parametry s tabulkovou hodnotou poskytují snadný způsob, jak přenášet více řádků dat z klientské aplikace na SQL Server, aniž by bylo nutné provést více cyklů nebo používat speciální logiku na straně serveru pro zpracování dat. Parametry hodnot tabulky můžete použít k zapouzdření řádků dat v klientské aplikaci a odeslání dat na server v jednom parametrizovaném příkazu. Příchozí datové řádky se ukládají do proměnné tabulky, na které je pak možné pracovat pomocí jazyka Transact-SQL.

Hodnoty sloupců v parametrech s hodnotami tabulky jsou přístupné pomocí standardních příkazů Transact-SQL SELECT. Parametry s hodnotami tabulky jsou silného typu a jejich struktura se automaticky ověří. Velikost parametrů s hodnotou tabulky je omezena pouze pamětí serveru.

Poznámka:

Data v parametru s hodnotou tabulky nelze vrátit. Parametry s hodnotou tabulky jsou pouze pro vstup; klíčové slovo OUTPUT není podporováno.

Další informace o tabulkových parametrech najdete v následujících zdrojích.

Zdroj	Popis
Použít parametry Table-Valued (databázový stroj)	Popisuje, jak vytvořit a používat parametry s hodnotou tabulky.
typy tabulekUser-Defined	Popisuje uživatelem definované typy tabulek, které slouží k deklaraci parametrů hodnot tabulky.

Přenášení více řádků v předchozích verzích SQL Serveru

Před zavedením parametrů s hodnotou tabulky do SYSTÉMU SQL Server 2008 byly možnosti předávání více řádků dat uložené proceduře nebo parametrizovaný příkaz SQL omezeny. Vývojář si může vybrat z následujících možností pro předávání více řádků na server:

• Pomocí řady jednotlivých parametrů můžete znázorňovat hodnoty ve více sloupcích a řádcích dat. Množství dat, která lze předat pomocí této metody, je omezen počtem povolených parametrů. Procedury SQL Serveru můžou mít maximálně 2100 parametrů. Logika na straně serveru je nutná k sestavení těchto jednotlivých hodnot do proměnné tabulky nebo dočasné tabulky pro zpracování.

• Sbalte několik datových hodnot do řetězců s oddělovači nebo dokumentů XML a pak tyto textové hodnoty předejte procedurě nebo příkazu. To vyžaduje, aby procedura nebo příkaz zahrnoval logiku potřebnou k ověření datových struktur a oddělení hodnot.

• Vytvořte řadu jednotlivých příkazů SQL pro úpravy dat, které ovlivňují více řádků, jako jsou například ty vytvořené voláním Update metody SqlDataAdapter. Změny lze odeslat na server jednotlivě nebo dávkově do skupin. I když je však odeslán v dávkách, které obsahují více příkazů, každý příkaz se provádí samostatně na serveru.

• bcp Pomocí nástroje nebo objektu SqlBulkCopy načtěte mnoho řádků dat do tabulky. I když je tato technika velmi efektivní, nepodporuje zpracování na straně serveru, pokud nejsou data načtena do dočasné tabulky nebo proměnné tabulky.

Vytváření typů parametrů Table-Valued

Parametry tabulkových hodnot jsou založeny na silně typovaných strukturách tabulek, které jsou definovány pomocí příkazů Transact-SQL CREATE TYPE. Před použitím parametrů hodnot tabulky v klientských aplikacích musíte vytvořit typ tabulky a definovat strukturu v SQL Serveru. Další informace o vytváření typů tabulek najdete v tématu User-Defined Typy tabulek.

Následující příkaz vytvoří typ tabulky s názvem CategoryTableType, který se skládá ze sloupců CategoryID a CategoryName:

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

Po vytvoření typu tabulky můžete deklarovat parametry s hodnotou tabulky na základě daného typu. Následující Transact-SQL fragment ukazuje, jak deklarovat parametr s hodnotou tabulky v definici uložené procedury. Všimněte si, že klíčové slovo READONLY je vyžadováno pro deklarování parametru s hodnotou tabulky.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Úprava dat pomocí parametrů Table-Valued (Transact-SQL)

Parametry hodnotné tabulkou lze použít v úpravách dat založených na sadě, které ovlivňují více řádků spuštěním jednoho příkazu. Můžete například vybrat všechny řádky v parametru s hodnotou tabulky a vložit je do databázové tabulky nebo můžete vytvořit aktualizační příkaz spojením parametru s hodnotou tabulky k tabulce, kterou chcete aktualizovat.

Následující příkaz Transact-SQL UPDATE ukazuje použití parametru s hodnotou tabulky jeho spojením k tabulce Categories. Pokud použijete parametr s hodnotou tabulky s join v klauzuli FROM, musíte ho také aliasovat, jak je znázorněno zde, kde parametr s hodnotou tabulky je aliasován jako "ec":

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

Tento Transact-SQL příklad ukazuje, jak vybrat řádky z parametru s hodnotou tabulky k provedení INSERT v rámci jedné operace založené na sadě.

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

Omezení parametrů Table-Valued

Parametry s hodnotou tabulky mají několik omezení:

• Uživatelem definovaným funkcím CLR nelze předat parametry s hodnotou tabulky.

• Parametry s hodnotou tabulky je možné indexovat pouze pro podporu omezení JEDINEČNÉho nebo PRIMÁRNÍHO KLÍČE. SQL Server neudržuje statistiky o parametrech s hodnotami tabulky.

• Parametry typu tabulka jsou v kódu Transact-SQL jen pro čtení. Hodnoty sloupců v řádcích parametru s hodnotou tabulky nelze aktualizovat a nelze vložit ani odstranit řádky. Chcete-li upravit data, která se předávají uložené proceduře nebo parametrizovanému příkazu v parametru s hodnotou tabulky, musíte data vložit do dočasné tabulky nebo do proměnné tabulky.

• Příkazy ALTER TABLE nelze použít k úpravě návrhu parametrů s hodnotou tabulky.

Konfigurace příkladu SqlParameter

System.Data.SqlClient podporuje naplnění parametrů hodnot tabulky objektů z DataTable, DbDataReader nebo IEnumerable<T> \ SqlDataRecord. Typ názvu pro parametr typu table-valued musíte zadat pomocí vlastnosti TypeName objektu SqlParameter. Musí TypeName odpovídat názvu kompatibilního typu, který byl dříve vytvořen na serveru. Následující fragment kódu ukazuje, jak nakonfigurovat vkládání SqlParameter dat.

V následujícím příkladu addedCategories obsahuje proměnná hodnotu DataTable. Pokud chcete zjistit, jak se proměnná naplní, podívejte se na příklady v další části předání parametru Table-Valued uložené proceduře.

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

Můžete také použít libovolný objekt odvozený z DbDataReader pro streamování řádků dat do tabulkového parametru, jak je předvedeno v tomto fragmentu:

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

Předání parametru Table-Valued uložené proceduře

Tento příklad ukazuje, jak předat data tabulkového parametru do uložené procedury. Kód extrahuje přidané řádky do nového DataTable pomocí metody GetChanges. Kód pak definuje SqlCommand, nastaví CommandType vlastnost na StoredProcedure. Naplní se SqlParameter pomocí metody AddWithValue a SqlDbType se nastaví na Structured. Pak se SqlCommand spustí pomocí metody 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  

Předání parametru Table-Valued do parametrizovaného příkazu SQL

Následující příklad ukazuje, jak vložit data do dbo. Kategorie tabulky pomocí příkazu INSERT s poddotazEM SELECT, který má parametr s hodnotou tabulky jako zdroj dat. Při předávání parametru table-valued do parametrizovaného příkazu SQL je nutné zadat název typu parametru table-valued pomocí nové TypeName vlastnosti .SqlParameter Toto TypeName se musí shodovat s názvem kompatibilního typu, který byl dříve vytvořen na serveru. Kód v tomto příkladu používá vlastnost TypeName k odkazu na typovou strukturu definovanou v dbo.CategoryTableType.

Poznámka:

Pokud zadáte hodnotu sloupce identity v parametru typu tabulky, musíte pro relaci vydat příkaz 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  

Streamování řádků pomocí třídy DataReader

Můžete také použít libovolný objekt odvozený z DbDataReader ke streamování řádků dat do parametrů tabulkové hodnoty. Následující fragment kódu ukazuje načtení dat z databáze Oracle pomocí OracleCommand a OracleDataReader. Kód pak nakonfiguruje SqlCommand pro vyvolání uložené procedury s jedním vstupním parametrem. Vlastnost SqlDbType objektu je nastavena SqlParameter na Structuredhodnotu . AddWithValue předá výsledkovou sadu OracleDataReader uložené proceduře jako parametr s hodnotou tabulky.

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

Viz také

• konfigurace parametrů a datových typů parametrů
• Příkazy a Parametry
• Parametry adaptéru dat
• Operace s daty SQL Serveru v ADO.NET
• Přehled ADO.NET