MYSQL- 分頁預存程序

標籤：

　　工作需要，用到MYSQL的分頁功能，在網上找到一個不錯的分頁預存程序，代碼整理了一下！

　　預存程序代碼

CREATE PROCEDURE `sp_hj_splitpage`( in _pagecurrent int,/*當前頁*/ in _pagesize int,/*每頁的記錄數*/ in _ifelse varchar(1000),/*顯示欄位*/ in _where varchar(1000),/*條件*/ in _order varchar(1000)/*排序*/)COMMENT ‘分頁預存程序‘BEGIN if _pagesize<=1 then   set _pagesize=20; end if; if _pagecurrent < 1 then   set _pagecurrent = 1;  end if;  set @strsql = concat(‘select ‘,_ifelse,‘ from ‘,_where,‘ ‘,_order,‘ limit ‘,_pagecurrent*_pagesize-_pagesize,‘,‘,_pagesize);  prepare stmtsql from @strsql;  execute stmtsql;  deallocate prepare stmtsql;  set @strsqlcount=concat(‘select count(1) as count from ‘,_where);/*count(1) 這個欄位最好是主鍵*/ prepare stmtsqlcount from @strsqlcount;  execute stmtsqlcount;  deallocate prepare stmtsqlcount; END

　　

　　調用樣本：

call sp_hj_splitpage(1,3,‘*‘,‘hj_shangpin_cbj where 1=1‘,‘order by id desc‘);

 

　　C#調用樣本：

#region 分頁預存程序        /// <summary>        /// 分頁預存程序        /// </summary>        /// <param name="table">表,可以關聯：如 A left join B on A.id=B.Aid </param>        /// <param name="fileds">欄位值，擷取全部欄位用"*"</param>        /// <param name="order">按什麼欄位排序</param>        /// <param name="orderType">排序的方式 有ASC和DESC兩種</param>        /// <param name="pageIndex">頁索引</param>        /// <param name="pageSize">頁大小</param>        /// <param name="strWhere">查詢條件,如不查可空 ""</param>        /// <returns>返回一個DataTable</returns>        public static DataTable GetSplitPageList(string table, string fileds, string order, string orderType, int pageSize, int pageIndex, string strWhere)        {            using (MySqlConnection conn = new MySqlConnection(connectionString))            {                string strCmd = 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 = new MySqlCommand(strCmd, conn);                DataTable dt = new DataTable();                MySqlDataReader dr = cmd.ExecuteReader();                dt.Load(dr);                return dt;            }        }        /// <summary>        /// 獲得分頁總數        /// </summary>        /// <param name="table"></param>        /// <param name="strWhere"></param>        /// <returns></returns>        public static int GetSplitPageListCount(string table, string strWhere)        {            using (MySqlConnection conn = new MySqlConnection(connectionString))            {                conn.Open();                string strSql = "select count(*) from " + table + " where " + strWhere;                MySqlCommand cmd = new MySqlCommand(strSql, conn);                return int.Parse(cmd.ExecuteScalar().ToString());            }        }        #endregion

 

 

原文網址：http://blog.csdn.net/jxncwzb/article/details/2883467

 

MYSQL- 分頁預存程序