Partager via


Configuration des paramètres et des types de données de paramètre (ADO.NET)

Les objets de commande utilisent des paramètres pour passer des valeurs à des instructions SQL ou à des procédures stockées, en fournissant la vérification et la validation des types. Contrairement au texte de la commande, l'entrée de paramètre est traitée comme une valeur littérale et non pas comme du code exécutable. Cela vous permet de vous protéger des attaques « par injection de code SQL », dans lesquelles un attaquant insère une commande qui compromet la sécurité sur le serveur dans une instruction SQL.

Les commandes paramétrées améliorent également les performances d'exécution des requêtes car elles permettent au serveur de base de données de faire correspondre la commande entrante avec un plan de requête mis en cache approprié. Pour plus d'informations, consultez Mise en mémoire cache et réutilisation du plan d'exécution et Réutilisation des paramètres et des plans d'exécution dans la documentation en ligne de SQL Server. Outre les avantages relatifs à la sécurité et aux performances, les commandes paramétrées fournissent une méthode pratique d'organisation des valeurs passées à une source de données.

Un objet DbParameter peut être créé à l'aide de son constructeur ou en l'ajoutant à la propriété DbParameterCollection en appelant la méthode Add de la collection DbParameterCollection. La méthode Add prendra comme entrée des arguments de constructeur ou un objet Parameter existant, selon le fournisseur de données.

Fourniture de la propriété ParameterDirection

Lorsque vous ajoutez des paramètres, vous devez fournir une propriété ParameterDirection pour les paramètres autres que les paramètres d'entrée. Le tableau ci-dessous indique les valeurs ParameterDirection que vous pouvez utiliser avec l'énumération ParameterDirection.

Nom de membre

Description

Input

Le paramètre est un paramètre d'entrée. Il s'agit de la valeur par défaut.

InputOutput

Le paramètre peut être à la fois un paramètre d'entrée et de sortie.

Output

Le paramètre est un paramètre de sortie.

ReturnValue

Le paramètre représente une valeur de retour d'une opération telle qu'une procédure stockée, une fonction intégrée ou une fonction définie par l'utilisateur.

Utilisation d'espaces réservés de paramètres

La syntaxe des espaces réservés des paramètres dépend de la source de données. Les fournisseurs de données .NET Framework gèrent différemment la dénomination et la spécification des paramètres et des espaces réservés de paramètres. Cette syntaxe est personnalisée en fonction d'une source de données spécifique, comme le décrit le tableau ci-dessous.

Fournisseur de données

Syntaxe d'attribution de noms aux paramètres

System.Data.SqlClient

Utilise des paramètres nommés au format @nom_paramètre.

System.Data.OleDb

Utilise des marqueurs de paramètres positionnels indiqués par un point d'interrogation (?).

System.Data.Odbc

Utilise des marqueurs de paramètres positionnels indiqués par un point d'interrogation (?).

System.Data.OracleClient

Utilise des paramètres nommés dans le format :NomParam (ou NomParam).

Spécification des types de données de paramètre

Le type de données d'un paramètre est spécifique au fournisseur de données .NET Framework. Si vous spécifiez le type, la valeur de Parameter est convertie en type de fournisseur de données .NET Framework avant d'être passée à la source de données. Vous pouvez également spécifier le type d'un Parameter de façon générique en affectant à la propriété DbType de l'objet Parameter un DbType particulier.

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

Type .NET Framework

DbType

SqlDbType

OleDbType

OdbcType

OracleType

bool

Boolean

Bit

Boolean

Bit

Byte

byte

Byte

TinyInt

UnsignedTinyInt

TinyInt

Byte

byte[]

Binary

VarBinary. Cette conversion implicite échouera si le tableau d'octets est supérieur à la taille maximale d'un VarBinary, soit 8 000 octets. Pour des tableaux d'octets supérieurs à 8 000 octets, définissez explicitement SqlDbType.

VarBinary

Binary

Raw

char

 

La déduction de SqlDbType à partir de char n'est pas prise en charge.

Char

Char

Byte

DateTime

DateTime

DateTime

DBTimeStamp

DateTime

DateTime

DateTimeOffset

DateTimeOffset

DateTimeOffset dans SQL Server 2008. La déduction de SqlDbType à partir de DateTimeOffset n'est pas prise en charge dans les versions de SQL Server antérieures à SQL Server 2008.

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

BigInt

BigInt

BigInt

Number

object

Object

Variant

Variant

La déduction d'OdbcType à partir d'Object n'est pas prise en charge.

Blob

string

String

NVarChar. Cette conversion implicite échouera si la chaîne est supérieure à la taille maximale de NVarChar, soit 4 000 caractères. Pour les chaînes supérieures à 4 000 caractères, définissez explicitement SqlDbType.

VarWChar

NVarChar

NVarChar

TimeSpan

Time

Time dans SQL Server 2008. La déduction de SqlDbType à partir de TimeSpan n'est pas prise en charge dans les versions de SQL Server antérieures à SQL Server 2008.

DBTime

Time

DateTime

UInt16

UInt16

La déduction de SqlDbType à partir de UInt16 n'est pas prise en charge.

UnsignedSmallInt

Int

UInt16

UInt32

UInt32

La déduction de SqlDbType à partir de UInt32 n'est pas prise en charge.

UnsignedInt

BigInt

UInt32

UInt64

UInt64

La déduction de SqlDbType à partir de UInt64 n'est pas prise en charge.

UnsignedBigInt

Numeric

Number

 

AnsiString

VarChar

VarChar

VarChar

VarChar

 

AnsiStringFixedLength

Char

Char

Char

Char

 

Currency

Money

Currency

La déduction d'OdbcType à partir de Currency n'est pas prise en charge.

Number

 

Date

Date dans SQL Server 2008. La déduction de SqlDbType à partir de Date n'est pas prise en charge dans les versions de SQL Server antérieures à SQL Server 2008.

DBDate

Date

DateTime

 

SByte

La déduction de SqlDbType à partir de SByte n'est pas prise en charge.

TinyInt

La déduction de OdbcType à partir de SByte n'est pas prise en charge.

SByte

 

StringFixedLength

NChar

WChar

NChar

NChar

 

Time

Time dans SQL Server 2008. La déduction de SqlDbType à partir de Time n'est pas prise en charge dans les versions de SQL Server antérieures à SQL Server 2008.

DBTime

Time

DateTime

 

VarNumeric

La déduction de SqlDbType à partir de VarNumeric n'est pas prise en charge.

VarNumeric

La déduction de OdbcType à partir de VarNumeric n'est pas prise en charge.

Number

RemarqueRemarque

Les conversions du type decimal vers d'autres types sont des conversions restrictives qui arrondissent la valeur décimale à la valeur entière la plus proche de zéro.Si le résultat de la conversion ne peut pas être représenté dans le type de destination, un OverflowException est levé.

RemarqueRemarque

Lorsque vous envoyez une valeur de paramètre Null au serveur, vous devez spécifier DBNull, et non null (Nothing dans Visual Basic).Dans le système, la valeur null désigne un objet vide qui ne possède pas de valeur.DBNull est utilisé pour représenter des valeurs null.Pour plus d'informations sur les valeurs Null de base de données, consultez Gestion des valeurs null (ADO.NET).

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 DbCommandBuilder. Les classes SqlCommandBuilder et OleDbCommandBuilder fournissent une méthode statique, DeriveParameters, qui remplit automatiquement la collection de paramètres d'un objet Command qui utilise les informations sur les paramètres provenant d'une procédure stockée. Notez que DeriveParameters remplace toutes les informations existantes sur les paramètres pour la commande.

RemarqueRemarque

La dérivation des informations de paramètre entraîne une baisse des performances car elle requiert un aller-retour supplémentaire vers la source de données pour extraire 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.

Pour plus d'informations, voir Génération de commandes à l'aide de CommandBuilders (ADO.NET).

Utilisation des paramètres avec un objet SqlCommand ou une procédure stockée

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 de meilleures performances et améliorées grâce à une sécurité supplémentaire. Bien qu'une procédure stockée puisse être appelée en passant 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.

RemarqueRemarque

Les instructions paramétrées sont exécutées sur le serveur à l'aide de sp_executesql,, ce qui permet la réutilisation des plans de requête.Les curseurs ou variables locaux dans le lot sp_executesql ne sont pas visibles pour le lot qui appelle sp_executesql.Les modifications dans le contexte de la base de données durent uniquement jusqu'à la fin de l'instruction sp_executesql.Pour plus d'informations, voir la documentation en ligne de SQL Server.

Lorsque vous utilisez des paramètres avec un objet SqlCommand pour exécuter une procédure stockée SQL Server, les noms des paramètres ajoutés à la collection Parameters doivent correspondre à ceux des marqueurs de paramètres dans la procédure stockée. 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. Il traite les paramètres de la procédure stockée comme des paramètres nommés et recherche les marqueurs de paramètres correspondants. Par exemple, la procédure stockée CustOrderHist est définie à l'aide d'un paramètre nommé @CustomerID. Lorsque votre code exécute la procédure stockée, il doit également utiliser un paramètre nommé @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Exemple

Cet exemple montre comment appeler une procédure stockée SQL Server dans l'exemple de base de données Northwind. Le nom de la procédure stockée est dbo.SalesByCategory et il possède un paramètre d'entrée nommé @CategoryName avec un type de données nvarchar(15). Le code crée un nouveau SqlConnection à l'intérieur d'un bloc using pour que la connexion soit libérée une fois la procédure terminée. Les objets SqlCommand et SqlParameter sont créés et leurs propriétés sont définies. Un SqlDataReader exécute SqlCommand et retourne le jeu de résultats provenant de la procédure stockée, en affichant la sortie dans la fenêtre de console.

RemarqueRemarque

Au lieu de créer les objets SqlCommand et SqlParameter puis de définir les propriétés dans des instructions distinctes, vous pouvez choisir d'utiliser l'un des constructeurs surchargés pour définir plusieurs propriétés dans une instruction unique.

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub
static void GetSalesByCategory(string connectionString, 
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

Utilisation des paramètres avec OleDbCommand ou OdbcCommand

Lorsque vous utilisez des paramètres avec un 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 .NET Framework pour 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 paramètres ajoutés à la collection Parameters.

Les fournisseurs de données .NET Framework pour OLE DB et .NET Framework pour 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.

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
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;

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
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;

Voir aussi

Concepts

Paramètres de DataAdapter (ADO.NET)

Autres ressources

Commandes et paramètres (ADO.NET)

Mappages de types de données dans ADO.NET