Tabellvärdesparametrar

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 standardsatser för Transact-SQL SELECT . Tabellvärdesparametrar är starkt typade och deras struktur valideras automatiskt. Storleken på tabellvärdesparametrar begränsas endast av serverminnet.

Anmärkning

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 Table-Valued parametrar (databasmotor)	Beskriver hur du skapar och använder tabellvärdesparametrar.
User-Defined tabelltyper	Beskriver användardefinierade tabelltyper som används för att deklarera tabellvärdesparametrar.

Överföra 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 med metoden Update i SqlDataAdapter. Skicka ändringar individuellt eller i batchar. Även i en batch kör servern varje instruktion separat.

• 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 Table-Valued parametertyper

Tabellvärdeparametrar baseras på starkt skrivna tabellstrukturer som definieras med hjälp av Transact-SQL-instruktioner CREATE TYPE . 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 iUser-Defined 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 av 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 Table-Valued parametrar (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-instruktion UPDATE 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 aliasera den, som du ser här, där tabellvärdesparametern 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ärdeparameter 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 Table-Valued parametrar

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änsningarna UNIQUE eller PRIMARY KEY. SQL Server underhåller inte statistik över tabellvärdesparametrar.

• Tabellvärdesparametrar är skrivskyddade i Transact-SQL-koden. 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 satser för att ändra utformningen av tabellvärdesparametrar.

Konfigurera ett SqlParameter-exempel

Installera Microsoft.Data.SqlClient NuGet-paket och importera Microsoft.Data.SqlClient och System.Data. För postsekvenser för poster på klientsidan använder du Microsoft.Data.SqlClient.Server.SqlDataRecord och Microsoft.Data.SqlClient.Server.SqlMetaData. Ersätt inte SQL CLR-typerna på serversidan från Microsoft.SqlServer.Server med andra typer.

Microsoft.Data.SqlClient stöder tabellvärdesparametervärden från DataTable, DbDataReader eller IEnumerable<T> av SqlDataRecord-objekt. Ange parameterns TypeName till namnet på en kompatibel tabelltyp som tidigare skapats på servern. Följande fragment konfigurerar parametern 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 Table-Valued-parameter 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 Table-Valued-parameter till en lagrad procedur

Det här exemplet skickar tabellvärdesparameterdata till en lagrad procedur. Koden anropar GetChanges för att extrahera tillagda rader till den nya DataTable. Den skapar SqlCommand med CommandType inställd på StoredProcedure. Den lägger till parametern med AddWithValue och ställer in SqlDbType till Structured. Slutligen anropar koden 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

Skicka en Table-Valued-parameter till en parameteriserad SQL-instruktion

I följande exempel infogas data i dbo.Categories med en INSERT-instruktion och en SELECT-underfråga som läser tabellvärdesparametern. För en parameteriserad SQL-instruktion anger du namnet på TypeName, en kompatibel tabelltyp som tidigare skapats på servern. I det här exemplet används dbo.CategoryTableType.

Anmärkning

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 använda alla objekt som härleds från DbDataReader för att strömma rader till en tabellvärdesparameter. Följande fragment hämtar data från en Oracle-databas med OracleCommand och OracleDataReader. Koden konfigurerar SqlCommand för att anropa en lagrad procedur med en indataparameter. Den ställer in SqlDbType till Structured och skickar resultatuppsättningen OracleDataReader genom AddWithValue som den tabellvärderade parametern.

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

Se även

• Konfigurera parametrar och parameterdatatyper
• Kommandon och parametrar
• DataAdapter-parameterar
• SQL Server-datahantering i ADO.NET
• översikt över ADO.NET