access|datagrid| Dynamic | pagination | statement DataGrid Connect Access Quick Pagination Method (4)--Dynamically generate SQL statements
Using System;
Using System.Text;
namespace paging
{
<summary>
Summary description of the fastpaging.
</summary>
public class Fastpaging {
Private fastpaging () {
}
<summary>
Gets a SELECT statement that sorts and pages the query based on the specified field.
</summary>
<param name= "PageSize" > The number of records to display per page. </param>
<param name= "PageIndex" > The index of the page to display. </param>
<param name= The total number of records in the "RecordCount" > Datasheet. </param>
<param name= "TableName" > The data table to query. </param>
<param name= "QueryFields" > Fields to query. </param>
<param name= "PrimaryKey" > Primary key field. </param>
<param name= "Ascending" > is in ascending order. </param>
<param Name= the filter criteria for the "condition" > Query. </param>
<returns> returns a SELECT statement that sorts and pages the query. </returns>
public static String Paging (
int PageSize,
int PageIndex,
int RecordCount,
String TableName,
String QueryFields,
String PrimaryKey,
BOOL Ascending,
String condition)
{
#region Realize
StringBuilder sb = new StringBuilder ();
int pagecount = Getpagecount (recordcount,pagesize); Total number of pages paged
int middleindex = Getmidpageindex (PageCount); Index of intermediate pages
int firstindex = 0; Index of the first page
int lastindex = pageCount-1; Index of last page
#region @PageIndex <= @FirstIndex
if (PageIndex <= firstindex) {
Sb. Append ("select Top"). Append (pageSize). Append (""). Append (queryfields)
. Append ("from"). Append (tablename);
if (condition!= String.Empty)
Sb. Append ("WHERE"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (ascending));
}
#endregion
#region @FirstIndex < @PageIndex <= @MiddleIndex
else if (PageIndex > FirstIndex && pageIndex <= middleindex) {
Sb. Append ("select Top"). Append (pageSize). Append (""). Append (queryfields)
. Append ("from"). Append (tablename)
. Append ("WHERE"). Append (PrimaryKey);
if (ascending)
Sb. Append ("> ("). Append ("Select MAX (");
Else
Sb. Append ("< ("). Append ("select MIN");
Sb. Append (PrimaryKey). Append (") from (select top)
. Append (Pagesize*pageindex). Append (""). Append (PrimaryKey)
. Append ("from"). Append (tablename);
if (condition!= String.Empty)
Sb. Append ("WHERE"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (Ascending))
. Append (") TableA)");
if (condition!= String.Empty)
Sb. Append ("and"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (ascending));
}
#endregion
#region @MiddleIndex < @PageIndex < @LastIndex
else if (PageIndex > Middleindex && pageIndex < lastindex) {
Sb. Append ("SELECT *" (select top)
. Append (pageSize). Append (""). Append (queryfields)
. Append ("from"). Append (tablename)
. Append ("WHERE"). Append (PrimaryKey);
if (ascending)
Sb. Append ("< ("). Append ("select MIN");
Else
Sb. Append ("> ("). Append ("Select MAX (");
Sb. Append (PrimaryKey). Append (") from (select top)
. Append (recordcount-pagesize* (pageindex+1)). Append (""). Append (PrimaryKey)
. Append ("from"). Append (tablename);
if (condition!= String.Empty)
Sb. Append ("WHERE"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (!ascending))
. Append (") TableA)");
if (condition!= String.Empty)
Sb. Append ("and"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (!ascending))
. Append (") TableB Order By"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (ascending));
}
#endregion
#region @PageIndex >= @LastIndex
else if (PageIndex >= lastindex) {
Sb. Append ("SELECT * from"). Append (Recordcount-pagesize*lastindex)
. Append (""). Append (queryfields)
. Append ("from"). Append (tablename);
if (condition!= String.Empty)
Sb. Append ("WHERE"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (!ascending))
. Append (") TableA Order By"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (ascending));
}
#endregion
Return SB. ToString ();
#endregion
}
<summary>
Gets a SELECT statement that sorts and pages the query based on the specified field.
</summary>
<param name= "PageSize" > The number of records to display per page. </param>
<param name= "PageIndex" > The index of the page to display. </param>
<param name= The total number of records in the "RecordCount" > Datasheet. </param>
<param name= "TableName" > The data table to query. </param>
<param name= "QueryFields" > Fields to query. </param>
<param name= "PrimaryKey" > Primary key field. </param>
public static String Paging (
int PageSize,
int PageIndex,
int RecordCount,
String TableName,
String QueryFields,
String PrimaryKey)
{
return paging (PageSize, PageIndex, RecordCount, TableName, queryfields, PrimaryKey,
True, String.Empty);
}
<summary>
Gets a SELECT statement that sorts and pages the query based on the specified field.
</summary>
<param name= "PageSize" > The number of records to display per page. </param>
<param name= "PageIndex" > The index of the page to display. </param>
<param name= The total number of records in the "RecordCount" > Datasheet. </param>
<param name= "TableName" > The data table to query. </param>
<param name= "QueryFields" > Fields to query. </param>
<param name= "PrimaryKey" > Primary key field. </param>
<param name= "Ascending" > is in ascending order. </param>
<returns> returns a SELECT statement that sorts and pages the query. </returns>
public static String Paging (
int PageSize,
int PageIndex,
int RecordCount,
String TableName,
String QueryFields,
String PrimaryKey,
BOOL Ascending)
{
return paging (PageSize, PageIndex, RecordCount, TableName, queryfields, PrimaryKey,
Ascending, String.Empty);
}
<summary>
Gets a SELECT statement that sorts and pages the query based on the specified field.
</summary>
<param name= "PageSize" > The number of records to display per page. </param>
<param name= "PageIndex" > The index of the page to display. </param>
<param name= The total number of records in the "RecordCount" > Datasheet. </param>
<param name= "TableName" > The data table to query. </param>
<param name= "QueryFields" > Fields to query. </param>
<param name= "PrimaryKey" > Primary key field. </param>
<param Name= the filter criteria for the "condition" > Query. </param>
<returns> returns a SELECT statement that sorts and pages the query. </returns>
public static String Paging (
int PageSize,
int PageIndex,
int RecordCount,
String TableName,
String QueryFields,
String PrimaryKey,
String condition)
{
return paging (PageSize, PageIndex, RecordCount, TableName, queryfields, PrimaryKey,
true, condition);
}
<summary>
Calculates the number of pages.
</summary>
The total number of <param name= "RecordCount" > Tables is recorded. </param>
<param name= "PageSize" > The number of records displayed per page. </param>
<returns> number of pages. </returns>
public static int Getpagecount (int recordCount, int pageSize)
{
return (int) math.ceiling (double) recordcount/pagesize);
}
<summary>
Calculates the page index of an intermediate page.
</summary>
<param name= "PageCount" > Number of pages. </param>
<returns> the page index of the middle page. </returns>
public static int Getmidpageindex (int pagecount)
{
return (int) math.ceiling ((double) PAGECOUNT/2)-1;
}
<summary>
Gets the way the sort is sorted ("ASC" means ascending, and "DESC" indicates descending).
</summary>
<param name= "Ascending" > is ascending. </param>
<returns> sort ("ASC" means ascending, "DESC" means descending). </returns>
public static String Getsorttype (bool ascending)
{
Return (ascending?) "ASC": "DESC");
}
<summary>
Gets a Boolean value that indicates whether the sort is in ascending order.
</summary>
<param name= "OrderType" > Sort ("ASC" means ascending, "DESC" indicates descending). </param>
<returns> "ASC" is true; "DESC" is false; other is true. </returns>
public static bool Isascending (String OrderType)
{
Return ((ordertype.toupper () = = "DESC") (false:true);
}
}
}
