asp.net mvc paging using dataset

9/30/2009

I have been doing some ASP.NET MVC experiments lately, and one of the major features missing in the framework is a nice API to page, so here is what I came up with. What do you think? Not bad for a UI guy huh ?

using System.Data.SqlClient;
using System.Data;
using System;
using System.Configuration;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Configuration;
using System.Collections;

namespace FNG
{
    //returns DS (dataset)
    public class ReturnDs
    {
        public string dbConnectionString;

        public DataSet populate(string strSQL)
        {
            SqlCommand dbcomm;
            SqlDataAdapter DA = new SqlDataAdapter();
            DataSet DS = new DataSet();
            using (SqlConnection connection = new SqlConnection(dbConnectionString))
            {
                dbcomm = new SqlCommand(strSQL, connection);
                DA.SelectCommand = dbcomm;
                DA.Fill(DS);
                return DS;
            }

        }
    }

    //paged class
    public class Paged
    {
        public string dbConnectionString;
        public string sql;
        public string currentPage;
        public int itemsPerPage;

        public string strPreviousText;
        public string strNextText;
        public string pageNumberPrefix = "";

        //templage
        public string templateHeader;
        public string templateFooter;
        public string template;
        public string template2;
        

        private int intNumberofItems;
        private DataSet ds = new DataSet();

        //gets records HTML
        public string GetHtml()
        {
            int perPage = itemsPerPage;
            string p = currentPage;

            StringBuilder sb = new StringBuilder();

            ReturnDs DataAdapter = new ReturnDs();
            DataAdapter.dbConnectionString = dbConnectionString;

            ds = DataAdapter.populate(sql);

            int RowCount;
            RowCount = ds.Tables[0].Rows.Count;

            //sets shared number of rows
            intNumberofItems = RowCount;

            //gets girst page  if nothing
            if (String.IsNullOrEmpty(p))
            {
                p = "1";
            }

            int i;

            int start;
            int end;

            int page = int.Parse(p);
            int lastPage = (RowCount / perPage);

            //set max possble page number
            if (RowCount <= lastPage)
            {
                end = RowCount - 1;
            }

            if (page != 1)
            {
                start = ((page - 1) * perPage);
                end = (start + perPage) - 1;
            }
            else
            {
                start = 0;
                end = perPage - 1;

            }

            sb.Append(templateHeader);

            ArrayList fieldPlaceHolder = new ArrayList();

            Regex r = new Regex(@"{(.*?)}", RegexOptions.IgnoreCase);

            Match m = r.Match(template);

            while (m.Success)
            {
                for (int i3 = 1; i3 <= 2; i3++)
                {
                    Group g = m.Groups[i3];
                    CaptureCollection cc = g.Captures;
                    for (int j = 0; j < cc.Count; j++)
                    {
                        Capture c = cc[j];
                        fieldPlaceHolder.Add(c);
                        //sb.Append("Capture" + j + "='" + c + "', Position=" + c.Index);
                    }
                }
                m = m.NextMatch();
            }

            for (i = start; i <= end; i++)
            {
                //make sure index does not run over
                if (i <= (RowCount - 1))
                {
                    //append table
                    
                    //generates alternating grows
                    if (i % 2 == 0)
                    {
                        //checks if alt template was set
                        if(String.IsNullOrEmpty(template2))
                        {
                            sb.Append(template);
                        }
                        else
                        {
                            sb.Append(template2);
                        }
                        
                    }
                    else
                    {
                        sb.Append(template);
                    } 


                    //runs replace for the object placehodlers
                    for (int i2 = 0; i2 < fieldPlaceHolder.Count; i2++)
                    {

                        

                        sb.Replace("{" + fieldPlaceHolder[i2].ToString() + "}", ds.Tables[0].Rows[i][fieldPlaceHolder[i2].ToString()].ToString());
                    } 
                }

            }

            sb.Append(templateFooter);

            return sb.ToString();
        }

        //gets pagin links
        public string PagingLinks()
        {
            StringBuilder sb = new StringBuilder();

            if (String.IsNullOrEmpty(currentPage))
            {
                currentPage = "1";
            }

            int intCurrentPage = int.Parse(currentPage);
            int intPerPage = itemsPerPage;


            string strPageFileName = pageNumberPrefix; //adds prefix before the paging (useful for ajax by adding '#')

            if (intCurrentPage < 1)
            {
                intCurrentPage = 1;
            }

            var number_of_pages = (intNumberofItems / intPerPage);

            int i = 0;

            //hide paging if only one page
            if (number_of_pages >= 1)
            {
                sb.Append("<div id='paging' class='clearfix'>");
                //previous record
                if (!(intCurrentPage == 1))
                {
                    sb.Append(" <a href='" + strPageFileName.ToString() + (intCurrentPage - 1).ToString() + "' class='p'>« " + strPreviousText + "</a>");
                }

                if (number_of_pages < 20)
                {
                    // if there are less than 20 record no paging breaking

                    //numbers
                    for (i = 0; i <= number_of_pages; i++)
                    {
                        if (!(i == intCurrentPage - 1))
                        {
                            sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                        }
                        else
                        {
                            sb.Append("<span>" + (i + 1) + " </span>");
                        }

                    }
                }
                else
                {

                    if (intCurrentPage <= 10)
                    {

                        //numbers
                        for (i = 0; i <= 10; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }
                        }

                        sb.Append("<span class='pg_dots'>...</span>");

                        //numbers
                        for (i = number_of_pages - 2; i <= number_of_pages; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }

                        }
                    }

                    else if (intCurrentPage >= number_of_pages - 8)
                    {
                        //last 10 numbers scenerio
                        //numbers
                        for (i = 0; i <= 3; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }
                        }

                        sb.Append("<span class='pg_dots'>...</span>");

                        //numbers
                        for (i = number_of_pages - 10; i <= number_of_pages; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }
                        }
                    }
                    else
                    {
                        //numbers
                        for (i = 0; i <= 3; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }
                        }

                        sb.Append("<span class='pg_dots'>...</span>");

                        //numbers
                        for (i = intCurrentPage - 5; i <= intCurrentPage + 3; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }
                        }

                        sb.Append("<span class='pg_dots'>...</span>");

                        //numbers
                        for (i = number_of_pages - 3; i <= number_of_pages; i++)
                        {
                            if (!(i == intCurrentPage - 1))
                            {
                                sb.Append(" <a href='" + strPageFileName + (i + 1) + "'> " + (i + 1) + " </a>");
                            }
                            else
                            {
                                sb.Append("<span>" + (i + 1) + " </span>");
                            }

                        }
                    }
                }


                //next record
                if (!(intCurrentPage == number_of_pages + 1))
                {
                    sb.Append(" <a href='" + strPageFileName + (intCurrentPage + 1) + "' class='n'>" + strNextText + " »</a>");
                }
                sb.Append("</div><!-- end of 'paging' -->");

                //builds string
                return sb.ToString();
            }
            else
            {
                return "";

            }
        }
    }

}

Now, here is how you can use this class:

public ActionResult Paging(string p)
        {

            Paged pg = new Paged();
            pg.dbConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["dataConnectionString"].ConnectionString;
            pg.sql = "SELECT TOP 500 * FROM guests";
            pg.currentPage = p;
            pg.itemsPerPage = 6;

            pg.strNextText = "Next";
            pg.strPreviousText = "Prev";
            //pg.pageNumberPrefix = "";

            pg.templateHeader = "<table class=\"data_table2\" style=\"width:100%\"><tr><th>id</th><th>name</th><th>email</th></tr>";
            pg.template = "<tr><td>{id}</td><td>{name}</td><td>{email}</td></tr>";
            pg.template2 = "<tr class='alt'><td>{id}</td><td>{name}</td><td>{email}</td></tr>";
            pg.templateFooter = "</table>";
            
            ViewData["message"] = pg.GetHtml() + pg.PagingLinks();

            return View();
        }