Táblázat-értékű paraméterek

A táblaértékkel rendelkező paraméterek lehetővé teszik több adatsor üzembe helyezése egy ügyfélalkalmazásból az SQL Serverre anélkül, hogy több ciklikus utazást vagy speciális kiszolgálóoldali logikát kellene megkövetelni az adatok feldolgozásához. Táblaértékkel rendelkező paraméterekkel befoglalhatja egy ügyfélalkalmazás adatsorait, és egyetlen paraméteres parancsban küldheti el az adatokat a kiszolgálónak. A bejövő adatsorok egy táblaváltozóban vannak tárolva, amely a Transact-SQL használatával működtethető.

A táblaértékkel rendelkező paraméterek oszlopértékei standard Transact-SQL SELECT utasításokkal érhetők el. A táblázatos paraméterek erősen típusosak, és a rendszer automatikusan ellenőrzi a szerkezetüket. A táblaértékelt paraméterek méretét csak a kiszolgáló memóriája korlátozza.

Megjegyzés:

Táblaértékkel rendelkező paraméterben nem lehet adatokat visszaadni. A táblaértékkel rendelkező paraméterek csak bemenetek; a OUTPUT kulcsszó nem támogatott.

A táblaértékű paraméterekkel kapcsolatos további információkért tekintse meg az alábbi erőforrásokat.

Erőforrás	Leírás
Table-Valued paraméterek (adatbázismotor) használata	Ismerteti, hogyan hozhat létre és használhat táblaértékű paramétereket.
User-Defined táblázattípusok	A táblaértékelt paraméterek deklarálásához használt, felhasználó által definiált táblatípusokat ismerteti.

Több sor átadása az SQL Server korábbi verzióiban

Az SQL Server 2008 táblaértékkel rendelkező paramétereinek bevezetése előtt a több sornyi adat tárolt eljárásnak vagy paraméteres SQL-parancsnak való továbbításának lehetőségei korlátozottak voltak. A fejlesztő a következő lehetőségek közül választhat, ha több sort ad át a kiszolgálónak:

• Az egyes paraméterek sorozatával több oszlopban és adatsorban szereplő értékeket jelölhet. Az ezzel a módszerrel átadható adatok mennyiségét az engedélyezett paraméterek száma korlátozza. Az SQL Server-eljárások legfeljebb 2100 paraméterrel rendelkezhetnek. A kiszolgálóoldali logika szükséges ahhoz, hogy ezeket az egyedi értékeket egy táblaváltozóba vagy egy ideiglenes táblába állítsa össze feldolgozásra.

• Több adatértéket csoportosított sztringekbe vagy XML-dokumentumokba csomagolhat, majd ezeket a szöveges értékeket átadhatja egy eljárásnak vagy utasításnak. Ehhez az eljárásnak vagy utasításnak tartalmaznia kell az adatstruktúrák érvényesítéséhez és az értékek szétválasztásához szükséges logikát.

• Hozz létre egyéni SQL-utasítások sorozatát több sort érintő adatmódosításokhoz, például a Update metódus meghívásával egy SqlDataAdapter objektumra. A módosításokat egyenként vagy csoportokba kötegelve is elküldheti a kiszolgálónak. Azonban még akkor is, ha több utasítást tartalmazó kötegekben küldik el, a rendszer minden utasítást külön hajt végre a kiszolgálón.

• bcp A segédprogrammal vagy az SqlBulkCopy objektummal számos adatsort tölthet be egy táblába. Bár ez a technika nagyon hatékony, nem támogatja a kiszolgálóoldali feldolgozást, kivéve, ha az adatok egy ideiglenes tábla- vagy táblaváltozóba vannak betöltve.

Table-Valued paramétertípusok létrehozása

Táblázatos paraméterek a Transact-SQL CREATE TYPE utasításokkal definiált, szigorúan tipizált táblastruktúrákon alapulnak. Ahhoz, hogy táblaértékű paramétereket használhasson az ügyfélalkalmazásokban, létre kell hoznia egy táblázattípust, és meg kell határoznia a struktúrát az SQL Serverben. További információ a táblatípusok létrehozásáról: User-Defined Táblázattípusok.

Az alábbi utasítás létrehoz egy CategoryTableType nevű táblázattípust, amely CategoryID és CategoryName oszlopokból áll:

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

A táblatípus létrehozása után az adott típus alapján deklarálhatja a táblaértékelt paramétereket. Az alábbi Transact-SQL töredék bemutatja, hogyan deklarálhat táblaértékű paramétert egy tárolt eljárásdefinícióban. Vegye figyelembe, hogy a READONLY kulcsszó szükséges egy táblaértékű paraméter deklarálásához.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Adatok módosítása Table-Valued paraméterekkel (Transact-SQL)

A táblaértékkel rendelkező paraméterek egy utasítás végrehajtásával több sort érintő, set-alapú adatmódosításokban használhatók. Kijelölheti például egy táblaértékű paraméter összes sorát, és beszúrhatja őket egy adatbázistáblába, vagy létrehozhat egy frissítési utasítást úgy, hogy egy táblaértékű paramétert csatlakozik a frissíteni kívánt táblához.

Az alábbi Transact-SQL UPDATE utasítás bemutatja, hogyan használható táblaértékű paraméter a Kategóriák táblához való csatlakozással. Ha táblaértékkel rendelkező paramétert használ egy JOIN záradékban, akkor azt is aliasként kell használnia, ahogy az itt látható, ahol a táblaértékként megadott paraméter "ec" néven van elnevezve:

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

Ez a Transact-SQL példa bemutatja, hogyan jelölhet ki sorokat egy táblaértékkel rendelkező paraméterből az INSERT egyetlen készletalapú műveletben való végrehajtásához.

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

A Table-Valued paraméterek korlátozásai

A táblaértékelő paraméterekre számos korlátozás vonatkozik:

• A táblaértékelt paraméterek nem adhatók át a CLR felhasználó által definiált függvényeinek.

• A táblaértékkel rendelkező paraméterek csak az EGYEDI vagy AZ ELSŐDLEGES KULCS korlátozásainak támogatásához indexelhetők. Az SQL Server nem tart fenn statisztikát a táblaértékkel megadott paraméterekről.

• A táblaértékkel rendelkező paraméterek írásvédettek Transact-SQL kódban. Táblázatértékkel rendelkező paraméter soraiban nem frissítheti az oszlopértékeket, és nem szúrhat be és nem törölhet sorokat. A táblaértékelő paraméterben tárolt eljárásnak vagy paraméteres utasításnak átadott adatok módosításához be kell szúrnia az adatokat egy ideiglenes táblába vagy egy táblaváltozóba.

• Az ALTER TABLE utasításokkal nem módosíthatja a táblaértékelt paraméterek kialakítását.

SqlParameter-példa konfigurálása

System.Data.SqlClient támogatja a táblaértékű paraméterek feltöltését, amelyeket DataTable, DbDataReader vagy IEnumerable<T> \ SqlDataRecord objektumokból lehet előállítani. Meg kell adnia egy típusnevet a táblaértékkel rendelkező paraméterhez egy TypeNameSqlParametertulajdonság használatával. A TypeName névnek meg kell egyeznie a kiszolgálón korábban létrehozott kompatibilis típus nevével. Az alábbi kódrészlet bemutatja, hogyan konfigurálható SqlParameter adatok beszúrása.

Az alábbi példában a addedCategories változó tartalmaz egy DataTable. A változó feltöltésének módjáról a következő szakaszban található példákból tájékozódhat, amely egy Table-Valued paramétert ad át egy tárolt eljárásnak.

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

Használhat bármilyen származtatott objektumot az DbDataReader elemből, hogy adatsorokat továbbítson egy táblázatos paraméterbe, ahogyan ez a következő példában látható:

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

Table-Valued paraméter átadása tárolt eljárásnak

Ez a példa bemutatja, hogyan adhatja át a táblaértékkel rendelkező paraméteradatokat egy tárolt eljárásnak. A kód a DataTable metódus használatával kivonja a hozzáadott sorokat egy új GetChanges-ba. A kód ezután definiál egy SqlCommand-t, és a CommandType tulajdonságot StoredProcedure-ra állítja. A SqlParameter a AddWithValue metódus használatával kerül kitöltésre, és az SqlDbType értéke Structured-ra van állítva. A SqlCommand ezt követően a ExecuteNonQuery módszerrel van végrehajtva.

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

Table-Valued paraméter átadása paraméteres SQL-utasításnak

Az alábbi példa bemutatja, hogyan lehet adatokat beszúrni a dbo.Categories táblába egy INSERT utasítással, amely egy SELECT allekérdezést használ adatforrásként, és táblaérték típusú paraméterrel rendelkezik. Ha egy táblaértékű paramétert egy paraméterezett SQL-utasításnak ad át, a táblaértékű paraméter típusnevét az TypeName új SqlParameter tulajdonság használatával kell megadnia. Ennek TypeName meg kell egyeznie a kiszolgálón korábban létrehozott kompatibilis típus nevével. A példában szereplő kód a TypeName tulajdonság használatával hivatkozik a dbo-ban definiált típusstruktúrára. CategoryTableType.

Megjegyzés:

Ha egy táblaértékű paraméter identitásoszlopának értékét adja meg, ki kell adnia a SET IDENTITY_INSERT utasítást a munkamenethez.

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

Adattáblák streamelése a DataReaderrel

Bármely származtatott DbDataReader objektumot használhat az adatsorok táblázatértékkel rendelkező paraméterbe való streameléséhez is. Az alábbi kódrészlet bemutatja, hogyan lehet egy Oracle-adatbázisból adatokat lekérni OracleCommand segítségével és OracleDataReader segítségével. A kód ezután konfigurál egy SqlCommand tárolt eljárás egyetlen bemeneti paraméterrel való meghívására. A SqlDbType tulajdonsága a SqlParameter értéke Structured-ra van állítva. Az AddWithValue eredményhalmazt OracleDataReader táblaértékkel rendelkező paraméterként adja át a tárolt eljárásnak.

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

Lásd még

• Paraméterek és paraméteradattípusok konfigurálása
• Parancsok és paraméterek
• DataAdapter-paraméterek
• SQL Server-adatműveletek a ADO.NET
• ADO.NET áttekintése