Les procédures stockées offrent de nombreux avantages dans les applications pilotées par des données. En utilisant les procédures stockées, les opérations de base de données peuvent être encapsulées dans une commande unique, optimisées pour une meilleure performance et améliorées grâce à une sécurité supplémentaire. Tandis qu'une procédure stockée peut être appelée en passant simplement son nom suivi des arguments de paramètre comme instruction SQL, l'utilisation de la collection Parameters de l'objet DbCommand ADO.NET vous permet de définir plus explicitement les paramètres de procédure stockée et d'accéder aux paramètres de sortie et aux valeurs de retour.
Pour appeler une procédure stockée, affectez StoredProcedure à la propriété CommandType de l'objet Command. Une fois StoredProcedure affecté à CommandType, vous pouvez utiliser la collection Parameters pour définir les paramètres, comme dans l'exemple suivant.
.gif) Remarque |
|---|
| L'objet OdbcCommand exige que vous fournissiez la syntaxe ODBC CALL complète lorsque vous appelez une procédure stockée. |
Exemple
' Assumes that connection is a valid SqlConnection object.
Dim salesCommand As SqlCommand = New SqlCommand( _
"SalesByCategory", connection)
salesCommand.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = salesCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15)
parameter.Value = "Beverages"
connection.Open()
Dim reader As SqlDataReader = salesCommand.ExecuteReader()
Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))
Do While reader.Read()
Console.WriteLine("{0}, ${1}", reader.GetString(0), reader.GetDecimal(1))
Loop
reader.Close()
connection.Close()
// Assumes that connection is a valid SqlConnection object.
SqlCommand salesCommand = new SqlCommand("SalesByCategory",
connection);
salesCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = salesCommand.Parameters.Add(
"@CategoryName", SqlDbType.NVarChar, 15);
parameter.Value = "Beverages";
connection.Open();
SqlDataReader reader = salesCommand.ExecuteReader();
Console.WriteLine(
"{0}, {1}", reader.GetName(0), reader.GetName(1));
while (reader.Read())
{
Console.WriteLine("{0}, ${1}", reader.GetString(0),
reader.GetDecimal(1));
}
reader.Close();
connection.Close();
Un objet Parameter peut être créé à l'aide du constructeur Parameter ou en appelant la méthode Add de la collection Parameters d'une Command. Parameters.Add prendra comme entrée les arguments de constructeur ou un objet Parameter existant. Lorsque vous affectez une référence null au Value d'un Parameter, utilisez DBNull.Value.
Pour les paramètres autres que Input, vous devez définir la propriété ParameterDirection pour spécifier si le type de paramètre est InputOutput, Output ou ReturnValue. L'exemple suivant illustre la différence entre la création des paramètres Input, Output et ReturnValue pour les divers fournisseurs.
Exemple SqlClient
' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand("SampleProc", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = command.Parameters.Add( _
"RETURN_VALUE", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = command.Parameters.Add( _
"@InputParm", SqlDbType.NVarChar, 12)
parameter.Value = "Sample Value"
parameter = command.Parameters.Add( _
"@OutputParm", SqlDbType.NVarChar, 28)
parameter.Direction = ParameterDirection.Output
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Console.WriteLine( _
"{0}, {1}", reader.GetName(0), reader.GetName(1))
Do While reader.Read()
Console.WriteLine( _
"{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop
reader.Close()
connection.Close()
Console.WriteLine( _
" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine( _
"RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)
// Assumes that connection is a valid SqlConnection object.
SqlCommand command = new SqlCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = command.Parameters.Add(
"RETURN_VALUE", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add(
"@InputParm", SqlDbType.NVarChar, 12);
parameter.Value = "Sample Value";
parameter = command.Parameters.Add(
"@OutputParm", SqlDbType.NVarChar, 28);
parameter.Direction = ParameterDirection.Output;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
Console.WriteLine(
"{0}, {1}", reader.GetName(0), reader.GetName(1));
while (reader.Read())
{
Console.WriteLine(
"{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}
reader.Close();
connection.Close();
Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);
Exemple OleDb
Dim command As OleDbCommand = New OleDbCommand( _
"SampleProc", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As OleDbParameter = command.Parameters.Add( _
"RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue
parameter = command.Parameters.Add( _
"@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"
parameter = command.Parameters.Add( _
"@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output
connection.Open()
Dim reader As OleDbDataReader = command.ExecuteReader()
Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))
Do While reader.Read()
Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop
reader.Close()
connection.Close()
Console.WriteLine(" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)
OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;
OleDbParameter parameter = command.Parameters.Add(
"RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add(
"@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";
parameter = command.Parameters.Add(
"@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));
while (reader.Read())
{
Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}
reader.Close();
connection.Close();
Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);
Exemple Odbc
Dim command As OdbcCommand = New OdbcCommand( _
"{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = command.Parameters.Add( _
"@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"
parameter = command.Parameters.Add( _
"@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output
connection.Open()
Dim reader As OdbcDataReader = command.ExecuteReader()
Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1))
Do While reader.Read()
Console.WriteLine("{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop
reader.Close()
connection.Close()
Console.WriteLine(" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)
OdbcCommand command = new OdbcCommand( _
"{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;
OdbcParameter parameter = command.Parameters.Add( _
"RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
parameter = command.Parameters.Add( _
"@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";
parameter = command.Parameters.Add( _
"@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
Console.WriteLine("{0}, {1}", reader.GetName(0), reader.GetName(1));
while (reader.Read())
{
Console.WriteLine( _
"{0}, {1}", reader.GetInt32(0), reader.GetString(1));
}
reader.Close();
connection.Close();
Console.WriteLine(" @OutputParm: {0}", command.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", command.Parameters["RETURN_VALUE"].Value);
Utilisation des paramètres avec SqlCommand
Lorsque des paramètres sont utilisés avec l'objet SqlCommand, les noms des paramètres ajoutés à la propriété Parameters doivent correspondre à ceux des marqueurs de paramètre de votre procédure stockée. Le fournisseur de données .NET Framework pour SQL Server traite les paramètres de la procédure stockée comme des paramètres nommés et recherche les marqueurs de paramètre correspondants.
Le fournisseur de données .NET Framework pour SQL Server ne prend pas en charge l'espace réservé de point d'interrogation (?) pour le passage des paramètres à une instruction SQL ou une procédure stockée. Dans ce cas, vous devez utiliser des paramètres nommés, comme dans l'exemple suivant où @CustomerID est le paramètre nommé.
SELECT * FROM Customers WHERE CustomerID = @CustomerID
Utilisation des paramètres avec OleDbCommand ou OdbcCommand
Lorsque des paramètres sont utilisés avec l'objet OleDbCommand ou OdbcCommand, l'ordre des paramètres ajoutés à la collection Parameters doit correspondre à celui des paramètres définis dans votre procédure stockée. Les fournisseurs de données .NET Framework pour OLE DB et ODBC traitent les paramètres d'une procédure stockée comme des espaces réservés et appliquent des valeurs de paramètre par ordre. En outre, les paramètres des valeurs de retour doivent être les premiers ajoutés à la collection Parameters.
Les fournisseurs de données .NET Framework pour OLE DB et ODBC ne prennent pas en charge les paramètres nommés pour le passage des paramètres à une instruction SQL ou une procédure stockée. Dans ce cas, vous devez utiliser l'espace réservé de point d'interrogation (?), comme dans l'exemple suivant.
SELECT * FROM Customers WHERE CustomerID = ?
En conséquence, l'ordre dans lequel les objets Parameter sont ajoutés à la collection Parameters doit directement correspondre à la position de l'espace réservé ? pour le paramètre.
Dérivation des informations sur les paramètres
Les paramètres peuvent aussi être dérivés d'une procédure stockée à l'aide de la classe CommandBuilder. Les classes SqlCommandBuilder et OleDbCommandBuilder fournissent une méthode statique, DeriveParameters, qui remplit automatiquement la collection Parameters d'un objet Command avec les informations sur les paramètres provenant d'une procédure stockée. Notez que DeriveParameters remplacent les informations sur les paramètres existantes pour Command.
La dérivation des informations sur les paramètres nécessite l'ajout d'un trajet vers la source de données pour obtenir les informations. Si les informations sur les paramètres sont connues au moment du design, vous pouvez améliorer la performance de votre application en définissant les paramètres de manière explicite.
L'exemple de code suivant illustre comment remplir la collection Parameters d'un objet Command à l'aide de CommandBuilder.DeriveParameters.
' Assumes that connection is a valid SqlConnection object.
Dim salesCommand As SqlCommand = New SqlCommand( _
"Sales By Year", connection)
salesCommand.CommandType = CommandType.StoredProcedure
connection.Open()
SqlCommandBuilder.DeriveParameters(salesCommand)
connection.Close()
// Assumes that connection is a valid SqlConnection object.
SqlCommand salesCommand = new SqlCommand("Sales By Year", connection);
salesCommand.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(salesCommand);
connection.Close();
Voir aussi