.net中使用oracle資料庫分頁的土辦法

近日公司一網站項目，要調用其它系統(call center系統)的oracle資料庫資料，只能串連查詢，無法建立預存程序，所以只能在sql語句上動腦筋實現分頁：

/// <summary>
        /// Oracle通用分頁查詢函數 by 菩提樹下的楊過 2010-01-07
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fields">(要查詢的)欄位列表</param>
        /// <param name="sqlWhere">查詢條件(不帶where)</param>
        /// <param name="orderFields">排序欄位</param>
        /// <param name="pageSize">分頁大小</param>
        /// <param name="pageIndex">頁索引</param>
        /// <param name="recordCount">(輸出參數)記錄總數</param>
        /// <param name="pageCount">(輸出參數)分頁總數</param>
        /// <returns></returns>
        public DataSet QueryOracle(string tableName, string fields, string sqlWhere, string orderFields, int pageSize, int pageIndex, out int recordCount, out int pageCount)
        {
            if (string.IsNullOrEmpty(sqlWhere)) { sqlWhere = "1=1"; }
            DataSet ds = null;
            EnLib.Database _db = null;
            recordCount = 0;
            pageCount = 0;
            try
            {
                _db = EnLib.DatabaseFactory.CreateDatabase();
                //先計算總記錄數
                string sql = string.Format("select Count(*) from {0} where {1}", tableName, sqlWhere);
                recordCount = int.Parse(_db.ExecuteScalar(CommandType.Text, sql).ToString());

                //再確定總頁數
                if (recordCount % pageSize == 0)
                {
                    pageCount = recordCount / pageSize;
                }
                else
                {
                    pageCount = recordCount / pageSize + 1;
                }

                if (pageIndex <= 0) { pageIndex = 1; }

                if (pageIndex > pageCount) { pageIndex = pageCount; }

                //確定起始點
                int _start = pageSize * (pageIndex - 1) + 1;

                int _end = _start + pageSize - 1;

                if (_end > recordCount) { _end = recordCount; }

                //如果fields為*，則查詢所有欄位列表
                if (fields == "*")
                {
                    sql = "select table_name,column_name from user_tab_columns where table_name='" + tableName.ToUpper().Trim() + "'";
                    IDataReader dr = _db.ExecuteReader(CommandType.Text, sql);
                    string _allFields = "";
                    while (dr.Read())
                    {
                        _allFields += dr[1] + ",";
                    }
                    dr.Close();
                    fields = _allFields.Trim(',');
                    if (orderFields.Length == 0) 
                    {
                        orderFields = fields.Split(',')[0];
                    }
                }

                if (orderFields.Length == 0)
                {
                    orderFields = fields.Split(',')[0];
                }

                //構造sql語句
                sql = string.Format("select RecordID,{0} from (select rownum RecordID,{0} from (select {0} from {1} where {2} order by {3})) where RecordID between {4} and {5}", fields, tableName, sqlWhere, orderFields, _start, _end);

                ds = _db.ExecuteDataSet(CommandType.Text, sql);
            }
            catch
            {

            }            

            return ds;
        }

 

   

注:用到了微軟的企業庫EnLib