Tabellvärdesparametrar

Artikel
03/02/2024

Tabellvärdesparametrar är ett enkelt sätt att konvertera flera rader med data från ett klientprogram till SQL Server utan att kräva flera turer eller särskild logik på serversidan för bearbetning av data. Du kan använda tabellvärdesparametrar för att kapsla in rader med data i ett klientprogram och skicka data till servern i ett enda parameteriserat kommando. Inkommande datarader lagras i en tabellvariabel som sedan kan användas med hjälp av Transact-SQL.

Kolumnvärden i tabellvärdesparametrar kan nås med transact-SQL SELECT-standardinstruktioner. Tabellvärdesparametrar skrivs starkt och deras struktur verifieras automatiskt. Storleken på tabellvärdesparametrar begränsas endast av serverminnet.

Kommentar

Du kan inte returnera data i en tabellvärdesparameter. Tabellvärdesparametrar är endast indata. nyckelordet OUTPUT stöds inte.

Mer information om tabellvärdesparametrar finns i följande resurser.

Resurs	beskrivning
Använda tabellvärdesparametrar (databasmotor)	Beskriver hur du skapar och använder tabellvärdesparametrar.
Användardefinierade tabelltyper	Beskriver användardefinierade tabelltyper som används för att deklarera tabellvärdesparametrar.

Skicka flera rader i tidigare versioner av SQL Server

Innan tabellvärdesparametrar introducerades i SQL Server 2008 var alternativen för att skicka flera rader med data till en lagrad procedur eller ett parameteriserat SQL-kommando begränsades. En utvecklare kan välja mellan följande alternativ för att skicka flera rader till servern:

Använd en serie enskilda parametrar för att representera värdena i flera kolumner och rader med data. Mängden data som kan skickas med den här metoden begränsas av antalet tillåtna parametrar. SQL Server-procedurer kan ha högst 2 100 parametrar. Logik på serversidan krävs för att montera dessa enskilda värden i en tabellvariabel eller en tillfällig tabell för bearbetning.
Paketera flera datavärden i avgränsade strängar eller XML-dokument och skicka sedan textvärdena till en procedur eller instruktion. Detta kräver att proceduren eller -instruktionen innehåller den logik som krävs för att verifiera datastrukturerna och separera värdena.
Skapa en serie enskilda SQL-instruktioner för dataändringar som påverkar flera rader, till exempel de som skapas genom att anropa Update metoden för en SqlDataAdapter. Ändringar kan skickas till servern individuellt eller batchvis i grupper. Men även när de skickas i batchar som innehåller flera instruktioner körs varje -instruktion separat på servern.
bcp Använd verktygsprogrammet eller SqlBulkCopy objektet för att läsa in många rader med data i en tabell. Även om den här tekniken är mycket effektiv stöder den inte bearbetning på serversidan om inte data läses in i en tillfällig tabell eller tabellvariabel.

Skapa tabellvärdesparametertyper

Tabellvärdeparametrar baseras på starkt skrivna tabellstrukturer som definieras med hjälp av Transact-SQL CREATE TYPE-uttryck. Du måste skapa en tabelltyp och definiera strukturen i SQL Server innan du kan använda tabellvärdesparametrar i dina klientprogram. Mer information om hur du skapar tabelltyper finns i Användardefinierade tabelltyper.

Följande instruktion skapar en tabelltyp med namnet CategoryTableType som består av kolumnerna CategoryID och CategoryName:

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

När du har skapat en tabelltyp kan du deklarera tabellvärdesparametrar baserat på den typen. Följande Transact-SQL-fragment visar hur du deklarerar en tabellvärdeparameter i en definition för lagrad procedur. Observera att nyckelordet READONLY krävs för att deklarera en tabellvärdesparameter.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Ändra data med tabellvärdesparametrar (Transact-SQL)

Tabellvärdesparametrar kan användas i uppsättningsbaserade dataändringar som påverkar flera rader genom att köra en enda instruktion. Du kan till exempel välja alla rader i en tabellvärdeparameter och infoga dem i en databastabell, eller så kan du skapa en uppdateringsinstrukitet genom att koppla en tabellvärdesparameter till den tabell som du vill uppdatera.

Följande Transact-SQL UPDATE-instruktion visar hur du använder en tabellvärdesparameter genom att koppla den till tabellen Kategorier. När du använder en tabellvärdesparameter med en JOIN i en FROM-sats måste du även alias den, som du ser här, där parametern table-valued aliaseras som "ec":

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

Det här Transact-SQL-exemplet visar hur du väljer rader från en tabellvärdesparameter för att utföra en INSERT i en enda uppsättningsbaserad åtgärd.

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

Begränsningar för tabellvärdesparametrar

Det finns flera begränsningar för tabellvärdesparametrar:

Du kan inte skicka tabellvärdesparametrar till CLR-användardefinierade funktioner.
Tabellvärdeparametrar kan bara indexeras för att stödja begränsningar för UNIK eller PRIMÄR NYCKEL. SQL Server underhåller inte statistik över tabellvärdesparametrar.
Tabellvärdesparametrar är skrivskyddade i Transact-SQL-kod. Du kan inte uppdatera kolumnvärdena i raderna i en tabellvärdesparameter och du kan inte infoga eller ta bort rader. Om du vill ändra data som skickas till en lagrad procedur eller parameteriserad instruktion i tabellvärdesparametern måste du infoga data i en tillfällig tabell eller i en tabellvariabel.
Du kan inte använda ALTER TABLE-instruktioner för att ändra utformningen av tabellvärdesparametrar.

Konfigurera ett SqlParameter-exempel

System.Data.SqlClienthar stöd för att fylla i tabellvärdesparametrar från DataTableeller \ DbDataReader IEnumerable<T>SqlDataRecord objekt. Du måste ange ett typnamn för parametern table-valued med hjälp TypeName av egenskapen för en SqlParameter. TypeName Måste matcha namnet på en kompatibel typ som tidigare skapats på servern. Följande kodfragment visar hur du konfigurerar SqlParameter för att infoga data.

I följande exempel innehåller variabeln addedCategories en DataTable. Om du vill se hur variabeln fylls i kan du se exemplen i nästa avsnitt, Skicka en tabellvärdeparameter till en lagrad procedur.

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

Du kan också använda alla objekt som härleds från DbDataReader för att strömma rader med data till en tabellvärdeparameter, som du ser i det här fragmentet:

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

Skicka en tabellvärdeparameter till en lagrad procedur

Det här exemplet visar hur du skickar tabellvärdesparameterdata till en lagrad procedur. Koden extraherar tillagda rader till en ny DataTable med hjälp GetChanges av metoden . Koden definierar sedan en SqlCommand, som anger CommandType egenskapen till StoredProcedure. SqlParameter Fylls i med hjälp AddWithValue av metoden och SqlDbType är inställd på Structured. SqlCommand Körs sedan med hjälp ExecuteNonQuery av metoden .

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

Skicka en tabellvärdesparameter till en parameteriserad SQL-instruktion

I följande exempel visas hur du infogar data i dbo. Kategoritabell med hjälp av en INSERT-instruktion med en SELECT-underfråga som har en tabellvärdeparameter som datakälla. När du skickar en tabellvärdesparameter till en parameteriserad SQL-instruktion måste du ange ett typnamn för parametern table-valued med hjälp av den nya TypeName egenskapen för en SqlParameter. Detta TypeName måste matcha namnet på en kompatibel typ som tidigare skapats på servern. Koden i det här exemplet använder TypeName egenskapen för att referera till den typstruktur som definierats i dbo. CategoryTableType.

Kommentar

Om du anger ett värde för en identitetskolumn i en tabellvärdesparameter måste du utfärda instruktionen SET IDENTITY_INSERT för sessionen.

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

Strömma rader med en DataReader

Du kan också använda alla objekt som härleds från DbDataReader för att strömma rader med data till en tabellvärdeparameter. Följande kodfragment visar hur du hämtar data från en Oracle-databas med hjälp av en OracleCommand och en OracleDataReader. Koden konfigurerar sedan en SqlCommand för att anropa en lagrad procedur med en enda indataparameter. Egenskapen SqlDbType för är inställd på SqlParameter Structured. Skickar AddWithValue resultatuppsättningen OracleDataReader till den lagrade proceduren som en tabellvärdesparameter.

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

Dela via