.net中使用oracle資料庫分頁小技巧

近日公司一網站專案，要呼叫其它系統(call center系統)的oracle資料庫資料，只能連線查詢，無法建立儲存過程，所以只能在sql語句上動腦筋實現分頁：

/// 

        /// Oracle通用分頁查詢函式 by 菩提樹下的楊過 2010-01-07
        /// 

        /// 表名
        /// (要查詢的)欄位列表
        /// 查詢條件(不帶where)
        /// 排序欄位
        /// 分頁大小
        /// 頁索引
        /// (輸出引數)記錄總數
        /// (輸出引數)分頁總數
        /// 
        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 
                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