Use DataReader in asp.net for efficient Paging

Share the paging code of the two methods
1. Use DataReader for paging
Copy codeThe Code is as follows:
/// <Summary>
/// PageList for DataReader
/// </Summary>
/// <Param name = "connectionString"> </param>
/// <Param name = "SQL"> </param>
/// <Param name = "pageSize"> </param>
/// <Param name = "curPage"> </param>
/// <Param name = "pageCount"> </param>
/// <Param name = "count"> </param>
/// <Param name = "paiparms"> </param>
/// <Returns> </returns>
Public DataTable PageListReader (string connectionString, string SQL, int pageSize, int curPage, out int pageCount, out int count, params DbParameter [] cmdParms)
{
Int first = 0;
Int last = 0;
Int fieldCount = 0;
Using (SqlConnection conn = new SqlConnection (connectionString ))
{
SqlCommand cmd = conn. CreateCommand ();
PrepareCommand (cmd, conn, null, CommandType. Text, SQL, limit parms );
SqlDataReader reader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
DataTable dt = new DataTable ();
FieldCount = reader. FieldCount;
For (int I = 0; I <fieldCount; I ++)
{
DataColumn col = new DataColumn ();
Col. ColumnName = reader. GetName (I );
Col. DataType = reader. GetFieldType (I );
Dt. Columns. Add (col );
}
Count = 0;
First = (curPage-1) * pageSize + 1;
Last = curPage * pageSize;
While (reader. Read ())
{
Count ++;
If (count> = first & last> = count)
{
DataRow r = dt. NewRow ();
For (int I = 0; I <fieldCount; I ++)
{
R [I] = reader [I];
}
Dt. Rows. Add (r );
}
}
Reader. Close ();
PageCount = Convert. ToInt32 (Math. Ceiling (double) count/(double) pageSize ));
Return dt;
}
}

2. Use ROW_NUMBER () for paging
Copy codeThe Code is as follows:
/// <Summary>
/// Obtain data by PAGE (SQL Server 2005) for ROW_NUMBER ()
/// </Summary>
/// <Param name = "connectionString"> database link </param>
/// <Param name = "SQL"> obtain the dataset SQL </param>
/// <Param name = "fldSort"> multiple sorting fields </param>
/// <Param name = "pageSize"> Number of entries displayed per page </param>
/// <Param name = "curPage"> current page number </param>
/// <Param name = "pageCount"> total number of pages </param>
/// <Param name = "count"> total number of records </param>
/// <Param name = "paiparms"> DbParameter </param>
/// <Returns> DataTable </returns>
Public DataTable PageList (string connectionString, string SQL, string fldSort, int pageSize, int curPage, out int pageCount, out int count, params DbParameter [] partition parms)
{
StringBuilder strSql = new StringBuilder ();
StrSql. AppendFormat (@ "SELECT count (0) from {0} as MyTableCount;
Select * from (
SELECT ROW_NUMBER () OVER (order by {1}) RowNumber ,*
From {0} mytable
) Mytable2
Where RowNumber between {2} and {3 }"
, SQL, fldSort, Convert. ToString (curPage-1) * pageSize + 1), Convert. ToString (curPage * pageSize )));
DataSet ds = ExecuteQuery (connectionString, CommandType. Text, strSql. ToString (), partition parms );
Count = Convert. ToInt32 (ds. Tables [0]. Rows [0] [0]);
PageCount = Convert. ToInt32 (Math. Ceiling (double) count/(double) pageSize ));
Return ds. Tables [1];
}