Utilisation de paramètres avec le contrôle SqlDataSource

Article
10/22/2014

Mise à jour : novembre 2007

Les instructions et les procédures stockées SQL incluent souvent des paramètres qui sont évalués au moment de l'exécution. Une instruction SQL écrite avec des paramètres est connue sous le nom d'instruction SQL paramétrée.

Lorsque vous utilisez le contrôle SqlDataSource, vous pouvez spécifier des requêtes et des instructions SQL qui utilisent des paramètres. Vous pouvez ainsi bénéficier d'une plus grande souplesse dans vos scénarios de liaison de données dans la mesure où les informations de la base de données sont lues et écrites sur la base de valeurs évaluées au moment de l'exécution. Vous pouvez obtenir des valeurs de paramètre de différentes sources, et notamment des variables d'application ASP.NET, des identités d'utilisateur et des valeurs sélectionnées par l'utilisateur. Les paramètres peuvent vous servir à fournir des critères de recherche pour la récupération de données, à fournir des valeurs à insérer, à mettre à jour ou à supprimer dans un magasin de données, et à fournir des valeurs servant à trier, paginer et filtrer.

Utilisation de paramètres

À l'instar de tous les contrôles de source de données, le contrôle SqlDataSource accepte des paramètres d'entrée au moment de l'exécution et les gère dans des collections de paramètres. Chaque opération de données possède une collection de paramètres associée. Pour les opérations de sélection, vous pouvez utiliser la collection SelectParameters, pour les mises à jour, la collection UpdateParameters, etc.

Vous pouvez spécifier un nom, un type, une direction et une valeur par défaut pour chaque paramètre. Dans le cas de paramètres qui obtiennent des valeurs d'un objet spécifique, tel qu'un contrôle, une variable de session ou le profil utilisateur, vous devez définir des propriétés supplémentaires. Par exemple, pour un objet ControlParameter, vous devez définir la propriété ControlID afin d'identifier le contrôle à partir duquel récupérer la valeur de paramètre et définir la propriété PropertyName pour spécifier la propriété qui contient la valeur de paramètre. Pour plus d'informations, consultez Utilisation de paramètres avec des contrôles de source de données.

En outre, le contrôle SqlDataSource crée automatiquement des paramètres selon les valeurs passées par un contrôle lié aux données (par exemple un contrôle GridView ou FormView) qui prend en charge les opérations de mise à jour, d'insertion et de suppression automatiques. Pour plus d'informations, consultez Comment un contrôle de source de données crée des paramètres pour des champs liés aux données.

Spécification de paramètres dans les commandes

Lorsque vous utilisez le contrôle SqlDataSource, vous pouvez affecter des instructions SQL paramétrées ou le nom d'une procédure stockée aux propriétés de commande du contrôle. Si vous spécifiez une procédure stockée pour une commande, vous devez préciser que le type de la commande est StoredProcedure.

Noms de paramètres

Le contrôle SqlDataSource ajoute la valeur de la propriété ParameterPrefix au début de tous les noms de paramètres. (Le préfixe par défaut est "@".)

Si un contrôle lié aux données, tel qu'un contrôle GridView, est lié au contrôle SqlDataSource, le contrôle lié aux données passe les valeurs actuelles et d'origine de l'enregistrement au contrôle SqlDataSource lors d'une opération de mise à jour ou de suppression. Les valeurs actuelles sont passées dans le dictionnaire Values. Les valeurs d'origine sont passées dans les dictionnaires Keys ou OldValues. Le contenu de ces dictionnaires est ajouté à la collection Parameters de l'objet DbCommand sous-jacent pour une opération de données spécifique.

Dans les commandes SQL du contrôle SqlDataSource, vous utilisez une convention d'affectation de noms afin que les anciennes valeurs passées dans la commande correspondent aux espaces réservés des paramètres. Vous établissez le format du nom d'espace réservé en définissant la propriété OldValuesParameterFormatString du contrôle SqlDataSource. Affectez à la propriété OldValuesParameterFormatString une chaîne qui inclut "{0}" comme espace réservé pour le nom du champ. Par exemple, si vous affectez la valeur "old_ {0}" à la propriété OldValuesParameterFormatString, les noms des paramètres des valeurs d'origine seront résolus en noms de champs précédés du préfixe "@old_". Prenons l'exemple d'une opération de mise à jour sur un champ nommé LastModifiedDate. La valeur actuelle du champ est passée dans le dictionnaire Values et la valeur d'origine du champ est passée dans le dictionnaire OldValues. Un paramètre nommé @LastModifiedDate est créé pour passer la valeur actuelle et un paramètre nommé @old\_LastModifiedDate est créé pour passer la valeur d'origine. Vous pouvez ensuite inclure les deux paramètres dans une instruction SQL pour distinguer les valeurs actuelle et d'origine du champ, comme illustré dans l'exemple suivant :

UPDATE Table1 SET LastModifiedDate = @LastModifiedDate
  WHERE Key = @Key AND LastModifiedDate = @old_LastModifiedDate

Il est essentiel de pouvoir séparer les valeurs actuelles et d'origine dans une commande lors de l'exécution de contrôles d'accès concurrentiel optimiste ou de l'utilisation d'une source de données dont la clé primaire peut être modifiée.

Pour plus d'informations sur les dictionnaires passés par un contrôle lié aux données, consultez Comment un contrôle de source de données crée des paramètres pour des champs liés aux données.

Utilisation des paramètres avec le fournisseur SqlClient

Par défaut, le contrôle SqlDataSource a recours au fournisseur de données System.Data.SqlClientpour utiliser SQL Server comme source de données. Le fournisseur System.Data.SqlClient prend en charge des paramètres nommés comme espaces réservés, comme illustré dans l'exemple suivant :

SELECT * FROM Employees WHERE LastName = @LastName 
  AND FirstName = @FirstName

Avec les paramètres nommés, l'ordre dans lequel les paramètres sont spécifiés dans la collection de paramètres de la commande n'est pas important. Toutefois, vous devez veiller à ce que les noms des paramètres utilisés dans votre commande SQL correspondent aux noms des paramètres de la collection associée.

L'exemple suivant montre comment utiliser des paramètres nommés dans une commande SQL pour un contrôle SqlDataSource qui utilise le fournisseur System.Data.SqlClient.

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>

Utilisation des paramètres avec les fournisseurs OLE DB et ODBC

Si vous vous connectez à une source de données OLE DB ou ODBC, vous pouvez configurer le contrôle SqlDataSource pour qu'il ait respectivement recours au fournisseur System.Data.OleDb ou System.Data.Odbc dans le cadre de l'utilisation de votre source de données. Les fournisseurs System.Data.OleDb et System.Data.Odbc prennent uniquement en charge les paramètres positionnels identifiés par le caractère "?", comme illustré dans l'exemple suivant :

SELECT * FROM Employees WHERE LastName = ? AND FirstName = ?

Lorsque vous utilisez les fournisseurs System.Data.OleDb et System.Data.Odbc avec des instructions SQL paramétrées, l'ordre dans lequel vous spécifiez les espaces réservés des paramètres doit correspondre à l'ordre des paramètres de la collection de paramètres associée. Vous pouvez contrôler l'ordre des paramètres en les spécifiant explicitement dans la collection de l'opération de données associée, telle que la collection UpdateParameters de l'opération UpdateCommand liée. Lorsque vous créez explicitement une collection de paramètres pour les paramètres créés automatiquement à partir de valeurs passées par un contrôle lié aux données, les paramètres créés explicitement se substituent à tous les paramètres générés automatiquement. De cette façon, vous êtes assuré que les paramètres sont passés dans l'ordre requis. Si vous appelez une procédure stockée qui retourne une valeur, vous devez spécifier un paramètre avec une valeur Direction de ReturnValue comme premier paramètre dans la collection des paramètres de commande.

Remarque :
Par défaut, les paramètres basés sur les champs dépendants d'un contrôle lié aux données sont ajoutés à la commande à partir des dictionnaires de paramètres dans l'ordre suivant : Values, Keys, OldValues. Pour les opérations de suppression, seul le dictionnaire Keys est utilisé. Pour les opérations d'insertion, seul le dictionnaire Values est utilisé. Pour plus d'informations sur les dictionnaires passés par un contrôle lié aux données, consultez Comment un contrôle de source de données crée des paramètres pour des champs liés aux données.

Par défaut, les paramètres basés sur les champs dépendants d'un contrôle lié aux données sont ajoutés à la commande à partir des dictionnaires de paramètres dans l'ordre suivant : Values, Keys, OldValues. Pour les opérations de suppression, seul le dictionnaire Keys est utilisé. Pour les opérations d'insertion, seul le dictionnaire Values est utilisé. Pour plus d'informations sur les dictionnaires passés par un contrôle lié aux données, consultez Comment un contrôle de source de données crée des paramètres pour des champs liés aux données.

L'exemple suivant montre comment spécifier les paramètres d'un contrôle SqlDataSource qui utilise le fournisseur System.Data.OleDb. Les paramètres sont spécifiés explicitement afin de garantir que l'ordre des paramètres de la collection correspond à l'ordre des espaces réservés dans les instructions SQL.

<Fields>                  
  <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>                    
  <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
  <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>                    
  <asp:TemplateField HeaderText="Birth Date">
    <ItemTemplate> 
      <asp:Label ID="BirthDateLabel" Runat="Server" 
                 Text='<%# Eval("BirthDate", "{0:d}") %>' />
    </ItemTemplate>
    <InsertItemTemplate>
      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </InsertItemTemplate>
    <EditItemTemplate>
      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"
                    VisibleDate='<%# Eval("BirthDate") %>'
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </EditItemTemplate>
  </asp:TemplateField>                    
</Fields>

<Fields>                  
  <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>                    
  <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
  <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>                    
  <asp:TemplateField HeaderText="Birth Date">
    <ItemTemplate> 
      <asp:Label ID="BirthDateLabel" Runat="Server" 
                 Text='<%# Eval("BirthDate", "{0:d}") %>' />
    </ItemTemplate>
    <InsertItemTemplate>
      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </InsertItemTemplate>
    <EditItemTemplate>
      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"
                    VisibleDate='<%# Eval("BirthDate") %>'
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </EditItemTemplate>
  </asp:TemplateField>                    
</Fields>