Hi ukd,
Check this example. Now please take its reference and correct your code.
For this sample I have used NorthWind database that you can download using the link given below.
Download Northwind Database
SQL
CREATE PROCEDURE [dbo].[Customers_GetCustomersPaging]
@PageIndex INT
,@PageSize INT
,@RecordCount INT OUT
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY CustomerId) RowNumber
,CustomerId
,ContactName
,CompanyName
INTO #Temp
FROM Customers
SELECT @RecordCount = COUNT(*) FROM #Temp
SELECT * FROM #Temp
WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1
DROP TABLE #Temp
END
HTML
<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>
<table class="Repeater" cellspacing="0" rules="all" border="1">
<tr>
<th>
Customer Id
</th>
<th>
Customer Name
</th>
<th>
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>
Namespaces
C#
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections.Generic
Code
C#
private int PageSize = 5;
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("Customers_GetCustomersPaging", 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("First", "1", currentPage > 1));
if (currentPage != 1)
{
pages.Add(new ListItem("<<", (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(">>", (currentPage + 1).ToString()));
}
//pages.Add(new ListItem("Last", 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 = 5
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("Customers_GetCustomersPaging", 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("First", "1", currentPage > 1))
If currentPage <> 1 Then
pages.Add(New ListItem("<<", (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(">>", (currentPage + 1).ToString()))
End If
'pages.Add(New ListItem("Last", 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
