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.  

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.

In previous articles i explained How to Bind,save,edit,update,delete records from DataList and Custom paging in Repeater control and Bind,Save and perform run time calculations in asp.net Repeater and Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and Bind,Save,Edit,Update,Cancel,Delete and paging example in DetailsView and JavaScript validation in asp.net and Implement Jquery form validations .

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 Object, ByVal e As System.EventArgs) Handles 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 Object, ByVal e As System.EventArgs) Handles ddlNoOfRec.SelectedIndexChanged

        BindPages(1)

    End Sub

    Protected Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click

        BindPages(1)

    End Sub

    Protected Sub btnPrev_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrev.Click

        BindPages(Convert.ToInt32(lblPageFrom.Text) - 1)

    End Sub

    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click

        BindPages(Convert.ToInt32(lblPageFrom.Text) + 1)

    End Sub

    Protected Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click

        BindPages(Convert.ToInt32(lblPageTo.Text))

    End Sub

    Protected Sub dtlpaging_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles 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."

• Tweet
• Share
• Share
• Share

Related Post