|
|
|
Asp.Net Alphabetical Paging Control with Stored Procedure |
|
Posted by
Moderator1
on
5/2/2009 10:50:19 AM
|
Category:
Asp.Net 2.0 |
|
|
Total Views :
13844 |
|
Adding to your Favorites....
|
|
|
 |
|
|
|
|
|
Introduction |
|
This article explains the concept of creating Alphabetical Paging Control in Asp.Net.
Three different methods of alphabetical paging are explained with free and source
code to download. |
|
|
|
Description |
Most web applications, which are displaying contents in a grid or list style, includes
the Paging Technique, otherwise, the Pager, to reduce the length of the list displayed
in a page. On technical aspect, it reduces the time taken for a page to load in
the Internet. From the user perspective, user got the freedom to look at any page,
by just click on the desired page numbers to view its content. This article is going
to show you, how to create an alphabetic paging control in Asp.Net applications
with three different methods. Method 1, is creating a pager control with alphabets
from A to Z, Method 2 is creating a pager control with the alphabets that is available in the database table, and method 3 is achieving both method 1 and 2 by using a
Stored Procedure.
|
|
|
Straight to the Job
Open an aspx page in your Visual Studio, add a DataList and a GridView control into
it. The DataList control is the Pager control, used to display the Page Numbers,
in our terms Paging Alphabets and the GridView control is used to display the list
of customers from the SQL Server database. First, in the Item Template section of
the DataList, add a LinkButton and name it as lnkbtnPaging. Bind the Text and CommandArgument
property of the LinkButton with the value as PageText and PageIndex respectively.
PageText and PageIndex are the two columns from the DataTable, which we are going
to create in the code-behind section to bind with this DataList. This DataTable
will have values used for paging. Note that both PageText and PageIndex will have
the same value. Then design the GridView control to display columns from the Customer
table. For the purpose of this article, we are going to display the Customer Name,
Age, Gender and Email Id along with their some date. So the aspx page is ready,
now we are going to move on the code-behind section. |
|
|
|
In this article, we are going to show you three different methods of creating a
Paging control. Lets go one by one.
Method 1: Create Pager with Alphabets from A-Z
The first method is to create an alphabetical paging control with alphabets from
A to Z. For this we have to loop through 65 to 90, and convert the value to its
equivalent character, which is from A to Z. Then store each value into a DataTable
row and bind it with the DataList control. The code for this method is given below
|
private void CreateAlphaPagings()
{
DataTable dt = new DataTable();
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageText");
if (this.ViewState["Paging"] == null)
{
for (int i = 65; i <= 90; i++)
{
DataRow dr = dt.NewRow();
dr[0] = Char.ConvertFromUtf32(i);
dr[1] = Char.ConvertFromUtf32(i);
dt.Rows.Add(dr);
}
DataRow drNew = dt.NewRow();
drNew["PageIndex"] = "All";
drNew["PageText"] = "All";
dt.Rows.Add(drNew);
this.ViewState["Paging"] = dt;
}
else
dt = (DataTable)this.ViewState["Paging"];
DataList1.DataSource = dt;
DataList1.DataBind();
}
|
|
|
|
The above method creates a DataTable with two columns PageIndex and PageText. We
loop through 65 to 90,
whose ASCII character equivalent is from A to Z. We are using
Char.ConvertFromUtf32 method to convert the number to alphabets. After conversion
we assign the value to the PageIndex and PageText columns of the DataTable. Sometimes,
the user needs to view all the data in a single page. To provide this option we
have added another DataRow with value “All”. We have also used a ViewState variable
in this method, in order to avoid the for-loop being executed every time this method
is called. So for the first time, when the page loads, the ViewState is empty or
null, thus execute the for-loop to create the rows for the DataTable, then the DataTable
created will be stored in the ViewState called “Paging”.
When this method is called
subsequently, the ViewState “Paging” it will supply the alphabetical paging DataTable
rows. Finally we bind the DataList is bind with the paging DataTable. |
|
|
Bind the GridView control
To display the Customer information in a GridView control, we create a method called
BindGrid, which takes a parameter called StartAlpha. This parameter is responsible
to filter the customer records before binding it with the GridView control. If the
parameter value is “All”, then all records will be displayed in the GridView control.
The BindGrid method is given below.
|
private void BindGrid(string StartAlpha)
{
string sql = "";
if (StartAlpha == "All")
sql = "Select * from Customers Order By Cus_Code Desc";
else
sql = "Select * from Customers Where Cus_Name Like '" + StartAlpha
+ "%' Order By Cus_Code Desc ";
SqlDataAdapter da = new SqlDataAdapter(sql, "YourConnectionString");
DataTable dtSelect = new DataTable();
da.Fill(dtSelect);
GridView1.DataSource = dtSelect;
GridView1.DataBind();
} |
Page Load Event
In the page load event, we have to display the pager in the DataList and the Customer
information in the GridView control.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.ViewState["SelectedText"] = "All";
CreateAlphaPagings();
BindGrid(this.ViewState["SelectedText"].ToString());
}
}
|
In the first line, we create a ViewState variable as SelectedText and assign value
as “All”. The second line calls the pager method, which will bind the DataList with
alphabets from A to Z. The last line calls the BindGrid method with ViewState variable
SelectedText as its parameter. So when the page loads the GridView will display
all the Customer records.
DataList ItemCommand Event
When the alphabets in the DataList is clicked, it has to filter the records in the
GridView control. Now look at the below code.
protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
LinkButton lbkbtnPaging = (LinkButton)e.CommandSource;
BindGrid(e.CommandArgument.ToString());
this.ViewState["SelectedText"] = e.CommandArgument.ToString();
CreateAlphaPagings();
}
|
In the ItemCommand of the DataList control, we try to achieve two tasks.
The first task is to enable the paging and second task is to disable the clicked
pager link. So in the first line, we find the LinkButton inside the DataList control,
in second line we call the BindGrid method, which is responsible to bind the Customer
Information with the GridView control based on the parameter passed. Then we reassign
the ViewState SelectedText with the new value of the selected pager LinkButton.
Last, we call the CreateAlphaPagings method to bind the DataList control.
The intention to call the CreateAlphaPagings method repeatedly is to bind the DataList
control to disable the clicked Pager LinkButton. This can be achieved by the writing
a small piece of code in the DataList ItemDataBound event.
DataList ItemDataBound Event
protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.AlternatingItem
|| e.Item.ItemType == ListItemType.Item)
{
if (this.ViewState["SelectedText"] != null)
{
LinkButton lbkbtnPaging = (LinkButton)e.Item.FindControl("lnkbtnPaging");
if (this.ViewState["SelectedText"].ToString() == lbkbtnPaging.Text)
lbkbtnPaging.Enabled = false;
}
}
} |
|
In the above block of code, we check the value in the ViewState and the
LinkButtons’s Text value inside the DataList control. If both matches, then we make
the LinkButton’s Enabled property as false. So this will disable the most recently
selected LinkButton.
Save all and press F5. Your browser will open with Alpabetical Paging Control and
the GridView will display all the Customer records. Click on the Pager link on any
alphabets. The GridView will display only records starts with the alphabet you clicked.
That’s it.
Method 2: Create Pager with Alphabets available in SQL Server Table
Instead of creating a pager will all alphabets from A to Z, it is better to create
a pager with alphabets that is available in the SQL Server Table, in our case in
Customer Table. This helps the user from not clicking on alphabets with no records.
To achieve this, we create a method that queries the Customer Table to return only
the first character from the Customer Name column. Rest everything is usual.
|
|
|
private void CreatePagingsWithFirstLetter()
{
DataTable dtSelect = new DataTable();
if (this.ViewState["Paging"] == null)
{
SqlDataAdapter da = new SqlDataAdapter("Select Distinct Upper(Left(Cus_Name,1))
[PageText], Upper(Left(Cus_Name,1)) [PageIndex] from Customers
Order By
PageText Asc", "YourConnectionString");
da.Fill(dtSelect);
DataRow drNew = dtSelect.NewRow();
drNew["PageIndex"] = "All";
drNew["PageText"] = "All";
dtSelect.Rows.Add(drNew);
this.ViewState["Paging"] = dtSelect;
}
else
dtSelect = (DataTable)this.ViewState["Paging"];
DataList1.DataSource = dtSelect;
DataList1.DataBind();
}
|
Method 3: Create Alphabetic Pager with Stored Procedure
We all knew the benefits of using Stored Procedures in the web applications. Not
only the performance of the application, but the code reusability has also to be
considered to create a Stored Procedure. So we have shown here the easy way to create
an Alphabetical Pager using Stored Procedure. Our Paging Stored Procedure is designed
in such a way to handle both the methods mentioned above, it will return either
alphabets from A to Z or it will return only the alphabets that is available in
Customer table. The structure of the Stored Procedure is given below
Create Proc Proc_Paging(@OptionId Int) As
Begin
Create Table #Paging (PageIndex VarChar(3), PageText VarChar(3))
If (@OptionId=1)
Begin
Declare @StartIndex Int, @EndIndex Int, @Incr Int
Select @StartIndex = Ascii('A')
Select @EndIndex = Ascii('Z')
Select @Incr = @StartIndex
While (@EndIndex >= @Incr )
Begin
Insert Into #Paging
Select Char(@Incr), Char(@Incr)
Select @Incr = @Incr+1
End
End
Else
begin
Insert Into #Paging
Select Distinct Upper(Left(Cus_Name,1)) [PageIndex],
Upper(Left(Cus_Name,1))[PageText]
from Customers Order By PageText Asc
End
Insert Into #Paging Values ('All', 'All')
Select * from #Paging
End
|
The Stored Procedure takes a parameter @OptionId to decide the type output. If the
parameter @OptionId is 1, it will return all alphabets from A to Z. Otherwise it
will return only the first letter of the Customer Name column in the Customer Table.
To create the pager, we have to bind the Stored Procedure with the DataList control
with the following code.
private void CreateAlphaPagings()
{
DataTable dt = new DataTable();
if (this.ViewState["Paging"] == null)
{
SqlDataAdapter da = new SqlDataAdapter("Proc_Paging", "YourConnectionString");
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@OptionId", SqlDbType.Int).Value
= 2;
da.Fill(dt);
this.ViewState["Paging"] = dt;
}
else
dt = (DataTable)this.ViewState["Paging"];
DataList1.DataSource = dt;
DataList1.DataBind();
} |
So, we have shown you three ways to create an Alphabetical Paging Control. We always
recommends the Method 3 as the best option, but it is upto you to decide based on
your requirements.
To view our sample Asp.Net Application on Alphabetical Paging Control, click here. |
|
You need to Login or Register to download Source Code.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|