Táblaértékkel megadott paraméterek

Cikk
03/05/2024

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ékű paraméterek oszlopértékei standard Transact-SQL Standard kiadás LECT utasításokkal érhetők el. A táblaértékű paraméterek erősen be vannak gépelve, és a rendszer automatikusan érvényesíti a szerkezetüket. A táblaértékelt paraméterek méretét csak a kiszolgáló memóriája korlátozza.

Feljegyzé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
Táblaértékű paraméterek használata (adatbázismotor)	Ismerteti, hogyan hozhat létre és használhat táblaértékű paramétereket.
Felhasználó által definiált táblatí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.
Egyéni SQL-utasítások sorozatának létrehozása több sort érintő adatmódosításokhoz, például a metódus SqlDataAdaptermeghívásával Update létrehozottakhoz. 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.

Táblaértékű paramétertípusok létrehozása

A táblaértékes paraméterek a Transact-SQL CREATE TYPE utasításokkal definiált, erősen gépelt 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: Felhasználó által definiált 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 táblaértékű 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 egy IN Standard kiadás RT egyetlen készletalapú művelet végrehajtásához.

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

A táblaértékelt 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 a 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.SqlClienttámogatja a táblaértékkel rendelkező paraméterek feltöltését az objektumokból DbDataReader vagy IEnumerable<T> \ SqlDataRecord objektumokból.DataTable Meg kell adnia egy típusnevet a táblaértékkel rendelkező paraméterhez egy TypeName SqlParametertulajdonsá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ó 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: Táblaértékű paraméter átadása 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"

Bármely származtatott objektumot is használhat, amelyből DbDataReader adatsorokat streamelhet egy táblaértékkel rendelkező paraméterbe, ahogy az ebben a töredékben 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

Táblaértékű 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 metódus használatával kinyeri a hozzáadott sorokat egy GetChanges újbaDataTable. A kód ezután definiál egy SqlCommand, a tulajdonságot a CommandType következőre StoredProcedureállítva: . A SqlParameter rendszer a metódussal tölti ki a AddWithValue kitöltést, és az SqlDbType érték a következőre Structuredvan állítva: . Ezt SqlCommand követően a rendszer a metódussal hajtja végre.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

Táblaértékű paraméter átadása paraméteres SQL-utasításnak

Az alábbi példa bemutatja, hogyan szúrhat be adatokat a dbo-ba. A táblázatot egy IN Standard kiadás RT utasítással kategorizálja egy Standard kiadás LECT-alqueryvel, amely adatforrásként táblaértékes paraméterrel rendelkezik. Ha egy táblaértékű paramétert paraméteres SQL-utasításnak ad át, meg kell adnia a táblaértékelő paraméter típusnevét egy SqlParameterúj TypeName tulajdonság használatával. 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.

Feljegyzés

Ha egy táblaértékű paraméter identitásoszlopának értékét adja meg, ki kell adnia a munkamenethez tartozó Standard kiadás T IDENTITY_IN Standard kiadás RT utasítást.

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

Streamelési sorok 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, hogy egy Oracle-adatbázis adatainak lekérése egy és egy OracleCommand OracleDataReader. 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ág értéke a SqlParameter következő.Structured 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()

Táblaértékkel megadott paraméterek

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

Táblaértékű paramétertípusok létrehozása

Adatok módosítása táblaértékű paraméterekkel (Transact-SQL)

A táblaértékelt paraméterek korlátozásai

SqlParameter-példa konfigurálása

Táblaértékű paraméter átadása tárolt eljárásnak

Táblaértékű paraméter átadása paraméteres SQL-utasításnak

Streamelési sorok DataReaderrel

Lásd még

További források