Parametry s hodnotami v tabulkách

Parametry hodnotné tabulkou poskytují snadný způsob, jak zařadovat více řádků dat z klientské aplikace na SQL Server, aniž by bylo nutné provést více odezvy nebo 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 hodnotou tabulky lze získat přístup 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 hodnotné tabulkou jsou jen pro vstupy; Klíčové slovo OUTPUT není podporováno.

Další informace o parametrech s hodnotou tabulky najdete v následujících zdrojích informací.

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

Předávání 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ůch

Parametry hodnotné tabulkou jsou založené na strukturách tabulek silného typu, 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 naleznete v tématu Typy tabulek definované uživatelem.

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í fragment jazyka Transact-SQL 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ů s hodnotou tabulky (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 příklad jazyka Transact-SQL 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ů s hodnotou tabulky

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 s hodnotou tabulky jsou v kódu Jazyka 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.SqlClientpodporuje naplnění parametrů hodnot tabulky z DataTableDbDataReader objektů neboSqlDataRecord IEnumerable<T> \ objektů. Název typu parametru table-valued je nutné zadat pomocí TypeName vlastnosti .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 s hodnotou tabulky do uložené procedury.

// 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 streamování řádků dat do parametru s hodnotou tabulky, jak je znázorněno 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 s hodnotou tabulky uložené proceduře

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

Předání parametru s hodnotou tabulky 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 Musí TypeName se shodovat s názvem kompatibilního typu, který byl dříve vytvořen na serveru. Kód v tomto příkladu TypeName používá vlastnost odkazovat na strukturu typů definovanou v dbo. CategoryTableType.

Poznámka:

Pokud zadáte hodnotu sloupce identity v parametru s hodnotou tabulky, musíte vydat příkaz SET IDENTITY_INSERT pro relaci.

// 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 streamování řádků dat do parametru s hodnotou tabulky. Následující fragment kódu ukazuje načtení dat z databáze Oracle pomocí a OracleCommand OracleDataReader. Kód pak nakonfiguruje SqlCommand vyvolání uložené procedury s jedním vstupním parametrem. Vlastnost SqlDbType objektu je nastavena SqlParameter na Structuredhodnotu . Předá AddWithValue OracleDataReader sadu výsledků 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é typy parametrů
• Příkazy a parametry
• Parametry adaptéru dat
• Operace dat na SQL Serveru v ADO.NET
• Přehled ADO.NET