Parametri dei comandi degli adattatori dati

Articolo
09/12/2008

Aggiornamento: novembre 2007

I comandi di un adattatore di dati, definiti nella proprietà CommandText degli oggetti SelectCommand,InsertCommand, UpdateCommand eDeleteCommand, spesso implicano l'utilizzo di parametri. Questi ultimi vengono utilizzati in fase di esecuzione per passare i valori alle istruzioni SQL o alle stored procedure rappresentate dai comandi.

Nota:
Nella versione precedente di Visual Studio per le comunicazioni tra un'applicazione e un database venivano utilizzati gli adattatori dati. Mentre gli adattatori dati sono comunque un componente essenziale dei .Provider di dati .NET Framework (ADO.NET), gli oggetti TableAdapter sono componenti generati dalla finestra di progettazione che consentono di semplificare il processo di spostamento dei dati fra l'applicazione dell'utente e un database. Per ulteriori informazioni sull'utilizzo degli oggetti TableAdapter, vedere Cenni preliminari sugli oggetti TableAdapter.

Nella versione precedente di Visual Studio per le comunicazioni tra un'applicazione e un database venivano utilizzati gli adattatori dati. Mentre gli adattatori dati sono comunque un componente essenziale dei .Provider di dati .NET Framework (ADO.NET), gli oggetti TableAdapter sono componenti generati dalla finestra di progettazione che consentono di semplificare il processo di spostamento dei dati fra l'applicazione dell'utente e un database. Per ulteriori informazioni sull'utilizzo degli oggetti TableAdapter, vedere Cenni preliminari sugli oggetti TableAdapter.

I parametri vengono utilizzati in due contesti:

Parametri di selezione: nelle applicazioni di produzione si recupera di frequente solo un sottoinsieme dei dati in un database. A tal fine, viene utilizzata un'istruzione SQL o una stored procedure contenente una clausola WHERE con un parametro per i criteri di selezione utilizzati in fase di esecuzione. Inoltre, quando si aggiornano o eliminano record, si utilizza una clausola WHERE che individua con esattezza il record o i record da modificare. I valori utilizzati nella clausola WHERE vengono in genere derivati in fase di esecuzione.

Parametri di aggiornamento: quando si aggiorna un record esistente o si inserisce un nuovo record, i valori delle colonne nel record nuovo o modificato vengono definiti in fase di esecuzione. Inoltre, i valori utilizzati durante il controllo della concorrenza ottimistica vengono stabiliti mediante i parametri.

Nota:
In Oracle quando si utilizzano parametri denominati in un'istruzione SQL o in una stored procedure, è necessario inserire il simbolo di due punti (:) prima del nome del parametro. Se invece si fa riferimento a un parametro denominato in altri punti del codice (ad esempio quando viene chiamato il metodo Add), non è necessario far precedere il nome del parametro dai due punti (:), in quanto vengono forniti automaticamente dal provider di dati. Per ulteriori informazioni, vedere Classe OracleParameter.

In Oracle quando si utilizzano parametri denominati in un'istruzione SQL o in una stored procedure, è necessario inserire il simbolo di due punti (:) prima del nome del parametro. Se invece si fa riferimento a un parametro denominato in altri punti del codice (ad esempio quando viene chiamato il metodo Add), non è necessario far precedere il nome del parametro dai due punti (:), in quanto vengono forniti automaticamente dal provider di dati. Per ulteriori informazioni, vedere Classe OracleParameter.

Parametri di selezione

Quando si selezionano i record da inserire in un dataset, spesso nella clausola WHERE vengono inclusi uno o più parametri in modo che in fase di esecuzione sia possibile specificare i record da recuperare. È possibile, ad esempio, che gli utenti ricerchino in un database di libri la parola chiave di un titolo specifico digitata in una pagina Web. Per consentire questa operazione, si potrebbe specificare un'istruzione SQL simile alla seguente come proprietà CommandText di un oggetto SelectCommand. I parametri vengono indicati con un segnaposto (un punto interrogativo) o con la variabile di un parametro denominato. Nei parametri relativi alle query contenenti oggetti OleDbCommand e OdbcCommand vengono utilizzati i punti interrogativi. Nelle query contenenti oggetti SqlCommand vengono utilizzati parametri denominati che iniziano con un simbolo @, mentre se contengono oggetti OracleCommand vengono utilizzati parametri denominati che iniziano con un segno di due punti (:).

Una query che utilizza i segnaposto potrebbe essere simile alla seguente:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

Una query che utilizza parametri SqlCommand denominati potrebbe essere simile alla seguente:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

Una query che utilizza parametri OracleCommand denominati potrebbe essere simile alla seguente:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

Nell'applicazione creata viene chiesto all'utente di specificare la parola chiave di un titolo, quindi viene impostato il valore del parametro ed eseguito il comando.

Nota:
In alcuni casi potrebbe essere necessario recuperare l'intero contenuto di una tabella del database, ad esempio nel caso in cui si imposti una tabella di ricerca, anche se generalmente si desidera recuperare solo i dati necessari per continuare a garantire l'efficienza dell'applicazione.

In Visual Studio è possibile generare istruzioni SQL con parametri mediante il Generatore di query. Se si trascinano elementi da Esplora server, in Visual Studio sarà possibile configurare i parametri solo in alcuni casi, mentre sarà necessario completare la configurazione manualmente.

Parametri di aggiornamento

A prescindere che l'oggetto SelectCommand di un adattatore contenga un comando con parametri, i comandi relativi alle proprietà UpdateCommand, InsertCommand e DeleteCommand li contengono sempre.

I comandi per le proprietà UpdateCommand e InsertCommand richiedono dei parametri per ogni colonna del database da aggiornare. Inoltre, le istruzioni UpdateCommand e DeleteCommand richiedono una clausola WHERE con parametri che identifichi il record da aggiornare, simile a quella spesso utilizzata per configurare l'oggetto SelectCommand.

Si consideri un'applicazione con la quale gli utenti possono acquistare libri. Al momento dell'acquisto, gli utenti gestiscono un carrello implementato come una tabella dati e nella tabella ShoppingCart gestiscono un record per ciascun libro che desiderano acquistare, in cui l'ID del libro e l'ID del cliente agiscono da chiave per il record relativo al carrello.

Quando gli utenti aggiungono un libro nel proprio carrello, l'applicazione richiama un'istruzione SQL INSERT. Nell'adattatore la sintassi dell'istruzione potrebbe essere simile alla seguente:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

I tre punti interrogativi rappresentano i segnaposto dei parametri in cui verranno inseriti in fase di esecuzione i valori per l'ID del cliente, l'ID del libro e la quantità. Se si utilizzano parametri denominati, la stessa query potrebbe avere il seguente aspetto:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

Se l'utente decide di modificare uno degli elementi di un articolo nel carrello, ad esempio la quantità, l'applicazione chiama un'istruzione SQL UPDATE. La sintassi dell'istruzione potrebbe essere simile alla seguente:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Se si utilizzano parametri denominati, la sintassi dell'istruzione potrebbe essere simile alla seguente:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

In questa istruzione nei parametri della clausola SET vengono inseriti i valori aggiornati del record modificato. I parametri della clausola WHERE identificano i record da aggiornare e in essi vengono inseriti i valori originali del record.

È possibile inoltre rimuovere un articolo dal carrello. In tal caso, se si utilizzano i segnaposto dei parametri, l'applicazione chiama un'istruzione SQL DELETE. La sintassi di tale istruzione sarà simile alla seguente:

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

Se si utilizzano parametri denominati, la sintassi sarà simile alla seguente:

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

Insieme di parametri e oggetti parametro

Per consentire il passaggio dei valori dei parametri in fase di esecuzione, ciascuno dei quattro oggetti Command di un adattatore dati supporta una proprietà Parameters. Essa contiene un insieme di singoli oggetti parametro con una corrispondenza univoca con i segnaposto di un'istruzione.

Nella tabella riportata di seguito viene riportato l'insieme di parametri corrispondenti per ciascun adattatore dati:

Adattatore dati	Insieme di parametri
SqlDataAdapter	SqlParameterCollection
OleDbDataAdapter	OleDbParameterCollection
OdbcDataAdapter	OdbcParameterCollection
OracleDataAdapter	OracleParameterCollection

Nota:
In Oracle quando si utilizzano parametri denominati in un'istruzione SQL o in una stored procedure, è necessario inserire il simbolo di due punti (:) prima del nome del parametro. Se invece si fa riferimento a un parametro denominato in altri punti del codice (ad esempio quando viene chiamato il metodo Add), non è necessario far precedere il nome del parametro dai due punti (:), in quanto vengono forniti automaticamente dal provider di dati .NET Framework per Oracle.

Se si utilizza l'insieme di parametri, non sarà necessario creare manualmente un comando SQL come stringa con i valori specificati in fase di esecuzione e sarà inoltre possibile eseguire il controllo dei tipi dei parametri.

Se si configura l'adattatore mediante la Configurazione guidata adattatore dati, l'insieme di parametri verrà impostato e configurato automaticamente per tutti i quattro comandi dell'adattatore. Se si trascinano elementi da Esplora server nel form o nel componente, in Visual Studio verranno effettuate le seguenti configurazioni:

Se si trascina una tabella o alcune colonne nella finestra di progettazione, in Visual Studio verranno generati un oggetto SelectCommand senza parametri (più specificamente un'istruzione SQL SELECT) e gli oggetti UpdateCommand, InsertCommand e DeleteCommand con parametri. Se si desidera includere dei parametri nell'istruzione dell'oggetto SelectCommand, sarà necessario configurarli manualmente.
Se si trascina una stored procedure nella finestra di progettazione, in Visual Studio verrà generato un oggetto SelectCommand con i parametri richiesti dalla stored procedure. Se tuttavia si desidera utilizzarli, sarà necessario configurare autonomamente gli oggetti UpdateCommand, InsertCommand e DeleteCommand insieme ai relativi parametri.

In genere, per creare delle query con parametri per l'adattatore, sarà necessario ricorrere alla Configurazione guidata adattatore dati. Se necessario, è tuttavia possibile configurare manualmente i parametri mediante la finestra Proprietà.

Struttura dell'insieme di parametri

Esiste una corrispondenza univoca tra gli elementi dell'insieme di parametri di un comando e i parametri necessari per l'oggetto Command corrispondente. Se l'oggetto Command è un'istruzione SQL, si stabilirà una corrispondenza tra gli elementi dell'insieme e i segnaposto (punti interrogativi) nell'istruzione. La seguente istruzione UPDATE richiede un insieme di cinque elementi di parametri:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Di seguito è riportata la stessa istruzione con parametri denominati.

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Se l'oggetto Command fa riferimento a una stored procedure, il numero di elementi dei parametri nell'insieme sarà determinato dalla stessa stored procedure. È possibile che non vi sia una corrispondenza esatta tra i parametri e i segnaposto in un'istruzione SQL.

È inoltre possibile assegnare dei nomi ai parametri nelle stored procedure. In tal caso, la posizione di un parametro nell'insieme non è importante. Ciascun elemento di parametro dell'insieme dispone invece di una proprietà ParameterName utilizzata per stabilire la corrispondenza tra tale elemento e il parametro corrispondente nella stored procedure.

Se si configura manualmente l'insieme di parametri, sarà necessario identificare con esattezza i parametri richiesti dalla stored procedure. Molte stored procedure restituiscono un valore. In tal caso, è necessario assicurarsi che tale valore venga restituito all'applicazione nell'insieme di parametri. Inoltre, alcune stored procedure includono più istruzioni SQL. In tal caso, è necessario assicurarsi che l'insieme di parametri rifletta tutti i valori passati a tutte le istruzioni nella stored procedure.

Se i parametri sono senza nome, come avviene nelle stored procedure, la posizione degli elementi dell'insieme corrisponderà alla posizione dei parametri richiesti dal comando. Se il comando è una stored procedure e restituisce un valore, per tale valore restituito sarà riservato il primo elemento dell'insieme (elemento zero).

È quindi possibile fare riferimento a singoli oggetti parametro in base alla posizione di indice all'interno dell'insieme. Tuttavia, gli oggetti parametro supportano anche una proprietà ParameterName che consente di fare riferimento ai parametri indipendentemente dall'ordine. Supponendo che il secondo parametro dell'insieme sia denominato Title_Keyword, è possibile ad esempio che le due istruzioni che seguono siano equivalenti:

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

Nella programmazione è in genere preferibile utilizzare il nome di un parametro anziché fare riferimento ai parametri in base al valore di indice. In tal modo infatti si riduce la necessità di manutenzione in caso di modifica del numero dei parametri, evitando così di dover ricordare l'eventuale valore restituito dalla stored procedure. Il riferimento a un parametro in base al nome anziché al valore di indice comporta un leggero aumento di overhead, compensato dalla semplicità di programmazione e dalla gestibilità dell'applicazione.

Definizione dei valori dei parametri

È possibile stabilire il valore di un parametro in due modi:

Impostando la proprietà Value del parametro in modo esplicito
Associando i parametri alle colonne di una tabella Dataset, in modo che i valori possano essere estratti dalle righe di dati, quando necessario.

È possibile impostare in modo esplicito il valore del parametro quando si inseriscono dati in un dataset o si chiama un comando, vale a dire per i parametri di selezione. Nell'esempio precedente relativo alla ricerca di libri, è possibile che nell'applicazione sia disponibile una casella di testo in cui gli utenti immettono la parola chiave di un titolo. In tal caso, il valore del parametro viene impostato in modo esplicito sul testo della casella di testo prima della chiamata al metodo Fill dell'adattatore. Per eseguire tale operazione, potrebbe essere utilizzato un codice simile a quello riportato di seguito, in cui il contenuto di una casella di testo viene definito come parametro prima che un dataset venga riempito.

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

I valori dei parametri associati vengono utilizzati durante gli aggiornamenti. Quando si chiama il metodo Update di un adattatore, tale metodo esamina i record di una tabella dataset, eseguendo singolarmente per ciascun record il tipo di aggiornamento appropriato: aggiornamento (update), inserimento (insert), eliminazione (delete). In questo caso, i valori dei parametri sono già disponibili come colonne nei record del dataset. Quando, ad esempio, il processo di aggiornamento coinvolge un nuovo record di una tabella Dataset, vale a dire un record per il quale è necessario chiamare un'istruzione INSERT nel database, i valori per la clausola VALUE dell'istruzione INSERT possono essere letti direttamente all'esterno del record.

Questi sono scenari tipici, ma non gli unici disponibili. Le stored procedure restituiscono talvolta dei dati utilizzando parametri out o attraverso il valore da esse restituito. In questo caso, è necessario associare i valori restituiti alle colonne di una tabella Dataset.

È possibile inoltre impostare i parametri di aggiornamento in modo esplicito. L'adattatore supporta un evento RowUpdating che viene chiamato ogni volta che una riga viene aggiornata. È possibile creare un gestore per questo evento e impostarvi i valori dei parametri. Sarà quindi possibile svolgere un controllo molto preciso dei valori dei parametri ed eseguire processi, quali la creazione dinamica dei valori dei parametri, prima che vengano scritti nel record del database.