How to Filter the record using Alphabets pager in asp.net

How to Filter the record using Alphabets pager in asp.net

6:08:00 PM
Description:-

In this tutorial I am going to explain how to filter the record using Alphabets pager in asp.net.
To implement this functionality I am using datalist control (alphabets pager) and gridview control (to show the record).I have created table Tb_Movie and dummy data.

Id
int
Name
varchar(50)
Genre
varchar(50)
Budget
int

Create a store-procedure to get data from database:-

CREATE PROCEDURE Sp_FilterRecord
            (
            @filter varchar(100)
            )
AS
BEGIN
            SET NOCOUNT ON;
            If @filter='all'
            begin
Select * from Tb_Movie
end
else begin
Select * from Tb_Movie where Name like @filter + '%'
end
END
GO

Html:-

<html xmlns="http://www.w3.org/1999/xhtml">
   <head runat="server">
      <style>
         .linkbtn
         {
         padding:5px;
         background:#000;
         color:#fff;
         text-decoration:none;
         border: 2px solid #2196F3;
         }
      </style>
   </head>
   <body>
      <form id="form1" runat="server">
         <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
               <ContentTemplate>
                  <asp:DataList ID="dtlalphabets" runat="server" RepeatDirection="Horizontal">
                     <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1" CssClass="linkbtn" runat="server" Text='<%#Eval("Value")%>'>LinkButton</asp:LinkButton>
                     </ItemTemplate>
                  </asp:DataList>
                  <asp:HiddenField ID="HiddenField1" runat="server" />
                  <br />
                  <asp:GridView ID="GridView1" Width="50%" runat="server" ShowHeaderWhenEmpty="True" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" AllowPaging="true" PageSize="2" OnPageIndexChanging="GridView1_PageIndexChanging">
                     <EmptyDataRowStyle ForeColor="red" Font-Bold="true"/>
                     <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                     <Columns>
                        <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-HorizontalAlign="Center"/>
                        <asp:BoundField DataField="Genre" HeaderText="Genre" ItemStyle-HorizontalAlign="Center"/>
                        <asp:BoundField DataField="Budget" HeaderText="Budget (In Crore)" ItemStyle-HorizontalAlign="Center"/>
                     </Columns>
                     <EditRowStyle BackColor="#999999" />
                     <EmptyDataTemplate>No Record Exist</EmptyDataTemplate>
                     <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>
               </ContentTemplate>
            </asp:UpdatePanel>
         </div>
      </form>
   </body>
</html>

Import the namespace:-

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Create sqlconnection:-

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());

Page load event of page:-

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ViewState["CurrentAlphabet"] = "ALL";
        GenerateAlphabetsAtoZ();
        BindGrid();
    }
}

Generate alphabets A to Z:-

Write a method to generate alphabets and bind to datalist.

private void GenerateAlphabetsAtoZ()
{
    try
    {
        List<ListItem> alphabets = new List<ListItem>();
        ListItem alphabet = new ListItem();
        alphabet.Value = "ALL";
        alphabet.Selected = alphabet.Value.Equals(ViewState["CurrentAlphabet"]);
        alphabets.Add(alphabet);
        for (int i = 65; i <= (65+25); i++)
        {
            alphabet = new ListItem();
            alphabet.Value = Char.ConvertFromUtf32(i);
            alphabets.Add(alphabet);
        }
        dtlalphabets.DataSource = alphabets;
        dtlalphabets.DataBind();
    }
    catch (Exception ex)
    { }
}

Bind Gridview:-

Write another method to bind the gridview.

public void BindGrid()
{
    SqlCommand cmd = new SqlCommand("Sp_FilterRecord", con);
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
    cmd.Parameters.AddWithValue("@filter", ViewState["CurrentAlphabet"]);
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adp.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    else
    {
        DataTable dtnew = new DataTable();
        GridView1.DataSource =dtnew;
        GridView1.DataBind();
    }
}

Event for linkbutton:-

Write the below given code for linkbutton which is placed in Datalist control.

protected void LinkButton1_Click(object sender, EventArgs e)
{
    LinkButton lnkAlphabet = (LinkButton)sender;
    ViewState["CurrentAlphabet"] = lnkAlphabet.Text;
    this.GenerateAlphabetsAtoZ();
    GridView1.PageIndex = 0;
    this.BindGrid();
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    BindGrid();
}

Finally write the below code on PageIndex event of gridview. 

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
     GridView1.PageIndex = e.NewPageIndex;
     BindGrid();
}
Umesh Patel

Related Posts

Next
« Prev Post
Previous
Next Post »

Thanks for comments.....