dataset访问Oraclel数据库

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

namespace Best.Biz.Repository.Base
{
    /// <summary>
    /// 数据库访问类
    /// </summary>
    public static class OracleDbHelper
    {
        /// <summary>
        /// 默认数据库连接字符串，在程序配置文件中的ConnectionStrings段定义，名称是OracleDbConnectionString
        /// </summary>
        private static readonly string dbConnectionString = ConfigurationManager.ConnectionStrings["OracleDbConnectionString"].ToString();

        /// <summary>
        /// 用指定的连接字符串执行SQL，返回DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string sql, string connectionString)
        {
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
                {
                    DataSet ds = new DataSet();
                    oda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行SQL，返回DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string sql)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
                {
                    DataSet ds = new DataSet();
                    oda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand())
                {
                    PrepareCommand(oraCmd, oraConn, null, sql, parameters);
                    using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
                    {
                        DataSet ds = new DataSet();
                        oda.Fill(ds, "ds");
                        oraCmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parametersCollection">参数集合</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql, OracleParameterCollection parametersCollection)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand())
                {
                    PrepareCommand(oraCmd, oraConn, null, sql, parametersCollection);
                    using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
                    {
                        DataSet ds = new DataSet();
                        oda.Fill(ds, "ds");
                        oraCmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }

        public static int ExecuteSqlCount(string sql, OracleParameterCollection parametersCollection)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    PrepareCommand(oraCmd, oraConn, null, sql, parametersCollection);
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    return oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行SQL语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        public static void ExecuteSql(string sql, params OracleParameter[] parameters)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    PrepareCommand(oraCmd, oraConn, null, sql, parameters);
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行SQL
        /// </summary>
        /// <param name="sql">SQL语句</param>
        public static void ExecuteSql(string sql)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用指定连接字符串执行SQL
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        public static void ExecuteSql(string sql, string connectionString)
        {
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用指定连接字符串执行SQL
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="parameters">参数</param>
        public static void ExecuteSql(string sql, string connectionString, params OracleParameter[] parameters)
        {
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    PrepareCommand(oraCmd, oraConn, null, sql, parameters);
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用指定连接字符串执行SQL，返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteSqlCount(string sql, string connectionString)
        {
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    return oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行SQL，返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteSqlCount(string sql)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
                {
                    if (oraConn.State != ConnectionState.Open)
                        oraConn.Open();
                    return oraCmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用默认连接字符串执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="params">参数列表</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlCount(string sql, params OracleParameter[] @params)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand())
                {
                    PrepareCommand(oraCmd, oraConn, null, sql, @params);
                    int affectedRows = oraCmd.ExecuteNonQuery();
                    oraCmd.Parameters.Clear();
                    return affectedRows;
                }
            }
        }

        /// <summary>
        /// 为准备执行的命令绑定参数
        /// </summary>
        /// <param name="cmd">OracleCommand</param>
        /// <param name="conn">OracleConnection</param>
        /// <param name="trx">OracleTransaction</param>
        /// <param name="cmdText">要执行的命令</param>
        /// <param name="parameters">参数数组</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trx, string cmdText, OracleParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trx != null)
                cmd.Transaction = trx;
            cmd.CommandType = CommandType.Text;
            if (parameters != null)
            {
                foreach (OracleParameter param in parameters)
                {
                    if (param.Value == null)
                    {
                        param.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(param);
                }
            }
        }

        /// <summary>
        /// 为准备执行的命令绑定参数
        /// </summary>
        /// <param name="cmd">OracleCommand</param>
        /// <param name="conn">OracleConnection</param>
        /// <param name="trx">OracleTransaction</param>
        /// <param name="cmdText">要执行的命令</param>
        /// <param name="parametersCollection">参数集合</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trx, string cmdText, OracleParameterCollection parametersCollection)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trx != null)
                cmd.Transaction = trx;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (parametersCollection != null)
            {
                foreach (OracleParameter param in parametersCollection)
                {
                    if (param.Value == null)
                    {
                        param.Value = DBNull.Value;
                    }
                    //赋值，避免"The OracleParameter is already contained by another OracleParameterCollection. "错误
                    if (param.Direction != ParameterDirection.Output)
                        cmd.Parameters.Add(param.ParameterName, param.OracleType, param.Size).Value = param.Value;
                    else
                        cmd.Parameters.Add(param.ParameterName, param.OracleType, param.Size).Direction = param.Direction;
                }
            }
        }

        /// <summary>
        /// 使用Oracle的特性进行分页，使用默认连接字符串
        /// </summary>
        /// <param name="tableName">查询的表名</param>
        /// <param name="condition">查询条件</param>
        /// <param name="selectColumns">查询的列</param>
        /// <param name="size">分页大小</param>
        /// <param name="offset">开始位置</param>
        /// <param name="sortDirection">排序方式</param>
        /// <param name="sortColumn">排序列</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecutePagedResult(string tableName, string condition, string selectColumns, int size, int offset, string sortDirection, string sortColumn)
        {
            offset = offset / size;
            offset += 1;
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            sql.Append(selectColumns);
            sql.Append(" from ");
            sql.Append(tableName);
            sql.Append(" where rowid in (select rid from (select rownum rn,rid from(select rowid rid,");
            sql.Append(sortColumn);
            sql.Append(" from ");
            sql.Append(tableName);
            sql.Append(" where 1=1 ");
            sql.Append(condition);
            sql.Append(" order by ");
            sql.Append(sortColumn);
            sql.Append(" ");
            sql.Append(sortDirection);
            sql.Append(") where rownum<=");
            sql.Append(offset * size);
            sql.Append(") where rn>=");
            sql.Append(((offset - 1) * size + 1));
            sql.Append(") order by ");
            sql.Append(sortColumn);
            sql.Append(" ");
            sql.Append(sortDirection);
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleDataAdapter oda = new OracleDataAdapter(sql.ToString(), oraConn))
                {
                    DataSet ds = new DataSet();
                    oda.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 普通分页方法，使用默认连接字符串
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="size"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static DataSet ExecutePagedResult(string sql, int size, int pageIndex)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                using (OracleDataAdapter oda = new OracleDataAdapter(sql, oraConn))
                {
                    DataSet ds = new DataSet();
                    oda.Fill(ds, size * pageIndex, size, "PagedTable");
                    return ds;
                }
            }
        }

        /// <summary>
        /// 使用默认连接字符串执行SQL，返回指定字段的值
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dataField">要返回的字段名</param>
        /// <returns>指定字段的值</returns>
        public static string ExecuteDataField(string sql, string dataField)
        {
            DataSet ds = new DataSet();
            using (OracleConnection oConn = new OracleConnection(dbConnectionString))
            {
                using (OracleDataAdapter da = new OracleDataAdapter(sql, oConn))
                {
                    da.Fill(ds);
                }
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
                {
                    return ds.Tables[0].Rows[0][dataField].ToString();
                }
                return null;
            }
            return null;
        }

        /// <summary>
        /// 使用默认连接字符串执行SQL，返回指定字段的值
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dataField">要返回的字段名</param>
        /// <param name="parametersCollection">参数集合</param>
        /// <returns>指定字段的值</returns>
        public static string ExecuteDataField(string sql, string dataField, OracleParameterCollection parametersCollection)
        {
            DataSet ds = new DataSet();
            using (OracleConnection oConn = new OracleConnection(dbConnectionString))
            {
                using (OracleCommand oraCmd = new OracleCommand())
                {
                    PrepareCommand(oraCmd, oConn, null, sql, parametersCollection);
                    using (OracleDataAdapter oda = new OracleDataAdapter(oraCmd))
                    {
                        oda.Fill(ds, "ds");
                        oraCmd.Parameters.Clear();
                    }
                }
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
                {
                    return ds.Tables[0].Rows[0][dataField].ToString();
                }
                return null;
            }
            return null;
        }

        /// <summary>
        /// 使用指定连接字符串执行SQL，返回指定字段的值
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dataField">要返回的字段名</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>指定字段的值</returns>
        public static string ExecuteDataField(string sql, string dataField, string connectionString)
        {
            DataSet ds = new DataSet();
            using (OracleDataAdapter da = new OracleDataAdapter(sql, connectionString))
            {
                da.Fill(ds);
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0][dataField] != DBNull.Value)
                {
                    return ds.Tables[0].Rows[0][dataField].ToString();
                }
                return null;
            }
            return null;
        }

        /// <summary>
        /// 使用指定的连接字符串，返回符合查询条件的记录数
        /// </summary>
        /// <param name="tableName">要查询的表名</param>
        /// <param name="condition">查询条件</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>记录数</returns>
        public static int ExecuteCount(string tableName, string condition, string connectionString)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentOutOfRangeException("tableName", "TableName Can not be null or empty");
            }
            return int.Parse(ExecuteDataField("SELECT COUNT(*) COUNTER FROM " + tableName + " WHERE 1=1 " + condition, "COUNTER", connectionString));
        }

        /// <summary>
        /// 使用默认的连接字符串，返回符合查询条件的记录数
        /// </summary>
        /// <param name="tableName">要查询的表名</param>
        /// <param name="condition">查询条件</param>
        /// <returns>记录数</returns>
        public static int ExecuteCount(string tableName, string condition)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentOutOfRangeException("tableName", "TableName Can not be null or empty");
            }
            return int.Parse(ExecuteDataField("SELECT COUNT(*) COUNTER FROM " + tableName + " WHERE 1=1 " + condition, "COUNTER"));
        }

        /// <summary>
        /// 使用默认的连接字符串，返回Count Sql的查询结果。查询语句中COUNT(*)的别名必须是COUNTER。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>查询结果</returns>
        public static int ExecuteCount(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentOutOfRangeException("sql", "Sql Can not be null or empty");
            }
            if (!sql.Contains("COUNTER"))
            {
                throw new ArgumentOutOfRangeException("sql", "Sql must contains COUNTER");
            }
            return int.Parse(ExecuteDataField(sql, "COUNTER"));
        }

        /// <summary>
        /// 使用默认的连接字符串执行SQL，返回DataReader，注意用完之后必须关闭DataReader。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataReader</returns>
        public static OracleDataReader ExecuteDataReader(string sql)
        {
            OracleConnection oraConn = new OracleConnection(dbConnectionString);
            using (OracleCommand oraCmd = new OracleCommand(sql, oraConn))
            {
                if (oraConn.State != ConnectionState.Open)
                    oraConn.Open();
                OracleDataReader dr = oraCmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
        }

        /// <summary>
        /// 使用默认连接字符串执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlList">Sql List</param>        
        /// <returns>返回0成功，否则失败</returns>
        public static int ExecuteSqlTransaction(List<String> sqlList)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                if (oraConn.State != ConnectionState.Open)
                    oraConn.Open();
                OracleCommand cmd = new OracleCommand { Connection = oraConn };
                OracleTransaction tx = oraConn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string sql in sqlList)
                    {
                        if (!String.IsNullOrEmpty(sql))
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return 0;
                }
                catch
                {
                    tx.Rollback();
                    throw;
                }
            }
        }

        /// <summary>
        /// 使用默认连接字符串执行存储过程，返回DataReader ( 注意：调用该方法后，一定要对DataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>DataReader</returns>
        public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            OracleConnection oraConn = new OracleConnection(dbConnectionString);
            if (oraConn.State != ConnectionState.Open)
                oraConn.Open();
            OracleCommand command = BuildQueryCommand(oraConn, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            OracleDataReader returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        /// <summary>
        /// 使用默认连接字符串执行存储过程，返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                DataSet dataSet = new DataSet();
                if (oraConn.State != ConnectionState.Open)
                    oraConn.Open();
                OracleDataAdapter oda = new OracleDataAdapter
                {
                    SelectCommand = BuildQueryCommand(oraConn, storedProcName, parameters)
                };
                oda.Fill(dataSet, tableName);
                return dataSet;
            }
        }

        /// <summary>
        /// 使用默认连接字符串执行存储过程，指定超时时间，返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="timeOut">超时时间</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int timeOut)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                DataSet dataSet = new DataSet();
                if (oraConn.State != ConnectionState.Open)
                    oraConn.Open();
                OracleDataAdapter oda = new OracleDataAdapter
                {
                    SelectCommand = BuildQueryCommand(oraConn, storedProcName, parameters)
                };
                oda.SelectCommand.CommandTimeout = timeOut;
                oda.Fill(dataSet, tableName);
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand</returns>
        private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand oraCmd = new OracleCommand(storedProcName, connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            foreach (OracleParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    oraCmd.Parameters.Add(parameter);
                }
            }
            return oraCmd;
        }

        /// <summary>
        /// 使用默认连接字符串执行存储过程，返回影响的行数。
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                if (oraConn.State != ConnectionState.Open)
                    oraConn.Open();
                OracleCommand command = BuildIntCommand(oraConn, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                int result = (int)command.Parameters["ReturnValue"].Value;
                return result;
            }
        }

        /// <summary>
        /// 使用默认连接字符串执行存储过程，返回结果。
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>存储过程执行的结果</returns>
        public static string RunSimpleProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (OracleConnection oraConn = new OracleConnection(dbConnectionString))
            {
                if (oraConn.State != ConnectionState.Open)
                    oraConn.Open();
                OracleCommand command = BuildStringCommand(oraConn, storedProcName, parameters);
                command.ExecuteNonQuery();
                return command.Parameters["ReturnValue"].Value.ToString();
            }
        }

        /// <summary>
        /// 创建 OracleCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand 对象实例</returns>
        private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new OracleParameter("ReturnValue",
                                                       OracleType.Int32, 10, ParameterDirection.ReturnValue,
                                                       false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 创建 OracleCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="connection">连接字符串</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand 对象实例</returns>
        private static OracleCommand BuildStringCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new OracleParameter("ReturnValue",
                                                       OracleType.VarChar, 255, ParameterDirection.ReturnValue,
                                                       false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 使用默认连接字符串进行分页查询，并将总记录数放在DataSet中名字为RecCount的表中。
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="selectColumns">查询结果列</param>
        /// <param name="condition">查询条件</param>
        /// <param name="size">分页大小</param>
        /// <param name="pageIndex">查询页</param>
        /// <param name="sortColumns">排序字段</param>
        /// <param name="sortDirection">排序方向</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryWithCount(string tableName, string selectColumns, string condition, int size, int pageIndex, string sortColumns, string sortDirection)
        {
            string sql = "select " + selectColumns + " from " + tableName;
            if (condition != "")
            {
                sql += " where " + condition;
            }
            sql += " order by " + sortColumns + " " + sortDirection;
            using (OracleConnection oarConn = new OracleConnection(dbConnectionString))
            {
                using (OracleDataAdapter oda = new OracleDataAdapter(sql, oarConn))
                {
                    DataSet ds = new DataSet();
                    oda.Fill(ds, pageIndex * size, size, tableName);
                    sql = "select count(*) as COUNTER from " + tableName;
                    if (condition != "")
                    {
                        sql += " where " + condition;
                    }
                    oda.SelectCommand.CommandText = sql;
                    oda.Fill(ds, "RecCount");
                    return ds;
                }
            }
        }

        /// <summary>
        /// 使用默认连接字符串进行分页查询，并将总记录数放在DataSet中名字为RecCount的表中。
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="selectColumns">查询结果列</param>
        /// <param name="condition">查询条件</param>
        /// <param name="groupBy">Group字段</param>
        /// <param name="size">分页大小</param>
        /// <param name="pageIndex">查询页</param>
        /// <param name="sortColumns">排序字段</param>
        /// <param name="sortDirection">排序方向</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryWithCount(string tableName, string selectColumns, string condition, string groupBy, int size, int pageIndex, string sortColumns, string sortDirection)
        {
            string sql = "select " + selectColumns + " from " + tableName;
            if (condition != "")
            {
                sql += " where " + condition;
            }
            if (groupBy != "")
            {
                sql += " Group by " + groupBy;
            }
            sql += " order by " + sortColumns + " " + sortDirection;
            using (OracleConnection sConn = new OracleConnection(dbConnectionString))
            {
                using (OracleDataAdapter oda = new OracleDataAdapter(sql, sConn))
                {
                    DataSet ds = new DataSet();
                    oda.Fill(ds, pageIndex * size, size, tableName);

                    if (groupBy != "")
                    {
                        sql = "select count(*) as COUNTER from " + "(Select " + selectColumns + " from " + tableName +
                                 " where 1=1 and " + condition + " group by " + groupBy + ") a";
                    }
                    else
                    {
                        sql = "select count(*) as COUNTER from " + tableName;
                        if (condition != "")
                        {
                            sql += " where " + condition;
                        }
                    }

                    oda.SelectCommand.CommandText = sql;
                    oda.Fill(ds, "RecCount");
                    return ds;
                }
            }
        }
    }
}