Wednesday, 16 January 2013

filter gridview using dropdownlist C#

filter gridview using dropdownlist C#

filter gridview using dropdownlist C#/VB.NET

DropDownList to Filter GridView


This example explains how to filter gridview using DropDownlist in asp.net C#

Step 1) Add GridView and DropDownList.

filter gridview using dropdownlist C# VB.NET

        <asp:ScriptManager ID="ScriptManager1" runat="server" ></asp:ScriptManager>
       
        <table>
                  
            <asp:Label ID="Label4" runat="server"
                Text="<h1>Filter Gridview using DropDownList<h1>"
                Width="300"></asp:Label> 
            <tr><td style="vertical-align:top;">
                 <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server"
            AutoPostBack="true"
            OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

        </asp:DropDownList>
            
               

            </ContentTemplate>
             
//Update Panel should be conditional because DropDownList exists outside of the

//UpdatePanel  .
//Only this UpdatePanel content will be Updated and UpdatePanels with UpdateMode='Always' also will be updated.

</asp:UpdatePanel>
            </td><td style="vertical-align:top">
    <div>
        <asp:UpdatePanel runat="server" UpdateMode="Conditional">
            <ContentTemplate>
        <asp:GridView ID="GridView1" runat="server"
             ShowFooter="True" AutoGenerateColumns="false"
             AllowPaging="true"
            ShowHeaderWhenEmpty="true"
             EmptyDataText="No data found"

            >
            <FooterStyle BackColor="YellowGreen" />
            <Columns>
                <asp:TemplateField HeaderText="Shipped Date">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%#Eval("ShippedDate","{0:D}") %>'>

                        </asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="OrderID">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%#Eval("OrderID") %>'>

                        </asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Label ID="Label3" runat="server" Text="Total">

                        </asp:Label>
                    </FooterTemplate>

                </asp:TemplateField>
                <asp:TemplateField HeaderText="Subtotal">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%#Eval("Subtotal","{0:C}") %>'>

                        </asp:Label>
                    </ItemTemplate>
                   
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Year">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%#Eval("Year") %>'>

                        </asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>

            </Columns>
        </asp:GridView>
                            </ContentTemplate>
            <Triggers>
                <asp:AsyncPostBackTrigger ControlID="DropDownList1" />
            </Triggers>
        </asp:UpdatePanel>

    </div>
                </td></tr></table>
    </form>

Step 2)  Call SQL Stored Procedure to get the DATA.

    void Calling_Stored_Procedures_using_adonet(DateTime begingdate,
            DateTime enddate)
        {
        try
        {
        SqlConnection conn = new SqlConnection(@"Server=.\sqlexpress2012;
                                               database=northwind;trusted_connection=yes;");
       

        //SqlCommand sel = new SqlCommand(stroredProc);
        SqlCommand sel = new SqlCommand();
        SqlParameter sqlBegin=sel.Parameters.Add("@Beginning_Date", SqlDbType.DateTime);
        sqlBegin.Direction = ParameterDirection.Input;
        sqlBegin.Value = begingdate;
        SqlParameter sqlEnd = sel.Parameters.Add("@Ending_Date",SqlDbType.DateTime);
        sqlEnd.Direction = ParameterDirection.Input;
        sqlEnd.Value = enddate;
        sel.CommandType = CommandType.StoredProcedure;
        sel.CommandText = "[dbo].[Sales by Year]";
        sel.Connection = conn;
        SqlDataAdapter ad = new SqlDataAdapter(sel);

        conn.Open();
        ad.Fill(dt);

        BindDropDownList();
        }
        catch (SqlException ex)
        {
        Response.Write(ex.Message);
        }
        }



Step 3)  First Bind to DropDownList  

void BindDropDownList()
        {
        if (!IsPostBack)
        {
          //Using LINQ to DATATABLE , select only Distinct Values
        var query = (from t in dt.AsEnumerable()
                     select t.Field<String>("Year")).Distinct();

        DropDownList1.DataSource = query;

        DropDownList1.DataBind();
        DropDownList1.Items.Insert(0, "--Year--");
        }
        }

Step 4)   Add DropDownList   Event Handler SelectedIndexChanged,

Make AutoPostBack=true

In this actually bind to GridView using Selected Value in DropDownList

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
        String year =DropDownList1.SelectedValue.ToString();
        DataView view =dt.DefaultView;
        view.RowFilter = "Year='" + year + "'";
        GridView1.DataSource = view;//dt.Select("Year='"+year+"'");
        GridView1.DataBind();

        }


OUTPUT
filter gridview using dropdownlist C#/VB.NET,Stored procedures ,ado.net

Tags:Asp:GridView,asp:DropDownList,ADO.NET Stored Procedures,ADO.NET DataTable,DropDownList SelectIndexChanged,FIlter GridView based on DropDownList value,Filtering GridView using DropDownList,LINQ to DataTable Distinct,UPDATEPANEL CONDITIONAL,AJAX GRIDVIEW,AJAX DROPDOWNLIST,