Parametry s tabulkovými hodnotami

Stáhnout ADO.NET

Parametry s hodnotou tabulky poskytují snadný způsob, jak zařabit více řádků dat z klientské aplikace na SQL Server. Pro zpracování dat nevyžadují více odezv nebo speciální logiku na straně serveru. 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 v tabulce jsou silného typu a jejich struktura je automaticky ověřena. Velikost parametrů s hodnotou tabulky je omezena pouze pamětí serveru.

Poznámka:

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

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

Resource	Description
Použít parametry Table-Valued (databázový stroj)	Popisuje, jak vytvořit a používat parametry s hodnotou tabulky.
Vytvoření uživatelem definovaného typu tabulky	Popisuje uživatelem definované typy tabulek, které slouží k deklaraci parametrů hodnot tabulky.
uživatelsky definované typy tabulek	Popisuje uživatelem definované typy tabulek, které slouží k deklaraci parametrů hodnot tabulky.

Předávání více řádků v dřívějších verzích SQL Serveru

Před zavedením tabulkových parametrů byly možnosti předávání více řádků dat uložené proceduře nebo parametrizovanému příkazu SQL omezené. 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. Tato metoda vyžaduje, aby procedura nebo příkaz zahrnoval logiku pro ověřování 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 efektivní, nepodporuje zpracování na straně serveru, pokud se data nenačtou do dočasné tabulky nebo proměnné tabulky.

Vytváření typů parametrů s hodnotou tabulky

Parametry s hodnotou tabulky jsou založené na strukturách tabulek se silně typovanými typy, které jsou definované pomocí Transact-SQL příkazů CREATE TYPE. Před použitím parametrů s hodnotami 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 Použití parametrů Table-Valued (databázový stroj).

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. Klíčové slovo READONLY je vyžadováno pro deklaraci parametrů s typem 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 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ů tabulkových hodnot

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

Parametry typu tabulky nelze předávat funkcím CLR definovaným uživatelem.
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 Transact-SQL jen pro čtení. Hodnoty sloupců v řádcích parametru s hodnotou tabulky nelze aktualizovat a řádky nelze vložit ani odstranit. Chcete-li upravit data předaná do uložené procedury nebo parametrizovaného 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ů hodnot tabulky.

Konfigurace příkladu SqlParameter

Microsoft.Data.SqlClient podporuje naplnění parametrů s hodnotou tabulky z DataTableobjektu , DbDataReadernebo IEnumerable<T> \ SqlDataRecord objektů. Zadejte název typu parametru typu tabulka pomocí vlastnosti . 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";

Můžete také použít libovolný objekt odvozený z DbDataReader ke streamování řádků dat do parametru typu 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;

Předání parametru s hodnotou tabulky do uložené procedury

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

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 s tabulkovou hodnotou do parametrizovaného příkazu SQL je nutné zadat typ parametru s tabulkovou hodnotou pomocí nové vlastnosti TypeName SqlParameter. Toto TypeName se musí odpovídat názvu 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 parametrizované tabulce, musíte vydat příkaz SET IDENTITY_INSERT pro sérii.

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

Streamování řádků pomocí DataReader

Můžete také použít libovolný objekt odvozený z DbDataReader ke streamování řádků dat jako tabulkový parametr. Následující fragment kódu ukazuje, jak načíst data 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 . Tento AddWithValue předává sadu výsledků 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();

Další kroky

Operace s daty SQL Serveru v ADO.NET

Váš názor

Byla tato stránka užitečná?

Last updated on 2026-01-16