Filter the record using Alphabets pager in asp.net - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

Filter the record using Alphabets pager in asp.net

In this tutorial I am going to explain how to filter the record using Alphabets pager in asp.net


Filter the record using Alphabets pager in asp.net

Description:
To implement this functionality I am using datalist control (alphabets pager) and gridview control (to show the record).

Implementation:
I have created table Tb_Movie and dummy data.
Id
int
Name
varchar(50)
Genre
varchar(50)
Budget
int

Create a store 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 markup:
<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:
C# code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.net Code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Create sqlconnection
C# code:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());

VB.net Code:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ToString())

Page load event of page
C# code:
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ViewState["CurrentAlphabet"] = "ALL";
            GenerateAlphabetsAtoZ();
            BindGrid();
        }
    }

VB.net Code:
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ViewState("CurrentAlphabet") = "ALL"
            GenerateAlphabetsAtoZ()
            BindGrid()
        End If
    End Sub

Generate alphabets A to Z
Write a method to generate alphabets and bind to datalist.
C# code:
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)
        { }
    }

VB.net Code:
  Private Sub GenerateAlphabetsAtoZ()
        Try
            Dim alphabets As New List(Of ListItem)()
            Dim alphabet As New ListItem()
            alphabet.Value = "ALL"
            alphabet.Selected = alphabet.Value.Equals(ViewState("CurrentAlphabet"))
            alphabets.Add(alphabet)
            For i As Integer = 65 To 90
                alphabet = New ListItem()
                alphabet.Value = [Char].ConvertFromUtf32(i)
                alphabets.Add(alphabet)
            Next
            dtlalphabets.DataSource = alphabets
            dtlalphabets.DataBind()
        Catch ex As Exception
        End Try
    End Sub

Bind Gridview
Write another method to bind the gridview.
C# code:
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();
        }

    }

VB.net Code:
Public Sub BindGrid()
        Dim cmd As New SqlCommand("Sp_FilterRecord", con)
        cmd.CommandType = CommandType.StoredProcedure
        con.Open()
        cmd.Parameters.AddWithValue("@filter", ViewState("CurrentAlphabet"))
        Dim adp As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        adp.Fill(dt)
        If dt.Rows.Count > 0 Then
            GridView1.DataSource = dt
            GridView1.DataBind()
        Else
            Dim dtnew As New DataTable()
            GridView1.DataSource = dtnew
            GridView1.DataBind()
        End If
    End Sub

Event for linkbutton
Write the below given code for linkbutton which is placed in Datalist control.
C# Code:
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();
    }

VB.net Code:
Protected Sub LinkButton1_Click(sender As Object, e As EventArgs)
        Dim lnkAlphabet As LinkButton = DirectCast(sender, LinkButton)
        ViewState("CurrentAlphabet") = lnkAlphabet.Text
        Me.GenerateAlphabetsAtoZ()
        GridView1.PageIndex = 0
        Me.BindGrid()
    End Sub
    Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        GridView1.PageIndex = e.NewPageIndex
        BindGrid()
    End Sub

Finally write the below code on PageIndex event of gridview.
C# Code:
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindGrid();
    }

VB.net Code:
    Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        GridView1.PageIndex = e.NewPageIndex
        BindGrid()
    End Sub


We have done it. 

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon