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!

Paging In Sql Server 2000 Store Procedure

You have heard or at least done Paging of the gridview control on the server side. When someone talk about the paging of the data on the sql server side I was surprise how to do the paging of the data on sql server side on in the store procedure which will return data. Then I have start search on doing paging of data on the store procedure side.

Here is piece of code which is used to create the store procedure and the name of the store procedure is GetCustomer. The store procedure will take two parameter one the PageNumber and the second one the PageSize. At the beginning of the store procedure is the declaration of the variable which are used in the store procedure. First is the StartingRow and the EndingRow variable which are used to return the pageSize records by comparing the StartingRow and The EndingRow with the RowNumber of the return result set. Next variable is the TotalRecods which is used to save the total of the records and used to calculate the row number as I have discuss in my last Post, where I have discuss how to calculate row number in sql server 2000 in detail.

ALTER  PROCEDURE [GetCustomer]
 @PageNumber  INT,
@PageSize   INT
AS
Declare @StartingRow  INT,
@EndingRow   INT,
 @TotalRecords INT

Declare @tblTemporary TABLE
(
RowNumber   INT,
CustomerID  VARCHAR(10),
CompanyName NVARCHAR(200),
ContactName NVARCHAR(200),
Country     NVARCHAR(50)
)

Set @TotalRecords =(Select Count(*) from Customers)
Set @EndingRow = @PageNumber * @PageSize
Set @StartingRow = @EndingRow - @PageSize

Insert INTO @tblTemporary
Select @TotalRecords -(Select Count(*) from Customers WHERE Customer.CustomerID < CustomerID) AS RowNumber,
CustomerID,CompanyName,ContactName,Country from Customers AS Customer

Select * from @tblTemporary
Where RowNumber> @StartingRow AND RowNumber <= @EndingRow   

Next is the declaration of the table to save the return record set. Here I have included the required columns which I need to display to user plus the addition column RowNumber, which is used to filter the records based on the StartingRow and EndingRow. After the declaration of the variables next is the assignment statements. In the first of the assignment statement, TotalRow is assign value by selecting the Count(*) from the customer table.Next is to calculate the EndingRow value by multipling the PageNumber with PageSize and at the last assignment statement, calculating the StartingRow by substracting the PageSize from the EndingRow variable.
In the next statement, calculating the Row Number and required columns from the customer table and inserting the returned record in the @tblTemporary table. At the end of the store procedure selecting the records from the tblTemporary table and place the where clause so that the RowNumber of the tblTemporary table will be in between the StartingRow and EndingRow.

Note: Replace the alter keyword at the start of the store procedure with the create keyword so that new store procedure will be created.

The database for this store procedure is the northwind and table is the customer table. After creating the store procedure you can run these commands to see the result

exec getCustomer 1,25
exec getCustomer 2,25

All and any comments / bugs / suggestions are welcomed!