.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