Filtering the Data with Data Source Controls in ASP.NET

ASP.NET comes with Data Source Controls (SqlDataSource,EntityDataSource,LinqDataSource and ObjectDataSource) which allows you to bind the data to your data-bound controls. The SelectParameters element in Data Source controls enables you to supply values at run time for filtering the data. This post outlines the steps to using the Filters with Data Source controls. The Possible sources that you can use to send the values to Data Source SelectParameters element are SessionParameter,QueryStringParameter,CookieParameter,ControlParameter and FormParameter

1. Create a new web forms project in Visual Studio 2012, Add a DropDownList control to page from toolbox.

Enable AutoPostBack property as shown above. The idea is to show the product categories in the DropDownList controls from AdventureWorksDatabase and use this as a Filter control for displaying the values in GridView. Now click the Edit Items link, Insert an item with its text to “Select a Value”

2. Now click the Choose Data Source link and then click <New data source> from the drop down

Click the Database and give an id to data source and say ok. Now select the desired database connection string or create a new one

3.Specify the ID and Name columns of the table that you want to bind it to the drop-down control

4. Click Next and say finish then you will return to the Data Source Configuration Wizard for the drop-down list where you can set display field and id field as shown below

5. The markup of the page now looks as below

 <asp:DropDownList ID="ddlProducts" runat="server" AppendDataBoundItems="True" 
   AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="Name"    DataValueField="ProductCategoryID">
   <asp:ListItem  Value="" Selected="True">Select a Value</asp:ListItem>
 </asp:DropDownList>
   

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:AdventureWorksLT2008R2ConnectionString %>" 
SelectCommand="SELECT [ProductCategoryID], [Name] FROM [ProductCategory]">
</asp:SqlDataSource>

6.Now run the application, You will see the dropdown with values and when you change the values in drop-down page will get refresh

7.Now add the GridView control to the default.aspx page below the drop-down list control , choose the data source for Grid View and click database as you did for the Drop Down data source, Select the table that you want to bind it to the control and click Where condition add the where clause as shown below and click Add button and then say ok

8. Here you are using ConrolParameter as a source and giving the control parameter id in parameter properties section, The mark for Grid View looks as below

 <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2" CellPadding="4"     ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
     <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
  </asp:GridView>

9. The Data Source Markup for Grid View looks as below

  <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksLT2008R2ConnectionString %>" 
            SelectCommand="SELECT [Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost] FROM [AdventureWorksLT2008R2].[SalesLT].[Product] WHERE ([ProductCategoryID] = @ProductCategoryID)">
            <SelectParameters>
                <asp:ControlParameter ControlID="ddlProducts" Name="ProductCategoryID" PropertyName="SelectedValue" Type="Int32" />
            </SelectParameters>
  </asp:SqlDataSource>

10. Now run the application and change the values in drop down then you will get the data in GridView. This actually filters the data that you want to see in Grid View control