Page DataGrid with huge count of records

Introduction

Many of you are familiar with paging in an ASP.NET DataGrid. And, also, most of you must know that standard methods of paging in the .NET Framework does not work (of course, we should not blame the technology for that). So in this article, I would like to tell you about a fast paging method for SQL query results with a large number of records. Other articles of the same sort can be found at this site, but I want to share my thoughts in a shorter form. Please enjoy my short, but very useful (as I can assume) article.

Using the code

For the methods below you need to define what tables and columns you want to retrieve from the DB. Also, you have to determine what join of the tables to use and what order to be provided. So, let's begin.

This method is used to get the items count from the from the query (is necessary for paging, will be illustrated later):

public int GetRecordCount( string tableName )
{
    string sql = "select count(*) from "+ tableName;
    return Convert.ToInt32(executeScalar(sql));
}

This method is used to create an SQL query for the database:

public string CreateSql(int pageNumber, int pageSize, string primaryKey, 
                        string fieldNames, string fromTables, 
                        string condition, string orderBy)
{
    string sql = " SELECT TOP "+pageSize+" "+ 
                 fieldNames+" FROM "+ fromTables +" ";
    if (condition!=null && !string.Empty.Equals(condition))
    {
        if (!condition.ToLower().Trim().StartsWith("where"))
        {
            sql += " where ";
        }
        sql += condition + " and ";
        
    }
    if (sql.ToLower().IndexOf("where")==-1)
        sql+= " where ";

    sql+= " " +primaryKey +" NOT IN " +
          " (SELECT TOP "+(pageNumber*pageSize)+ 
          " "+primaryKey+" FROM " + fromTables+" ";
    if (condition!=null && !string.Empty.Equals(condition))
    {
        if (!condition.ToLower().Trim().StartsWith("where"))
        {
            sql += " where ";
        }
        sql += condition;
    }

    if (orderBy!=null && !string.Empty.Equals(orderBy))
    {
        sql+= " ORDER BY "+orderBy+") "+
            " ORDER BY "+orderBy;
    }
    else
    {
        sql+=")";
    }
    return sql;
}

Running the SQL query:

public void RunSql(string sqlString, 
            out SqlDataReader dataReader)
{
    SqlCommand cmd = CreateSqlCommand(sqlString,  null);
    dataReader = cmd.ExecuteReader();
}

Two overloaded methods of query creation are provided. The first one is used when the total number of records in the query (not in one page) is known, and the second in the other case.

public SqlDataReader GetListByPage (int pageNumber, int pageSize, 
                     string primaryKey, string fieldNames, 
                     string fromTables, string condition, string orderBy)
{
    string sql= CreateSql (pageNumber,pageSize, primaryKey, 
                fieldNames,fromTables,condition,orderBy);
    SqlDataReader dr;
    RunSql(sql, out dr);
    return dr;
}

public SqlDataReader GetListByPage (out int rowcount, int pageNumber, 
                     int pageSize, string primaryKey, string fieldNames, 
                     string fromTables, string condition, string orderBy)
{
    string countSql = fromTables;
    if (condition!=null && !string.Empty.Equals(condition))
    {

        if (!condition.ToLower().Trim().StartsWith("where"))
        {
            countSql += " where ";
        }
        countSql += condition;
    }
    rowcount = GetRecordCount(countSql);
    return GetListByPage(pageNumber, pageSize, primaryKey, 
                         fieldNames, fromTables, condition, orderBy);        
}

Here, is a small sample of using this code:

myDataGrid.DataSource = db.GetListByPage(out rowcount, 
                        PageIndex,myDataGrid.PageSize, 
                        "KeyField",fieldNames,tables,
                        search,OrderBy);
myDataGrid.VirtualItemCount = rowcount;
myDataGrid.CurrentPageIndex = PageIndex;

And don't forget to set the AllowCustomPaging property to true in your DataGrid. That's it for now.

It would be nice to see this implemented against this

http://www.aspfaqs.com/webtech/062899-1.shtml

for extra performance


http://www.codeproject.com/aspnet/PagingLarge.asp

it's general, and fast