Condividi tramite

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 SELECT standard Transact-SQL. 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.

Annotazioni

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

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

Conto risorse Descrizione
Usare parametri con valori di tabella (motore di database) Viene descritto come creare e usare parametri con valori di tabella.
Tipi di tabellaUser-Defined Vengono descritti i tipi di tabella definiti dall'utente utilizzati 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 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 chiamando il Update metodo di un oggetto SqlDataAdapter. Le modifiche possono essere inviate al server singolarmente o in batch in gruppi. Tuttavia, anche se inviati in batch che contengono più istruzioni, ogni istruzione viene eseguita separatamente nel server.

  • 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 tipizzate definite tramite le 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 con valori di 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.

Nell'istruzione UPDATE seguente Transact-SQL viene illustrato come usare un parametro con valori di tabella aggiungendolo alla tabella Categories. Quando si utilizza un parametro di tipo tabella con un JOIN in una clausola FROM, è necessario anche aliasarlo, come illustrato di seguito, in cui il parametro di tipo 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;

In questo esempio Transact-SQL viene illustrato come selezionare le righe da un parametro con valori di tabella per eseguire un'operazione INSERT in un'unica operazione basata su 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 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 progettazione dei parametri con valori di tabella.

Configurazione di un esempio di SqlParameter

System.Data.SqlClient supporta il popolamento di parametri con valori di tabella da oggetti DataTable, DbDataReader o IEnumerable<T> \ SqlDataRecord. È necessario specificare un nome di tipo per il parametro con valori di tabella usando la TypeName proprietà di un oggetto SqlParameter. Deve TypeName corrispondere al nome di un tipo compatibile creato in precedenza nel server. Il frammento di codice seguente illustra come configurare SqlParameter per l'inserimento di 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 viene illustrato come passare i dati dei parametri con valori di tabella a una stored procedure. Il codice estrae le righe aggiunte in un nuovo DataTable usando il metodo GetChanges. Il codice definisce quindi un SqlCommandoggetto , impostando la CommandType proprietà su StoredProcedure. L'oggetto SqlParameter viene popolato utilizzando il AddWithValue metodo e l'oggetto SqlDbType è impostato su Structured. l'oggetto SqlCommand viene quindi eseguito utilizzando il ExecuteNonQuery metodo .

// 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 viene illustrato come inserire dati nella tabella dbo.Categories utilizzando un'istruzione INSERT con una subquery SELECT che utilizza un parametro basato su valori di tabella come origine dati. Quando si passa 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 usando la nuova TypeName proprietà di un oggetto SqlParameter. Il TypeName deve corrispondere al nome di un tipo compatibile creato in precedenza sul server. Il codice in questo esempio usa la TypeName proprietà per fare riferimento alla struttura del tipo definita in dbo. CategoryTableType.

Annotazioni

Se si specifica un valore per una colonna Identity in un parametro con valori di tabella, è necessario rilasciare 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();  
}  

' 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

È anche possibile usare qualsiasi oggetto derivato da DbDataReader per trasmettere righe di dati a un parametro con valori di tabella. Il frammento di codice seguente illustra il recupero di dati da un database Oracle usando un OracleCommand e un OracleDataReader oggetto. Il codice configura quindi un SqlCommand oggetto per richiamare una stored procedure con un singolo parametro di input. La SqlDbType proprietà di SqlParameter è impostata su Structured. Il AddWithValue passa 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();  

' 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