Filter GridView using DropDownList SQlDataSource C#

Filter GridView using DropDownList SQlDataSource C#

 

Step 1) Add DropDownList and GridView and SQLDataSource to Page

 

 <asp:DropDownList ID="DropDownList1"
            runat="server" AutoPostBack="True"
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

   <asp:GridView ID="GridView1" runat="server"
            AllowSorting="True"
            EmptyDataText="no data found"
            ShowFooter="True" ShowHeaderWhenEmpty="True">
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="Sales by Year" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:Parameter DefaultValue="1996-01-01"
                    Name="Beginning_Date" Type="DateTime" />
                <asp:Parameter DefaultValue="1998-01-01"
                    Name="Ending_Date" Type="DateTime" />
            </SelectParameters>
        </asp:SqlDataSource>

in above SQLDataSource binding to Stored Procedure(i.e Sales by Year), Which accepts 2 parameters
Bengin Date and EndDate(Hard-coded).

Step 2) Get SQLDataSource DataSet

 //note: Select Statement in SQLDATASource creates a DataSet(stored in Memory)

        protected void Page_Load(object sender, EventArgs e)
        {
        BindCulture();
        var ds = SqlDataSource1.Select(new DataSourceSelectArguments() { });
        view = (DataView)ds;
        BindDropDownWithDistinctValues();
        GridView1.DataBind();
        }

Step 3) Bind Distinct Values from DataSet to DropDownList

        void BindDropDownWithDistinctValues()
        {
        if (!IsPostBack)
        {
        DataTable dt = view.Table;
        var query = (from y in dt.AsEnumerable()
                    select y.Field<String>("Year")).Distinct();
        DropDownList1.DataSource = query;
        DropDownList1.DataBind();
        }
        }

Step 4)   Add SelectionIndexChanged for DropDownList and Set AutoPostback=true

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
        view.RowFilter = "Year ='" + DropDownList1.SelectedValue + "'";
       
        GridView1.DataSource = view;
        GridView1.DataBind();

        }

Step 5) Run the Page