Hi susanta,
I have created sample that full-fill your requirement.
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below.
Install Microsoft Northwind and Pubs Sample databases in SQL Server Management Studio
SQL
CREATE PROCEDURE GetCustomersPageWise
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
INTO #Results
FROM [Customers]
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
GO
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
.Repeater, .Repeater td, .Repeater td
{
border: 1px solid #ccc;
}
.Repeater td
{
background-color: #eee !important;
}
.Repeater th
{
background-color: #6C6C6C !important;
color: White;
font-size: 10pt;
line-height: 200%;
}
.Repeater span
{
color: black;
font-size: 10pt;
line-height: 200%;
}
.page_enabled, .page_disabled
{
display: inline-block;
height: 20px;
min-width: 20px;
line-height: 20px;
text-align: center;
text-decoration: none;
border: 1px solid #ccc;
}
.page_enabled
{
background-color: #eee;
color: #000;
}
.page_disabled
{
background-color: #6C6C6C;
color: #fff !important;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table class="Repeater" cellspacing="0" rules="all" border="1">
<tr>
<th scope="col" style="width: 80px">
Customer Id
</th>
<th scope="col" style="width: 150px">
Customer Name
</th>
<th scope="col" style="width: 150px">
Company Name
</th>
</tr>
<asp:Repeater ID="rptCustomers" runat="server">
<ItemTemplate>
<tr>
<td>
<asp:Label ID="lblCustomerId" runat="server" Text='<%# Eval("CustomerId") %>' />
</td>
<td>
<asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName") %>' />
</td>
<td>
<asp:Label ID="lblCompanyName" runat="server" Text='<%# Eval("CompanyName") %>' />
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</form>
</body>
</html>
C#
private int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GetCustomersPageWise(1);
}
}
private void GetCustomersPageWise(int pageIndex)
{
string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
IDataReader idr = cmd.ExecuteReader();
rptCustomers.DataSource = idr;
rptCustomers.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
private void PopulatePager(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
pages.Add(new ListItem("<<", "1", currentPage > 1));
if (currentPage != 1)
{
pages.Add(new ListItem("Previous", (currentPage - 1).ToString()));
}
if (pageCount < 4)
{
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
else if (currentPage < 4)
{
for (int i = 1; i <= 4; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("...", (currentPage).ToString(), false));
}
else if (currentPage > pageCount - 4)
{
pages.Add(new ListItem("...", (currentPage).ToString(), false));
for (int i = currentPage - 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
else
{
pages.Add(new ListItem("...", (currentPage).ToString(), false));
for (int i = currentPage - 2; i <= currentPage + 2; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("...", (currentPage).ToString(), false));
}
if (currentPage != pageCount)
{
pages.Add(new ListItem("next", (currentPage + 1).ToString()));
}
pages.Add(new ListItem(">>", pageCount.ToString(), currentPage < pageCount));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetCustomersPageWise(pageIndex);
}
VB.Net
Private PageSize As Integer = 10
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.GetCustomersPageWise(1)
End If
End Sub
Private Sub GetCustomersPageWise(pageIndex As Integer)
Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("GetCustomersPageWise", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
con.Open()
Dim idr As IDataReader = cmd.ExecuteReader()
rptCustomers.DataSource = idr
rptCustomers.DataBind()
idr.Close()
con.Close()
Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
Me.PopulatePager(recordCount, pageIndex)
End Using
End Using
End Sub
Private Sub PopulatePager(recordCount As Integer, currentPage As Integer)
Dim dblPageCount As Double = CDbl(CDec(recordCount) / CDec(PageSize))
Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
Dim pages As New List(Of ListItem)()
If pageCount > 0 Then
pages.Add(New ListItem("<<", "1", currentPage > 1))
If currentPage <> 1 Then
pages.Add(New ListItem("Previous", (currentPage - 1).ToString()))
End If
If pageCount < 4 Then
For i As Integer = 1 To pageCount
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
ElseIf currentPage < 4 Then
For i As Integer = 1 To 4
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
pages.Add(New ListItem("...", (currentPage).ToString(), False))
ElseIf currentPage > pageCount - 4 Then
pages.Add(New ListItem("...", (currentPage).ToString(), False))
For i As Integer = currentPage - 1 To pageCount
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
Else
pages.Add(New ListItem("...", (currentPage).ToString(), False))
For i As Integer = currentPage - 2 To currentPage + 2
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
pages.Add(New ListItem("...", (currentPage).ToString(), False))
End If
If currentPage <> pageCount Then
pages.Add(New ListItem("next", (currentPage + 1).ToString()))
End If
pages.Add(New ListItem(">>", pageCount.ToString(), currentPage < pageCount))
End If
rptPager.DataSource = pages
rptPager.DataBind()
End Sub
Protected Sub Page_Changed(sender As Object, e As EventArgs)
Dim pageIndex As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
Me.GetCustomersPageWise(pageIndex)
End Sub
Screenshot
