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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.