DataGrid connection ACCESS fast paging method (4) - Dynamically generate SQL statements

/// Get the SELECT statement that is sorted and paid according to the specified field.

///

/// The number of records to display per page.

/// The index of the page to display.

/// The total number of records in the data table.

/// The data table to query.

/// The field you want to query.

/// Primary key field.

/// is arranged as ascending.

/// Filtering criteria for query.

/// Returns the SELECT statement of sorting and paging queries.

Public static string paning Page

Int PageSize,

Int pageIndex,

INT Recordcount,

String Tablename,

String queryfields,

String PrimaryKey,

Bool ascending,

String condition

{

#Region implementation

StringBuilder SB = New StringBuilder ();

INT PageCount = getPageCount (RecordCount, Pagesize); // Package total

INT MIDDLEINDEX = getMidpageIndex (pageCount); // Index of the intermediate page

INT firstIndex = 0; // The index of the first page

INT LastIndex = PageCount - 1; // The index of the last page

#Region @PageIndex <= @firstindex

IF (PageIndex <= firstIndex) {

SB.Append ("SELECT TOP") .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));

}

#ndregion

#Region @firstindex <@PageIndex <= @middleindex

Else IF (PageIndex> FirstIndex && pageindex <= middleindex) {

SB.Append ("SELECT TOP") .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 ("") .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));

}

#ndregion

#Region @MiddleIndex <@PageIndex <@LastIndex

Else IF (PageIndex> MiddleIndex && PageIndex

Sb.append ("SELECT *"

.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 *). 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));

}

#ndregion

#Region @PageIndex> = @LastIndex

Else IF (pageindex> = lastindex) {

Sb.append ("SELECT * from (select top") .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));

}

#ndregion

Return sb.toString ();

#ndregion

}

///