I saw the article of the DataGrid Access pagination before, it was very good, but I only mentioned the primary key paging. I organized it, I wrote a general paging method. You can refer to it. If you have any questions, please point out, as follows:
public class FastPageSql {private int allCount; // table records the total number of private bool isDesc; // Sort private string primaryKey; // private string orderKey primary key of the table; // sort key private string selectFields; // private fields to be selected String querycondition; // filter condition private string Tablename; // Table Name Private INT PerpageCount; / / Each page Display Private Int PageIndex; // Display Page Index Private Int TotalIndex; // Total Page Private Int MiddleIndex; // The number of middle pages;
public FastPageSql (int allcount, bool isdesc, string primarykey, string orderkey, string tablename, int perpagecount, int pageindex, string selectfields, string querycondition) {allCount = allcount; isDesc = isdesc; primaryKey = primarykey; orderKey = orderkey; selectFields = selectfields ; queryCondition = querycondition; tableName = tablename; perPageCount = perpagecount; pageIndex = pageindex; totalIndex = getTotalIndex (); middleIndex = (int) (totalIndex / 2);} private int getTotalIndex () {if (allCount% perPageCount> 0) return (INT) 1; Else Return AllCount / perpagecount;
} Public string getfastpagesql () {string sql = ""; if (pageindex <= 1) {#Region Chuan code SQL = "SELECT TOP" PerpageCount " SELECTFIELDS " from " Tablename " ((QueryCondition) ! = "") ":" "" "" "" order by " orderserkey " " " "": "ASC") ""; #ENDREGON} Else IF 1
{
# rr 中 页
IF (PrimaryKey == OrderKey)
SQL = "SELECT TOP" PerpageCount " SELECTFIELDS " from " Tablename " WHERE " PrimaryKey " ((isDesc)? "<": ") (SELECT" ((isdesc)? " ":" MAX ") " (" primarykey ") " " " " " " " TABLENAME " " " " " ""? "Where" querycondition "": ") " ORDER BY " ORDERKEY " " ((iSDesc)?" "ASC") "))" ((querycondition! = ")?" And " querycondition " ":") "ORDER BY" ORDERKEY " ((ISDESC)?" DESC ":" ASC ") " "; ELSE
sql = "select" selectFields "from (select top" perPageCount "" selectFields "from (select top" perPageCount * pageIndex "" selectFields "from" tableName "" ((queryCondition! = "")? "Where" querycondition "": "") " " "" "" "" "" "):" ASC ") ") ORDER BY " ORDERKEY " ((ISDESC)? "ASC": "DESC") ") ORDER BY" ORDERKEY "" ((ISDESC)? "DESC": "ASC") ""
#ndregion
Else IF (MiddleIndex)
{
#REGION between the middle page to the last page
IF (PrimaryKey == OrderKey)
SQL = "SELECT" SELECTFIELDS "" SELECTFIELDS "from" TABLENAME "Where" primarykey " ((isdesc)?"> ":" <") " (SELECT " (ISDESC)? "MAX": "MIN") "(" PrimaryKey ") from (SELECT TOP" "" " " " " TABLENAME " (( Querycondition! = "" "" "" ":" "" "ordesc" ")" "" ":" "" ")") ")") "" "" "" "" "" "" "" " ! = "" "": "" "" "" order by " orderkey " ((isdesc)? "ASC": "DESC") ") Order by" OrderKey "" (ISDESC)? "DESC": "ASC") ""; ELSE
sql = "select top" perPageCount "" selectFields "from (select top" (allCount- (pageIndex-1) * perPageCount) "" selectFields "from" tableName "" ((queryCondition! = "" )? "" ":" "" " " "" "" "" "" "" "" "" "" "" "" "" "" "" "," " "" DESC ":" ASC ") " "
#ndregion
} Else if (pageindex> = TotalIndex) {#Region Last page SQL = "SELECT" SELECTFIELDS "from (SELECT TOP" (allcount- (totalindex-1) * perpagecount) "" SELECTFIELDS " TABLENAME " " (Querycondition! =")? "" Querycondition "": ") " ORDER BY " ORDERKEY " " " ASC ":" DESC ") ") Order By " ORDERKEY " " ((ISDESC)?" DESC ":" ASC ") " "; #ENDREGON} Return SQL;}}
The returned SQL statement will only select the records you need for the current page, and you can fill it directly into the DataSet!
