Using Parameters with Data Source Controls for Filtering

Article
12/04/2014

ASP.NET data source controls can accept input parameters so that you can pass values to them at run time. 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 enables you to filter data and create master-detail applications with little or no custom code.

You can also use parameter to customize the values passed to a data source by a data-bound control, such as a GridView or FormView control, that supports automatic update, insert, and delete operations. For example, you can use parameter objects to strongly type values or to retrieve output values from the data source. Additionally, parameterized queries can make an application more secure by protecting against SQL injection attacks.

Parameter values can be obtained from a variety of sources. Parameter objects enable you to supply values to parameterized data operations from Web server control properties, cookies, session state, QueryString fields, user profile properties, and other sources.

Parameter Types

You can specify several types of parameter objects in your Web application. The type of the parameter object determines where the parameter value comes from. The Parameter class is the base class for all parameter objects and includes Name, Type, Direction, and DefaultValue properties that are common to all parameter types. You typically use the Parameter base class to specify how a data source should handle parameter values obtained from an associated data-bound control, such as the values passed by a GridView control for an Update or Delete operation.

You can use parameter types that derive from the Parameter class to obtain values from other sources, as described in the following table.

Parameter Type	Description
ControlParameter	Sets a parameter to the property value of a Control on an ASP.NET Web page. You specify the Control using the ControlID property. You specify the name of the property that supplies the parameter value using the ControlParameter object's PropertyName property. Some controls that derive from Control define a ControlValuePropertyAttribute, which determines the default property from which to retrieve the control's value. The default property is used whenever the PropertyName property is not explicitly set. The ControlValuePropertyAttribute is applied to the following control properties: Calendar.SelectedDate CheckBox.Checked DetailsView.SelectedValue FileUpload.FileBytes GridView.SelectedValue Label.Text TextBox.Text TreeView.SelectedValue
CookieParameter	Sets a parameter to the value of an HttpCookie object. You specify the name of the HttpCookie object using the CookieName property. If the specified HttpCookie object does not exist, then the value of the DefaultValue property is used as the parameter value. Note Only single-valued cookies are supported.
FormParameter	Sets a parameter to the value of an HTML form field. You specify the name of the HTML form field using the FormField property. If the specified HTML form field value does not exist, then the value of the DefaultValue property is used as the parameter value.
ProfileParameter	Sets a parameter to the value of a property from the current user profile (Profile). You specify the name of the profile property using the PropertyName property. If the specified profile property does not exist, then the value of the DefaultValue property is used as the parameter value. For information about user profiles, see ASP.NET Profile Properties Overview.
QueryStringParameter	Sets a parameter to the value of a QueryString field. You specify the name of the QueryString field using the QueryStringField property. If the specified QueryString field does not exist, then the value of the DefaultValue property is used as the parameter value.
SessionParameter	Sets a parameter to the value of a Session object. You specify the name of the Session object using the SessionField property. If the specified Session object does not exist, then the value of the DefaultValue property is used as the parameter value.

Strongly Typing Parameter Values

By default, parameters are typed as Object. If a parameter value is of another type, such as DateTime or Int32, you can create Parameter objects explicitly and set the parameter's Type property to a TypeCode value.

Parameter Direction

Parameters are input parameters by default. In some cases, such as when you use stored procedures, you might need to read a value returned from the data source. If so, you can set the Parameter object's Direction property to ensure that you capture information that the data source returns to your Web application. The supported parameter direction settings are Input, InputOutput, Output, and ReturnValue. You will typically handle a data source control event, such as an Inserted or Updated event, to obtain the parameter's return value after the data operation is completed.

Data Source Controls and Parameters

Data source controls support parameterized operations in different ways. For example, the LinqDataSource control enables you to substitute run-time values into a clause in a LINQ query. The SqlDataSource and AccessDataSource controls enable you to specify parameter placeholders in an SQL statement, such as the SelectCommand. The ObjectDataSource control uses parameters to determine the appropriate method signature to call for a particular data operation, such as the SelectMethod. For more information, see Using Parameters with the SqlDataSource Control and Using Parameters with the ObjectDataSource Control.

Data source controls typically include a parameter collection for each data operation. When selecting data, you can specify a SelectParameters collection, when updating a data item you can specify an UpdateParameters collection, and so on. The contents of the parameters collection for a particular action are then used to supply values to the underlying data source. When inserting, updating, or deleting data, the data source control creates parameters for bound fields, combines them with the explicitly specified parameters collection (if any), and then passes the resulting collection to the data source. For information on the parameter names and values that a data source control creates based on data from a bound control, see Using Parameters with Data Source Controls for Inserting and Updating.

The following example shows a SqlDataSource control that retrieves information based on a value from a QueryString field.

<asp:SqlDataSource id="Employees1" runat="server"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees
                 WHERE EmployeeID = @empId">
  <SelectParameters>
    <asp:QueryStringParameter Name="empId" QueryStringField="empId" />
  </SelectParameters>
</asp:SqlDataSource>

The following example shows a SqlDataSource control that retrieves information based on a value from another control on the page.

<asp:DropDownList id="DropDownList1" runat="server" 
    autopostback="True">
  <asp:listitem selected>Sales Representative</asp:listitem>
  <asp:listitem>Sales Manager</asp:listitem>
  <asp:listitem>Vice President, Sales</asp:listitem>
</asp:DropDownList></p>

<asp:SqlDataSource id="Employees" runat="server"
  ConnectionString="<%$ ConnectionStrings:Northwind%>"
  SelectCommand="SELECT LastName FROM Employees WHERE Title = @Title">
  <SelectParameters>
    <asp:ControlParameter Name="Title" 
      ControlID="DropDownList1"
      PropertyName="SelectedValue"/>
  </SelectParameters>
</asp:sqldatasource>

The following example shows a LinqDataSource control that creates the Where clause based on a value from a QueryString field.

<asp:LinqDataSource 
      ContextTypeName="NorthwindDataContext" 
      TableName="Employees" 
      Where="EmployeeID = @empID" 
      ID="LinqDataSource1" 
      runat="server">
  <WhereParameters>
    <asp:QueryStringParameter Type="Int32" DefaultValue="1" 
        Name="empID" QueryStringField="empID" />
  </WhereParameters>
</asp:LinqDataSource>

The following example shows a LinqDataSource control that creates the Order By clause based on a selection by the user. The AutoGenerateOrderByClause property is set to true. Therefore, the LinqDataSource control creates the Order By clause with the parameters in the OrderByParameters collection.

<asp:DropDownList AutoPostBack="true" ID="DropDownList1" runat="server">
    <asp:ListItem Value="Category"></asp:ListItem>
    <asp:ListItem Value="Price"></asp:ListItem>
</asp:DropDownList>
<asp:LinqDataSource 
    ContextTypeName="ExampleDataContext" 
    TableName="Products" 
    AutoGenerateOrderByClause="true"
    ID="LinqDataSource1" 
    runat="server">
    <OrderByParameters>
      <asp:ControlParameter
         ControlID="DropDownList1" 
         Type="String" />
    </OrderByParameters>
</asp:LinqDataSource>

The following example shows a SqlDataSource control that uses parameterized commands to query and modify data from a data-bound control. Parameters are explicitly specified in order to strongly type parameter values and to specify output parameters.

<%@ Page language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "https://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="https://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" 
          Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>" 
          RunAt="server">
        </asp:SqlDataSource>


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

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

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

          DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

          ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
          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>

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "https://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="https://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" 
          Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>" 
          RunAt="server">
        </asp:SqlDataSource>


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

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

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

          DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

          ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
          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>

Partager via

Using Parameters with Data Source Controls for Filtering

Parameter Types

Strongly Typing Parameter Values

Parameter Direction

Data Source Controls and Parameters

See Also

Reference

Concepts

Ressources supplémentaires