Custom paging in Data Grid using C#.net
Pagination is a good technique used to improve the performance of the application and usability. But it’s always not suitable to use the built-in pagination approach of Data Grid, in that case we may require to write our own custom paging. In this article i will explain implementing custom paging using Link Buttons, in this approach we will write our paging logic in the stored procedure to improve the performance of the application.
My example page contains a just a search button, when user click on that button he will be shown results in grid along with our custom page bar. So my design will contain one asp button & a grid & page bar.
First we will start with design of the page then we will go into Server side coding ( Through out this example i will be using C#.Net) and finally stored procedure logic for pagination.
User Interface ( UI) Design
<form id="cPaging" runat="server">
<asp:Button ID="srchButton" Text="BindData"runat="server"
OnClick="BindData" />
<div id="displayResults" runat="server">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<asp:Label ID="lblCurrentPage" runat="server" Visible="false"></asp:Label>
<div class="indexerStyle" id="pageIndexer" runat="server">
<asp:button id="btnPrev" runat="server" font-bold="True" text=" < " oncommand="Nav_OnClick" commandname="Prev" />
<asp:linkbutton id="lnkbtn0" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn1" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn2" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn3" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn4" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn5" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn6" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn7" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:linkbutton id="lnkbtn8" runat="server" oncommand="Nav_OnClick" commandname="Page" forecolor="black" />
<asp:button id="btnNext" runat="server" font-bold="True" text=" > " oncommand="Nav_OnClick" commandname="Next" />
</div>
</div>
</form>
There is nothing great in the above code, i have a grid and used two buttons and nine link buttons in my paging bar, you can have your own number of buttons depending up on the number of pages you want to show in page bar. My custom page bar UI will look some thing like this.
Now lets get into server side coding.The following variables are declared Global in my Class.
public static int startPage = 1;
//This value is always 1, as page bar index always start with 1.
public static int endPage = 9; //In my case i have 8 link buttons so have 9 as end page, so if you want to have n link buttons by default declare it as n+1
public static int gridPageSize = 5; //Number of records you want to show in grid at a time.
Lets see what is there in page load function of my class.
if (!Page.IsPostBack)
{
displayResults.Style.Add("Display", "none"); //By default your grid section will be disabled.
}
By default our grid will not be shown, we just show them only ‘srchButton’. So when user click on that button we will get the results. So lets write our Button click function.
protected void BindData(object sender, EventArgs e)
{
//Write your code for Button click
getData(gridPageSize,1); //By default first page will be binded to grid.
}
private void getData(int pageSize,int pageIndex)
{
try
{
string connectString = ConnectionStrings["NothWindconnectionString"].ConnectionString;
//System.configuration.Configurationmanager
//Prepare Sql connection
SqlConnection con = new SqlConnection(connectString);
con.Open();
//Prepare Sql Command
SqlCommand cmd = new SqlCommand("dbo.customPagingSp", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlPar1 = cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
sqlPar1.Value = pageIndex;
SqlParameter sqlPar2 = cmd.Parameters.Add("@PageSize", SqlDbType.Int);
sqlPar2.Value = pageSize;
//Use Data Adapter to get results
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
DataToGrid(ds,pageIndex);
}
catch (Exception ex)
{
throw ex;
}
}
In the ’srchButton’ on click function i am calling another function getData, which will take the size of the gird & index of the page which need to be shown to user. By default when user click on button it will be always one. In the getData function we have our logic to get records from data base. I am sending two parameters to the stored procedure pageIndex & pageSize to do our pagination logic in the stored procedure. Please add the following connection string to your web.config file.
<add name="NothWindconnectionString" connectionString="Data Source=.\SQLEXPRESS; DataBase = NorthWind; Integrated Security=True;user id=sa; password = sa" providerName="System.Data.SqlClient" />
In the above function we have internally called another function ‘DataToGrid’,
private void DataToGrid(DataSet ds,int currentPage)
{
GridView1.DataSource = ds;
GridView1.DataBind();
int totalCount = Int32.Parse(ds.Tables[0].Rows[0]["TotalCount"].ToString());
changeControlsText(totalCount, currentPage);
displayResults.Style.Add("Display", "inline");
}
All the data binding is happening in the above function, in the results data set we are sending ‘TotalCount’ as a column which contains the total number of records matched our criteria (using this value & grid page size we can know total number of pages). Internally again we are calling one more function ‘changeControlsText’ which takes totalCount & currentPage as parameters and will render our custom Page Bar. After rendering finished we are enabling grid & page index bar.
private void changeControlsText(int totalCount, int currentPage)
{
int remainder = 0;
int totalPages = 0;
Math.DivRem(totalCount, gridPageSize, out remainder);
if (remainder == 0)
totalPages = totalCount / gridPageSize;
else
totalPages = (totalCount / gridPageSize) + 1;
//Using the total count & gridPage size we have found out the total number of pages
lblCurrentPage.Text = currentPage.ToString();
//This label is hidden label, which will be having the current page user is looking at.
EnableLinkButtons(currentPage, totalPages);
//Call this function to Enable or disable appropriate Link Buttons.
}
The above function calculates the number of pages we got based on the total count & grid page size. Here we are using a hidden label to store the current page user is looking at. By default this value be 1 ( as default page is 1 always).
private void EnableLinkButtons(int currentPage, int totalPages)
{
//If we got only one page, then disable previous & next button.
if (totalPages == 1)
{
btnPrev.Enabled = false;
btnNext.Enabled = false;
}
else
{
//we got more than one page, then how the button should show?
if (currentPage == 1) //if the current page is '1' then Previous button is disable & Next button is enabled.
{
btnPrev.Enabled = false;
btnNext.Enabled = true;
}
else if (currentPage == totalPages) //If we are on the last page then disable Next button and enable Prev button.
{
btnPrev.Enabled = true;
btnNext.Enabled = false;
}
else //Neither of the above then enable both Prev and Next buttons.
{
btnPrev.Enabled = true;
btnNext.Enabled = true;
}
}
//Now we are done with Next & prev buttons, now render our pager buttons.
//Set the startpage of indexer
if (currentPage > endPage - 1) //If user click on last button of your page indexer Then what show we do?
{
if (totalPages - currentPage < 4) //here 4 is total buttons/2 ( middle)
{
startPage = totalPages - 8; // instead of 8 you can customize on your own.
}
else
startPage = currentPage - 4; //4 is middle number
}
else if (currentPage < startPage + 1)//What if user clicks on first button of page indexer?
{
startPage = currentPage - 4;
}
if (startPage < 1) //if some how the logic above goes to negative then make sure your start page is always 1.
startPage = 1;
string controlID = "";
int value;
for (int i = 0; i < 9; i++)
{
controlID = "lnkbtn" + i.ToString();//Get your link button id.
value = startPage + i;//Assign a value to it.
if (value <= totalPages) //so Enable all the pages till you meet cross 8 or you reached max pages.
{
makeVisibleButtons(controlID, value.ToString(), currentPage, true);
endPage = value;
}
else
makeVisibleButtons(controlID, value.ToString(), currentPage, false);
}
}
Function ‘EnableLinkButtons’ Contains the most of our logic for page bar, In my example i have implemented using the following requirements.
- My custom page bar ‘Previous’ & ‘Next’ Button should be disabled if we got only one page.
- My custom page bar ‘Previous’ Button is disabled if user is at first page
- My custom page bar ‘Next’ Button is disabled if user is at last page.
- My custom page bar should be user friendly like if we got 20 pages in our results, by default we will show him 9 pages, when user click on 9th page we should show next few pages in the page bar.
In my above code i have used few hard coded numbers, you may change those numbers to suite your requirements. So the main agenda of the above logic is finding out the start page number & last page number to be shown in custom page bar. once we got those number enable those till we reach total pages count or 9 in our case.
private void makeVisibleButtons(string ControlID, string value, int currentPage, bool vsble)
{
LinkButton linkBtn = (LinkButton)FindControl(ControlID);
linkBtn.Visible = vsble;
linkBtn.ForeColor = System.Drawing.Color.Black;
linkBtn.Text = value;
if (currentPage == Int32.Parse(value))
{
linkBtn.ForeColor = System.Drawing.Color.Red;
}
}
This function will enable or disable link buttons based on the parameters it got.
We are done with the most of the logic of custom page bar, now i will finish this with Onclick function for link buttons.
protected void Nav_OnClick(object sender, CommandEventArgs e)
{
int _currentPageNumber = 1;
switch (e.CommandName)
{
case "Prev":
_currentPageNumber = Int32.Parse(lblCurrentPage.Text) - 1;
break;
case "Page":
LinkButton lnkbutton = (LinkButton)sender;
_currentPageNumber = Int32.Parse(lnkbutton.Text);
break;
case "Next":
_currentPageNumber = Int32.Parse(lblCurrentPage.Text) + 1;
break;
}
getData(gridPageSize,_currentPageNumber);
}
From UI we are sending commandName as either ‘Page’ or ‘Previous’ or ‘Next’ for respective buttons. Based on the command name we have to identify which page user requesting, then call the function getData by passing pagesize & user requested page page.
For most of the code i have written in line comments, please go through them for better understanding. So we are done with server side coding of the page indexer. If you want to know how out stored procedure should like then read my previous post Data Pagination in the stored Procedure. If you require more help in designing your custom page indexer leave a comment, i would be very happy to help you.