Oracle.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

相关文章