you can check asp.net Datalist Paging on following link
http://amberdev.rockschat.co.uk/GemCollector/
I have created a stored procedure that return paged result set.
ALTER Proc [dbo].[DatalistPaging]
@CategoriesString as varchar(5000),
@PageIndex int=0,
@PageSize int=10,
@Sort VARCHAR(100)=''
As
BEGIN
create table #DataList Paging (Description varchar(200)
,RRP decimal(10,4)
,WebPrice decimal(10,4)
,WebText varchar(200)
,LeadColour varchar(20)
,Prefix varchar(25)
,Gemstone varchar(50)
,CountryName varchar(20)
,CutName varchar(25)
,ShapeName varchar(30)
,Width decimal(6,2)
,[Length] decimal(6,2)
,Depth decimal(6,2)
,Colour varchar(35)
,Clarity varchar(30)
,Grade varchar(5)
,Quantity INT
,Carats decimal(10,4)
,Treatment int
,Calibrated bit
,ProductDetailID int
,ProductCode VARCHAR(10) )
DECLARE @Categories VARCHAR(100)
SET @Categories =RTRIM(LTRIM(@Categories))
INSERT INTO #DataList Paging (Description
,RRP
,WebPrice
,WebText
,LeadColour
,Prefix
,Gemstone
,CountryName
,CutName
,ShapeName
,Width
,[Length]
,Depth
,Colour
,Clarity
,Grade
,Quantity
,Carats
,Treatment
,Calibrated
,ProductDetailID
,ProductCode)
SELECT
ProductDetails.Description
,ProductDetails.RRP
,isnull(ProductDetails.WebPrice ,0) WebPrice
,ProductDetails.WebText
,CategoryLeadColour.LeadColourName AS LeadColour
,CategoryGemstoneVariation.Name as Prefix
,TypeStone.TypeName as Gemstone
,Country.CountryName
,CategoryCut.CutName
,CategoryShape.ShapeName
,ProductDetailGemstone.Width
,ProductDetailGemstone.[Length]
,ProductDetailGemstone.Depth --(if it is > 0)
,CategoryHue.HueName AS Colour
,ISNULL(CategoryClarity.Name,'') AS Clarity
,ISNULL(CategoryClarity.grade,'') as Grade
,ProductDetailGemstone.Quantity
,ProductDetailGemstone.Carats
,ProductDetailGemstone.Treatment
,ProductDetailGemstone.Calibrated
,ProductDetails.ProductDetailID
,ProductDetails.ProductCode
FROM ProductDetails
Inner join ProductDetailGemstone on ProductDetails.ProductDetailID=ProductDetailGemstone.ProductDetailId
LEFT JOIN CategoryShape on CategoryShape.ShapeId=Shape
LEFT JOIN CategoryCut on CategoryCut.CutId=Cut
LEFT OUTER JOIN Country On Country.CountryId = ProductDetailGemstone.Country
LEFT OUTER JOIN CategoryClarity ON CategoryClarity.Id = ProductDetailGemstone.Clarity
LEFT OUTER JOIN CategoryTreatment ON CategoryTreatment.Id = ProductDetailGemstone.Treatment
LEFT OUTER JOIN CategoryHue ON CategoryHue.HueId = ProductDetailGemstone.Colour
LEFT JOIN TypeStone on TypeStone.TypeId=ProductDetailGemstone.Gemstone
LEFT JOIN CategoryGemstoneVariation ON CategoryGemstoneVariation.id=ProductDetailGemstone.Variation
LEFT JOIN CategoryLeadColour ON CategoryLeadColour.LeadColourId=ProductDetails.LeadColour
WHERE CategoryShape.ShapeName =@Categories OR
CategoryCut.CutName = @Categories OR
TypeStone.TypeName = @Categories OR
CategoryGemstoneVariation.Name = @Categories
OR CategoryLeadColour.LeadColourName = @Categories
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
ProductDetailID int
)
-- Insert into the temp table
IF @Sort='sorthigh'
INSERT INTO #PageIndex (ProductDetailID)
SELECT ProductDetailID FROM #temp ORDER BY ISNULL(#temp.WebPrice,0) desc --#temp.ProductDetailID
ELSE IF @Sort='sortlow'
INSERT INTO #PageIndex (ProductDetailID)
SELECT ProductDetailID FROM #temp ORDER BY ISNULL(#DataList Paging.WebPrice,0)
ELSE
INSERT INTO #PageIndex (ProductDetailID)
SELECT ProductDetailID FROM #DataList Paging ORDER BY #DataList Paging.ProductDetailID
SELECT ProductCode,
dbo.RoundCaratsWt(Carats) as Carats
,Prefix,Gemstone,Description,RRP
,Convert(decimal(10,2),ISNULL(WebPrice,0)) WebPrice
,WebText
,LeadColour
,CountryName AS Country
,CutName AS Cut
,ShapeName As Shape
,Width
,[Length]
,Depth
,Colour
,Clarity
,Grade
,Quantity
,Treatment
,Calibrated
,#DataList Paging.ProductDetailID
FROM #DataList Paging , #PageIndex PageIndex
WHERE #DataList Paging.ProductDetailID = PageIndex.ProductDetailID AND PageIndex.IndexID > @PageLowerBound AND PageIndex.IndexID < @PageUpperBound ORDER BY PageIndex.IndexID Declare @NoofRows INT Declare @NoofPages INT Select @NoofRows=COUNT(1) FROM #DataList Paging --SET @NoofPages = @NoofRows/@PageSize +1 return @NoofRows END
Now call this Stored procedure and get Dataset to populate records to Asp.net datalist Paging in following way:
public DataTable Get_Datalist_Paging (out int intNoofPage) {
string strConn = GlobalDAL.GetConnection();
try
{
SqlParameter[] paramsToStore = new SqlParameter[5]; paramsToStore[0] = new SqlParameter("@CategoriesString", SqlDbType.VarChar);
paramsToStore[0].Value = CategoriesString;
paramsToStore[0].Size = 5000;
paramsToStore[1] = new SqlParameter("@PageIndex", SqlDbType.Int);
paramsToStore[1].Value = PageIndex;
paramsToStore[2] = new SqlParameter("@PageSize", SqlDbType.Int);
paramsToStore[2].Value = PageSize;
paramsToStore[3] = new SqlParameter("@Sort", SqlDbType.VarChar);
paramsToStore[3].Value = Sort;
paramsToStore[4] = new SqlParameter("Return_Value", SqlDbType.Int);
paramsToStore[4].Direction = ParameterDirection.ReturnValue; DataSet ds = SqlHelper.ExecuteDataset(strConn, CommandType.StoredProcedure, "Gem_GetProductsFromCategories", paramsToStore);
intNoofPage = Convert.ToInt32(paramsToStore[4].Value);
return ds.Tables[0];
} catch (Exception excp)
{
throw excp;
}
}
Call following method on page_Load event to populate records to Asp.net datalist Paging --
DataTable objResult = objProducts.Get_Datalist_Paging(out intNoofRows);
public void BindList(DataTable dt)
{
try
{
dlstProductDetail.DataSource = dt;
dlstProductDetail.DataBind();
}
catch (Exception ex)
{
}
finally
{
dt = null;
}
}
private void UpdatePagingText()
{
if (pageIndex > 1 && pageIndex < intpage1 =" pageIndex" intpage2 =" pageIndex;" intpage3 =" pageIndex" pageindex ="=">= 3)
{
intPage1 = pageIndex - 2;
intPage2 = pageIndex - 1;
intPage3 = pageIndex;
}
else if (pageIndex == 1)
{
intPage1 = 1;
intPage2 = 2;
intPage3 = 3;
}
lbtn1.Text = intPage1.ToString();
lbtn1Bottom.Text = intPage1.ToString();
lbtn2.Text = intPage2.ToString();
lbtn2Bottom.Text = intPage2.ToString();
lbtn3.Text = intPage3.ToString();
lbtn3Bottom.Text = intPage3.ToString();
lbtn1.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + lbtn1.Text + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
lbtn1Bottom.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + lbtn1.Text + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
lbtn2.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + lbtn2.Text + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
lbtn2Bottom.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + lbtn2.Text + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
lbtn3.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + lbtn3.Text + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
lbtn3Bottom.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + lbtn3.Text + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
if (pageIndex < navigateurl =" strapplicationurl" navigateurl =" strapplicationurl" pageindex ="=" visible =" false;" visible =" false;"> 1)
{
lbtnPrev.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + (pageIndex - 1) + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
lbtnPrevBottom.NavigateUrl = strapplicationurl + "Gemstones/" + strCategoryLogo + "/" + (pageIndex - 1) + "/" + strPageSetting + "/" + strPageSorting + "/" + strFilter;
}
else if (pageIndex == 1)
{
lbtnPrev.Visible = false;
lbtnPrevBottom.Visible = false;
}
// ShowPageSettings();
}
Handling for above navigate url I have created a
HttpModule for url rewrite-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
namespace GemsCollectorBAL.Categories
{
class CategoryModule : IHttpModule
{
public void Init(System.Web.HttpApplication context)
{
context.BeginRequest += new EventHandler(OnBeginRequest);
}
private void OnBeginRequest(object sender, EventArgs e)
{
string strCategrories = string.Empty;
string strPageIndex = string.Empty;
string strPageSetting = string.Empty;
string strSorting = string.Empty;
string strFilter = string.Empty;
int temp = 0;
HttpApplication app;
string requestUrl;
app = sender as HttpApplication;
requestUrl = app.Request.Url.ToString();
strCategrories += "PageIndex=" + strPageIndex + "&";
strCategrories += "PageSetting=" + strPageSetting + "&";
strCategrories += "PageSorting=" + strSorting + "&";
strCategrories += "Filter=" + strFilter ;
}
}
}
String strCurrentPath;
String strCustomPath;
strCurrentPath = app.Request.Path.ToLower();
if (strCurrentPath.IndexOf("/gemstones/") >= 0)
{
strCustomPath = "~/Categories/CategoryDetails.aspx?" + strCategrories;
// rewrite the URL
app.Context.RewritePath(strCustomPath);
}
}
public void Dispose()
{
}
}
}
Register this HttpModule to web.config to Rewrite Url:
<httpModules>
<add name="CategoryModule" type="GemsCollectorBAL.Categories.CategoryModule,GemsCollectorBAL"/>
</httpModules>
UI Code Looks like in this way for Asp.net Datalist Paging:
<tr id="trPaging" runat="server">
<td class="topSubMenu">
<table cellpadding="0" cellspacing="0">
<tr>
<td style="width: 100px" id="tdPageof" runat="server" enableviewstate="false">
<b>Page
<%=pageIndex %>
of
<%=Noofpages %>
</b>
</td>
<td id="tdPaging" runat="server" style="width: 425px">
<asp:HyperLink ID="lbtnPrev" runat="server" CssClass="topSubMenu"
EnableViewState="False" >Previous</asp:HyperLink>
<asp:HyperLink ID="lbtn1" runat="server" Visible="false" CssClass="topSubMenu" enableviewstate="false">1</asp:HyperLink>
<asp:HyperLink ID="lbtn2" runat="server" Visible="false" CssClass="topSubMenu" enableviewstate="false">2</asp:HyperLink>
<asp:HyperLink ID="lbtn3" runat="server" Visible="false" CssClass="topSubMenu" enableviewstate="false">3</asp:HyperLink>
<asp:HyperLink ID="lbtnNext" runat="server" CssClass="topSubMenu"
EnableViewState="False" >Next</asp:HyperLink>
<asp:HyperLink ID="lbtnShowPaging" runat="server" CssClass="topSubMenu"
EnableViewState="False" >Show All</asp:HyperLink>
</td>
<td align="right">
<b>Sort By:</b>
<asp:HyperLink ID="lbtnHighestPrice" runat="server" CssClass="PagingLinks"
EnableViewState="False">Highest price</asp:HyperLink>
<asp:HyperLink ID="lbtnLowestPrice" runat="server" CssClass="PagingLinks"
EnableViewState="False">Lowest price</asp:HyperLink>
</td>
</tr>
</table>
</td>
</tr>
</table>
<div style="width: 800px">
<asp:DataList ID="dlstProductDetail" runat="server" RepeatDirection="Horizontal"
OnItemDataBound="dlstProductDetail_ItemDataBound" RepeatColumns="4"
EnableViewState="False">
<ItemTemplate>
<table class="ItemBorder" cellpadding="0" cellspacing="0" border="0">
<tr id="trTitle" runat="server" EnableViewState="false">
<td align="left" class="ItemTop" colspan="2" runat="server" id="tdView" onserverclick="tdView_Click" EnableViewState="false">
<asp:HyperLink ID="lbtnProductHeader" Width="100%" ForeColor="White" NavigateUrl="" runat="server" enableviewstate="false"></asp:HyperLink>
</td>
</tr>
<tr>
<td colspan="2" align="center" style="padding-top: 5px">
<asp:HyperLink ID="imgProducts" ToolTip='<%# DataBinder.Eval(Container.DataItem, "ProductCode")%>' CssClass="ProductImage" runat="server" Height="100px" Width="100px" enableviewstate="false"></asp:HyperLink>
</td>
</tr>
<tr>
<td valign="top">
<table cellpadding="0" cellspacing="0">
</table>
</td>
<td align="right">
</td>
</tr>
<tr>
<td style="width: 20px">
</td>
<td style="padding-top: 10px; width: *" align="right">
<span class="webprice" enableviewstate="false">£<%# DataBinder.Eval(Container.DataItem, "WebPrice")%></span>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</div>
Summary : So we can use this mechanism to all Asp.net binding controls.
