Efficient paging with datareader

Since sql2005, row_number () has been used for paging. The last project to maintain sqlserver is 2000, and The paging cannot be rewritten. Writing Test Analysis is much faster than row_number ().

Share the paging code of the two methods

1. Use datareader for paging

/// <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

/// <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];
}