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 hodnotou 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 s hodnotami tabulky (databázový modul)	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é Update metodou SqlDataAdapter. Odešlete změny jednotlivě nebo v dávkách. I v rámci dávky server spouští každý příkaz samostatně.

• 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 s hodnotou tabulky jsou založeny na strukturách tabulek s pevným typem, 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 READONLY klíčové slovo je vyžadováno pro deklarování parametru typu tabulka.

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, jak použít parametr s hodnotou tabulky tak, že ho spojíte s tabulkou 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 je parametr s hodnotou tabulky 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 tabulkového parametru a provést INSERT v rámci jediné operace nad sadou.

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 tabulkovou hodnotou lze indexovat pouze pro podporu omezení UNIQUE nebo PRIMARY KEY. 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.

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

Konfigurace příkladu SqlParameter

Nainstalujte balíček NuGet Microsoft.Data.SqlClient a importujte Microsoft.Data.SqlClient a System.Data. Pro sekvence záznamů na straně klienta použijte Microsoft.Data.SqlClient.Server.SqlDataRecord a Microsoft.Data.SqlClient.Server.SqlMetaData. Nenahrazujte serverové typy SQL CLR z Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient podporuje hodnoty parametrů typu tabulka z DataTable, DbDataReader nebo IEnumerable<T> objektů SqlDataRecord. Nastavte vlastnost TypeName parametru na název kompatibilního typu tabulky vytvořeného dříve na serveru. Následující fragment nakonfiguruje parametr pro vložení 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 předává data parametrů typu tabulky do uložené procedury. Kód volá GetChanges k extrahování přidaných řádků do nového DataTable. Vytvoří se SqlCommand s CommandType nastaveným na StoredProcedure. Přidá parametr s AddWithValue a nastaví SqlDbType na Structured. Nakonec kód volá 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 vloží data do dbo.Categories pomocí příkazu INSERT a poddotazu SELECT, který čte parametr s hodnotou tabulky. U parametrizovaného příkazu SQL nastavte TypeName na název kompatibilního typu tabulky, který byl dříve vytvořen na serveru. Tento příklad používá dbo.CategoryTableType.

Poznámka:

Pokud zadáte hodnotu do sloupce identity v parametru typu tabulka, musíte pro danou relaci spustit 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

K streamování řádků do parametru s hodnotou tabulky můžete použít libovolný objekt odvozený z DbDataReader. Následující fragment načte data z databáze Oracle s OracleCommand a OracleDataReader. Kód nakonfiguruje SqlCommand, aby vyvolal uloženou proceduru s jedním vstupním parametrem. Nastaví SqlDbType na Structured a předá sadu výsledků AddWithValue prostřednictvím OracleDataReader 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