Parametri con valori di tabella

I parametri con valori di tabella offrono un modo semplice per effettuare il marshalling di più righe di dati da un'applicazione client a SQL Server senza richiedere più round trip o logica speciale sul lato server per l'elaborazione dei dati. È possibile usare parametri con valori di tabella per incapsulare 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 che può quindi essere gestita tramite Transact-SQL.

È possibile accedere ai valori di colonna nei parametri con valori di tabella usando istruzioni standard Transact-SQL SELECT . I parametri con valori di tabella sono fortemente tipizzati e la relativa struttura viene convalidata automaticamente. Le dimensioni dei parametri con valori di tabella sono limitate solo dalla memoria del server.

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

Passaggio di più righe nelle versioni precedenti di SQL Server

Prima dell'introduzione dei parametri con valori di tabella a SQL Server 2008, le opzioni per passare più righe di dati a una stored procedure o 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 singoli parametri per rappresentare i valori in più colonne e righe di dati. La quantità di dati che è possibile passare tramite questo metodo è limitata dal numero di parametri consentiti. Le procedure di SQL Server possono avere 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 documenti XML e quindi passare tali valori di testo a una routine o un'istruzione. È quindi necessario che la routine o l'istruzione includano la logica necessaria per convalidare le strutture di dati e separare i valori.

• Creare una serie di singole istruzioni SQL per le modifiche ai dati che influiscono su più righe, ad esempio quelle create con il metodo Update di SqlDataAdapter. Invia modifiche singolarmente o in batch. Anche all'interno di un batch, il server esegue ogni istruzione separatamente.

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

Creazione di tipi di parametri Table-Valued

I parametri con valori di tabella sono basati su strutture di tabella fortemente tipizzate definite tramite istruzioni Transact-SQL CREATE TYPE. È necessario creare un tipo di tabella e definire la struttura in SQL Server prima di poter usare parametri con valori di tabella nelle applicazioni client. Per altre informazioni sulla creazione di tipi di tabella, vedere User-Defined Tipi di tabella.

L'istruzione seguente crea 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 parametri con valori di tabella in base a tale tipo. Il frammento di Transact-SQL seguente illustra come dichiarare un parametro con valori di tabella in una definizione di stored procedure. Si noti che la parola chiave READONLY è necessaria per dichiarare un parametro di tipo tabella.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modifica dei dati con parametri Table-Valued (Transact-SQL)

I parametri con valori di tabella possono essere usati nelle modifiche ai dati basate su set che influiscono su più righe eseguendo una singola istruzione. Ad esempio, è possibile selezionare tutte le righe in un parametro con valori di tabella e inserirle in una tabella di database oppure creare un'istruzione di aggiornamento unendo un parametro con valori di tabella alla tabella da aggiornare.

L'istruzione Transact-SQL UPDATE seguente illustra come usare un parametro con valori di tabella unendo il parametro alla tabella Categories. Quando si usa un parametro con valori di tabella con un JOIN in una clausola FROM, è anche necessario aliasrlo, come illustrato di seguito, in cui il parametro con valori di tabella viene aliasato come "ec":

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

Questo esempio Transact-SQL illustra come selezionare le righe da un parametro con valori di tabella per eseguire un'operazione INSERT basata su un singolo set.

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

Limitazioni dei parametri di Table-Valued

Esistono diverse limitazioni per i parametri con valori di tabella:

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

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

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

• Non è possibile utilizzare istruzioni ALTER TABLE per modificare la struttura dei parametri con valori di tabella.

Configurazione di un esempio di SqlParameter

Installare il pacchetto NuGet Microsoft.Data.SqlClient e importare Microsoft.Data.SqlClient e System.Data. Per le sequenze di record sul lato client, usare Microsoft.Data.SqlClient.Server.SqlDataRecord e Microsoft.Data.SqlClient.Server.SqlMetaData. Non sostituire i tipi CLR lato server SQL con Microsoft.SqlServer.Server.

Microsoft.Data.SqlClient supporta i valori dei parametri con valori di tabella da DataTable, DbDataReader o IEnumerable<T> di oggetti SqlDataRecord. Impostare il TypeName del parametro sul nome di un tipo di tabella compatibile creato in precedenza sul server. Il frammento seguente configura il parametro per l'inserimento dei dati.

Nell'esempio seguente la addedCategories variabile contiene un oggetto DataTable. Per informazioni sul popolamento della variabile, vedere gli esempi nella sezione successiva Passando un parametro Table-Valued 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";

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

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

' 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

Passaggio di un parametro Table-Valued a una stored procedure

In questo esempio vengono passati i dati dei parametri con valori di tipo tabella a una stored procedure. Il codice chiama GetChanges per estrarre le righe aggiunte nel nuovo DataTable. Crea l'oggetto SqlCommand con CommandType impostato su StoredProcedure. Aggiunge il parametro con AddWithValue e imposta SqlDbType a Structured. Infine, il codice chiama 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

Passaggio di un parametro Table-Valued a un'istruzione SQL con parametri

Nell'esempio seguente vengono inseriti dati in dbo.Categories con un'istruzione INSERT e una SELECT sottoquery che legge il parametro con valori di tabella. Per un'istruzione SQL con parametri, impostare TypeName sul nome di un tipo di tabella compatibile creato in precedenza nel server. Questo esempio usa dbo.CategoryTableType.

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

Streaming di righe con un DataReader

È possibile usare qualsiasi oggetto derivato da DbDataReader per trasmettere le righe a un parametro strutturato a livello di tabella. Il frammento seguente recupera i dati da un database Oracle con OracleCommand e OracleDataReader. Il codice configura SqlCommand per richiamare una stored procedure con un parametro di input. Imposta SqlDbType su Structured e passa il set di risultati OracleDataReader tramite AddWithValue 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();

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

Vedere anche

• configurazione dei parametri e dei tipi di dati dei parametri
• Comandi e parametri
• Parametri DataAdapter
• Operazioni dei dati di SQL Server in ADO.NET
• Panoramica di ADO.NET