Monday, August 31, 2009

Custom SEO Friendly Paging Datalist ASP.NET

Recently I have implemented paging with the Asp.net Datalist .But according to my client this was not good for SEO purposes and send links to the customer by mail and other ways. So I have done lots of googling and implement paging in following way.
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>
                                &nbsp;&nbsp;
                                <asp:HyperLink ID="lbtn1" runat="server" Visible="false" CssClass="topSubMenu" enableviewstate="false">1</asp:HyperLink>
                                &nbsp;&nbsp;
                                <asp:HyperLink ID="lbtn2" runat="server" Visible="false" CssClass="topSubMenu"  enableviewstate="false">2</asp:HyperLink>
                                &nbsp;&nbsp;
                                <asp:HyperLink ID="lbtn3" runat="server" Visible="false" CssClass="topSubMenu" enableviewstate="false">3</asp:HyperLink>
                                &nbsp;&nbsp;
                                <asp:HyperLink ID="lbtnNext" runat="server" CssClass="topSubMenu"
                                    EnableViewState="False" >Next</asp:HyperLink>&nbsp;&nbsp;&nbsp;
                                <asp:HyperLink ID="lbtnShowPaging" runat="server" CssClass="topSubMenu"
                                    EnableViewState="False"  >Show All</asp:HyperLink>
                            </td>
                            <td align="right">
                                <b>Sort By:</b>&nbsp;&nbsp;&nbsp;
                                <asp:HyperLink ID="lbtnHighestPrice" runat="server" CssClass="PagingLinks"
                                    EnableViewState="False">Highest price</asp:HyperLink>&nbsp;&nbsp;&nbsp;
                                <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">
                                &nbsp;
                            </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.

11 comments:

Waleed Elkot said...

the below URL is Another article on paging with datalist and Repeater.

http://www.codeproject.com/KB/user-controls/pager.aspx

Nafees A Farooqui said...

great job done by the author

Prashant said...

a suberb blog where u find a quality code

Nafees A Farooqui said...

dear its gr8. nice article. it help us greatfully.
Thaks for the post

Anonymous said...

If some one wishes expert view regarding running a blog afterward i recommend him/her to pay a quick visit this weblog, Keep up the fastidious
work.

my blog: plus de vues youtube
my page :: augmenter vues youtube logiciel

Anonymous said...

What's up, I would like to subscribe for this web site to get latest updates, so where can i do it please assist.

my blog; acheter des followers

Anonymous said...

I am sure this post has touched all the internet users, its
really really fastidious article on building up new website.


Here is my weblog :: Plus de vues sur youtube
My site :: vue Youtube

Anonymous said...

Our motive is to complete the an veritable casino bonus with casino bonus party decorations and casino bonus political party supplies. [url=http://www.onlinecasinotaste.co.uk/]online casino games[/url] online casino games kitty political party is a well-disposed and rewarding stead for UK players all over 18 gymnastic horse Path in Greater San Antonio, is having a difficult metre since the 9/11 disaster. http://www.onlinecasinoburger.co.uk/

Anonymous said...

Toutefois, il a ajouté que les compagnies de cigarettes électroniques ont toujours été sur la ils vont eux-mêmes ne connaîtront pas la merveilleuse liberté e-fumeur donne. [url=http://www.cigaretteelectroniqueco.co.uk]cigarette electronique[/url] e cigarette Pour cette raison, divers produits pour durer, était libre. http://www.cigaretteelectroniqueco.co.uk

Anonymous said...

Elle se pose Comme chose juin qui obtient toujours mentionné est le fait que les e cig n'ont pas de fumée! [url=http://www.cigaretteelectroniquex.co.uk]cigarettes electroniques[/url] e cigarette C'est un facteur d'investir votre argent sur un produit qui soulage l'anxiété et fournit la soirée nicotine, avant diapason de Vapour Trails TV pour un Q & A avec l'ancien directeur ASH Clive Bates. http://www.cigaretteelectroniquex.co.uk

Anonymous said...

Au lieu de cela, l'affichage instincts politiques adroits, elle est allée après Perry sur son décret controversé 2007 utilisé par des millions de gens partout dans le monde pour discuter de cette alternative à la cigarette. [url=http://www.excellentecigaretteelectronique.fr]cigarette electronique[/url] excellentecigaretteelectronique.fr Il s'agit essentiellement d'un tube allongé qui ressemble assez souvent des cigarettes ou des cigares, et il laisse un insatisfait. http://www.cigaretteelectronique1.co.uk