Utilizzo di parametri con un DataAdapter

  • Articolo

DataAdapter dispone di quattro proprietà che consentono di recuperare e aggiornare i dati dell'origine dati. La proprietà SelectCommand restituisce i dati dell'origine dati. Le proprietà InsertCommand, UpdateCommand e DeleteCommand vengono utilizzate per gestire le modifiche nell'origine dati. La proprietà SelectCommand deve essere impostata prima di chiamare il metodo Fill di DataAdapter. Le proprietà InsertCommand, UpdateCommand o DeleteCommand devono essere impostate prima di chiamare il metodo Update di DataAdapter, a seconda delle modifiche apportate ai dati nel DataSet. Se, ad esempio, sono state aggiunte righe, la proprietà InsertCommand deve essere impostata prima di chiamare Update. Quando Update elabora una riga inserita, aggiornata o eliminata, DataAdapter utilizza la rispettiva proprietà Command per l'operazione. Le informazioni correnti sulla riga modificata vengono passate all'oggetto Command mediante l'insieme Parameters.

Quando viene aggiornata una riga nell'origine dati, ad esempio, viene chiamata l'istruzione UPDATE che utilizza un identificatore univoco per identificare la riga nella tabella da aggiornare. In genere, il valore dell'identificatore univoco corrisponde a quello del campo di una chiave primaria. Nell'istruzione UPDATE vengono utilizzati i parametri che contengono l'identificatore univoco e le colonne e i valori da aggiornare, come illustrato nell'istruzione SQL riportata di seguito.

UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID

In questo esempio, il campo CompanyName viene aggiornato con il valore del parametro @CompanyName nella riga in cui CustomerID è uguale al valore del parametro @CustomerID. Le informazioni della riga modificata vengono recuperate dai parametri mediante la proprietà SourceColumn dell'oggetto Parameter. Di seguito sono riportati i parametri della precedente istruzione UPDATE di esempio.

custDA.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")
Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@CustomerID", _
                                              SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original

Il metodo Add dell'insieme Parameters accetta il nome del parametro, il tipo specifico di DataAdapter, le dimensioni (se applicabili al tipo) e il nome del SourceColumn dal DataTable. Si noti che SourceVersion del parametro @CustomerID è impostato su Original. Questo valore assicura che l'aggiornamento della riga esistente nell'origine dati venga eseguito se il valore della colonna o delle colonne identificative è stato cambiato nel DataRow modificato. In questo caso il valore Original della riga corrisponde al valore corrente nell'origine dati e il valore Current della riga contiene il valore aggiornato. SourceVersion per il parametro @CompanyName non è impostato e viene utilizzato il valore predefinito, ovvero il valore Current della riga.

Nell'esempio seguente vengono illustrate le istruzioni SQL da utilizzare come CommandText per proprietà SelectCommand, InsertCommand, UpdateCommand e DeleteCommand di DataAdapter. Per gli oggetti OleDbDataAdapter e OdbcDataAdapter, è necessario utilizzare il punto interrogativo (?) come segnaposto per identificare i parametri. Per l'oggetto SqlDataAdapter, è necessario utilizzare parametri denominati.

SqlClient

Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"
Dim insertSQL As String = "INSERT INTO Customers (CustomerID, CompanyName) " & _
                          "VALUES (@CustomerID, @CompanyName)"

Dim updateSQL As String = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
                          "WHERE CustomerID = @OldCustomerID"

Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = @CustomerID"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City";
string insertSQL = "INSERT INTO Customers (CustomerID, CompanyName) " +
                   "VALUES (@CustomerID, @CompanyName)";

string updateSQL = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
                   "WHERE CustomerID = @OldCustomerID";

string deleteSQL = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

OleDb o Odbc

Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?"
Dim insertSQL AS String = "INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"

Dim updateSQL AS String = "UPDATE Customers SET CustomerID = ?, CompanyName = ? WHERE CustomerID = ?"

Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";
string insertSQL = "INSERT INTO Customers (CustomerID, CompanyName) " +
                   "VALUES (?, ?)";

string updateSQL = "UPDATE Customers SET CustomerID = ?, CompanyName = ? " +
                   "WHERE CustomerID = ? ";

string deleteSQL = "DELETE FROM Customers WHERE CustomerID = ?";

Le istruzioni delle query con parametri definiscono i parametri di input e di output che è necessario creare. Per creare un parametro, utilizzare il metodo Parameters.Add o il costruttore Parameter per specificare il nome della colonna, il tipo di dati e le dimensioni. Per tipi di dati intrinseci, ad esempio Integer, non è necessario includere le dimensioni oppure è possibile specificare quelle predefinite.

Nell'esempio di codice seguente vengono creati i parametri per l'istruzione SQL dell'esempio precedente e viene riempito un DataSet.

SqlClient

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As SqlDataAdapter = New SqlDataAdapter

Dim selectCMD AS SqlCommand = New SqlCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter();         

SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

// Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

OleDb

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
                                                       "Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter 

Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
                                                "Integrated Security=SSPI;Initial Catalog=northwind;");
OleDbDataAdapter custDA = new OleDbDataAdapter();

OleDbCommand selectCMD = new OleDbCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

// Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

Odbc

Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
                                                     "Trusted_Connection=yes;Database=northwind")
Dim custDA As OdbcDataAdapter = New OdbcDataAdapter

Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD

' Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"

Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
                                              "Trusted_Connection=yes;Database=northwind;");
OdbcDataAdapter custDA = new OdbcDataAdapter();

OdbcCommand selectCMD = new OdbcCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;

//Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";

DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

Nota   Se non viene specificato un nome, al parametro viene assegnato il nome predefinito incrementale "ParameterN", che inizia con "Parameter1". È consigliabile evitare la convenzione di denominazione "ParametroN" quando si specifica il nome del parametro in quanto il nome indicato può entrare in conflitto con un nome predefinito esistente in ParameterCollection. Se il nome specificato esiste già, verrà generata un'eccezione.

Parameter.DbType

Il tipo di un parametro è specifico del provider di dati .NET Framework. Se il tipo viene specificato, il valore del Parameter viene convertito nel tipo del provider di dati .NET Framework prima che il valore passi all'origine dati. Se il tipo non viene specificato, il tipo con cui il Parameter è noto al provider di dati .NET Framework verrà dedotto da ADO.NET in base al tipo .NET Framework del Value del Parameter.

È possibile inoltre specificare il tipo di un Parameter in modo generico impostando la proprietà DbType dell'oggetto Parameter su un particolare System.Data.DbType. Il tipo con cui il Parameter è noto al provider di dati .NET Framework viene inoltre dedotto da ADO.NET in base al DbType dell'oggetto Parameter.

Il tipo con cui un oggetto Parameter è noto al provider di dati .NET Framework viene dedotto in base al tipo .NET Framework del Value dell'oggetto Parameter o in base al DbType dell'oggetto Parameter. Nella tabella che segue viene mostrato il tipo del Parameter dedotto in base all'oggetto passato come valore del Parameter o al DbType specificato.

Tipo di .NET Framework System.Data.DbType SqlDbType OleDbType OdbcType OracleType
bool Boolean Bit Boolean Bit Byte
byte Byte TinyInt UnsignedTinyInt TinyInt Byte
byte[] Binary VarBinary. La conversione implicita non riesce se la matrice di byte ha una dimensione superiore a quella massima di VarBinary, che è di 8000 byte. Per le matrici di byte superiori a 8000 byte, impostare in modo esplicito SqlDbType. VarBinary Binary Raw
char     La deduzione di un SqlDbType da char non è supportata. Char Char Byte
DateTime DateTime DateTime DBTimeStamp DateTime DateTime
Decimal Decimal Decimal Decimal Numeric Number
double Double Float Double Double Double
float Single Real Single Real Float
Guid Guid UniqueIdentifier Guid UniqueIdentifier Raw
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BitInt BigInt BigInt Number
object Object Variant Variant La deduzione di un OdbcType da Object non è supportata. Blob
string String NVarChar. La conversione implicita non riesce se la stringa ha una dimensione superiore a quella massima di un NVarChar, che è di 4000 caratteri. Per le stringhe superiori a 4000 caratteri, impostare in modo esplicito SqlDbType. VarWChar NVarChar NVarChar
TimeSpan Time La deduzione di un SqlDbType da TimeSpan non è supportata. DBTime Time DateTime
UInt16 UInt16 La deduzione di un SqlDbType da UInt16 non è supportata. UnsignedSmallInt Int UInt16
UInt32 UInt32 La deduzione di un SqlDbType da UInt32 non è supportata. UnsignedInt BigInt UInt32
UInt64 UInt64 La deduzione di un SqlDbType da UInt64 non è supportata. UnsignedBigInt Numeric Number
    AnsiString VarChar VarChar VarChar VarChar
    AnsiStringFixedLength Char Char Char Char
Currency Money Currency La deduzione di un OdbcType da Currency non è supportata. Number
    Date La deduzione di un SqlType da Date non è supportata. DBDate Date DateTime
    SByte La deduzione di un SqlType da SByte non è supportata. TinyInt La deduzione di un OdbcType da SByte non è supportata. SByte
    StringFixedLength NChar WChar NChar NChar
    Time La deduzione di un SqlType da Time non è supportata. DBTime Time DateTime
    VarNumeric La deduzione di un SqlDbType da VarNumeric non è supportata. VarNumeric La deduzione di un OdbcType da VarNumeric non è supportata. Number

Nota   I provider di dati .NET Framework forniti con .NET Framework versione 1.0 non verificano Precision e Scale dei valori dei parametri Decimal, con la possibile conseguenza di un troncamento dei dati inseriti nell'origine dati. Se si utilizza .NET Framework versione 1.0, convalidare Precision e Scale dei valori Decimal prima di impostare i valori dei parametri.

Con .NET Framework versione 1.1 e successive, quando il valore di un parametro Decimal viene impostato con una Precision non valida, verrà generata un'eccezione. Valori Scale che eccedono la scala del parametro Decimal vengono anch'essi troncati.

Parameter.Direction

Nella tabella seguente sono elencati i valori che è possibile utilizzare con l'enumerazione ParameterDirection per impostare Direction di Parameter.

Nome membro Descrizione
Input Il parametro è un parametro di input. Si tratta dell'impostazione predefinita.
InputOutputI Il parametro può essere sia di input che di output.
Output Il parametro è un parametro di output.
ReturnValue Il parametro rappresenta un valore restituito.

Nell'esempio di codice seguente viene illustrato come impostare Direction di Parameter.

myParm.Direction = ParameterDirection.Output

Parameter.SourceColumn e Parameter.SourceVersion

SourceColumn e SourceVersion possono essere passati come argomenti al costruttore Parameter oppure possono essere impostati come proprietà di un Parameter esistente. SourceColumn è il nome del DataColumn derivato dal DataRow in cui viene recuperato il valore di Parameter. SourceVersion specifica quale versione DataRow viene utilizzata da DataAdapter per recuperare il valore.

Nella tabella seguente sono elencati i valori di enumerazione DataRowVersion disponibili per l'utilizzo con SourceVersion.

Nome membro Descrizione
Current Il parametro utilizza il valore corrente della colonna. Si tratta dell'impostazione predefinita.
Default Il parametro utilizza il valore DefaultValue della colonna.
Original Il parametro utilizza il valore originale della colonna.
Proposed Il parametro utilizza un valore proposto.

Nell'esempio di codice seguente viene definita un'istruzione UPDATE in cui la colonna CustomerID viene utilizzata come SourceColumn di due parametri: @CustomerID (SET CustomerID = @CustomerID) e @OldCustomerID (WHERE CustomerID = @OldCustomerID). Il parametro @CustomerID viene utilizzato per aggiornare la colonna CustomerID sul valore corrente in DataRow. Di conseguenza viene utilizzato SourceColumn CustomerID con un SourceVersion uguale a Current. Il parametro @OldCustomerID viene utilizzato per identificare la riga corrente nell'origine dati. Poiché il valore della colonna corrispondente viene rilevato nella versione Original della riga, viene utilizzato lo stesso SourceColumn (CustomerID) con un SourceVersion uguale a Original.

SqlClient

custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")

custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")

Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
                               SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

OleDb

custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5,"CustomerID")

custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName")

Dim myParm As OleDbParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
                                 OleDbType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName");

OleDbParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", OleDbType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

Odbc

custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID")

custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName")

Dim myParm As OdbcParameter = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", _
                              OdbcType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID");

custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName");

OdbcParameter myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", OdbcType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;

UpdatedRowSource

È possibile controllare come viene eseguito il mapping dei valori restituiti dall'origine dati su un DataSet mediante la proprietà UpdatedRowSource dell'oggetto Command. Impostando la proprietà UpdatedRowSource su uno dei valori di enumerazione UpdateRowSource, è possibile controllare se i parametri restituiti dal comando DataAdapter vengono ignorati o applicati alla riga modificata nel DataSet. È possibile inoltre specificare se la prima riga restituita (se esiste) viene applicata alla riga modificata nel DataSet.

Nella tabella seguente vengono descritti i diversi valori dell'enumerazione UpdateRowSource e viene illustrato il modo in cui influenzano il comportamento di un comando utilizzato con un DataAdapter.

UpdateRowSource Description
Both È possibile eseguire il mapping dei parametri di output e della prima riga di un gruppo di risultati restituiti sulla riga modificata nel DataSet.
FirstReturnedRecord È possibile eseguire il mapping sulla riga modificata nel DataSet solo dei dati nella prima riga di un gruppo di risultati restituiti.
None Tutti i parametri di output, o righe, di un gruppo di risultati restituiti vengono ignorati.
OutputParameters È possibile eseguire il mapping sulla riga modificata nel DataSet solo dei parametri di output.

Vedere anche

Utilizzo di provider di dati .NET Framework per accedere ai dati | Utilizzo di stored procedure con un comando | Enumerazione DataRowVersion | Classe OleDbDataAdapter | Classe OdbcDataAdapter | Enumerazione ParameterDirection | Classe SqlDataAdapter