Implement Paging in DataList with MySql Database in ASP.Net

Prasunjeet
 
on Aug 23, 2015 03:21 AM
5211 Views

I want datalist pagination without stored procedure.

i am currently using mysql database with asp.net.

please help me out.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Shashikant
 
on Aug 24, 2015 02:20 AM
on Sep 02, 2015 01:45 AM

Here I have created sample that full-fill your requirement using below article.

Use and connect to MySQL Database in ASP.Net Application using MySQLConnector

HTML

<div>
    <asp:DataList ID="dlCustomers" runat="server" RepeatDirection="Horizontal" RepeatColumns="3">
        <ItemTemplate>
            <table cellpadding="2" cellspacing="0" class="Item">
                <tr>
                    <td class="header">
                        <b><u>
                            <%# Eval("ContactName")%></u></b>
                    </td>
                </tr>
                <tr>
                    <td class="body">
                        <b>Id: </b>
                        <%# Eval("CustomerId") %><br />
                        <b>Company Name: </b>
                        <%# Eval("CompanyName") %><br />
                    </td>
                </tr>
            </table>
        </ItemTemplate>
    </asp:DataList>
    <br />
    <asp:Repeater ID="rptPager" runat="server">
        <ItemTemplate>
            <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
        </ItemTemplate>
    </asp:Repeater>
</div>

Code

private int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}

private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(constring))
    {
        using (MySqlCommand cmd = new MySqlCommand("GetCustomers_Pager", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("_PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("_PageSize", PageSize);
            cmd.Parameters.Add("_RecordCount", MySqlDbType.Int32, 4);
            cmd.Parameters["_RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            using (IDataReader idr = cmd.ExecuteReader())
            {
                dlCustomers.DataSource = idr;
                dlCustomers.DataBind();
            }
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["_RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}

private void PopulatePager(int recordCount, int currentPage)
{
    List<ListItem> pages = new List<ListItem>();
    int startIndex, endIndex;
    int pagerSpan = 5;

    //Calculate the Start and End Index of pages to be displayed.
    double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
    int pageCount = (int)Math.Ceiling(dblPageCount);
    startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
    endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
    if (currentPage > pagerSpan % 2)
    {
        if (currentPage == 2)
        {
            endIndex = 5;
        }
        else
        {
            endIndex = currentPage + 2;
        }
    }
    else
    {
        endIndex = (pagerSpan - currentPage) + 1;
    }

    if (endIndex - (pagerSpan - 1) > startIndex)
    {
        startIndex = endIndex - (pagerSpan - 1);
    }

    if (endIndex > pageCount)
    {
        endIndex = pageCount;
        startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
    }

    //Add the First Page Button.
    if (currentPage > 1)
    {
        pages.Add(new ListItem("First", "1"));
    }

    //Add the Previous Button.
    if (currentPage > 1)
    {
        pages.Add(new ListItem("<<", (currentPage - 1).ToString()));
    }

    for (int i = startIndex; i <= endIndex; i++)
    {
        pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
    }

    //Add the Next Button.
    if (currentPage < pageCount)
    {
        pages.Add(new ListItem(">>", (currentPage + 1).ToString()));
    }

    //Add the Last Button.
    if (currentPage != pageCount)
    {
        pages.Add(new ListItem("Last", pageCount.ToString()));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}

protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}

Screenshot