Táblaértékkel megadott paraméterek

ADO.NET letöltése

A táblaértékkel rendelkező paraméterekkel egyszerűen menthet több adatsort egy ügyfélalkalmazásból az SQL Serverre. Nem igényelnek több oda-oda utazást vagy speciális kiszolgálóoldali logikát 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á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.

Megjegyzés:

Táblaértékkel rendelkező paraméterben nem lehet adatokat visszaadni. Az táblaértékkel rendelkező paraméterek csak bemenetként használhatók; az 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.

Resource	Description
Table-Valued paraméterek (adatbázismotor) használata	Ismerteti, hogyan hozhat létre és használhat táblaértékű paramétereket.
Felhasználó által definiált táblatípus létrehozása	A táblaértékelt paraméterek deklarálásához használt, felhasználó által definiált táblatípusokat ismerteti.
Felhasználó által definiált 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

A táblaértékű paraméterek bevezetése előtt a több sornyi adat tárolt eljárásnak vagy paraméteres SQL-parancsnak való átadá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 a módszerhez az eljárásnak vagy utasításnak tartalmaznia kell az adatstruktúrák ellenőrzésére és az értékek szétválasztására szolgáló logikát.

• Több sort érintő adatmódosításokhoz egyéni SQL-utasítások sorozatát hozzon létre, például a Update metódus SqlDataAdapter általi meghívásával létrehozott esetekben. 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 hatékony, nem támogatja a kiszolgálóoldali feldolgozást, hacsak az adatokat nem tölti be egy ideiglenes tábla- vagy táblaváltozóba.

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

A táblaértékek 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áblatípust, és meg kell határoznia a struktúrát az SQL Serverben. A táblatípusok létrehozásáról további információt a Table-Valued paraméterek (adatbázismotor) használata című témakörben talál.

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. 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ékkel rendelkező 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 táblaértékelt paraméterek korlátozásai

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

• Nem adhat át táblaértékű paramétereket 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 tartja fenn a táblaértékelt paraméterek statisztikáit.

• A táblaértékkel rendelkező paraméterek írásvédettek Transact-SQL kódban. Nem frissítheti az oszlopértékeket egy táblaértékű paraméter soraiban, és nem szúrhat be és nem törölhet sorokat. Ha egy táblaértékű paraméterben tárolt eljárásnak vagy paraméteres utasításnak átadott adatokat szeretne módosítani, 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

Microsoft.Data.SqlClient támogatja a táblaértékkel rendelkező paraméterek feltöltését a DataTable, DbDataReader, vagy IEnumerable<T> \ SqlDataRecord objektumokból. Adja meg a táblaértékű paraméter típusnevét a TypeName tulajdonságának SqlParameter 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";

Olyan objektumokat is használhat, amelyek a DbDataReader-ből származnak, hogy adatsorokat streamelhessen egy paraméterbe, amely táblaértékkel rendelkezik, ahogy az alábbi töredék mutatja:

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

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 GetChanges metódus használatával kinyeri az újonnan hozzáadott sorokat egy DataTable-ba. A kód ezután definiál egy SqlCommand, amely beállítja a CommandType tulajdonságot StoredProcedure értékre. A SqlParameter a AddWithValue módszerrel van kitöltve, és az SqlDbTypeStructured értékre van állítva. A SqlCommand ezután a ExecuteNonQuery módszerrel kerül végrehajtásra.

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

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.Categories táblába egy INSERT utasítással, amelyhez egy SELECT al-lekérdezést használunk, amely táblaértékes paramétert tartalmaz adatforrásként. Ha egy táblaértékű paramétert paraméteres SQL-utasításnak ad át, meg kell adnia a táblaértékű paraméter típusnevét egy új TypeName tulajdonság használatával SqlParameter. 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();
}

Adatfolyam sorok egy 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 adatokat lekérni egy Oracle-adatbázisból egy OracleCommand és egy 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ág értéke a SqlParameter következő.Structured A AddWithValue a OracleDataReader eredményhalmazt táblaértékű 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();

Következő lépések

• SQL Server-adatműveletek az ADO.NET-ben