How to Bind and implement Custom paging in asp.net DataList control in 3 different ways

Introduction: In this article I am going to explain with example How to Bind DataList from Sql server database and implement custom paging in Data List in 3 different styles/ways in asp.net using both C# and Vb.Net languages.  

Bind and implement Custom paging in DataList control in asp.net
Click on image to view enlarged demo
Description:  As we all know there is no default paging in DataList control so you need to create your own Custom paging to perform the paging. So we are going to implement that. And you will learn the following through this example:
  • How to bind/Load/Fill DataList data control with the data from the Sql Server Database.
  • How to Implement Custom paging in 3 different ways in DataList control.
  • How to create and use Cursor in Sql server stored procedure.

Implementation: Let's create an asp.net sample website to understand the concept and see it in action.
  • First of all create a database e.g.  "dbBooks" in the Sql Server and a table with the columns and their data type as shown in the image and name it "tbBook"

Column Name
Data type
Book_ID
Int(Primary Key. So set Is Identity=True)
Title
varchar(100)
Author
varchar(100)
Publisher
varchar(100)
Price
Int







  • Also create a Stored Procedure to handle the paging as:
CREATE PROCEDURE Paging_Sp
                (
                                @pagenumber int,
                                @pagesize int
                )
               
AS
                declare @startingRec int
                declare @endRec int
                declare @startingBookId int
                declare @endBookId int
                declare @recCount int

                set @startingRec=@pagenumber*@pagesize-@pagesize+1
                               
                declare cur_book scroll cursor for select book_Id from tbbook order by book_Id
               
                open cur_book
               
                fetch absolute @startingRec from cur_book into @startingBookId
               
                select @recCount=count(*) from tbbook where book_Id>@startingBookId
               
                if @recCount<@pagesize
                                set @endRec=@startingRec+@recCount
                else
                                set @endRec=@pagenumber * @pagesize

                fetch absolute @endRec from cur_book into @endBookId
               
                close cur_book
               
                deallocate cur_book

                select count(*) from tbbook
                select * from tbbook where book_Id>=@startingBookId and book_Id<=@endBookId

Note: I have used the Cursor in this stored procedure to help in implementing Custom paging in DataList.
  • Now, In the web.config file create the connection string to connect the asp.net website with the Sql server database.
<connectionStrings>
    <add name="conStr" connectionString="Data Source=Lalit;Initial Catalog=dbBooks:Integrated Security=True"></add>
                </connectionStrings>

Note: Replace the Data Source and Initial Catalog as per your application.
  • In the <Form> tag of the design page (default.aspx) you need to designs the page as:
HTML Source Code

<div>
  <fieldset style="width:400px;">
  <legend>Custom Paging in DataList in asp.net</legend> 
  <table>
  <tr> 
  <td colspan="2">Select No. of Records: <asp:DropDownList ID="ddlNoOfRec" runat="server" AutoPostBack="True"
            onselectedindexchanged="ddlNoOfRec_SelectedIndexChanged" Width="65px">
            <asp:ListItem>1</asp:ListItem>
            <asp:ListItem>2</asp:ListItem>
            <asp:ListItem>3</asp:ListItem>
            <asp:ListItem>4</asp:ListItem>
            <asp:ListItem>5</asp:ListItem>
            <asp:ListItem>6</asp:ListItem>
            <asp:ListItem>7</asp:ListItem>
            <asp:ListItem>8</asp:ListItem>
            <asp:ListItem>9</asp:ListItem>
            <asp:ListItem>10</asp:ListItem>
        </asp:DropDownList></td>
  </tr>
  <tr><td colspan="2">
      <asp:DataList ID="DtlBooks" runat="server" BackColor="#DEBA84" 
            BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3"
            GridLines="Both" CellSpacing="2">
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
            <ItemStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
        <ItemTemplate>
            <b>Title  </b><%#Eval("Title") %><br />
            <b>Author  </b><%#Eval("Author") %><br />
            <b>Publisher  </b><%#Eval("Publisher") %><br />
            <b>Price  </b><%#Eval("Price") %><br />
        </ItemTemplate>
            <SelectedItemStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
        </asp:DataList></td></tr>
  </table>
        <br />
        <asp:Label ID="lblPageFrom" runat="server" Text="Label"></asp:Label>
&nbsp;
        <asp:Label ID="lblOf" runat="server" Text="Label"></asp:Label>
&nbsp;
        <asp:Label ID="lblPageTo" runat="server" Text="Label"></asp:Label>
        <br />
        <asp:Button ID="btnFirst" runat="server" Text="First" onclick="btnFirst_Click" />
        <asp:Button ID="btnPrev" runat="server" Text="Prev" onclick="btnPrev_Click" />
        <asp:Button ID="btnNext" runat="server" Text="Next" onclick="btnNext_Click" />
        <asp:Button ID="btnLast" runat="server" Text="Last" onclick="btnLast_Click" />       
        <br />
        <asp:DataList ID="dtlpaging" runat="server"
            onselectedindexchanged="dtlpaging_SelectedIndexChanged">
        <ItemTemplate>
        <asp:LinkButton ID="lk" Text='<%#Container.DataItem %>' CommandName="select" runat="server"></asp:LinkButton>
        </ItemTemplate>
        </asp:DataList>
        <br />
    </fieldset>
    </div>

Asp.Net C# code to implement Custom paging in DataList
  • In the code behind file(.aspx.cs) write the code as:
First of all include the following required namespaces and then write the code:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        if (!Page.IsPostBack)
        {
            BindPages(1);
        }
    }
    private void BindPages(Int32 pageNo)
    {
        SqlCommand cmd = new SqlCommand("Paging_Sp", con);
        cmd.CommandType = CommandType.StoredProcedure;
        Int32 noOfRec, repCol;
        noOfRec = Convert.ToInt32(ddlNoOfRec.SelectedValue);
        if (noOfRec <= 3)
        {
            repCol = noOfRec;
        }
        else if (noOfRec >3 && noOfRec <= 7)
        {
            repCol = Convert.ToInt32(noOfRec / 2);
        }
        else
        {
            repCol = Convert.ToInt32(noOfRec / 3);
        }
        DtlBooks.RepeatColumns = repCol;
        cmd.Parameters.Add("@pagenumber"SqlDbType.Int).Value = pageNo;
        cmd.Parameters.Add("@pagesize"SqlDbType.Int).Value = noOfRec;
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        Int32 tot = Convert.ToInt32(dr[0]);
        lblPageFrom.Text = pageNo.ToString();
        lblOf.Text = "of";
        Int32 a = Convert.ToInt32(tot / noOfRec);
        if (tot % noOfRec != 0)
        {
            a += 1;
        }

        lblPageTo.Text = a.ToString();

        if (dr.NextResult())
        {
            DtlBooks.DataSource = dr;
            DtlBooks.DataBind();
        }
        dr.Close();
        cmd.Dispose();

        btnFirst.Enabled = true;
        btnPrev.Enabled = true;
        btnNext.Enabled = true;
        btnLast.Enabled = true;

        if (pageNo == 1)
        {
            btnFirst.Enabled = false;
            btnPrev.Enabled = false;
        }
        if (pageNo == Convert.ToInt32(lblPageTo.Text))
        {
            btnNext.Enabled = false;
            btnLast.Enabled = false;
        }
        //For Google like code  as 1 2 3 4 5 6
        Int32 i;
        ArrayList ar = new ArrayList();
        for (i = 1; i <= Convert.ToInt32(lblPageTo.Text); i++)
        {
            ar.Add(i.ToString());
            dtlpaging.RepeatDirection = RepeatDirection.Horizontal;
            dtlpaging.DataSource = ar;
            dtlpaging.DataBind();
        }
    }
    protected void ddlNoOfRec_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindPages(1);
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        BindPages(1);
    }
    protected void btnPrev_Click(object sender, EventArgs e)
    {
        BindPages(Convert.ToInt32(lblPageFrom.Text) - 1);
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        BindPages(Convert.ToInt32(lblPageFrom.Text) + 1);
    }
    protected void btnLast_Click(object sender, EventArgs e)
    {
        BindPages(Convert.ToInt32(lblPageTo.Text));
    }
    protected void dtlpaging_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindPages(dtlpaging.SelectedIndex + 1);
    }


Asp.Net VB Section
  • In the <Form> tag of the design page (default.aspx) you need to designs the page as:
HTML Source Code

<div>
    <fieldset style="width:400px;">
  <legend>Custom Paging in DataList in asp.net</legend> 
  <table>
  <tr> 
  <td colspan="2">Select No. of Records: <asp:DropDownList ID="ddlNoOfRec" runat="server" AutoPostBack="True"
            Width="65px">
            <asp:ListItem>1</asp:ListItem>
            <asp:ListItem>2</asp:ListItem>
            <asp:ListItem>3</asp:ListItem>
            <asp:ListItem>4</asp:ListItem>
            <asp:ListItem>5</asp:ListItem>
            <asp:ListItem>6</asp:ListItem>
            <asp:ListItem>7</asp:ListItem>
            <asp:ListItem>8</asp:ListItem>
            <asp:ListItem>9</asp:ListItem>
            <asp:ListItem>10</asp:ListItem>
        </asp:DropDownList></td>
  </tr>
  <tr><td colspan="2">
      <asp:DataList ID="DtlBooks" runat="server" BackColor="#DEBA84" 
            BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3"
            GridLines="Both" CellSpacing="2">
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
            <ItemStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
        <ItemTemplate>
            <b>Title  </b><%#Eval("Title") %><br />
            <b>Author  </b><%#Eval("Author") %><br />
            <b>Publisher  </b><%#Eval("Publisher") %><br />
            <b>Price  </b><%#Eval("Price") %><br />
        </ItemTemplate>
            <SelectedItemStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
        </asp:DataList></td></tr>
  </table>
        <br />
        <asp:Label ID="lblPageFrom" runat="server" Text="Label"></asp:Label>
&nbsp;
        <asp:Label ID="lblOf" runat="server" Text="Label"></asp:Label>
&nbsp;
        <asp:Label ID="lblPageTo" runat="server" Text="Label"></asp:Label>
        <br />
        <asp:Button ID="btnFirst" runat="server" Text="First" />
        <asp:Button ID="btnPrev" runat="server" Text="Prev" />
        <asp:Button ID="btnNext" runat="server" Text="Next" />
        <asp:Button ID="btnLast" runat="server" Text="Last" />       
        <br />
        <asp:DataList ID="dtlpaging" runat="server" >
        <ItemTemplate>
        <asp:LinkButton ID="lk" Text='<%#Container.DataItem %>' CommandName="select" runat="server"></asp:LinkButton>
        </ItemTemplate>
        </asp:DataList>
        <br />
    </fieldset>
    </div>

Asp.Net VB code to implement Custom paging in DataList
  • In the code behind file (.aspx.vb) write the code as:
First of all import the following required namespaces and then write the code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("cn").ConnectionString)
    Protected Sub Page_Load(ByVal sender As ObjectByVal e As System.EventArgsHandles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        If Not Page.IsPostBack Then
            BindPages(1)
        End If
    End Sub

    Private Sub BindPages(ByVal pageNo As Int32)
        Dim cmd As New SqlCommand("Paging_Sp", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim noOfRec As Int32, repCol As Int32
        noOfRec = Convert.ToInt32(ddlNoOfRec.SelectedValue)
        If noOfRec <= 3 Then
            repCol = noOfRec
        ElseIf noOfRec > 3 AndAlso noOfRec <= 7 Then
            repCol = Convert.ToInt32(noOfRec / 2)
        Else
            repCol = Convert.ToInt32(noOfRec / 3)
        End If
        DtlBooks.RepeatColumns = repCol
        cmd.Parameters.Add("@pagenumber"SqlDbType.Int).Value = pageNo
        cmd.Parameters.Add("@pagesize"SqlDbType.Int).Value = noOfRec
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dr.Read()
        Dim tot As Int32 = Convert.ToInt32(dr(0))
        lblPageFrom.Text = pageNo.ToString()
        lblOf.Text = "of"
        Dim a As Int32 = Convert.ToInt32(tot / noOfRec)
        If tot Mod noOfRec <> 0 Then
            a += 1
        End If

        lblPageTo.Text = a.ToString()

        If dr.NextResult() Then
            DtlBooks.DataSource = dr
            DtlBooks.DataBind()
        End If
        dr.Close()
        cmd.Dispose()

        btnFirst.Enabled = True
        btnPrev.Enabled = True
        btnNext.Enabled = True
        btnLast.Enabled = True

        If pageNo = 1 Then
            btnFirst.Enabled = False
            btnPrev.Enabled = False
        End If
        If pageNo = Convert.ToInt32(lblPageTo.Text) Then
            btnNext.Enabled = False
            btnLast.Enabled = False
        End If
        'For Google like code  as 1 2 3 4 5 6
        Dim i As Int32
        Dim ar As New ArrayList()
        For i = 1 To Convert.ToInt32(lblPageTo.Text)
            ar.Add(i.ToString())
            dtlpaging.RepeatDirection = RepeatDirection.Horizontal
            dtlpaging.DataSource = ar
            dtlpaging.DataBind()
        Next
    End Sub

    Protected Sub ddlNoOfRec_SelectedIndexChanged(ByVal sender As ObjectByVal e As System.EventArgsHandles ddlNoOfRec.SelectedIndexChanged
        BindPages(1)
    End Sub

    Protected Sub btnFirst_Click(ByVal sender As ObjectByVal e As System.EventArgsHandles btnFirst.Click
        BindPages(1)
    End Sub

    Protected Sub btnPrev_Click(ByVal sender As ObjectByVal e As System.EventArgsHandles btnPrev.Click
        BindPages(Convert.ToInt32(lblPageFrom.Text) - 1)
    End Sub

    Protected Sub btnNext_Click(ByVal sender As ObjectByVal e As System.EventArgsHandles btnNext.Click
        BindPages(Convert.ToInt32(lblPageFrom.Text) + 1)
    End Sub

    Protected Sub btnLast_Click(ByVal sender As ObjectByVal e As System.EventArgsHandles btnLast.Click
        BindPages(Convert.ToInt32(lblPageTo.Text))
    End Sub

    Protected Sub dtlpaging_SelectedIndexChanged(ByVal sender As ObjectByVal e As System.EventArgsHandles dtlpaging.SelectedIndexChanged
        BindPages(dtlpaging.SelectedIndex + 1)
    End Sub

Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

3 comments

Click here for comments
November 09, 2013 ×

I have one question here sir
The cursors are the slowest way to access data from database
Then what is the reason of using cursor here
Is there any other way to do paging ?

Reply
avatar
November 11, 2013 ×

Hello Azad Chohan..There are also many other ways to implement paging in Datalist but i like this way..

Reply
avatar
November 11, 2013 ×

Is this way does not decrease the performance of paging??
I never did paging in datalist that is why i am asking to you because you have experience in this and more knowledge about this then me

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..