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();
}
