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

In the previous article I have explained how to Bind comma separated values tolistbox in asp.net in ASP.Net using C# and VB.net, how to get comma separatedvalues (data) from database and bind to checkboxlist in ASP.Net and how toinsert multiple selected items of Listbox to database as comma separated inASP.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)

)

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

HTML markup:

<style>

.linkbtn

{

padding:5px;

background:#000;

color:#fff;

text-decoration:none;

border: 2px solid #2196F3;

}

</style></head>

<body>

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">

<asp:DataList ID="dtlalphabets" runat="server" RepeatDirection="Horizontal">

<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">

<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>

<EmptyDataTemplate>No Record Exist</EmptyDataTemplate>

</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)

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.

Sunday, March 6, 2016

Filter the record using Alphabets pager in asp.net

No comments:

Post a Comment

Popular Posts

Labels