Hi vereato,
Refer below example.
Database
CREATE TABLE [dbo].[Images](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NOT NULL,
[Url] [nvarchar](200) NOT NULL,
[CategoryId] [int] NULL,
[CategoryName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Images] ON
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (1, N'Fall 714', N'http://farm3.staticflickr.com/2854/10380193164_9b65e4c5ed_n.jpg', 1, N'First Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (2, N'Fall 707', N'http://farm8.staticflickr.com/7395/10380186284_4f9ac522ed_n.jpg', 1, N'First Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (3, N'Fall 716', N'http://farm8.staticflickr.com/7424/10380408813_3cd984570d_n.jpg', 1, N'First Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (4, N'Fall 715', N'http://farm4.staticflickr.com/3723/8986453414_a869ddc3aa_n.jpg', 1, N'First Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (5, N'Fall 301', N'http://farm8.staticflickr.com/7130/6885958326_ecbb33e962_n.jpg', 2, N'Second Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (6, N'Fall 302', N'http://farm8.staticflickr.com/7051/7032054587_5f15e32a10_n.jpg', 2, N'Second Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (7, N'Fall 303', N'http://farm8.staticflickr.com/7228/6885959482_670fb32b58_n.jpg', 2, N'Second Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (8, N'Fall 304', N'http://farm8.staticflickr.com/7120/7032057469_ce0c1620d4_n.jpg', 2, N'Second Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (9, N'Fall 065', N'http://farm3.staticflickr.com/2828/10379529486_7abd3b99ef_n.jpg', 3, N'Third Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (10, N'Fall 053', N'http://farm4.staticflickr.com/3723/10379498815_2acedf4c6c_n.jpg', 3, N'Third Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (11, N'Fall 595', N'http://farm8.staticflickr.com/7397/10379841244_ec8c68ccc4_n.jpg', 3, N'Third Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (12, N'Fall 227', N'http://farm6.staticflickr.com/5528/10379655586_2a95e5f127_n.jpg', 3, N'Third Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (13, N'Fall 192', N'http://farm3.staticflickr.com/2837/10379605235_5e95702e08_n.jpg', 4, N'Fourth Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (14, N'Fall 249', N'http://farm3.staticflickr.com/2820/10379652095_983088cf4c_n.jpg', 4, N'Fourth Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (15, N'Fall 254', N'http://farm8.staticflickr.com/7380/10379642624_89b111ef11_n.jpg', 4, N'Fourth Category')
GO
INSERT [dbo].[Images] ([Id], [Name], [Url], [CategoryId], [CategoryName]) VALUES (16, N'Fall 525', N'http://farm8.staticflickr.com/7460/10379808224_336a427151_n.jpg', 4, N'Fourth Category')
GO
SET IDENTITY_INSERT [dbo].[Images] OFF
GO
-- [GetImagesPageWise] 1,6, NULL
CREATE PROCEDURE [dbo].[GetImagesPageWise]
@PageIndex INT = 1
,@PageSize INT = 3
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [Id] ASC
)AS RowNumber
,*
INTO #Results
FROM [Images]
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
HTML
<center>
<div style="margin: 5px">
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
CssClass='<%# If(Convert.ToBoolean(Eval("Enabled")), "page_enabled", "page_disabled")%>'
OnClick="Page_Changed" OnClientClick='<%# If(Not Convert.ToBoolean(Eval("Enabled")), "return false;", "") %>'></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
<asp:DataList ID="dlImages" runat="server" RepeatDirection="Horizontal" RepeatColumns="3">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" class="Item">
<tr>
<th class="header">
<%# Eval("Name") %>
</th>
</tr>
<tr>
<td class="body">
<img src='<%# Eval("Url") %>' alt='<%# Eval("Name") %>' style="height: 150px; width: 200px;" />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</center>
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Code
C#
private int PageSize = 6;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.GetImagesPageWise(1);
}
}
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetImagesPageWise(pageIndex);
}
private void GetImagesPageWise(int pageIndex)
{
string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("GetImagesPageWise", 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();
dlImages.DataSource = idr;
dlImages.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
private void PopulatePager(int recordCount, int currentPage)
{
List<ListItem> pages = new List<ListItem>();
int startIndex, endIndex;
int pagerSpan = 5;
//Calculate the Start and End Index of pages to be displayed.
double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
int pageCount = (int)Math.Ceiling(dblPageCount);
startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
if (currentPage > pagerSpan % 2)
{
if (currentPage == 2)
{
endIndex = 5;
}
else
{
endIndex = currentPage + 2;
}
}
else
{
endIndex = (pagerSpan - currentPage) + 1;
}
if (endIndex - (pagerSpan - 1) > startIndex)
{
startIndex = endIndex - (pagerSpan - 1);
}
if (endIndex > pageCount)
{
endIndex = pageCount;
startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
}
//Add the First Page Button.
if (currentPage > 1)
{
pages.Add(new ListItem("First", "1"));
}
//Add the Previous Button.
if (currentPage > 1)
{
pages.Add(new ListItem("<<", (currentPage - 1).ToString()));
}
for (int i = startIndex; i <= endIndex; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
//Add the Next Button.
if (currentPage < pageCount)
{
pages.Add(new ListItem(">>", (currentPage + 1).ToString()));
}
//Add the Last Button.
if (currentPage != pageCount)
{
pages.Add(new ListItem("Last", pageCount.ToString()));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
VB.Net
Private PageSize As Integer = 6
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.GetImagesPageWise(1)
End If
End Sub
Protected Sub Page_Changed(sender As Object, e As EventArgs)
Dim pageIndex As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
Me.GetImagesPageWise(pageIndex)
End Sub
Private Sub GetImagesPageWise(pageIndex As Integer)
Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("GetImagesPageWise", 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()
dlImages.DataSource = idr
dlImages.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 pages As New List(Of ListItem)()
Dim startIndex As Integer, endIndex As Integer
Dim pagerSpan As Integer = 5
'Calculate the Start and End Index of pages to be displayed.
Dim dblPageCount As Double = CDbl(CDec(recordCount) / Convert.ToDecimal(PageSize))
Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
startIndex = If(currentPage > 1 AndAlso currentPage + pagerSpan - 1 < pagerSpan, currentPage, 1)
endIndex = If(pageCount > pagerSpan, pagerSpan, pageCount)
If currentPage > pagerSpan Mod 2 Then
If currentPage = 2 Then
endIndex = 5
Else
endIndex = currentPage + 2
End If
Else
endIndex = (pagerSpan - currentPage) + 1
End If
If endIndex - (pagerSpan - 1) > startIndex Then
startIndex = endIndex - (pagerSpan - 1)
End If
If endIndex > pageCount Then
endIndex = pageCount
startIndex = If(((endIndex - pagerSpan) + 1) > 0, (endIndex - pagerSpan) + 1, 1)
End If
'Add the First Page Button.
If currentPage > 1 Then
pages.Add(New ListItem("First", "1"))
End If
'Add the Previous Button.
If currentPage > 1 Then
pages.Add(New ListItem("<<", (currentPage - 1).ToString()))
End If
For i As Integer = startIndex To endIndex
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
'Add the Next Button.
If currentPage < pageCount Then
pages.Add(New ListItem(">>", (currentPage + 1).ToString()))
End If
'Add the Last Button.
If currentPage <> pageCount Then
pages.Add(New ListItem("Last", pageCount.ToString()))
End If
rptPager.DataSource = pages
rptPager.DataBind()
End Sub
Screenshot
