About top paging of mssql access Database

However, the sorting field must be a unique record, and fields with the same name as the sorting field are not allowed in the select field list.
Although sql2k5 and later versions have provided rownum () for paging processing, there are still many development projects using sql2k.
Copy codeThe Code is as follows:
Uusing system. collections. generic;
Sing system;
Using system. text;
/// <Summary>
/// Construct the paging SQL statement
/// </Summary>
Public static class paginghelper
{
/// <Summary>
/// Obtain the paging SQL statement. The sorting field must constitute a unique record.
/// </Summary>
/// <Param name = "_ recordcount"> total number of records </param>
/// <Param name = "_ pagesize"> Number of records per page </param>
/// <Param name = "_ pageindex"> current page number </param>
/// <Param name = "_ safesql"> SQL query statement </param>
/// <Param name = "_ orderfield"> sort fields. Separate multiple fields with commas (,). </param>
/// <Returns> paging SQL statement </returns>
Public static string createpagingsql (int _ recordcount, int _ pagesize, int _ pageindex, string _ safesql, string _ orderfield)
{
// Re-combine the sorting fields to prevent errors
String [] arrstrorders = _ orderfield. split (new char [] {','}, stringsplitoptions. removeemptyentries );
Stringbuilder sboriginalorder = new stringbuilder (); // original sorting Field
Stringbuilder sbreverseo tutorial rder = new stringbuilder (); // opposite to the original sorting field, used for paging
For (int I = 0; I <arrstrorders. length; I ++)
{
Arrstrorders [I] = arrstrorders [I]. trim (); // remove Spaces
If (I! = 0)
{
Sboriginalorder. append (",");
Sbreverseorder. append (",");
}
Sboriginalorder. append (arrstrorders [I]);

Int index = arrstrorders [I]. indexof (""); // you can determine whether a logo can be upgraded or downgraded.
If (index> 0)
{
// Replace the lifting ID, which is required for paging
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 the total number of pages
_ 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 ();
// Use top n directly on the first page without querying by PAGE
If (_ pageindex = 1)
{
Sbsql. appendformat ("select top {0} *", _ pagesize );
Sbsql. appendformat ("from ({0}) as t", _ safesql );
Sbsql. appendformat ("order by {0}", sboriginalorder. tostring ());
}
// Reduce the number of top n entries on the last page.
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 ());
}
// The first half of the 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 ());
}
// The page with the second half of the 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>
/// Obtain the total number of records SQL statement
/// </Summary>
/// <Param name = "_ n"> limit the number of records </param>
/// <Param name = "_ safesql"> SQL query statement </param>
/// <Returns> SQL statement that records the total number of records </returns>
Public static string createtopnsql (int _ n, string _ safesql)
{
Return string. format ("select top {0} * from ({1}) as t", _ n, _ safesql );
}

/// <Summary>
/// Obtain the total number of records SQL statement
/// </Summary>
/// <Param name = "_ safesql"> SQL query statement </param>
/// <Returns> SQL statement that records the total number of records </returns>
Public static string createcountingsql (string _ safesql)
{
Return string. format ("select count (1) as recordcount from ({0}) as t", _ safesql );
}
}