C# Oracle 分页方法

方法很简单的 就不多说了.

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace Zgke.Data
{
    public class ConnOra
    {
        /// <summary>
        /// 连接语句
        /// </summary>
        public static OracleConnection OracleConn = new OracleConnection("Data Source=???;User ID=???;Password=???;Unicode=True");

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="p_SqlSelect">查询语句 Select * from 表</param>
        /// <param name="p_KeyColumn">主键</param>
        /// <param name="p_Order">排序方式 true:ASC false DESC</param>
        /// <param name="p_PageSize">页大小</param>      
        /// <param name="p_PageIndex">页索引  1为开始索引</param>
        /// <param name="p_RowCount">返回行数</param>     
        /// <param name="p_ErrorMessage">返回错误信息</param>  
        /// <returns>分页后结果，如果查询错误返回结果为new DataTable()</returns>
        public static DataTable TablePage(string p_SqlSelect, string p_KeyColumn, bool p_Order, int p_PageSize, int p_PageIndex, ref int p_RowCount, ref string p_ErrorMessage)
        {
            DataTable _Table = new DataTable();
            try
            {
                OracleDataAdapter _Adapter = new OracleDataAdapter("SELECT Count(*) FROM (" + p_SqlSelect + ")", OracleConn);
                _Adapter.Fill(_Table);
                p_RowCount = int.Parse(_Table.Rows[0][0].ToString());
                _Table.Clear();
                _Adapter.Dispose();
                int _StarRowIndex = (p_PageIndex - 1) * p_PageSize + 1;
                int _EndRowIndex = _StarRowIndex + p_PageSize;
                string _SelectCommand = "SELECT * FROM (SELECT a.*, row_number() over(ORDER BY " + p_KeyColumn + " " + (p_Order ? "ASC" : "DESC") + ") rn FROM (" + p_SqlSelect + ") a) WHERE rn BETWEEN " + _StarRowIndex.ToString() + " AND " + _EndRowIndex.ToString();
                OracleDataAdapter _AdapterData = new OracleDataAdapter(_SelectCommand, OracleConn);
                _AdapterData.Fill(_Table);
                _AdapterData.Dispose();
            }
            catch (OracleException _Ex)
            {
                p_ErrorMessage = _Ex.Message;
            }
            return _Table;
        }
    }
}