Saturday, August 1, 2009

Custom Pager for GridView Control

When I have posted my post on the paging of the record on the sql server side, using the store procedure , I then decided to use that paged data in one of the web application. So I develop a small application to display the page data on the grid view control. During the development of the small application I have design the custom paging for the grid view control and I want to share this custom paging with all of you , So lets start with the small application.
For this example I have grid view control and I have set the visible property of the Pager to false, so the pager of the grid view is not shown to the user. Next is the custom pager for the grid view control which consist of the four image button First, last, next and previous and one label to show the current page number.

For this example code I have modified the store procedure used in the my post, so that I can get the number of records from the store procedure and display total number of page to user and also to perform action when user reaches on the last page.
Here is the main function which is used to control all the databinding and also control the paging as well, this code is taken from the GridBinding function. In the first statement of the function is the declaration of the SqlConnection object and passing the connection string to the connection object,I have passed the connection string directly to the SqlConnection object you can place your connection string to web.config on in a common place where all of you web application can access the connection string. Next is the declaration of the SqCommand object, the SqlCommand object is declared with zero (0) parameter constructor and then following are the properties set for the SqlCommand object.
  1. Connection Property
  2. CommandText
  3. CommandType
As I am using store procedure here for this example, so I have set the CommandText to the name of the store procedure and assign CommandType property the StoredProcedure enum constant.
SqlConnection getCustomerConnection = new SqlConnection("Server=ServerName; DataBase=northwind; uid=username; pwd=password");
getCustomerConnection.Open();

SqlCommand getCustomerCommand = new SqlCommand();
getCustomerCommand.Connection = getCustomerConnection;
getCustomerCommand.CommandText = "getCustomer";
getCustomerCommand.CommandType = CommandType.StoredProcedure;

getCustomerCommand.Parameters.AddWithValue("@PageNumber", PageNumber);
getCustomerCommand.Parameters.AddWithValue("@PageSize", grdvCustomer.PageSize);
SqlParameter totalRecordParameter = new SqlParameter("@TotalRecord", SqlDbType.Int);
totalRecordParameter.Direction = ParameterDirection.Output;
getCustomerCommand.Parameters.Add(totalRecordParameter);

SqlDataAdapter getCustomerDataAdapter = new SqlDataAdapter();
getCustomerDataAdapter.SelectCommand = getCustomerCommand;
DataSet dtsCustomer = new DataSet();
getCustomerDataAdapter.Fill(dtsCustomer, "Customer");

grdvCustomer.DataSource = dtsCustomer.Tables["Customer"];
grdvCustomer.DataBind();
TotalPages = Convert.ToInt32(getCustomerCommand.Parameters["@TotalRecord"].Value.ToString());
EnableDistableButtons();
After assigning the Connection, CommandText and CommandType properties of the SqlCommand Object, next is to add the parameter and their values, The first parameter is the @PageNumer, which is used to get the records for the value passed, Second parameter is the @PageSize , in this case I am passing the value of the PageSize property of the grid view control. And the last parameter which is @TotalRecord , this parameter has direction to output and is used to calculate the number of pages. Next is the declaration of the SqlDataAdatper object which is used to fill the DataSet, after the declaration and initialization of the sqlDataAdapter the SelectCommand of the SqlDataAdapter is assign the SqlCommand object which is declare before. After that DataSet object is declared and initialized and in the next statement the dataset is filled and "Customer" is passed as table name. After the fill of the dataset, DataSource property of the grid view control is assign the Customer table of the dataset.
After databind of the grid view control is the assignment of the total records to the TotalPage property and here is the code of the TotalPage property. In the Property it is only used to calculate the total pages by dividing the PageSize propery of the grid view control. Here you can see if the reminder of the TotalRecord and PageSize is not equal to zero(0) then it will increament the page size by one.
get
{
int intTotalPages = 1;
if (ViewState["TotalPages"] != null)
int.TryParse(ViewState["TotalPages"].ToString(), out intTotalPages);
return intTotalPages;
}
set
{
int intTotalPage = value /grdvCustomer.PageSize ;
if (value % grdvCustomer.PageSize != 0)
intTotalPage++;
ViewState.Add("TotalPages", intTotalPage);
lblShowingPage.Text = PageNumber + " of " + intTotalPage;
}
After the TotalPages property call is the function which is used to enable, disabled the first, previous, next and last image button depending on the value of the PageNumber property and the TotalRecords property. The code behind the first, previous, next and the last image button is simple , where the PageNumber property is changed and the GridBinding function is called.

You can download the source code from here.

Note: In the source I have place the script of the GetCustomer store procedure place execute this script in the nortwind database. And replace the alter key work to the create key work so that new store procedure will be created.

All and any comments / bugs / suggestions are welcomed!


No comments: