Wednesday, 16 January 2013

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"

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

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

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)
        var ds = SqlDataSource1.Select(new DataSourceSelectArguments() { });
        view = (DataView)ds;

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;

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;


Step 5) Run the Page
Filter GridView using DropDownList SQlDataSource C# VB.NET