On the top paging method of the MSSQL Access database _mssql

But pros and cons, it requires that the sort field must form a unique record, and that fields that have the same name as the sorted field are not allowed in the list of fields after the select.
Although Sql2k5 and above have provided rownum () for paging, the use of SQL2K has been developed more

Copy Code code as follows:

Uusing System.Collections.Generic;
Sing system;
Using System.Text;
<summary>
To construct a page-after SQL statement
</summary>
public static Class Paginghelper
{
<summary>
To get the paging SQL statement, the sort field needs to form a unique record
</summary>
<param name= "_recordcount" > Total Records </param>
<param name= "_pagesize" > Number of records per page </param>
<param name= "_pageindex" > Current page </param>
<param name= "_safesql" >sql query statement </param>
<param name= "_orderfield" > Sort fields, multiple separated by "," </param>
<returns> Paging SQL statements </returns>
public static string Createpagingsql (int _recordcount, int _pagesize, int _pageindex, string _safesql, String _orderfield)
{
Rearrange sorted fields to prevent errors
string[] Arrstrorders = _orderfield.split (new char[] {', '}, stringsplitoptions.removeemptyentries);
StringBuilder sboriginalorder = new StringBuilder (); Original Sort Field
StringBuilder Sbreverseo Tutorial Rder = new StringBuilder (); In contrast to the original sort field, for paging
for (int i = 0; i < arrstrorders.length; i++)
{
Arrstrorders[i] = Arrstrorders[i].trim (); Remove space before and after
if (i!= 0)
{
Sboriginalorder.append (",");
Sbreverseorder.append (",");
}
Sboriginalorder.append (Arrstrorders[i]);

int index = Arrstrorders[i].indexof (""); Determine if there is a lifting mark
if (Index > 0)
{
Replace the lifting identification, paging required
BOOL flag = arrstrorders[i].indexof ("desc", StringComparison.OrdinalIgnoreCase)!=-1;
Sbreverseorder.appendformat ("{0} {1}", Arrstrorders[i].remove (index), flag?) "ASC": "desc");
}
Else
{
Sbreverseorder.appendformat ("{0} desc", Arrstrorders[i]);
}
}

Calculate Total Pages
_pagesize = _pagesize = = 0? _recordcount: _pagesize;
int PageCount = (_recordcount + _pagesize-1)/_pagesize;

Check the current page number
if (_pageindex < 1)
{
_pageindex = 1;
}
else if (_pageindex > PageCount)
{
_pageindex = PageCount;
}

StringBuilder sbsql = new StringBuilder ();
First page, use top n without paging query
if (_pageindex = 1)
{
Sbsql.appendformat ("Select top {0} *", _pagesize);
Sbsql.appendformat ("from ({0}) as T", _safesql);
Sbsql.appendformat ("ORDER by {0}", sboriginalorder.tostring ());
}
Last page, reduce a top n
else if (_pageindex = = PageCount)
{
Sbsql.append ("SELECT * from");
Sbsql.append ("(");
Sbsql.appendformat ("Select top {0} *", _recordcount-_pagesize * (_pageindex-1));
Sbsql.appendformat ("from ({0}) as T", _safesql);
Sbsql.appendformat ("ORDER by {0}", sbreverseorder.tostring ());
Sbsql.append (") as T");
Sbsql.appendformat ("ORDER by {0}", sboriginalorder.tostring ());
}
Pagination at first half of page
else if (_pageindex < (PAGECOUNT/2 + pagecount% 2))
{
Sbsql.append ("SELECT * from");
Sbsql.append ("(");
Sbsql.appendformat ("Select top {0} * from", _pagesize);
Sbsql.append ("(");
Sbsql.appendformat ("Select top {0} *", _pagesize * _pageindex);
Sbsql.appendformat ("from ({0}) as T", _safesql);
Sbsql.appendformat ("ORDER by {0}", sboriginalorder.tostring ());
Sbsql.append (") as T");
Sbsql.appendformat ("ORDER by {0}", sbreverseorder.tostring ());
Sbsql.append (") as T");
Sbsql.appendformat ("ORDER by {0}", sboriginalorder.tostring ());
}
Pagination at second half of page
Else
{
Sbsql.appendformat ("Select top {0} * from", _pagesize);
Sbsql.append ("(");
Sbsql.appendformat ("Select top {0} *" ((_recordcount% _pagesize) + _pagesize * (PageCount-_pageindex));
Sbsql.appendformat ("from ({0}) as T", _safesql);
Sbsql.appendformat ("ORDER by {0}", sbreverseorder.tostring ());
Sbsql.append (") as T");
Sbsql.appendformat ("ORDER by {0}", sboriginalorder.tostring ());
}

return sbsql.tostring ();
}

<summary>
Get the total number of records SQL statement
</summary>
<param name= "_n" > Limited number of records </param>
<param name= "_safesql" >sql query statement </param>
<returns> Total Records SQL statements </returns>
public static string Createtopnsql (int _n, string _safesql)
{
Return String.Format ("select top {0} * FROM ({1}) as T", _n, _safesql);
}

<summary>
Get the total number of records SQL statement
</summary>
<param name= "_safesql" >sql query statement </param>
<returns> Total Records SQL statements </returns>
public static string Createcountingsql (String _safesql)
{
Return String.Format ("SELECT count (1) as RecordCount from ({0}) as T", _safesql);
}
}