Hi mod7609,
If you don't want to use stored procedure then you have to write the query in the page itself. So i have created sample refering the below article.
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;
}
.Item
{
height: 150px;
width: 180px;
margin: 5px;
}
.Item, .Item td, .Item td
{
border: 1px solid #ccc;
}
.Item .header
{
background-color: #F7F7F7 !important;
color: Black;
font-size: 10pt;
line-height: 200%;
}
.page_enabled, .page_disabled
{
display: inline-block;
height: 25px;
min-width: 25px;
line-height: 25px;
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">
<asp:DataList ID="dlCustomers" runat="server" RepeatDirection="Horizontal" RepeatColumns="3">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" class="Item">
<tr>
<td class="header">
<b><u>
<%# Eval("ContactName") %></u></b>
</td>
</tr>
<tr>
<td class="body">
<b>City: </b>
<%# Eval("City") %><br />
<b>Postal Code: </b>
<%# Eval("PostalCode") %><br />
<b>Country: </b>
<%# Eval("Country")%><br />
<b>Phone: </b>
<%# Eval("Phone")%><br />
<b>Fax: </b>
<%# Eval("Fax")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<div style="margin: 5px">
<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>
</div>
</form>
</body>
</html>
C#
private int PageSize = 6;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GetCustomersPageWise(1);
}
}
private void GetCustomersPageWise(int pageIndex)
{
string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
string query = "SELECT ROW_NUMBER() OVER(ORDER BY [CustomerID] ASC)AS RowNumber,[CustomerID],[CompanyName],[ContactName],[City],[Country],[Phone],[Fax],[PostalCode]";
query += " INTO #Results FROM Customers";
query += " SELECT @RecordCount = COUNT(*) FROM #Results";
query += " SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1";
query += " DROP TABLE #Results";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.CommandType = CommandType.Text;
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();
dlCustomers.DataSource = idr;
dlCustomers.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();
}
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 = 6
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
Dim query As String = "SELECT ROW_NUMBER() OVER(ORDER BY [CustomerID] ASC)AS RowNumber,[CustomerID],[CompanyName],[ContactName],[City],[Country],[Phone],[Fax],[PostalCode]"
query += " INTO #Results FROM Customers"
query += " SELECT @RecordCount = COUNT(*) FROM #Results"
query += " SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1"
query += " DROP TABLE #Results"
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand(query, con)
cmd.CommandType = CommandType.Text
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()
dlCustomers.DataSource = idr
dlCustomers.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
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
