最好用的相容多種資料庫通用高效的大資料分頁功能

標籤：style   blog   http   color   使用   os   io   strong   

通用許可權管理系統底層有一個通用分頁查詢功能，該功能可實現多種資料庫的查詢，支援多表關聯分頁查詢，目前是最完善的分頁功能實現。

下面代碼是使用的方法：

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

後台代碼1

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

後台代碼2

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

後台代碼3

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

後台代碼4

 

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

 

後台代碼5

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

頁面後台代碼實現參考：

    public partial class TabSite : AuthBasePage    {        /// <summary>        /// 使用吉日嘎拉通用許可權管理系統底層功能實現的分頁查詢        /// 支援多表聯合關聯分頁查詢        ///         /// <author>        ///     <name>宋彪</name>        ///     <date>2014.08.07</date>        /// </author>           /// </summary>             /// <summary>        /// 頁碼        /// </summary>        protected int pageNo = 1;        /// <summary>        /// 頁容量        /// </summary>        protected int pageSize = 10;        /// <summary>        /// 查詢主表        /// </summary>        protected string tableName = "UserInfo A";        /// <summary>        /// 總記錄        /// </summary>        protected int totalRows;        /// <summary>        /// 排序        /// </summary>        protected string sort = BasePage.RequestString("sort", "SITE_CODE");        /// <summary>        /// 排序方向        /// </summary>        protected string direction = BasePage.RequestString("direction", "asc");        /// <summary>        /// 查詢關鍵詞        /// </summary>        protected string searchKey = RequestString("searchKey");        /// <summary>        /// 輸出模式        /// </summary>        protected string outPutMode = RequestString("outPutMode", "pagerlist");        protected void Page_Load(object sender, EventArgs e)        {            List<KeyValuePair<string, object>> dbParameters = new List<KeyValuePair<string, object>>();            List<string> listWhere = new List<string>();            string conditions = string.Empty;            //具體排序            string orderBy = " B." + sort + " " + direction;            //輸出欄位控制            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  ";            orderBy = " 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 " + dbHelper.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))            {                //頁面分頁資料                 pageNo = BasePage.RequestInt32("pageNo", 1);                pageSize = BasePage.RequestInt32("pageSize", 10);                //DataTable dt = CommonManager.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);                DataTable dt = DbLogic.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);                this.dataList.DataSource = dt;                this.dataList.DataBind();            }            else if (string.Equals(outPutMode, "dropdownjson", StringComparison.OrdinalIgnoreCase))            {                Response.ContentType = "application/json";                //下拉資料                selectField = " QUOTE_NAME as \"key\",QUOTE_ID as \"value\" ";                DataTable dtResult = DbLogic.GetDataTable(dbHelper, tableName, dbParameters, conditions, 0, orderBy, selectField);                //CommonManager.GetDataTable(dbHelper, tableName, dbParameters, conditions, 0, orderBy, 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 = "application/json";                //grid的分頁資料                pageNo = RequestInt32("pager.pageNo", 1);                pageSize = RequestInt32("pager.pageSize", 10);                //DataTable dt = CommonManager.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);                DataTable dt = DbLogic.GetDataTableByPage(dbHelper, out totalRows, tableName, selectField, pageNo, pageSize, conditions, dbParameters, orderBy);                StringBuilder jsonString = new StringBuilder();                if (!string.IsNullOrWhiteSpace(RequestString("openFirst")) && string.Equals("1", RequestString("openFirst"), StringComparison.OrdinalIgnoreCase))                {                    jsonString.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 + "\"");//查詢耗時 毫秒數                jsonString.Append("}");                Response.Write(jsonString);                Response.End();            }            else            {                Response.Write("本頁面需要傳入outPutMode參數");                Response.End();            }        }    }

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

分頁功能調用代碼

        /// <summary>        /// 吉日嘎拉 擷取分頁資料（防注入功能的）         /// 宋彪  2014-06-25 構造List<KeyValuePair<string, object>>比IDbDataParameter[]方便一些        /// dbHelper.MakeParameters(dbParameters)--》IDbDataParameter[]        /// </summary>        /// <param name="recordCount">記錄條數</param>        /// <param name="dbHelper">dbHelper</param>        /// <param name="tableName">資料來源表名</param>        /// <param name="selectField">選擇欄位</param>        /// <param name="pageIndex">當前頁</param>        /// <param name="pageSize">每頁顯示多少條</param>        /// <param name="conditions">查詢條件</param>        /// <param name="dbParameters">查詢參數</param>        /// <param name="orderBy">排序欄位</param>        /// <returns>資料表</returns>        public static DataTable GetDataTableByPage(IDbHelper dbHelper, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, List<KeyValuePair<string, object>> dbParameters, string orderBy)        {            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), orderBy);            }            return result;        }

 

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

底層分頁功能實現，可通過源碼查看

        /// <summary>        /// Oracle 擷取分頁資料（防注入功能的）相容多種資料庫        /// </summary>        /// <param name="dbHelper">資料庫連接</param>        /// <param name="tableName">資料來源表名</param>        /// <param name="selectField">選擇欄位</param>        /// <param name="pageIndex">當前頁</param>        /// <param name="pageSize">每頁顯示多少條</param>        /// <param name="conditions">查詢條件</param>        /// <param name="dbParameters">查詢參數</param>        /// <param name="orderBy">排序欄位</param>        /// <returns>資料表</returns>        public static DataTable GetDataTableByPage(IDbHelper dbHelper, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy, 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()))                {                    orderBy = " ORDER BY " + orderBy;                }                sqlQuery = string.Format("SELECT * FROM(SELECT ROWNUM RN, H.* FROM ((SELECT " + currentIndex +" "+ selectField+" FROM {0} {1} {2} )H)) Z WHERE Z.RN <={3} AND Z.RN >{4}"    , tableName, conditions, orderBy, 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}"                    , orderBy, tableName, conditions, sqlStart, sqlEnd);            }            else if (dbHelper.CurrentDbType == CurrentDbType.MySql                || dbHelper.CurrentDbType == CurrentDbType.SQLite)            {                sqlQuery = string.Format("SELECT {0} FROM {1} {2} ORDER BY {3} LIMIT {4}, {5}", selectField, tableName, conditions, orderBy, sqlStart, pageSize);            }                        var dt = new DataTable(tableName);            if (dbParameters != null && dbParameters.Length > 0)            {                dt = dbHelper.Fill(sqlQuery, dbParameters);            }            else            {                dt = dbHelper.Fill(sqlQuery);            }            return dt;        }

 

這個分頁功能可以相容多種資料庫，多表關聯查詢