Introduction
This is a Good Example of Using Custom paging and fetching only some of records then fetching all records at one time. This solution also requires writing stored procedures that return pieces of data to be display on each page.
Creating Store Procedure
CREATE PROCEDURE FetchCustomer
(
@PageIndex INT,
@PageSize INT
)
AS
Begin
SET NOCOUNT ON;
Declare @TotalCount INT;
WITH GetData AS (
Select ROW_NUMBER() OVER (ORDER BY ID ASC) as Row,FName,LName,Phone
From Customer
)
SELECT FName,LName,Phone FROM GetData
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize;
Select @TotalCount=ROW_NUMBER() OVER (ORDER BY ID ASC)
From Customer;
Select @TotalCount as Total;
End
--Sample:-
--exec FetchCustomer '1','5'
Code for .aspx
<asp:GridView ID="GridView1" runat="server" PageSize="5" AllowSorting="true" AutoGenerateColumns="false" CellPadding="4"
ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#5D7B9D" Font-Bold="true" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="FName" HeaderText="FNathis" SortExpression="FName" />
<asp:BoundField DataField="LName" HeaderText="LNathis" SortExpression="LName" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<Columns>
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="true" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="true" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<asp:GridView>
<table>
<tr>
<td style="height: 21px">
<asp:Label ID="lblPage" CssClass="txtBd" Visible="false" runat="server">Page<asp:Label>
<asp:Label ID="lblCurrentPage" runat="server" CssClass="txtBd" Visible="false"><asp:Label>
<asp:Label ID="lblOf" CssClass="txtBd" Visible="false" runat="server">of<asp:Label>
<asp:Label ID="lblTotalPages" runat="server" CssClass="txtBd" Visible="false"><asp:Label>
<asp:ImageButton ID="imgFirst" runat="server" Visible="false" ToolTip="First Page" CommandName="First" OnCommand="Navigation_Click" ImageUrl="~/Images/pnt_first.gif"><asp:ImageButton>
<asp:ImageButton ID="imgPrev" runat="server" Visible="false" ToolTip="Previous Page" OnCommand="Navigation_Click" CommandName="Prev" ImageUrl="~/Images/pnt_prev.gif"><asp:ImageButton>
<asp:ImageButton ID="imgNext" runat="server" Visible="false" ToolTip="Next Page" CommandName="Next" OnCommand="Navigation_Click" ImageUrl="~/Images/pnt_next.gif"><asp:ImageButton>
<asp:ImageButton ID="imgLast" runat="server" Visible="false" ToolTip="Last Page" CommandName="Last"OnCommand="Navigation_Click" ImageUrl="~/Images/pnt_last.gif"><asp:ImageButton>
<td>
<tr>
<table>
Explanation of .aspx
GridView code is simple it will view records, Navigation_Click event where it will perform opertion for On Cilck of First,Next,Prev and Last image buttons.
Explanation of Code Behind
double intTotalRecords;
double intTotalPages = 1.0;
int intCurrentPageNumber;
intTotalRecords will get the Total No of records, intTotalPages will get the Total No of Pages for Navigation and intCurrentPageNumber will give the Current Page Number of clicking on Image Button.
protected void Page_Load(object sender, EventArgs e)
{
intCurrentPageNumber = 1;
if (!IsPostBack)
{
BindGrid(GridView1.PageIndex + 1, GridView1.PageSize);
lblCurrentPage.Text = "1";
}
}
In Page Load, first time I have to put value of IntCurrentPageNumber as 1, PageIndex in grid for first time it will be 0, So I has to increment by 1.
private void BindGrid(int PageIndex,int PageSize)
{
Try
{
SqlConnection conn = null;
SqlCommand cmd = null;
DataSet ds = new DataSet();
String strConnection, strSQL;
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
conn = new SqlConnection(strConnection);
conn.Open();
strSQL = "FetchCustomer";
cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageIndex", SqlDbType.Int);
cmd.Parameters["@PageIndex"].Value = PageIndex;
cmd.Parameters.Add("@PageSize", SqlDbType.Int);
cmd.Parameters["@PageSize"].Value = PageSize;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
int count = Convert.ToInt16(ds.Tables[1].Rows[0]["Total"].ToString());
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
BindPaging(ds.Tables[0], count);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
private void BindPaging(DataTable dtCustomer,int Count)
{
try
{
if (dtCustomer.Rows.Count == 0)
{
this.GridView1.Visible = false;
this.imgFirst.Visible = false;
this.imgLast.Visible = false;
this.imgNext.Visible = false;
this.imgPrev.Visible = false;
this.lblPage.Visible = false;
this.lblCurrentPage.Visible = false;
this.lblOf.Visible = false;
this.lblTotalPages.Visible = false;
}
else
{
if (Count <>
{
this.GridView1.Visible = true;
this.imgFirst.Visible = false;
this.imgLast.Visible = false;
this.imgNext.Visible = false;
this.imgPrev.Visible = false;
this.lblPage.Visible = false;
this.lblCurrentPage.Visible = false;
this.lblOf.Visible = false;
this.lblTotalPages.Visible = false;
}
else
{
this.GridView1.Visible = true;
this.lblPage.Visible = true;
this.lblCurrentPage.Visible = true;
this.lblOf.Visible = true;
this.lblTotalPages.Visible = true;
this.imgFirst.Visible = true;
this.imgLast.Visible = true;
this.imgNext.Visible = true;
this.imgPrev.Visible = true;
}
}
if (!IsPostBack)
{
intTotalRecords = Count;
intTotalPages = intTotalRecords / GridView1.PageSize;
lblTotalPages.Text = System.Math.Ceiling(intTotalPages).ToString();
}
else
{
intTotalRecords = Count;
intTotalPages = intTotalRecords / GridView1.PageSize;
lblTotalPages.Text = System.Math.Ceiling(intTotalPages).ToString();
intTotalPages = double.Parse(lblTotalPages.Text);
}
if (intCurrentPageNumber == 1)
{
this.imgFirst.Visible = false;
imgPrev.Visible = false;
if (intTotalPages > 1)
imgNext.Visible = true;
else
imgNext.Visible = false;
}
else
{
imgPrev.Visible = true;
if (intCurrentPageNumber == intTotalPages)
{
imgLast.Visible = false;
imgNext.Visible = false;
}
else
imgNext.Visible = true;
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
BindGrid() method will Bind Grid according to its parameter pass into as PageIndex and PageSize.
BindPaging() method will display Image buttons, True or false depend on its click.
protected void Navigation_Click(object sender, CommandEventArgs e)
{
try
{
if (GridView1.Rows.Count > 0)
{
switch (e.CommandName)
{
case "First":
intCurrentPageNumber = 1;
GridView1.PageIndex = intCurrentPageNumber;
lblCurrentPage.Text = intCurrentPageNumber.ToString();
break;
case "Last":
intCurrentPageNumber = Convert.ToInt32(lblTotalPages.Text);
GridView1.PageIndex = intCurrentPageNumber;
lblCurrentPage.Text = intCurrentPageNumber.ToString();
break;
case "Next":
if (GridView1.PageIndex <>
{
intCurrentPageNumber = Convert.ToInt32(lblCurrentPage.Text) + 1;
GridView1.PageIndex = intCurrentPageNumber;
lblCurrentPage.Text = intCurrentPageNumber.ToString();
}
break;
case "Prev":
if (GridView1.PageIndex >= 0)
{
intCurrentPageNumber = Convert.ToInt32(lblCurrentPage.Text) - 1;
GridView1.PageIndex = intCurrentPageNumber;
lblCurrentPage.Text = intCurrentPageNumber.ToString();
}
break;
}
BindGrid(GridView1.PageIndex, GridView1.PageSize);
GridView1.PageIndex = 0;
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Navigation Click event will fire when click on Image Buttons. This will also Increment or Decrement Page Index. After that it will bind to grid
