[Ext. Net] Access Database page display of GridPanel

Using System; using System. collections. generic; using System. linq; using System. text; using System. data; using System. data. sqlClient; using System. data. oleDb; namespace DzPlatForm. DBUtility {public class AccessPage {private string _ StringConnection; public string StringConnection {get {return _ StringConnection;} set {_ StringConnection = value ;}} /// <summary> /// use by PAGE /// </summary> /// <param name = "query"> </param> /// <param name = "passCount"> </param> // <returns> </returns> private string recordID (string query, int passCount) {using (OleDbConnection m_Conn = new OleDbConnection (StringConnection) {m_Conn.Open (); OleDbCommand cmd = new OleDbCommand (query, m_Conn); string result = string. empty; using (OleDbDataReader dr = cmd. executeReader () {while (dr. read () {if (passCount <1) {result + = "," + dr. getInt32 (0);} passCount --;} m_Conn.Close (); m_Conn.Dispose (); return result. substring (1 );}} /// <summary> /// ACCESS efficient paging /// </summary> /// <param name = "pageIndex"> current page number </param> /// <param name = "pageSize"> page size </param> /// <param name = "strKey"> Primary Key </param> /// <param name = "showString"> displayed field </param> /// <param name = "queryString"> query string, support joint query </param> /// <param name = "whereString"> query conditions, if there are conditions, it must start with where </param> /// <param name = "orderString"> sorting rule </param> /// <param name = "pageCount"> outgoing parameters: total page count </param> /// <param name = "recordCount"> outgoing parameters: total record statistics </param> /// <returns> DataTable </returns> public DataTable ExecutePager (int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount) {if (pageIndex <1) pageIndex = 1; if (pageSize <1) pageSize = 10; if (string. isNullOrEmpty (showString) showString = "*"; if (string. isNullOrEmpty (orderString) orderString = strKey + "asc"; using (OleDbConnection m_Conn = new OleDbConnection (StringConnection) {m_Conn.Open (); string myVw = string. format ("({0}) tempVw", queryString); OleDbCommand success COUNT = new OleDbCommand (string. format ("select count (*) as recordCount from {0} {1}", myVw, whereString), m_Conn); recordCount = Convert. toInt32 (distinct count. executeScalar (); if (recordCount % pageSize)> 0) pageCount = recordCount/pageSize + 1; else pageCount = recordCount/pageSize; OleDbCommand cmdRecord; if (pageIndex = 1) // page 1 {cmdRecord = new OleDbCommand (string. format ("select top {0} {1} from {2} {3} order by {4}", pageSize, showString, myVw, whereString, orderString), m_Conn );} else if (pageIndex> pageCount) // exceeds the total number of pages {cmdRecord = new OleDbCommand (string. format ("select top {0} {1} from {2} {3} order by {4}", pageSize, showString, myVw, "where 1 = 2 ", orderString), m_Conn);} else {int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound-pageSize; string recordIDs = recordID (string. format ("select top {0} {1} from {2} {3} order by {4}", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound ); cmdRecord = new OleDbCommand (string. format ("select {0} from {1} where {2} in ({3}) order by {4}", showString, myVw, strKey, recordIDs, orderString ), m_Conn);} OleDbDataAdapter dataAdapter = new OleDbDataAdapter (cmdRecord); DataTable dt = new DataTable (); dataAdapter. fill (dt); m_Conn.Close (); m_Conn.Dispose (); return dt ;}}}}

Data source:

/// Refresh the data source /// </summary> /// <param name = "sender"> </param> /// <param name = "e"> </ param> protected void StoreProject_RefreshData (object sender, storeRefreshDataEventArgs e) {DateTime startdate; DateTime enddate; startdate = Convert. toDateTime (this. dfstartdate. text. trim (); enddate = Convert. toDateTime (this. dfenddate. text. trim (); int PageSize = this. pagingToolbar1.PageSize; // obtain the PagingToolBar currently on the page The PageSize value of int Count = 0; int CurPage = e. start/PageSize + 1; // obtain the current page number, that is, the page number StringBuilder strShow = new StringBuilder (); strShow. append ("ID, Project No., [Project No.-Mark No.], mark no., application date, product model, sample quantity, customer name 1, customer No., salesman, sample designer "); //, the maximum number of days for purchase reply strShow. append (", estimated completion time as, actual completion time as, actual completion time as, customer request delivery time, project score, customer product name 2, remarks, quote number, customer type, product type, product nature "); strShow. append (", whether to open the model, whether to charge, whether to perform authentication, mold fees, authentication fees, design reviewer, project category, Drawing Number, sample order number, project description, customer feedback, project Status, first order time, file Archive, product brand "); StringBuilder strSql = new StringBuilder (); strSql. append ("select ID, Project No., [Project No.-Mark No.], mark no., application date, product model, sample quantity, customer name 1, customer No., salesman, sample designer "); //, maximum daily strSql for purchase reply. append (", estimated completion time, actual completion time, customer request delivery time, project score, customer product name 2, remarks, quote number, customer type, product type, product nature "); strSql. append (", whether to open the model, whether to charge, whether to perform authentication, mold fees, authentication fees, design reviewer, project category, Drawing Number, sample order number, project description, customer feedback, project Status, initial time, file archiving, product brand "); strSql. append ("from Project Schedule where format (application date, 'yyyy-MM-dd')> = '" + string. fo Rmat ("{0: yyyy-MM-dd}", startdate) + "'"); strSql. append ("and format (application date, 'yyyy-MM-dd') <= '" + string. format ("{0: yyyy-MM-dd}", enddate) + "'"); StringBuilder strWhere = new StringBuilder (); strWhere. append ("where 1 = 1"); if (txtCustomerCode. text. trim ()! = "") {StrWhere. append ("and customer number like '" + txtmermercode. text. trim () + "'");} if (this. cbxCustomerType. selectedItem. text. trim ()! = "") {StrWhere. append ("and customer type like '" + cbxCustomerType. selectedItem. text. trim () + "'");} if (this. cbxProductType. selectedItem. text. trim ()! = "") {StrWhere. append ("and product type like '" + cbxProductType. selectedItem. text. trim () + "'");} if (this. cbxProjectStatus. selectedItem. text. trim ()! = "") {StrWhere. append ("and project status like '" + cbxProjectStatus. selectedItem. text. trim () + "'");} int pagecount = 0; // total number of pages = PageSize var store = this. gpProject. getStore (); DBCon db = new DBCon ("ZMProject"); AccessPage page = new AccessPage (); page. stringConnection = db. stringConnection (); DataTable dt = page. executePager (CurPage, PageSize, "ID", strShow. toString (), strSql. toString (), strWhere. toStri Ng (), "application date DESC", out pagecount, out Count); e. total = Count; if (Count> 0) {store. dataSource = dt; // bind Data store. dataBind ();} else {X. msg. alert ("query result", "<font style = 'color: red; '> no data found! </Font> "). Show ();}}

Display result: