Parametri con valori di tabella

  • Articolo

Scarica ADO.NET

I parametri con valori di tabella rappresentano un modo semplice per eseguire il marshalling di più righe di dati da un'applicazione client in SQL Server. Non richiedono più round trip né di logica speciale sul lato server per l'elaborazione dei dati. I parametri con valori di tabella possono essere usati per incapsulare le righe di dati in un'applicazione client e inviare i dati al server in un singolo comando con parametri. Le righe di dati in ingresso vengono archiviate in una variabile di tabella su cui è possibile operare tramite Transact-SQL.

I valori di colonna nei parametri con valori di tabella sono accessibili tramite istruzioni Transact-SQL SELECT standard. I parametri con valori di tabella sono fortemente tipizzati e la convalida della relativa struttura è automatica. La dimensione dei parametri con valori di tabella è limitata solo dalla memoria del server.

Nota

Non è possibile restituire dati in un parametro con valori di tabella. I parametri con valori di tabella sono di solo input; la parola chiave OUTPUT non è supportata.

Per altre informazioni sui parametri con valori di tabella, vedere le risorse seguenti.

Risorsa Descrizione
Usare parametri con valori di tabella (motore di database) Viene descritto come creare e usare parametri con valori di tabella.
Creazione di un tipo di tabella definito dall'utente Vengono descritti i tipi di tabella definiti dall'utente usati per dichiarare parametri con valori di tabella.
Tipi di tabella definiti dall'utente Vengono descritti i tipi di tabella definiti dall'utente usati per dichiarare parametri con valori di tabella.

Passaggio di più righe nelle versioni precedenti di SQL Server

Prima dell'introduzione dei parametri con valori di tabella, le opzioni per passare più righe di dati a una stored procedure oppure a un comando SQL con parametri erano limitate. Uno sviluppatore può scegliere tra le opzioni seguenti per passare più righe al server:

  • usare una serie di parametri singoli per rappresentare i valori in più colonne e righe di dati. La quantità di dati che è possibile passare usando questo metodo è limitata dal numero di parametri consentiti. Le stored procedure di SQL Server possono includere al massimo 2100 parametri. La logica lato server è necessaria per assemblare questi singoli valori in una variabile di tabella o in una tabella temporanea per l'elaborazione.

  • Aggregare più valori di dati in stringhe delimitate o in documenti XML, quindi passare i valori di testo a una procedura o a un'istruzione. Questo metodo richiede che la routine o l'istruzione includa la logica per convalidare le strutture dei dati e disaggregare i valori.

  • Creare una serie di singole istruzioni SQL per le modifiche ai dati che interessano più righe, ad esempio quelle create chiamando il metodo Update di un SqlDataAdapter. È possibile inviare le modifiche al server singolarmente o in batch in gruppi. Tuttavia, anche in caso di invio in batch contenenti più istruzioni, ogni istruzione viene eseguita separatamente sul server.

  • Usare il programma di utilità bcp o l'oggetto SqlBulkCopy per caricare molte righe di dati in una tabella. Sebbene questa tecnica sia efficiente, non supporta l'elaborazione sul lato server, a meno che i dati non vengano caricati in una tabella temporanea o in una variabile di tabella.

Creazione dei tipi di parametro con valori di tabella

I parametri con valori di tabella sono basati su strutture di tabella fortemente tipizzate definite tramite istruzioni CREATE TYPE Transact-SQL. Per usare i parametri con valori di tabella nelle applicazioni client, è prima necessario creare un tipo di tabella e definire la struttura in SQL Server. Per altre informazioni sulla creazione dei tipi di dati, vedere Usare parametri con valori di tabella (motore di database).

Nell'istruzione seguente viene creato un tipo di tabella denominato CategoryTableType costituito da colonne CategoryID e CategoryName:

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

Dopo aver creato un tipo di tabella, è possibile dichiarare i parametri con valori di tabella in base a tale tipo. Nel frammento Transact-SQL seguente viene illustrato come dichiarare un parametro con valori di tabella in una definizione di stored procedure. La parola chiave READONLY è obbligatoria per dichiarare un parametro con valori di tabella.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modifica di dati con parametri con valori di tabella (Transact-SQL)

I parametri con valori di tabella possono essere usati nelle modifiche ai dati basate su set che interessano più righe eseguendo un'unica istruzione. È possibile, ad esempio, selezionare tutte le righe in un parametro con valori di tabella e inserirle in una tabella di database oppure creare un'istruzione di aggiornamento tramite l'aggiunta di un parametro con valori di tabella alla tabella che si desidera aggiornare.

L'istruzione UPDATE Transact-SQL seguente illustra come usare un parametro con valori di tabella tramite la sua unione in join con la tabella Categories. Quando si usa un parametro con valori di tabella con un JOIN in una clausola FROM, è anche necessario usare un alias per il parametro, come illustrato di seguito, dove per il parametro con valori di tabella viene usato l'alias "ec":

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

In questo esempio Transact-SQL viene illustrato come selezionare le righe da un parametro con valori di tabella per eseguire un'istruzione INSERT in una singola operazione basata su set.

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

Limitazioni dei parametri con valori di tabella

Esistono diverse limitazioni per i parametri con valori di tabella:

  • Non è possibile passare parametri con valori di tabella a funzioni CLR definite dall'utente.

  • I parametri con valori di tabella possono essere indicizzati solo per supportare vincoli UNIQUE o PRIMARY KEY. SQL Server non gestisce statistiche su parametri con valori di tabella.

  • I parametri con valori di tabella sono di sola lettura nel codice Transact-SQL. Non è possibile aggiornare i valori delle colonne nelle righe di un parametro con valori di tabella e non è possibile inserire o eliminare righe. Per modificare i dati trasferiti a un'istruzione stored procedure o parametrizzata in un parametro con valori di tabella, è necessario inserire i dati in una tabella temporanea o in una variabile di tabella.

  • Non è possibile usare le istruzioni ALTER TABLE per modificare la progettazione dei parametri con valori di tabella.

Configurazione di un esempio di SqlParameter

Microsoft.Data.SqlClient supporta il popolamento di parametri con valori di tabella da oggetti DataTable, DbDataReader o IEnumerable<T> \ SqlDataRecord. Specificare un nome di tipo per il parametro con valori di tabella usando la proprietà TypeName di SqlParameter. Il TypeName deve corrispondere al nome di un tipo compatibile creato in precedenza nel server. Il frammento di codice seguente illustra come configurare SqlParameter per inserire dati.

Nell'esempio seguente la variabile addedCategories contiene un oggetto DataTable. Per vedere come viene popolata la variabile, vedere gli esempi nella sezione successiva Passaggio di un parametro con valori di tabella a una stored procedure.

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

È anche possibile usare qualsiasi oggetto derivato da DbDataReader per trasmettere righe di dati a un parametro con valori di tabella, come illustrato in questo frammento:

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

Passaggio di un parametro con valori di tabella a una stored procedure

In questo esempio viene illustrato come trasferire i dati dei parametri con valori di tabella a una stored procedure. Il codice estrae le righe aggiunte in una nuova DataTable usando il metodo GetChanges. Il codice definisce quindi un SqlCommand, impostando la proprietà CommandType su StoredProcedure. Il SqlParameter viene popolato usando il metodo AddWithValue e SqlDbType viene impostato su Structured. SqlCommand viene eseguito quindi usando il metodo 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();
}

Trasferimento di un parametro con valori di tabella a un'istruzione SQL con parametri

Nell'esempio seguente viene illustrato come inserire dati nella tabella dbo.Categories tramite un'istruzione INSERT con una sottoquery SELECT che dispone di un parametro con valori di tabella come origine dati. Quando si trasferisce un parametro con valori di tabella a un'istruzione SQL con parametri, è necessario specificare un nome di tipo per il parametro con valori di tabella tramite la nuova proprietà TypeName di un SqlParameter. Questo TypeName deve corrispondere al nome di un tipo compatibile creato in precedenza nel server. Il codice in questo esempio usa la proprietà TypeName per fare riferimento alla struttura del tipo definita in dbo.CategoryTableType.

Nota

Se si fornisce un valore per una colonna Identity in un parametro con valori di tabella, è necessario eseguire l'istruzione SET IDENTITY_INSERT per la sessione.

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

Streaming di righe con un DataReader

È anche possibile usare qualsiasi oggetto derivato da DbDataReader per trasmettere righe di dati a un parametro con valori di tabella. Nel frammento di codice seguente viene illustrato il recupero di dati da un database Oracle tramite un OracleCommand e un OracleDataReader. Il codice configura quindi un SqlCommand per richiamare una stored procedure con un singolo parametro di input. La proprietà SqlDbType del SqlParameter è impostata su Structured. AddWithValue trasferisce il set di risultati OracleDataReader alla stored procedure come parametro con valori di tabella.

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

Passaggi successivi