GridView Alphabet Paging

Introduction:

GridView paging feature allow us to display fixed number of records on the page and browse to the next page of records. Although paging is a great feature but sometimes we need to view all the items alphabetically. The idea behind this article is to provide a user with a list of all the alphabets and when the user clicks on a certain alphabet then all the records starting with that alphabet will be populated in the GridView control.

Populating the GridView Control:

The first task is to populate the GridView control. I will be using the Northwind database in my article which, is installed by default for SQL SERVER 2000 and SQL SERVER 7 databases. The code below is used to populate the GridView control.

private void BindData()
{
string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT ProductID, ProductName FROM Products", myConnection);

DataSet ds = new DataSet();
ad.Fill(ds);

gvCategories.DataSource = ds;
gvCategories.DataBind();
}

Creating the Alphabetical List:

The next task is to create an alphabetical list and display it in the GridView control. The best place to display the list is the GridView footer. Let’s check out the code which is used to create the list.

protected void gvCategories_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{

TableCell cell = e.Row.Cells[0];
cell.ColumnSpan = 2;

for (int i = 65; i <= (65 + 25); i++)
{
LinkButton lb = new LinkButton();

lb.Text = Char.ConvertFromUtf32(i) + " ";
lb.CommandArgument = Char.ConvertFromUtf32(i);
lb.CommandName = "AlphaPaging";

cell.Controls.Add(lb);

}
}
}

The RowCreated event is used to create the list. In the event first I check for the footer row. Once, the footer row is found I run a loop from 65 to 92 and convert each number into the character representation. The number 65 stands for “A”, 66 for “B” and so on till 92 for “Z”. Inside the loop I created LinkButton and set the Text property to the alphabet. Finally, the control is added to the cell collection.

Fetching the Records Based on the Alphabet:

In the last section we created the alphabets and displayed them in the footer of the GridView control. The next task is to capture the event generated by the alphabets when we click on them and fetch the results based on the alphabet. The RowCommand event is fired whenever you click on any alphabet. Take a look at the RowCommand event below:

protected void gvCategories_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AlphaPaging"))
{
string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
string selectQuery = "SELECT ProductID, ProductName FROM Products WHERE ProductName LIKE '" + e.CommandArgument + "%'";

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter ad = new SqlDataAdapter(selectQuery,myConnection);

DataSet ds = new DataSet();
ad.Fill(ds);

gvCategories.DataSource = ds;
gvCategories.DataBind();
}
}

At first I check that if the CommandName is “AlphaPaging”. This check is made since RowCommand handles all the events generated inside the GridView control. Next, I used the T-SQL LIKE operator to fetch the results from the database and populate the results in the GridView control.