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:
- Execute the Data Reader with the query.
- Loop the Data Reader to the position from where we want to start fetching the records.
- Fetch the required records.
- Convert this data into data Table.
- Break and Exit the loop.
- 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
.
- This function executes the query and returns the
DataTable
with records as per StartRow
, EndRow
- It starts by Executing the
DataReader
with SQL query.
- Executing the query will be very fast as so far no data processing is done yet.
- Thereafter, it will load
TableSchema
as row in schematable(DataTable)
.
- It will create new Data Table and load up its column with proper data type by looping
schematable
.
- Thereafter,
while
loop will start looping till it reaches the istart
variable from where it has to start fetching the data.
- It will fetch the data till
iEnd
variable is received, thereafter it will break the loop.
- 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
{
conn = new SqlConnection(ConnString);
conn.Open();
cmdsql = new SqlCommand(sQuery, conn);
dr = cmdsql.ExecuteReader(CommandBehavior.CloseConnection);
schematable = dr.GetSchemaTable();
dt = new 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);
}
if (iStart == 0) iStart = 1;
if (iEnd == 0) iEnd = 1;
icount = 1;
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
- 30th November, 2010: Initial post