Parametry tabelaryczne

Pobieranie ADO.NET

Parametry wartości tabeli umożliwiają łatwe przeprowadzanie marshalingu wielu wierszy danych z aplikacji klienckiej do programu SQL Server. Nie wymagają one wielu rund ani 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 wartości tabeli można uzyskać przy użyciu standardowych instrukcji Transact-SQL SELECT. 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 wartości tabeli 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.

Resource	Description
użyj parametrów Table-Valued (aparatu bazy danych)	Opisuje sposób tworzenia i używania parametrów wartości tabeli.
Tworzenie typu tabeli zdefiniowanej przez użytkownika	Opisuje typy tabel zdefiniowane przez użytkownika, używane do deklarowania parametrów tabelarycznych.
Typy tabel zdefiniowane przez użytkownika	Opisuje typy tabel zdefiniowane przez użytkownika, używane do deklarowania parametrów tabelowych.

Przekazywanie wielu wierszy w poprzednich wersjach programu SQL Server

Przed wprowadzeniem parametrów wartości tabeli 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. Ta metoda wymaga, aby procedura lub instrukcja zawierała logikę sprawdzania poprawności 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 wywołanie metody UpdateSqlDataAdapter. Zmiany można przesyłać do serwera pojedynczo lub grupować w partie. Jednak nawet w przypadku przesłania w partiach zawierających wiele instrukcji każda instrukcja jest wykonywana oddzielnie na serwerze.
Użyj programu narzędziowego bcp lub SqlBulkCopy obiektu, aby załadować wiele wierszy danych do tabeli. Mimo że ta technika jest wydajna, nie obsługuje przetwarzania po stronie serwera, chyba że dane są ładowane do tymczasowej tabeli lub zmiennej tabeli.

Tworzenie typów parametrów tabelarycznych

Parametry wartości tabeli są oparte na silnie typiowanych strukturach tabel, które są definiowane przy użyciu Transact-SQL instrukcji CREATE TYPE. Należy utworzyć typ tabeli i zdefiniować strukturę w programie SQL Server, aby można było używać parametrów wartości tabeli w aplikacjach klienckich. Aby uzyskać więcej informacji na temat tworzenia typów tabel, zobacz Używanie parametrów Table-Valued (aparat bazy danych).

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. Słowo kluczowe READONLY jest wymagane do deklarowania parametru typu tabelarycznego.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modyfikowanie danych przy użyciu parametrów wartości tabeli (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 o wartości tabeli przez dołączenie go do tabeli Categories. W przypadku używania parametru typu table-valued z klauzulą JOIN w klauzuli FROM należy również aliasować go, jak pokazano tutaj, gdzie parametr wartości tabeli 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 wartości tabeli w celu wykonania operacji 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 wartości tabeli

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 typu tabelarycznego można indeksować tylko w celu obsługi ograniczeń przy unikalnych lub podstawowych kluczach. 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 przekazane do procedury składowanej lub sparametryzowanej instrukcji w parametrze wartości tabeli, należy wstawić dane do tabeli tymczasowej lub do zmiennej tabeli.
Nie można użyć instrukcji ALTER TABLE, aby zmodyfikować projekt parametrów wartości tabeli.

Konfigurowanie przykładu parametru SqlParameter

Microsoft.Data.SqlClientobsługuje wypełnianie parametrów DataTabletabeli z obiektów , DbDataReaderlubIEnumerable<T> \ SqlDataRecord . Określ nazwę typu dla parametru typu tabelowego przy użyciu właściwości TypeName obiektu SqlParameter. Wartość TypeName musi być zgodna z nazwą zgodnego typu utworzonego wcześniej na serwerze. Poniższy fragment kodu przedstawia sposób konfigurowania SqlParameter w celu wstawiania danych.

W poniższym przykładzie zmienna addedCategories zawiera zmienną DataTable. Aby zobaczyć, jak zmienna jest wypełniana, znajdziesz przykłady w następnej sekcji, Przekazywanie parametru wartości tabelarycznej 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";

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;

Przekazywanie parametru tabelarycznego do procedury składowanej

W tym przykładzie pokazano, jak przekazać dane parametrów tabelarycznych do procedury składowanej. Kod dodaje wyodrębnione wiersze do nowego DataTable przy użyciu metody GetChanges. Następnie kod definiuje właściwość SqlCommand, ustawiając CommandType właściwość na StoredProcedure. Obiekt SqlParameter jest wypełniany za pomocą metody AddWithValue, a SqlDbType jest ustawiany na Structured. Następnie SqlCommand jest wykonywany przy użyciu 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();
}

Przekazywanie parametru wartości tabeli do sparametryzowanej instrukcji SQL

W poniższym przykładzie pokazano, jak wstawić dane do tabeli dbo.Categories przy użyciu instrukcji INSERT z podzapytaniem SELECT, które ma parametr typu tabelarycznego jako źródło danych. Podczas przekazywania parametru typu tabela do sparametryzowanej instrukcji SQL należy określić nazwę typu dla tego parametru przy użyciu nowej właściwości TypeName obiektu SqlParameter. Musi to TypeName być zgodne z nazwą zgodnego typu utworzonego wcześniej na serwerze. Kod w tym przykładzie używa właściwości TypeName do odwołania się do struktury typów zdefiniowanej w dbo.CategoryTableType.

Uwaga / Notatka

Jeśli podasz wartość dla kolumny tożsamości w parametrze wartości tabeli, musisz wydać instrukcję SET IDENTITY_INSERT dla 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();
}

Strumieniowanie wierszy za pomocą obiektu DataReader

Można również użyć dowolnego obiektu pochodzącego z DbDataReader , aby przesyłać strumieniowo wiersze danych do parametru wartości tabeli. Poniższy fragment kodu przedstawia pobieranie danych z bazy danych Oracle przy użyciu elementu OracleCommand i OracleDataReader. Następnie kod konfiguruje element SqlCommand , aby wywołać procedurę składowaną z pojedynczym parametrem wejściowym. Właściwość SqlDbType właściwości SqlParameter jest ustawiona na Structured. AddWithValue przekazuje zestaw wyników OracleDataReader do procedury składowanej jako parametr tabelaryczny.

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

Dalsze kroki

Operacje na danych programu SQL Server w ADO.NET

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2026-01-16