Utilisation des paramètres avec un DataAdapter

Le DataAdapter a quatre propriétés qui sont utilisées pour extraire des données d'une source de données et les mettre à jour. La propriété SelectCommand retourne les données de la source de données. Les propriétés InsertCommand, UpdateCommand et DeleteCommand sont utilisées pour gérer les modifications au niveau de la source de données. La propriété SelectCommand doit être définie avant d'appeler la méthode Fill du DataAdapter. Les propriétés InsertCommand, UpdateCommand ou DeleteCommand doivent être définies avant que la méthode Update du DataAdapter ne soit appelée, en fonction des modifications qui ont été apportées aux données dans le DataSet. Par exemple, si des lignes ont été ajoutées, InsertCommand doit être défini avant d'appeler Update. Lorsque Update traite une ligne insérée, mise à jour ou supprimée, le DataAdapter utilise la propriété Command respective pour traiter l'action. Les informations actuelles concernant la ligne modifiée sont passées à l'objet Command par l'intermédiaire de la collection Parameters.

Par exemple, lors de la mise à jour d'une ligne au niveau de la source de données, vous appelez l'instruction UPDATE, qui utilise un identificateur unique pour identifier la ligne dans la table à mettre à jour. L'identificateur unique est généralement la valeur d'un champ de clé primaire. L'instruction UPDATE utilise les paramètres qui contiennent l'identificateur unique ainsi que les colonnes et les valeurs à mettre à jour, comme indiqué dans l'instruction SQL suivante.

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

Dans cet exemple, le champ CompanyName est mis à jour avec la valeur du paramètre @CompanyName pour la ligne où CustomerID a la valeur du paramètre @CustomerID. Les paramètres extraient les informations de la ligne modifiée à l'aide de la propriété SourceColumn de l'objet Parameter. Suivent les paramètres pour l'instruction UPDATE précédemment donnée en exemple.

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

La méthode Add de la collection Parameters prend le nom du paramètre, le type spécifique au DataAdapter, la taille (si elle est applicable au type) et le nom du SourceColumn du DataTable. Notez que Original est affecté au SourceVersion du paramètre @CustomerID. Ceci garantit que la ligne existante dans la source de données est mise à jour si la valeur de la ou des colonnes d'identification ont été changées dans le DataRow modifié. Dans ce cas, la valeur de ligne Original correspondrait à la valeur actuelle de la source de données et la valeur de ligne Current contiendrait la valeur mise à jour. Le SourceVersion du paramètre @CompanyName n'est pas défini et utilisera la valeur de ligne Current par défaut.

Suivent des exemples qui illustrent les instructions SQL à utiliser comme CommandText pour les propriétés SelectCommand, InsertCommand, UpdateCommand et DeleteCommand du DataAdapter. Pour les objets OleDbDataAdapter et OdbcDataAdapter, vous devez utiliser les espaces réservés de point d'interrogation (?) pour identifier les paramètres. Pour l'objet SqlDataAdapter, vous devez utiliser les paramètres nommés.

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 ou 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 = ?";

Les instructions de requête paramétrées définissent les paramètres d'entrée et de sortie qui devront être créés. Pour créer un paramètre, utilisez la méthode Parameters.Add ou le constructeur Parameter pour spécifier le nom de colonne, le type de données et la taille. Pour les types de données intrinsèques, comme Integer, vous n'avez pas besoin d'inclure la taille ou vous pouvez spécifier la taille par défaut.

L'exemple de code suivant crée les paramètres pour l'instruction SQL de l'exemple précédent et remplit 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");

Remarque   Si aucun nom de paramètre n'est fourni, le paramètre reçoit un nom incrémentiel par défaut de ParameterN, commençant par « Parameter1 ». Il est recommandé d'éviter d'utiliser la convention d'affectation de noms « ParameterN » lorsque vous fournissez un nom de paramètre, car celui-ci peut entrer en conflit avec un nom de paramètre par défaut existant dans le ParameterCollection. Si le nom fourni existe déjà, une exception sera levée.

Parameter.DbType

Le type d'un paramètre est spécifique au fournisseur de données .NET Framework. La spécification du type convertit la valeur de Parameter en type du fournisseur de données .NET Framework avant de passer la valeur à la source de données. Si le type n'est pas spécifié, ADO.NET déduira le type de fournisseur de données .NET Framework du Parameter du type .NET Framework du Value de l'objet Parameter.

Vous pouvez également spécifier le type de Parameter de façon générique en définissant la propriété DbType de l'objet Parameter avec une certaine valeur System.Data.DbType. En outre, ADO.NET déduit le type du fournisseur de données .NET Framework d'un Parameter du DbType de l'objet Parameter.

Le type de fournisseur de données .NET Framework d'un objet Parameter est déduit du type .NET Framework du Value ou du DbType de l'objet Parameter. Le tableau suivant montre le type Parameter déduit de l'objet passé comme valeur Parameter ou du DbType spécifié.

Remarque   Les fournisseurs de données .NET Framework livrés avec le .NET Framework version 1.0 ne vérifient par le Precision et le Scale des valeurs de paramètre Decimal, en conséquence de quoi des données tronquées peuvent se trouver insérées dans la source de données. Si vous utilisez le .NET Framework version 1.0, validez le Precision et le Scale de vos valeurs Decimal avant de définir la valeur de paramètre.

Pour le .NET Framework version 1.1 et ultérieure, une exception est levée lorsqu'une valeur de paramètre Decimal est définie avec un Precision incorrect. Les valeurs Scale qui excèdent l'échelle du paramètre Decimal restent tronquées.

Parameter.Direction

Le tableau suivant présente les valeurs que vous pouvez utiliser avec l'énumération ParameterDirection pour définir le Direction du Parameter.

L'exemple de code suivant montre comment définir le Direction du Parameter.

myParm.Direction = ParameterDirection.Output

Parameter.SourceColumn, Parameter.SourceVersion

SourceColumn et SourceVersion peuvent être passés comme arguments au constructeur Parameter ou définis comme propriétés d'un Parameter existant. SourceColumn est le nom de DataColumn provenant du DataRow où la valeur de Parameter sera extraite. SourceVersion spécifie la version du DataRow que le DataAdapter utilise pour extraire la valeur.

Le tableau suivant présente les valeurs d'énumération DataRowVersion disponibles pour être utilisées avec SourceVersion.

L'exemple de code suivant définit une instruction UPDATE dans laquelle la colonne CustomerID est utilisée comme SourceColumn pour deux paramètres : @CustomerID (SET CustomerID = @CustomerID) et @OldCustomerID (WHERE CustomerID = @OldCustomerID). Le paramètre @CustomerID est utilisé pour mettre à jour la colonne CustomerID à la valeur actuelle de DataRow. En conséquence, le CustomerID SourceColumn avec un SourceVersion Current est utilisé. Le paramètre @OldCustomerID est utilisé pour identifier la ligne actuelle dans la source de données. Puisque la valeur de colonne correspondante se trouve dans la version Original de la ligne, le même SourceColumn (CustomerID) avec un SourceVersion Original est utilisé.

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

Vous pouvez contrôler la façon dont les valeurs retournées de la source de données sont mappées à nouveau au DataSet avec la propriété UpdatedRowSource de l'objet Command. En affectant l'une des valeurs d'énumération UpdateRowSource à la propriété UpdatedRowSource, vous pouvez contrôler si les paramètres retournés par la commande DataAdapter sont ignorés ou appliqués à la ligne modifiée dans le DataSet. Vous pouvez aussi spécifier que la première ligne retournée (si elle existe) soit appliquée à la ligne modifiée dans le DataSet.

Le tableau suivant décrit les différentes valeurs de l'énumération UpdateRowSource et la façon dont elles affectent le comportement d'une commande utilisée avec un DataAdapter.

Voir aussi

Utilisation des fournisseurs de données .NET Framework pour l'accès aux données | Utilisation des procédures stockées avec une commande | DataRowVersion, énumération | OleDbDataAdapter, classe | OdbcDataAdapter, classe | ParameterDirection, énumération | SqlDataAdapter, classe