Filter the record using Alphabets pager in asp.net | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF 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. 

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget