Custom Paging with Numbers

Custom Paging with Numbers

.aspx File

<div>
PageSize:
<asp:DropDownList ID=”ddlPageSize” runat=”server” AutoPostBack=”true” OnSelectedIndexChanged=”PageSize_Changed”>
<asp:ListItem Text=”10″ Value=”10″ />
<asp:ListItem Text=”25″ Value=”25″ />
<asp:ListItem Text=”50″ Value=”50″ />
<asp:ListItem Text=”75″ Value=”75″ />
</asp:DropDownList>
<hr />
<asp:GridView ID=”gvCustomerDetails” runat=”server”>
</asp:GridView>
<br />
<asp:Button ID=”btnprev” Text=”<<” OnClick=”MovetoPrev” runat=”server” />
<asp:Repeater ID=”rptPage” runat=”server”>
<ItemTemplate>
<asp:LinkButton ID=”lnkPage” runat=”server” Text='<%#Eval(“Text”)%>’ CommandArgument='<%# Eval(“Value”) %>’
OnClick=”GettingPageIndex”></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
<asp:Button ID=”btnnext” Text=”>>” OnClick=”MovetoNext” runat=”server” />
</div>

.Cs
#region Using Namespaces
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CustomerDetail;
using System.Web.UI.WebControls;
#endregion

public partial class GridViewPagingWithNumbers : System.Web.UI.Page
{
#region Private Constants
private const string SQL_CONNECTION = “Data Source=DBSRV;Initial Catalog=Shopping;User ID=sa;Password=sa”;
private const string SQL_SPNAME = “[sproc_GetCustomersDetails]”;
#endregion

#region Public Variables
public static int PAGE_NUMBER = 1;
public int RECORD_COUNT;
public static int PAGE_SIZE = 10;
#endregion

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
LoadCustomerDetails();
}
}

#region Load Customer Records
private void LoadCustomerDetails()
{
var CustomerDetails = new List<CustomerDetails>();
#region Fetching and Loading the Customer Details
try
{
using (var sqlConn = new SqlConnection(SQL_CONNECTION))
{
using (var sqlCmd = new SqlCommand(SQL_SPNAME, sqlConn))
{
sqlCmd.Parameters.AddWithValue(“@PageNo”, PAGE_NUMBER);
sqlCmd.Parameters.AddWithValue(“@PageSize”, PAGE_SIZE);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlConn.Open();
using (var sqlDataReader = sqlCmd.ExecuteReader())
{
while (sqlDataReader.Read())
{
CustomerDetails.Add(new CustomerDetails(Convert.ToInt32(sqlDataReader[“ID”]), Convert.ToString(sqlDataReader[“CustomerName”]),
Convert.ToString(sqlDataReader[“EmailID”]), Convert.ToString(sqlDataReader[“PhoneNo”])
));
}
sqlDataReader.NextResult();
sqlDataReader.Read();
RECORD_COUNT = Convert.ToInt32(sqlDataReader[“TotalRec”]);
}
}
gvCustomerDetails.DataSource = CustomerDetails.AsReadOnly();
gvCustomerDetails.DataBind();
this.CreatePages(RECORD_COUNT, PAGE_SIZE);
}
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
#endregion

private void  CreatePages(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
pages.Add(new ListItem(“First”, “1”));
if (PAGE_NUMBER >= 10 && (PAGE_NUMBER) <= pageCount )
{
for (int loop_Index = PAGE_NUMBER-5; loop_Index < PAGE_NUMBER + 5; loop_Index++)
{
pages.Add(new ListItem(loop_Index.ToString(), loop_Index.ToString()));
}
}
else
{
for (int loop_Index = 1; loop_Index <= 10; loop_Index++)
{
pages.Add(new ListItem(loop_Index.ToString(), loop_Index.ToString()));
}

}

pages.Add(new ListItem(“Last”, pageCount.ToString()));
}
rptPage.DataSource = pages;
rptPage.DataBind();
}
protected void PageSize_Changed(object sender, EventArgs e)
{
PAGE_SIZE = Convert.ToInt32(ddlPageSize.SelectedValue);
PAGE_NUMBER = 1;
LoadCustomerDetails();
}
protected void GettingPageIndex(object sender, EventArgs e)
{
PAGE_NUMBER = int.Parse((sender as LinkButton).CommandArgument);
LoadCustomerDetails();
}
protected void MovetoNext(object sender, EventArgs e)
{
PAGE_NUMBER++;
LoadCustomerDetails();
}
protected void MovetoPrev(object sender, EventArgs e)
{
PAGE_NUMBER–;
LoadCustomerDetails();
}
}

CustomerDetails.cs

#region Using Namespaces
using System;
using System.Data;
using System.Configuration;
using System.Web;
#endregion

namespace CustomerDetail
{
public class ConnectionDetails
{
#region Public Method
public string ConnectionInfo
{
get
{
return ConfigurationSettings.AppSettings[“myConnectionString”].ToString();
}
}
#endregion
}

public class CustomerDetails
{
#region Private Members
private readonly int c_id;
private readonly string c_email;
private readonly string c_phoneNumber;
private readonly string c_name;
#endregion

#region Public Members
public int ID
{
get { return c_id; }
}
public string Name
{
get { return c_name; }
}
public string Email
{
get { return c_email; }
}
public string PhoneNumber
{
get { return c_phoneNumber; }
}
#endregion

#region Public Const.
public CustomerDetails(int pID, string pName, string pEmail, string pPhoneNumber)
{
c_id = pID;
c_email = pEmail;
c_name = pName;
c_phoneNumber = pPhoneNumber;
}
#endregion

}

}

Stored Procedure

ALTER PROCEDURE [dbo].[sproc_GetCustomersDetails]
@PageNo        INT,
@PageSize    INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @StartIndex INT, @EndIndex INT, @TotalRec INT, @TotalPages INT;

SELECT @TotalRec = COUNT(1) FROM [tCustomer] WITH (NOLOCK)
WHERE [CustomerName] IS NOT NULL;

IF @PageNo <= 0 SET @PageNo = 1;
IF @PageSize <= 0 SET @PageSize = 1;
IF @PageSize > @TotalRec SET @PageSize = @TotalRec;

SET @TotalPages = CEILING(@TotalRec * 1.0 / @PageSize);
IF (@PageNo > @TotalPages) SET @PageNo = @TotalPages;

SET @StartIndex = (@PageNo – 1) * @PageSize + 1;
SET @EndIndex =  @StartIndex + @PageSize – 1;

IF @StartIndex > @TotalRec SET @StartIndex = @TotalRec;
IF @EndIndex > @TotalRec SET @EndIndex = @TotalRec;

WITH [Customer] AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [CustomerName] ASC) AS [RowNo], [ID]
FROM [tCustomer] WITH (NOLOCK)
WHERE [CustomerName] IS NOT NULL
)
SELECT
[I].[ID],
[I].[CustomerName],
[I].[EmailID],
[I].[PhoneNo]
FROM [tCustomer] AS [I] WITH (NOLOCK)
INNER JOIN [Customer] AS [II] WITH (NOLOCK) ON [II].[RowNo] BETWEEN @StartIndex AND @EndIndex AND [II].[ID] = [I].[ID]
ORDER BY [II].[RowNo] ASC;

SELECT @TotalPages AS [TotalPages], @TotalRec AS [TotalRec];
END;