AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 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 : 70709
Add to my favorites
Email to friend
  
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.
Viewer's Comments
Posted by Mahesh tare on 7/7/2009 2:06:44 AM
Very Good...Nice one
 
Posted by Jeff on 7/22/2009 10:21:27 AM
Thank you.. It's just what I've been looking for. Is it possible to add a hyperlink to the Customer Name that would link to page with detailed customer information? (Master/Detail)
 
Posted by fasiul hasan on 7/29/2009 7:41:48 AM
nice, it is avery good example
 
Posted by abin on 8/4/2009 6:16:30 AM
it is avery good example
 
Posted by abin on 8/4/2009 6:16:45 AM
cbcv
 
Posted by raj singla on 8/12/2009 9:07:11 AM
very nice coding
 
Posted by Farzana on 12/7/2009 9:26:35 PM
This is a very helpful code...but i am having a problem with this...kindly help me solve the problem. I followed exactly the same steps but instead of displaying A|B|C.... its displays PageIndex|PageIndex|PageIndex....so on...when i bind the linkbutton properties with the PageIndex and PageText it gives error "PageIndex not foun found in the context. Thankyou"
 
Posted by harsha on 2/17/2010 3:46:57 AM
'Excellent'
 
Posted by Tharani on 2/17/2010 3:49:32 AM
It is very useful to me .
 
Posted by Mr. Masud Parvez on 8/22/2010 10:54:33 PM
Thanks, Is it very helpful.
 
Posted by dss on 11/20/2010 4:58:09 PM
Thanks for the code. Question: How can I page through items when there are multiple items per page? When I tried your code, it worked . . . but only when the number of items to display was less than the page limit. For instance, I am unable to page through multiple 'A's . How can I page though the additional items?
 
Posted by Osmar Medina on 12/9/2010 5:28:50 PM
Excellent, works inmediatly Thanks
 
Posted by George Leite on 1/22/2011 1:41:29 PM
Thank you very much. Tried other ideas, but this was the best by far.
 
Posted by Jay How on 5/16/2011 4:01:35 PM
Thanks so much for this. I am having an issue. Basically I had to take your code and convert to VB for the code behind. I dont get any errors but get a blank page when I build and re-run the page.
 
Posted by jay ho on 5/18/2011 8:27:12 AM
Ok, I got it working, again so many thanks, this is exactly what I needed. One question. Is there a way for me to combine this with the ability to sort columns like if I had bound the datasource directly to the GridView?
 
Posted by NoIdea on 6/14/2011 11:33:51 PM
Thank u so much....... Excellent code.............
 
Posted by abc on 6/18/2011 8:07:13 AM
Nice one
 
Posted by Vikas on 7/4/2011 1:31:46 AM
Awesum....*
 
Posted by Stephen on 8/11/2011 7:41:40 AM
How colud I use the stored procedure provided in mysql?
 
Posted by Sudha on 9/8/2011 6:01:59 AM
Great work thanks :)
 
Posted by dhruvin bhatt on 8/28/2012 8:12:41 AM
greate work!..
 
 Rating & Comments
A word 'Excellent' means lot to the author of this article. You can give comments about this article but not the author.
Rate this Article:
Name:
Email Id:  
We never display your email id anywhere.
Comment/Question: Max. 500 letters
Seventy plus Three Equals To
 
Sponsored by
SearchSAP
The PHP Anthology: 101 Essential Tips, Tricks & Hacks, 2nd Edition - Free 207 Page Preview!
Simply SQL - Free 111 Page Preview!