Using Parameters with the SqlDataSource Control

Artikel
10/22/2014

SQL statements and stored procedures often include parameters that are evaluated at run time. An SQL statement written with parameters is referred to as a parameterized SQL statement.

When using the SqlDataSource control, you can specify SQL queries and statements that use parameters. This helps make your data-binding scenarios more flexible by reading and writing database information based on values that are evaluated at run time. You can get parameter values from various sources, including ASP.NET application variables, user identities, and user-selected values. You can use parameters to supply search criteria for data retrieval; to supply values to be inserted, updated, or deleted in a data store; and to supply values for sorting, paging, and filtering.

Using Parameters

Like all data source controls, the SqlDataSource control accepts input parameters at run time and manages them in parameter collections. Each data operation has a related parameter collection. For select operations, you can use the SelectParameters collection; for updates, you can use the UpdateParameters collection; and so on.

You can specify a name, type, direction, and default value for each parameter. Parameters that get values from a specific object, such as a control, session variable, or the user profile, require you to set additional properties. For example, a ControlParameter requires that you set the ControlID to identify the control to take the parameter value from, and set the PropertyName property to specify the property that contains the parameter value. For more information, see Using Parameters with Data Source Controls.

The SqlDataSource control will also automatically create parameters based on values passed by a data-bound control (such as a GridView or FormView control) that supports automatic update, insert, and delete operations. For more information, see How a Data Source Control Creates Parameters for Data-bound Fields.

Specifying Parameters in Commands

When you use the SqlDataSource control, you can set the control's command properties to parameterized SQL statements or to the name of a stored procedure. If you specify a stored procedure for a command, you must specify that the command type of the command is StoredProcedure.

Parameter Names

The SqlDataSource control adds the value of the ParameterPrefix property to the beginning of all parameter names. (The default prefix is "@".)

If a data-bound control such as a GridView control is bound to the SqlDataSource control, during an update or delete operation the data-bound control passes both current and original record values to the SqlDataSource control. The current values are passed in the Values dictionary. The original values are passed in the Keys or OldValues dictionaries. The contents of these dictionaries are appended to the underlying DbCommand object's Parameters collection for a given data operation.

In the SqlDataSource control's SQL commands, you use a naming convention to match parameter placeholders to the old values passed into the command. You establish the format of the placeholder name by setting the SqlDataSource control's OldValuesParameterFormatString property. Set the OldValuesParameterFormatString property to a string that includes "{0}" as a placeholder for the name of the field. For example, if you set the OldValuesParameterFormatString property to "old_{0}", the names of the original-value parameters will resolve to the field name prefixed with "@old_". Consider an update operation that involves a field named LastModifiedDate. The current value for the field is passed in the Values dictionary and the original value for the field is passed in the OldValues dictionary. A parameter named @LastModifiedDate is created to pass the current value and a parameter named @old\_LastModifiedDate is created to pass the original value. You can then include both parameters in an SQL statement to differentiate between the current and original values for the field, as shown in the following example:

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

The ability to separate current and original values in a command is essential when performing optimistic concurrency checks or working with a data source where the primary key can be modified.

For more information on the dictionaries passed by a data-bound control, see How a Data Source Control Creates Parameters for Data-bound Fields.

Using Parameters with the SqlClient Provider

By default, the SqlDataSource control uses the System.Data.SqlClient data provider to work with SQL Server as the data source. The System.Data.SqlClient provider supports named parameters as placeholders, as shown in the following example:

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

With named parameters, the order in which the parameters is specified in the command's parameters collection is not important. However, you must ensure that the parameter names that you use in your SQL command correspond to the names of the parameters in the associated collection.

The following example shows how to use named parameters in an SQL command for a SqlDataSource control that uses the System.Data.SqlClient provider. For the complete code example, see Modifying Data using the SqlDataSource Control.

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

Using Parameters with the OleDb and Odbc Providers

If you are connecting to an OLE DB or ODBC data source, you can configure the SqlDataSource control to use the System.Data.OleDb or System.Data.Odbc provider to work with your data source, respectively. The System.Data.OleDb and System.Data.Odbc providers support only positional parameters identified by the "?" character, as shown in the following example:

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

When you use the System.Data.OleDb and System.Data.Odbc providers with parameterized SQL statements, the order in which you specify the parameter placeholders must match the order of the parameters in the related parameter collection. You can control the order of parameters by explicitly specifying them in the collection for the related data operation, such as the UpdateParameters collection for the related UpdateCommand. When you explicitly create a parameter collection for parameters that are automatically created from values passed by a data-bound control, the explicitly created parameters overwrite any parameters that are automatically generated. This way you ensure that the parameters are passed in the order that you want. If you call a stored procedure that returns a value, you must specify a parameter with a Direction value of ReturnValue as the first parameter in the command parameters collection.

Note

By default, parameters based on bound fields from a data-bound control are added to the command from the parameter dictionaries in the following order: Values, Keys, OldValues. For delete operations, only the Keys dictionary is used. For insert operations, only the Values dictionary is used. For more information on the dictionaries passed by a data-bound control, see How a Data Source Control Creates Parameters for Data-bound Fields.

The following example shows how to specify parameters for a SqlDataSource control that uses the System.Data.OleDb provider. Parameters are explicitly specified to ensure the order of the parameters in the collection matches the order of the placeholders in the SQL statements.

<%@ Page language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  Sub EmployeesDropDownList_OnSelectedIndexChanged(sender As Object, e As EventArgs)
    EmployeeDetailsView.DataBind()
  End Sub

  Sub EmployeeDetailsView_ItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)
    EmployeesDropDownList.DataBind()
    EmployeesDropDownList.SelectedValue = e.Keys("EmployeeID").ToString()
    EmployeeDetailsView.DataBind()
  End Sub

  Sub EmployeeDetailsView_ItemDeleted(sender As Object, e As DetailsViewDeletedEventArgs)
    EmployeesDropDownList.DataBind()
  End Sub

  Sub EmployeeDetailsSqlDataSource_OnInserted(sender As Object, e As SqlDataSourceStatusEventArgs)
    Dim command As System.Data.Common.DbCommand = e.Command  
    EmployeesDropDownList.DataBind()
    EmployeesDropDownList.SelectedValue = _
      command.Parameters("@EmpID").Value.ToString()
    EmployeeDetailsView.DataBind()
  End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>Northwind Employees</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>Northwind Employees</h3>

        <table cellspacing="10">

          <tr>
            <td valign="top">
              <asp:DropDownList ID="EmployeesDropDownList" 
                DataSourceID="EmployeesSqlDataSource" 
                DataValueField="EmployeeID" 
                DataTextField="FullName"
                AutoPostBack="True"
                OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"
                RunAt="Server" />            
            </td>

            <td valign="top">                
              <asp:DetailsView ID="EmployeeDetailsView"
                DataSourceID="EmployeeDetailsSqlDataSource"
                AutoGenerateRows="false"
                AutoGenerateInsertbutton="true"
                AutoGenerateEditbutton="true"
                AutoGenerateDeletebutton="true"
                DataKeyNames="EmployeeID"     
                Gridlines="Both"
                OnItemUpdated="EmployeeDetailsView_ItemUpdated"
                OnItemDeleted="EmployeeDetailsView_ItemDeleted"      
                RunAt="server">

                <HeaderStyle backcolor="Navy"
                  forecolor="White"/>

                <RowStyle backcolor="White"/>

                <AlternatingRowStyle backcolor="LightGray"/>

                <EditRowStyle backcolor="LightCyan"/>

                <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:BoundField DataField="Address"    HeaderText="Address"/>                    
                  <asp:BoundField DataField="City"       HeaderText="City"/>                        
                  <asp:BoundField DataField="Region"     HeaderText="Region"/>
                  <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>                    
                </Fields>                    
              </asp:DetailsView>
            </td>                
          </tr>            
        </table>


        <asp:SqlDataSource ID="EmployeesSqlDataSource"  
          SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees" 
          ProviderName="System.Data.OleDb"
          Connectionstring="<%$ ConnectionStrings:NorthwindOleDbConnection %>" 
          RunAt="server">
        </asp:SqlDataSource>


        <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource" 
          SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
                         FROM Employees WHERE EmployeeID = ?"

          InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
                         VALUES (?, ?, ?, ?, ?, ?); 
                         SELECT @EmpID = SCOPE_IDENTITY()"

          UpdateCommand="UPDATE Employees SET LastName=?, FirstName=?, Address=?,
                           City=?, Region=?, PostalCode=?
                         WHERE EmployeeID=?"

          DeleteCommand="DELETE Employees WHERE EmployeeID=@original_EmployeeID"

          ProviderName="System.Data.OleDb"
          ConnectionString="<%$ ConnectionStrings:NorthwindOleDbConnection %>"
          OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
          RunAt="server">

          <SelectParameters>
            <asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
                                  Name="EmpID" Type="Int32" DefaultValue="0" />
          </SelectParameters>

          <InsertParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
          </InsertParameters>

          <UpdateParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="original_EmployeeID" Type="Int32" DefaultValue="0" />
          </UpdateParameters>

          <DeleteParameters>
            <asp:Parameter Name="original_EmployeeID" Type="Int32" DefaultValue="0" />
          </DeleteParameters>

        </asp:SqlDataSource>
      </form>
  </body>
</html>

<%@ Page language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  void EmployeesDropDownList_OnSelectedIndexChanged(Object sender, EventArgs e)
  {
    EmployeeDetailsView.DataBind();
  }

  void EmployeeDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
  {
    EmployeesDropDownList.DataBind();
    EmployeesDropDownList.SelectedValue = e.Keys["EmployeeID"].ToString();
    EmployeeDetailsView.DataBind();
  }

  void EmployeeDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
  {
    EmployeesDropDownList.DataBind();
  }

  void EmployeeDetailsSqlDataSource_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
  {
    System.Data.Common.DbCommand command = e.Command;   
    EmployeesDropDownList.DataBind();
    EmployeesDropDownList.SelectedValue = 
      command.Parameters["@EmpID"].Value.ToString();
    EmployeeDetailsView.DataBind();
  }

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>Northwind Employees</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>Northwind Employees</h3>

        <table cellspacing="10">

          <tr>
            <td valign="top">
              <asp:DropDownList ID="EmployeesDropDownList" 
                DataSourceID="EmployeesSqlDataSource" 
                DataValueField="EmployeeID" 
                DataTextField="FullName"
                AutoPostBack="True"
                OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"
                RunAt="Server" />            
            </td>

            <td valign="top">                
              <asp:DetailsView ID="EmployeeDetailsView"
                DataSourceID="EmployeeDetailsSqlDataSource"
                AutoGenerateRows="false"
                AutoGenerateInsertbutton="true"
                AutoGenerateEditbutton="true"
                AutoGenerateDeletebutton="true"
                DataKeyNames="EmployeeID"     
                Gridlines="Both"
                OnItemUpdated="EmployeeDetailsView_ItemUpdated"
                OnItemDeleted="EmployeeDetailsView_ItemDeleted"      
                RunAt="server">

                <HeaderStyle backcolor="Navy"
                  forecolor="White"/>

                <RowStyle backcolor="White"/>

                <AlternatingRowStyle backcolor="LightGray"/>

                <EditRowStyle backcolor="LightCyan"/>

                <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:BoundField DataField="Address"    HeaderText="Address"/>                    
                  <asp:BoundField DataField="City"       HeaderText="City"/>                        
                  <asp:BoundField DataField="Region"     HeaderText="Region"/>
                  <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>                    
                </Fields>                    
              </asp:DetailsView>
            </td>                
          </tr>            
        </table>


        <asp:SqlDataSource ID="EmployeesSqlDataSource"  
          SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees" 
          ProviderName="System.Data.OleDb"
          Connectionstring="<%$ ConnectionStrings:NorthwindOleDbConnection %>" 
          RunAt="server">
        </asp:SqlDataSource>


        <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource" 
          SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
                         FROM Employees WHERE EmployeeID = ?"

          InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
                         VALUES (?, ?, ?, ?, ?, ?); 
                         SELECT @EmpID = SCOPE_IDENTITY()"

          UpdateCommand="UPDATE Employees SET LastName=?, FirstName=?, Address=?,
                           City=?, Region=?, PostalCode=?
                         WHERE EmployeeID=?"

          DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

          ProviderName="System.Data.OleDb"
          ConnectionString="<%$ ConnectionStrings:NorthwindOleDbConnection %>"
          OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
          RunAt="server">

          <SelectParameters>
            <asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
                                  Name="EmpID" Type="Int32" DefaultValue="0" />
          </SelectParameters>

          <InsertParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
          </InsertParameters>

          <UpdateParameters>
            <asp:Parameter Name="LastName"   Type="String" />
            <asp:Parameter Name="FirstName"  Type="String" />
            <asp:Parameter Name="Address"    Type="String" />
            <asp:Parameter Name="City"       Type="String" />
            <asp:Parameter Name="Region"     Type="String" />
            <asp:Parameter Name="PostalCode" Type="String" />
            <asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
          </UpdateParameters>

          <DeleteParameters>
            <asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
          </DeleteParameters>

        </asp:SqlDataSource>
      </form>
  </body>
</html>

Freigeben über

Using Parameters with the SqlDataSource Control

Using Parameters

Specifying Parameters in Commands

Parameter Names

Using Parameters with the SqlClient Provider

Using Parameters with the OleDb and Odbc Providers

See Also

Concepts

Reference

Zusätzliche Ressourcen