mysql-Paging Stored Procedures

Work needs, the use of MySQL paging function, found on the internet a good paging stored procedures, the code to organize a bit!

　　Stored Procedure Code

CREATE PROCEDURE' Sp_hj_splitpage ' (inch_pagecurrentint,/*Current Page*/ inch_pagesizeint,/*number of records per page*/ inch_ifelsevarchar( +),/*Show Fields*/ inch_where varchar( +),/*conditions*/ inch_Order varchar( +)/*Sort*/) COMMENT'Paging Stored Procedures'BEGIN if_pagesize<=1  Then   Set_pagesize= -; End if; if_pagecurrent< 1  Then   Set_pagecurrent= 1; End if; Set @strsql =Concat'Select', _ifelse,' from',_where,' ',_Order,'Limit', _pagecurrent*_pagesize-_pagesize,',', _pagesize); PrepareStmtsql from @strsql; ExecuteStmtsql; deallocate PrepareStmtsql; Set @strsqlcount=Concat'Select COUNT (1) as Count from',_where);/*count (1) This field is preferably a primary key*/ PrepareStmtsqlcount from @strsqlcount; ExecuteStmtsqlcount; deallocate PrepareStmtsqlcount;END

　　

Invocation Example:

Call Sp_hj_splitpage (1,3,'*','hj_shangpin_cbj where 1=1  ','ORDER by id desc');

　C # Invocation Example:

#regionPaging Stored Procedures/// <summary>        ///Paging Stored Procedures/// </summary>        /// <param name= "table" >table, which can be associated with: as A left join B on A.id=b.aid</param>        /// <param name= "Fileds" >field value, get all fields with "*"</param>        /// <param name= "Order" >Sort by what field</param>        /// <param name= "OrderType" >Sort by ASC and DESC in two different ways</param>        /// <param name= "PageIndex" >Page Index</param>        /// <param name= "PageSize" >Page Size</param>        /// <param name= "strwhere" >query conditions, such as not to find out ""</param>        /// <returns>Returns a DataTable</returns>         Public StaticDataTable Getsplitpagelist (stringTablestringFileds,stringOrderstringOrderType,intPageSize,intPageIndex,stringstrwhere) {            using(Mysqlconnection conn =Newmysqlconnection (connectionString)) {                stringStrcmd =string. Format ("Call sp_hj_splitpage ({0},{1}, ' {2} ', ' {3} where {4} ', ' ORDER by {5} {6} ');", PageIndex, pageSize, fileds, table, strwhere, order, OrderType); Conn.                Open (); Mysqlcommand cmd=NewMysqlcommand (STRCMD, conn); DataTable DT=NewDataTable (); Mysqldatareader Dr=cmd.                ExecuteReader (); Dt.                Load (DR); returnDT; }        }        /// <summary>        ///get the total number of pages/// </summary>        /// <param name= "table" ></param>        /// <param name= "strwhere" ></param>        /// <returns></returns>         Public Static intGetsplitpagelistcount (stringTablestringstrwhere) {            using(Mysqlconnection conn =Newmysqlconnection (connectionString)) {Conn.                Open (); stringstrSQL ="Select COUNT (*) from"+ table +"where"+strwhere; Mysqlcommand cmd=NewMysqlcommand (strSQL, conn); return int. Parse (cmd. ExecuteScalar ().            ToString ()); }        }        #endregion

Original URL: http://blog.csdn.net/jxncwzb/article/details/2883467

mysql-Paging Stored Procedures