Let’s see how to do paging with Datalist in as Next, Previous and with customized Page numbers as that of Gridview.
Here I have the following things..
i) A datalist dlCollegeList to which we are to implement the paging
ii) Two Hyperlinks (hyPrevious & hyNext) for navigating backward and forward
iii) Finally, a literal (litPaging) for displaying page numbers.
Here am using the stored procedure to fill my datalist and now let’s move to the implementation.
My datalist and Navigation links markup is as follows.
<div>
<asp:DataList ID="dlCollegeList"runat="server"
OnItemDataBound="dlCollegeList_ItemDataBound"onitemcommand="dlCollegeList_ItemCommand"Width="100%"
>
<ItemTemplate>
<div class="col-photo"><asp:HyperLinkID="hyInstLogo" runat="server"
NavigateUrl='' ><asp:ImageButton ID="imgLogo"ToolTip='' runat="server"Width="63px" Height="60px"CssClass="col-photo-border"/>asp:HyperLink>div>
<div class="col-cour-name">
<h4><asp:Label ID="lblclgId"runat="server" Text=''Visible="false">asp:Label><asp:HyperLink ID="hyinstName"runat="server"
NavigateUrl=''Text=''>asp:HyperLink>h4>
div>
<divclass="cityname"><asp:HyperLinkID="hyCity" runat="server"Text=''>asp:HyperLink><asp:LabelID="lblCity" runat="server" Text=''Visible="false">asp:Label>div>
div>
ItemTemplate>
asp:DataList>
<div id="pagination">
<ul>
<li> <asp:HyperLinkID="hyPrevious"runat="server">Previousasp:HyperLink>li><li><asp:Literal ID="litPaging"runat="server"EnableViewState="true">asp:Literal>li><li><asp:HyperLink ID="hyNext"runat="server">Nextasp:HyperLink>li>
ul>
div>
div>
Stored Procedure :
CREATE PROCEDURE[dbo].[listInstitute]
@startRowIndex int,
@pageSize int,
@totalCount int output,
@category varchar(max)
AS
BEGIN
SET NOCOUNT ON;
SET @totalCount = 0
SET @startRowIndex =@startRowIndex + 1
BEGIN
SELECT * FROM (
Select collegedetails.*,ROW_NUMBER() OVER (ORDER BYcollegedetails.inst_name asc) asRowNum
FROM collegedetails wherecategory like '%' + @category + '%'
) as ArticleList
WHERE
category like '%'+@category+'%'and (RowNum BETWEEN@startRowIndex AND(@startRowIndex + @pageSize) -1)
ORDER BY inst_name DESC
SELECT @totalCount =Count(clgid) FROM collegedetailswhere category like'%'+@category+'%'
END
END
This is the stored procedure which is used to fill the datalist and lets see the attributes used here.
· The
startRowIndex is the one which sets the initial row to display and this
calculates the rowindex of records to be displayed in that particular
page.
· the pageSize denotes the number of records per page,
· totalCount gets the total number of records returned by the select command
Code Behind :
Declarations:
int _startIndex = 0;
int _thisPage = 1;
int _pageSize = 10;
int _totalNumberOfRows = 0;
string sPage;
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
private void BindGridView ()
{
DataTable dTable = newDataTable();
using (SqlConnection conn = newSqlConnection(connStr))
{
using (SqlCommand dCmd = newSqlCommand())
{
SqlParameter[] prms = newSqlParameter[4];
prms[0] = newSqlParameter("@startRowIndex",SqlDbType.Int);
prms[0].Value = _startIndex;
prms[1] = newSqlParameter("@pageSize",SqlDbType.Int);
prms[1].Value = _pageSize;
prms[2] = newSqlParameter("@totalCount",SqlDbType.Int);
prms[2].Direction =ParameterDirection.Output;
prms[3] = newSqlParameter("@category",SqlDbType.VarChar);
prms[3].Value = Request["category"].Replace("-"," ");
dCmd.CommandText ="listInstitute";
dCmd.CommandType =CommandType.StoredProcedure;
dCmd.Parameters.AddRange(prms);
dCmd.Connection = conn;
using (SqlDataAdapter dAd = newSqlDataAdapter())
{
// assign the select command to the Adapter object
dAd.SelectCommand = dCmd;
// now open the connection
conn.Open();
dTable.Clear();
dAd.Fill(dTable);
conn.Close(); // close the connection
}
_totalNumberOfRows =int.Parse(prms[2].Value.ToString());
}
}
dlCollegeList.DataSource = dTable;
dlCollegeList.DataBind();
litPaging.Text = GetPaging(_totalNumberOfRows, _pageSize, 10);
//LitPagingTop.Text = GetPaging(_totalNumberOfRows, _pageSize, 10);
}
string GetPaging(int totalRecords,int pageSize, inttotalNumericLinks)
{
if (totalRecords <= pageSize)
{
return "";
}
StringBuilder str = newStringBuilder();
//Get Total no of pages
int totalPages = totalRecords / pageSize + (totalRecords % pageSize > 0 ? 1 : 0);
//Get Current Page no
int currentPageNo = 1;
string pageUrl = Context.Request.Url.AbsolutePath;
if(Context.Request.QueryString["page"] == null)
{
currentPageNo = 1;
}
if(Context.Request.QueryString["page"] != null)
{
currentPageNo = 1 + (Convert.ToInt32(Context.Request.QueryString["page"]) - 1);
}
//Add previous button
if (currentPageNo > 1)
{
str.Append(string.Format("href=\"http://www.yoursite.com/ListInstitute/"+Request["category"].ToString()+ "/{0}\" title=\"Previous page\">", (currentPageNo - 2) + 1));
}
//Add Numeric link
int sp, ep;
if (totalNumericLinks >= totalPages)
{
sp = 1;
ep = totalPages;
}
else
{
if (currentPageNo - totalNumericLinks / 2 > 0)
{
ep = (currentPageNo + totalNumericLinks / 2 - (totalNumericLinks - 1) % 2);
ep = ep <>
}
else
{
ep = totalNumericLinks;
}
sp = ep - totalNumericLinks + 1 > 0 ? ep - totalNumericLinks + 1 : 1;
}
for (int p = sp; p <= ep; p++)
{
//For Current Page, No Link, Bold Text
if (p == currentPageNo)
{
str.Append(String.Format(" {0}", p.ToString()));
}
else
{
str.Append(String.Format(" + Request["category"].ToString() +"/{1}\" title=\"{0}\">{0} ", p, (p - 1) + 1));
}
}
if (currentPageNo == totalPages)
{
hyNext.Visible = false;
// hyNextTop.Visible = false;
}
if (currentPageNo == 1)
{
hyPrevious.Visible = false;
//hyPreviousTop.Visible = false;
}
if (Request["page"] == null)
{
hyNext.Visible = true;
//hyNextTop.Visible = true;
}
if (currentPageNo > 1)
{
hyPrevious.Visible = true;
//hyPreviousTop.Visible = true;
}
return str.ToString();
}
GetPaging
is the function which Is used to perform the paging as per our
requirement. Here I have customised the GetPaging function to return
only 10 pagenumbers. So the Literal control displays only 10 page
numbers in between the Previousand Next links and this can be modified according to your requirement.
You
could see the page numbers between the links. This pagenumber will be
passed through the query string for calculating the startindex and
rowindex of the records to be displayed. Moreover this paging is SEO
friendly.
BindGridView is the function which executes the stored procedure.
And now in my PageLoad my function is called as below..
protected void Page_Load(objectsender, EventArgs e)
{
if (!IsPostBack)
{
// check for current index
if (Request["page"] != null)
{
int.TryParse(Request["page"].ToString(), out _thisPage);
s =Convert.ToInt32(Request["page"].ToString());
st = (s - 1) * 10;
string str = Convert.ToString(st);
int.TryParse(str, out _startIndex);
}
int pSer = s - 1;
int nSer = s + 1;
if (s == 0)
{
nSer = 2;
}
BindGridViewArticels(); // bind articles
hyPrevious.NavigateUrl ="http://www.yoursite.com/ListInstitute/"+Request["category"].ToString()+"/" + pSer;
hyNext.NavigateUrl ="http://www.yoursite.com/ListInstitute/"+ Request["category"].ToString() +"/" + nSer;
}
if (_totalNumberOfRows <= 10)
{
hyNext.Visible = false;
hyPrevious.Visible = false;
}
}
And now with this Custom pagination for Datalist is done. So here is my Datalist with paging implemented.
You can see my Datalist with Pagination implemented.