Custom Paging without Changing/Breaking SQL Query in GridView

• Download source - 24.36 KB

Introduction

The idea of doing custom paging is not new and is being done since a long time. Most of the programs I have seen are using Custom paging through stored procedures or through queries which of course is the best way to do it, but what if you have already built one project and you don't want to change a stored procedure or query since it would bring other bugs and requires a lot of testing. Or what if you are using SQL Server 2000 where it does not support Row_Number() function and for that you had to take a long way to custom paging.

Background

To implement the solutions, the logic is very simple. Generally we use Data Table or Data set to bind with gridview or any data bound control. The problem with Data Table is that it loads up all the records and then binds with a particular page on Grid View. Say if you have 1 million records, it will then take x seconds to fill those 1 million records and bind to Grid which will only show a single page with 10 records. Now if we somehow let the Data Table fill only those records which we want to display, it would reduce the time of filling up. To simplify, we tell the Data Table to Fill 10 records starting from Record 2000 - 2010 or from 20,000 to 20,010. This way, we could be saving a lot of time.

To achieve the above, we will use the following logic:

1. Execute the Data Reader with the query.
2. Loop the Data Reader to the position from where we want to start fetching the records.
3. Fetch the required records.
4. Convert this data into data Table.
5. Break and Exit the loop.
6. Return the Data Table.

You would argue that looping like these through complete records will be the same thing as filling up with Data Table. But with the example attached, I have tested on my computer (Dual Core 1.2 Ghz, 2 GB RAM) fetching 2.5 million records takes 39 seconds while the same with this Data Reader takes 2.27 seconds. So let's now quickly move to the code to see how things are working.

Using the Code

The main heart of the logic is the function "DataReaderToDataTable" found in Common.cs. It has a parameter as sQuery, iStart, iEndRow.

1. This function executes the query and returns the DataTable with records as per StartRow, EndRow
2. It starts by Executing the DataReader with SQL query.
3. Executing the query will be very fast as so far no data processing is done yet.
4. Thereafter, it will load TableSchema as row in schematable(DataTable).
5. It will create new Data Table and load up its column with proper data type by looping schematable.
6. Thereafter, while loop will start looping till it reaches the istart variable from where it has to start fetching the data.
7. It will fetch the data till iEnd variable is received, thereafter it will break the loop.
8. Data Table will be returned to the calling function.

internal static DataTable DataReaderToDataTable(string sQuery, int iStart, int iEnd)
{
            DataTable schematable = null;
            DataTable dt = null;
            SqlCommand cmdsql;
            SqlDataReader dr = null;
            SqlConnection conn = null;
            long icount = 0;
            try
            {
                //Open the connection and execute the Data Reader          
                conn = new SqlConnection(ConnString);
                conn.Open();
                cmdsql = new SqlCommand(sQuery, conn);
                dr = cmdsql.ExecuteReader(CommandBehavior.CloseConnection);
                schematable = dr.GetSchemaTable();
                dt = new DataTable();

                //Get the Schema of Tables Columns and its types, 
                //and load the same into DataTable.
                for (int i = 0; i <= schematable.Rows.Count - 1; i++)
                {
                    DataRow dRow = schematable.Rows[i];
                    DataColumn column = new DataColumn();
                    column.DataType = System.Type.GetType(dRow["DataType"].ToString());
                    column.AllowDBNull = 
			(dRow["AllowDBNull"].ToString() == "True" ? true : false);
                    column.ColumnName = dRow["ColumnName"].ToString();
                    column.Caption = dRow["ColumnName"].ToString();
                    
                    dt.Columns.Add(column);
                    //More DataTable property can be added as required.
                }
                if (iStart == 0) iStart = 1;
                if (iEnd == 0) iEnd = 1;
                icount = 1;

                //Loop the Reader which is executed till the Start and Variable, 
                //Fetch and add the rows one by one to Data Table 
                //Till the End Count is reached.
                // Exit the loop and Return Datable.
                while (dr.Read())
                {
                    if (icount >= iStart && icount <= iEnd)
                    {
                        DataRow dRow = dt.NewRow();
                        for (int i = 0; i <= dr.FieldCount - 1; i++)
                        {
                            dRow[i] = dr.GetValue(i);
                        }
                        dt.Rows.Add(dRow);
                    }
                    else if (icount > iEnd)
                    {
                        break;
                    }
                    icount = icount + 1;
                }
            }
            catch (SystemException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                schematable.Dispose();
                dr.Close();
                dr.Dispose();
            }
            return dt;
}

Using this function alone will be sufficient to apply the Custom Paging in an alternate way. However, I have implemented this using the GridView.

All your suggestions are welcome to improve this code.
Happy coding!

History

• 30^th November, 2010: Initial post