Nyelv

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ékű paraméterek oszlopértékei standard Transact-SQL SELECT utasítások használatával é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ékű paraméterek csak bemenetiek; 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.

  • Hozzon létre egy sornyi egyedi SQL-utasítást olyan adatmódosításokhoz, amelyek több sort érintenek, például a SqlDataAdapterUpdate metódusa által létrehozottakhoz. Módosítások elküldése egyenként vagy kötegekben. A kiszolgáló még egy kötegen belül is külön hajtja végre az egyes utasításokat.

  • 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

A táblaértékes paraméterek Transact-SQL utasítások használatával CREATE TYPE definiált, erősen gépelt táblázatstruktú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 egy FROM záradékban lévő JOIN elemmel táblaértékű paramétert használ, akkor álnevet is kell adnia neki, ahogy az itt látható, ahol a táblaértékű 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 egyetlen készletalapú művelet végrehajtásához INSERT .

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ékű paraméterek csak a UNIQUE vagy PRIMARY KEY korlátozások támogatására 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.

  • A táblaértékű paraméterek definíciójának módosításához nem használhatók ALTER TABLE utasítások.

SqlParameter-példa konfigurálása

Telepítse a Microsoft.Data.SqlClient NuGet-csomagot, és importálja a Microsoft.Data.SqlClient és System.Data elemeket. Ügyféloldali rekordszekvenciákhoz használja a Microsoft.Data.SqlClient.Server.SqlDataRecord és Microsoft.Data.SqlClient.Server.SqlMetaData elemeket. Ne helyettesítse az SQL CLR kiszolgálóoldali típusait a(z) Microsoft.SqlServer.Server elemből.

Microsoft.Data.SqlClient támogatja a táblaértékű paraméterértékeket a DataTable objektumok DbDataReader, IEnumerable<T> vagy SqlDataRecord eleméből. Állítsa a paraméter TypeName értékét a kiszolgálón korábban létrehozott kompatibilis táblatípus nevére. Az alábbi kódrészlet az adatbeszúráshoz konfigurálja a paramétert.

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 egy tárolt eljárásnak továbbítja a táblaértékű paraméteradatokat. A kód meghívja a GetChanges elemet, hogy a hozzáadott sorokat az új DataTable-be nyerje ki. Létrehozza a CommandType elemet, amelyben a(z) SqlCommand értéke StoredProcedure. Hozzáadja a paramétert a(z) AddWithValue paraméterrel, és SqlDbType értékét Structured értékre állítja. Végül a kód meghívja 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

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

Az alábbi példa adatokat szúr be a(z) dbo.Categories elembe egy INSERT utasítással és egy SELECT olyan részkikérdezéssel, amely beolvassa a táblaértékű paramétert. Paraméteres SQL-utasítás esetén állítsa a(z) TypeName értékét a kiszolgálón korábban létrehozott kompatibilis táblatípus nevére. Ez a példa a dbo.CategoryTableType-t használja.

Megjegyzés:

Ha értéket ad meg egy táblázatértékű paraméter identitásoszlopához, ki kell adnia az adott munkamenethez a SET IDENTITY_INSERT 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

Adattáblák streamelése a DataReaderrel

A sorok táblázatértékű paraméterbe való streameléséhez a DbDataReader-ból származtatott bármely objektumot használhat. Az alábbi töredék adatokat kér le egy Oracle-adatbázisból OracleDataReader és OracleCommand használatával. A kód a SqlCommand elemet egy tárolt eljárás egyetlen bemeneti paraméterrel való meghívására konfigurálja. A SqlDbType értékét Structured értékre állítja, és a OracleDataReader eredményhalmazt AddWithValue segítségével adja át táblaértékű paraméterként.

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

  • Utolsó frissítés időpontja: