Custom Paging with Numbers

Custom Paging with Numbers
===========================

.aspx
======

<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 gridviewpaging2 : 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 Int64 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();
LoadPageSize();
}
}

#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”]);
lbl_TNOPages.Text = ”  ” + Convert.ToInt32(sqlDataReader[“TotalPages”]); ;
}
}
gvCustomerDetails.DataSource = CustomerDetails.AsReadOnly();
gvCustomerDetails.DataBind();
lbl_TPages.Text = ”  ” + (record_Count).ToString();
lbl_CPage.Text = ”  ” + (page_Number).ToString();
lbl_RRecords.Text = ”  ” + ((record_Count) – (page_Number * PAGE_SIZE)).ToString();
EnableAndDisableButton();
}
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
#endregion

#region Load PageSize in DropDown
public void LoadPageSize()
{
ddl_PageSize.Items.Add(“10”);
ddl_PageSize.Items.Add(“20”);
ddl_PageSize.Items.Add(“30”);
ddl_PageSize.Items.Add(“40”);
}
#endregion

#region Enable and Disable the Button
public void EnableAndDisableButton()
{
if (page_Number == 1)
{
btn_Previous.Enabled = false;
if (Int32.Parse(lbl_TPages.Text) > 0)
btn_Next.Enabled = true;
else
btn_Next.Enabled = false;
}
else
{
btn_Previous.Enabled = true;
if (page_Number == Int32.Parse(lbl_TPages.Text))
btn_Next.Enabled = false;
else
btn_Next.Enabled = true;
}
}
#endregion

#region Next and Previous Button Click
protected void Btn_Next_Click(object sender, EventArgs e)
{
try
{
Button btn = (Button)sender;
if (btn.Text == “Previous”)
page_Number–;
else
page_Number++;
LoadCustomerDetails();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion

#region DropDown SelectedIndex Change
protected void ddl_PageSize_SelectedIndexChanged(object sender, EventArgs e)
{
PAGE_SIZE = Convert.ToInt32(ddl_PageSize.SelectedItem.Text);
LoadCustomerDetails();
}
#endregion
}

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;