Best compatible with multiple databases universal and efficient big data paging function

General Rights Management System has a general-purpose paging query function, which can realize a variety of database query, support multi-table related paging query, is currently the most complete paging function implementation.

The following code is the method used:

///////////////////////////////

Background Code 1

///////////////////////////////

Background Code 2

///////////////////////////////

Background Code 3

///////////////////////////////

Background Code 4

///////////////////////////////

Background Code 5

///////////////////////////////

Page background Code Implementation reference:

    Public partial class Tabsite:authbasepage {//<summary>/////Use auspicious Galaton to implement paged query with the underlying function of the Rights management system Support multi-table joint related paging query//////<author>//<name> Song Biao </name>//<dat        e>2014.08.07</date>//</author>//</summary>//<summary>        Page number//</summary> protected int pageno = 1;        <summary>///page capacity///</summary> protected int pageSize = 10;        <summary>///Query Main Table///</summary> protected string tableName = "UserInfo A";        <summary>///Total Records///</summary> protected int totalrows; <summary>///Sort//</summary> protected string sort = basepage.requeststring ("Sort"        , "Site_code"); <summary>///direction///</summary> protectedString direction = basepage.requeststring ("direction", "ASC"); <summary>////Search keywords///</summary> protected string searchkey = RequestString ("Search        Key "); <summary>///Output mode///</summary> protected string outputmode = RequestString        Mode "," pagerlist "); protected void Page_Load (object sender, EventArgs e) {list<keyvaluepair<string, object>> db            Parameters = new list<keyvaluepair<string, object>> ();            list<string> listwhere = new list<string> (); string conditions = string.            Empty;            Specific sort string by order = "B." + sort + "" + direction;            Output field control string Selectfield = "b.code,b.name,b.area_name,b.type,-1 as DISTANCE";            String connectionString = confighelper.getconfigstring ("Connectionstringweb");  Idbhelper dbhelper = new Oraclehelper (connectionString);          TableName = "UserInfo A left JOIN usercontact B on a.id = B.uid";            = "B." + sort + "" + direction; if (!string.                Isnullorwhitespace (Searchkey)) {string searchkeytmp = Searchkey; if (Searchkey.indexof ("%") < 0) {searchkeytmp = string.                Format ("%{0}%", Searchkey); } listwhere.add ("(B.site_code like" + Dbhelper.getparameter ("Searchkey") + "or b.site_name like" + Dbhel Per.                GetParameter ("Searchkey") + "or b.manager like" + Dbhelper.getparameter ("Searchkey") + ")";            Dbparameters.add (New keyvaluepair<string, object> ("Searchkey", searchkeytmp)); } if (Listwhere.count > 0) {conditions = string.            Join ("and", Listwhere.toarray ()); } if (string.     Equals (Outputmode, "Pagerlist", StringComparison.OrdinalIgnoreCase)) {//page paging data            PageNo = Basepage.requestint32 ("PageNo", 1);                PageSize = Basepage.requestint32 ("PageSize", 10); DataTable dt = Commonmanager.getdatatablebypage (DBHelper, out Totalrows, TableName, Selectfield, PageNo, PageSize,                Conditions, dbparameters, by-and-by); DataTable dt = Dblogic.getdatatablebypage (DBHelper, out Totalrows, TableName, Selectfield, PageNo, pageSize, conditions,                Dbparameters);                This.dataList.DataSource = DT;            This.dataList.DataBind (); } else if (string.  Equals (Outputmode, "Dropdownjson", StringComparison.OrdinalIgnoreCase)) {Response.ContentType =                "Application/json";                Drop-down data Selectfield = "quote_name as \" Key\ ", quote_id as \" Value\ "";                DataTable Dtresult = dblogic.getdatatable (DBHelper, TableName, dbparameters, conditions, 0, by-and-Selectfield); Commonmanager.getdatatable (dbhelpEr, tableName, dbparameters, conditions, 0, Selectfield);                StringBuilder jsonstring = new StringBuilder ();                Jsonstring.append ("{\" list\ ":" + Datatablehelper.datatable2json (dtresult) + ",");                span = Datetime.now-begin; Jsonstring.append ("\" span\ ": \" "+ span.)                TotalMilliseconds + "\" ");                Jsonstring.append ("}");                Response.Write (jsonstring);            Response.End (); } else if (string. Equals (Outputmode, "Gridjson", StringComparison.OrdinalIgnoreCase)) {Response.ContentType = "AP                Plication/json ";                Paging data for grid PageNo = RequestInt32 ("Pager.pageno", 1);                PageSize = RequestInt32 ("Pager.pagesize", 10); DataTable dt = Commonmanager.getdatatablebypage (DBHelper, out Totalrows, TableName, Selectfield, PageNo, PageSize,                Conditions, dbparameters, by-and-by); DataTable dt = dblogic.getdAtatablebypage (DBHelper, out Totalrows, TableName, Selectfield, PageNo, pageSize, conditions, dbparameters, by-and-by);                StringBuilder jsonstring = new StringBuilder (); if (!string. Isnullorwhitespace (requeststring ("openFIRST")) && string. Equals ("1", RequestString ("openFIRST"), StringComparison.OrdinalIgnoreCase)) {Jsonstrin                G.append ("{\" rows\ ":" + datatablehelper.datatable2json (dt, true) + ","); } else {jsonstring.append ("{\" rows\ ":" + Datatablehelper.datatable2json (                DT) + ",");                } jsonstring.append ("\" pager.totalrows\ ": \" "+ totalrows +" \ ",");                span = Datetime.now-begin;                Jsonstring.append ("\" sort\ ": \" "+ sort +" \ ",");                Jsonstring.append ("\" direction\ ": \" "+ direction +" \ ","); Jsonstring.append ("\" span\ ": \" "+ span.)              TotalMilliseconds + "\" ");//query time-consuming milliseconds  Jsonstring.append ("}");                Response.Write (jsonstring);            Response.End ();                } else {Response.Write ("This page needs to pass in the Outputmode parameter");            Response.End (); }        }    }

///////////////////////////////

Paging function call Code

        <summary>///GIZHGA Get Paging data (anti-injection function)///Song Biao 2014-06-25 construction list<keyvaluepair<string, OB Ject>> than idbdataparameter[] convenient some//Dbhelper.makeparameters (dbparameters)-"idbdataparameter["///</ summary>//<param name= "RecordCount" > Number of records </param>//<param name= "DBHelper" >dbhelper </param>//<param name= "TableName" > Data source table name </param>//<param name= "Selectfield" > Selection Fields </param>///<param Name= "PageIndex" > Current page </param>//<param name= "PageSize" > How many pages are displayed per page Bar </param>//<param name= "conditions" > Query conditions </param>//<param name= "Dbparameters" > Check        Inquiry parameter </param>//<param name= "ORDER By" > Sort field </param>//<returns> datasheet </returns> public static DataTable Getdatatablebypage (Idbhelper dbhelper, out int recordCount, string tableName, string Select Field, int pageIndex, inT pageSize, string conditions, list<keyvaluepair<string, object>> dbparameters, string by) {            DataTable result = null;            RecordCount = 0; if (null! = DBHelper) {RecordCount = Dblogic.getcount (DBHelper, tableName, conditions, DBHelper .                Makeparameters (dbparameters)); result = Dblogic.getdatatablebypage (DBHelper, TableName, Selectfield, PageIndex, pageSize, conditions,            Dbhelper.makeparameters (dbparameters);        } return result; }

///////////////////////////////

The bottom paging function is realized and can be viewed through the source code

        <summary>///Oracle gets paging data (anti-injection) compatible with multiple databases//</summary>//<param name= "DBHelper" > Database connection </param>//<param name= "TableName" > Data source table name </param>///<param Name= "Selectfield" > select field </param>//<param name= "PageIndex" > Current page </param>//<param name= " PageSize "> How many bars per page </param>//<param name=" conditions "> Query conditions </param>//<param name= "Dbparameters" > Query parameters </param>//<param name= "ORDER By" > Sort field </param>//<returns> number According to table </returns> public static DataTable getdatatablebypage (Idbhelper dbhelper, String tableName, String Selectfi eld, int pageIndex, int pageSize, string conditions, idbdataparameter[] dbparameters, string by, string currentindex = null) {String Sqlstart = ((pageIndex-1) * pageSize).            ToString (); String sqlend = (PageIndex * pageSize). ToString (); if (Currentindex = = null) {Currentindex = string.            Empty; } if (!string.            IsNullOrEmpty (conditions)) {conditions = "WHERE" + conditions; } string sqlquery = String.            Empty; if (Dbhelper.currentdbtype = = currentdbtype.oracle) {if (!string.                IsNullOrEmpty (Orderby.trim ())) {ORDER by ' + by '; } sqlquery = string. Format ("Select * from" (select ROWNUM RN, h.* from ((select + Currentindex + "+ selectfield+" from {0} {1} {2})) Z WHE            RE Z.rn <={3} and Z.rn >{4} ", tableName, conditions, as-is, sqlend, Sqlstart); } else if (Dbhelper.currentdbtype = = currentdbtype.sqlserver) {sqlquery = string. Format ("Select * from" (select Row_number () over (ORDER by {0}) as RowIndex, "+ Selectfield +" from {1} {2}) as Pagetable WHere RowIndex between {3} and {4} ", by, tableName, conditions, Sqlstart, sqlend); } else if (Dbhelper.currentdbtype = = Currentdbtype.mysql | | dbhelper.currentdbtype = = CURRENTDBT Ype. SQLite) {sqlquery = string. Format ("Select {0} from {1} {2} ' ORDER by {3}" LIMIT {4}, {5} ", Selectfield, tableName, conditions, as-is, Sqlstart, PageS            ize);            } var dt = new DataTable (tableName); if (dbparameters! = null && dbparameters.length > 0) {dt = Dbhelper.fill (sqlquery, D            Bparameters);            } else {dt = Dbhelper.fill (sqlquery);        } return DT; }

This paging function can be compatible with a variety of databases, multi-table association queries