Jan 01 2008

ASP.NET, MySQL and paging

Posted by admin under ASP.NET articles

In this article we will create a simple one page ASP.NET web application, implementing paging using the cool MySQL feature LIMIT and SQL_CALC_FOUND_ROWS.

One of the coolest things about MySQL is some of its non standard features. It might sound like a weird thing to say, but being a web developer I just love the simplicity and performance MySQL gives me when it comes to paging. Cause in the typical web app you need paging.

The two problems a developer faces in such a paged web application

1. Two questions.

It's pretty neat to present a list of links to all pages available to the user, instead of just a next/prev link. Typically that means two questions.

select count(*) from bla bla ALL WHERE CONDIITIONS 

select * from bla bla ALL WHERE CONDIITIONS 

2. Just retrieving the rows for current page from the database.

 It a waste memory to read 100000 records into a dataset and the use a pagedatasource. It's of course better to let the database engine do the filtering and having it just return the actual records to be displayed on the current page.

 And also - you probably need to show the total number of pages available.

However when using the LIMIT clause in MySQL you solve the problem number two. As for number one - since we still need to get the total number of records, there is a feature for that as well, SQL_CALC_FOUND_ROWS.

So in short here's the code for reading the data:


 
        public DataTable GetData
        {
            get
            {
                //Where to start???
                long lStart = 0;
                if (Request["p"] != null && Request["p"].Length > 0)
                {
                    lCurrentPage = Convert.ToInt32(Request["p"]);
                }
                if ( lCurrentPage < 1 )
                    lCurrentPage = 1;
                lStart = (lCurrentPage * lPageSize) - lPageSize;

                string sSQL = "select SQL_CALC_FOUND_ROWS table_schema, table_name from information_schema.tables ";
                sSQL += " order by table_schema, table_name ";
                sSQL += "limit " + lStart.ToString() + "," + lPageSize.ToString() + ";";
                sSQL += "select found_rows();";
                DataSet ds = MySql.Data.MySqlClient.MySqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
                    sSQL);
                m_Data = ds.Tables[0];
                lTotalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                return m_Data;
            }
        }

We add the SQL_CALC_FOUND_ROWS select modifier before the column list. This will tell MySQL to calculate the TOTAL number of rows and make it available in a special variable - which we get with select found_rows(). As you can see we do send both the queries in one command top the server and get them back in a dataset with the result in one table and the count in the second. That's so cool.

The rest of the code is just a matter of some math to calculate the current page (`query params ?p=12 etc) and of course rendering (the example uses a simple repeater) 



 

As usual, the full app is available to download. VS 2005 solution, C#.

Attachments