//using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;

namespace Common
{
    public abstract class OracleHelper
    {
        public static OracleConnection OraclePrepareConnection
        {
            get
            {
                return OracleHelper.PrepareConnection();
            }
        }

        public static OracleConnection Connection
        {
            get
            {
                return (OracleConnection)null;
            }
        }

        private OracleHelper()
        {
        }

        public static int ExecuteSql(string sql, OracleConnection x)
        {
            OracleConnection oracleConnection = OracleHelper.PrepareConnection();
            try
            {
                using (OracleCommand oracleCommand = new OracleCommand(sql, oracleConnection))
                    return ((DbCommand)oracleCommand).ExecuteNonQuery();
            }
            catch (OracleException ex)
            {
                LogHelper.Error("SQL={0} Message={1}", (object)sql, (object)((Exception)ex).Message);
                throw;
            }
            finally
            {
                ((DbConnection)oracleConnection).Close();
            }
        }




        public static void ExecuteSqlTran(ArrayList sqlList, OracleConnection x)
        {
            OracleConnection oracleConnection = OracleHelper.PrepareConnection();
            try
            {
                using (OracleCommand oracleCommand = new OracleCommand())
                {

                    oracleCommand.Connection = x;

                    OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();


                    oracleCommand.Transaction = oracleTransaction;

                    for (int index = 0; index < sqlList.Count; ++index)
                    {
                        string str = sqlList[index].ToString();
                        try
                        {
                            if (!string.IsNullOrEmpty(str))
                            {
                                ((DbCommand)oracleCommand).CommandText = str;
                                ((DbCommand)oracleCommand).ExecuteNonQuery();
                            }
                        }
                        catch (OracleException ex)
                        {
                            LogHelper.Error("SQL={0} Message={1}", (object)str, (object)((Exception)ex).Message);
                        }
                    }
                    ((DbTransaction)oracleTransaction).Commit();
                }
            }
            catch (OracleException ex)
            {
                LogHelper.Error("SQL={0} Message={1}", (object)string.Empty, (object)((Exception)ex).Message);
                throw;
            }
            finally
            {
                ((DbConnection)oracleConnection).Close();
            }
        }

        public static OracleDataReader ExecuteReader(string sql, OracleConnection x)
        {
            OracleConnection oracleConnection = OracleHelper.PrepareConnection();
            try
            {
                using (OracleCommand oracleCommand = new OracleCommand(sql, oracleConnection))
                    return oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (OracleException ex)
            {
                LogHelper.Error("SQL={0} Message={1}", (object)sql, (object)((Exception)ex).Message);
                throw;
            }
            finally
            {
            }
        }

        public static int ExecuteSql(string sql, OracleConnection x, params OracleParameter[] cmdParms)
        {
            OracleConnection conn = OracleHelper.PrepareConnection();
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction)null, sql, cmdParms);
                    int num = ((DbCommand)cmd).ExecuteNonQuery();

                    ((DbParameterCollection)cmd.Parameters).Clear();


                    return num;
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            finally
            {
                ((DbConnection)conn).Close();
            }
        }

        public static bool ExecuteSqlTran(List<KeyValuePair<string, OracleParameter[]>> list, OracleConnection x)
        {
            if (list.Count == 0)
                return true;
            OracleConnection conn = OracleHelper.PrepareConnection();
            try
            {
                if (((DbConnection)conn).State != ConnectionState.Open)
                    ((DbConnection)conn).Open();
                using (OracleTransaction trans = conn.BeginTransaction())
                {
                    using (OracleCommand cmd = new OracleCommand())
                    {
                        using (List<KeyValuePair<string, OracleParameter[]>>.Enumerator enumerator = list.GetEnumerator())
                        {
                            while (enumerator.MoveNext())
                            {
                                KeyValuePair<string, OracleParameter[]> current = enumerator.Current;
                                string key = current.Key;
                                OracleParameter[] cmdParms = current.Value;
                                OracleHelper.PrepareCommand(cmd, conn, trans, key, cmdParms);
                                ((DbCommand)cmd).ExecuteNonQuery();

                                ((DbParameterCollection)cmd.Parameters).Clear();

                            }
                        }
                        ((DbTransaction)trans).Commit();
                        return true;
                    }
                }
            }
            catch (OracleException ex)
            {
                LogHelper.Error("SQL={0} Message={1}", (object)string.Empty, (object)((Exception)ex).Message);
                throw ex;
            }
            finally
            {
                ((DbConnection)conn).Close();
            }
        }

        public static object ExecuteScalar(string sql, OracleConnection x, params OracleParameter[] cmdParms)
        {
            OracleConnection conn = OracleHelper.PrepareConnection();
            try
            {
                OracleCommand cmd = new OracleCommand();
                OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction)null, sql, cmdParms);
                object obj = ((DbCommand)cmd).ExecuteScalar();

                ((DbParameterCollection)cmd.Parameters).Clear();
                return obj;
            }
            catch (OracleException ex)
            {
                LogHelper.Error("SQL={0} Message={1}", (object)sql, (object)((Exception)ex).Message);
                throw ex;
            }
            finally
            {
                ((DbConnection)conn).Close();
            }
        }

        public static OracleDataReader ExecuteReader(string sql, OracleConnection x, params OracleParameter[] cmdParms)
        {
            OracleConnection conn = OracleHelper.PrepareConnection();
            try
            {
                OracleCommand cmd = new OracleCommand();
                OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction)null, sql, cmdParms);
                OracleDataReader oracleDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                ((DbParameterCollection)cmd.Parameters).Clear();
                return oracleDataReader;
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                throw new Exception(ex.Message + sql);
            }
            finally
            {
            }
        }



        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
        {
            if (((DbConnection)conn).State != ConnectionState.Open)
                ((DbConnection)conn).Open();
            cmd.Connection = conn;
            ((DbCommand)cmd).CommandText = cmdText;
            if (trans != null)

                cmd.Transaction = trans;
            cmd.BindByName = true;
            ((DbCommand)cmd).CommandType = CommandType.Text;
            if (cmdParms == null)
                return;
            foreach (OracleParameter oracleParameter in cmdParms)
            {
                if (oracleParameter != null)
                    cmd.Parameters.Add(oracleParameter);
            }
        }

        private static OracleConnection PrepareConnection()
        {
            if (string.IsNullOrEmpty(ConfigurationManager.AppSettings["connectionString"]))
                throw new Exception("连接字符串尚未初始化！");
            OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.AppSettings["connectionString"]);
            if (((DbConnection)oracleConnection).State != ConnectionState.Open)
                ((DbConnection)oracleConnection).Open();
            return oracleConnection;
        }

        public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters, OracleConnection x)
        {
            OracleConnection x1 = OracleHelper.PrepareConnection();
            if (((DbConnection)x1).State != ConnectionState.Open)
                ((DbConnection)x1).Open();
            OracleCommand oracleCommand = OracleHelper.BuildQueryCommand(x1, storedProcName, parameters);
            ((DbCommand)oracleCommand).CommandType = CommandType.StoredProcedure;
            return oracleCommand.ExecuteReader();
        }





        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, OracleConnection x)
        {
            OracleConnection x1 = OracleHelper.PrepareConnection();
            DataSet dataSet = new DataSet();
            if (((DbConnection)x1).State != ConnectionState.Open)
                ((DbConnection)x1).Open();
            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();

            oracleDataAdapter.SelectCommand = (OracleHelper.BuildQueryCommand(x1, storedProcName, parameters));
            ((DbDataAdapter)oracleDataAdapter).Fill(dataSet, tableName);
            ((DbConnection)x1).Close();
            return dataSet;
        }





        private static OracleCommand BuildQueryCommand(OracleConnection x, string storedProcName, IDataParameter[] parameters)
        {
            OracleConnection oracleConnection = OracleHelper.PrepareConnection();
            OracleCommand oracleCommand = new OracleCommand(storedProcName, oracleConnection);
            ((DbCommand)oracleCommand).CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter oracleParameter in parameters)

                oracleCommand.Parameters.Add(oracleParameter);
            return oracleCommand;
        }



        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected, OracleConnection conn)
        {
            int result;//add
            OracleCommand oracleCommand = OracleHelper.BuildIntCommand(OracleHelper.PrepareConnection(), storedProcName, parameters);
            rowsAffected = ((DbCommand)oracleCommand).ExecuteNonQuery();

            result = (int)oracleCommand.Parameters["ReturnValue"].Value;
            ((DbConnection)conn).Close();//add
            return result;//add

        }




        private static OracleCommand BuildIntCommand(OracleConnection x, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand oracleCommand = OracleHelper.BuildQueryCommand(OracleHelper.PrepareConnection(), storedProcName, parameters);
            oracleCommand.Parameters.Add(new OracleParameter("ReturnValue", (OracleDbType)112, 4, ParameterDirection.ReturnValue, false, (byte)0, (byte)0, string.Empty, DataRowVersion.Default, (object)null));
            return oracleCommand;
        }

        public static string ToCountSql(string sql)
        {
            Match match = Regex.Match(sql, "(.*?)(FROM.*)", RegexOptions.IgnoreCase);
            if (!match.Success)
                return string.Format("select count(*) from ({0})", (object)sql);
            return string.Format("select count(1) {0}", (object)match.Groups[2].Value);
        }

        public static string ToPagerSql(string sql, int pageIndex, int pageSize, int recordCount)
        {
            return string.Format("select * from (select rownum r,t.* from ({0}) t where rownum <={1}) where r > {2}", (object)sql, (object)(pageIndex * pageSize > recordCount ? recordCount : pageIndex * pageSize), (object)((pageIndex - 1) * pageSize));
        }

        public static DataTable Execute(string sql, OracleConnection x, params OracleParameter[] cmdParams)
        {
            DataTable dataTable = new DataTable();
            OracleConnection conn = OracleHelper.PrepareConnection();
            try
            {
                OracleCommand cmd = new OracleCommand();
                OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction)null, sql, cmdParams);
                ((DbDataAdapter)new OracleDataAdapter(cmd)).Fill(dataTable);

                ((DbParameterCollection)cmd.Parameters).Clear();
            }
            catch (OracleException ex)
            {
                LogHelper.Error("SQL={0} Message={1}", (object)sql, (object)((Exception)ex).Message);
                throw ex;
            }
            finally
            {
                ((DbConnection)conn).Close();
            }
            return dataTable;
        }
    }
}






//老的
//using Oracle.ManagedDataAccess.Client;
//using System;
//using System.Collections;
//using System.Collections.Generic;
//using System.Configuration;
//using System.Data;
//using System.Data.Common;
//using System.Text.RegularExpressions;

//namespace Common
//{
//  public abstract class OracleHelper
//  {
//    private static Random _Random = new Random();
//    private static List<OracleConnection> _Connections;

//    public static OracleConnection Connection
//    {
//      get
//      {
//        if (OracleHelper._Connections == null)
//        {
//          OracleHelper._Connections = new List<OracleConnection>();
//          for (int index = 0; index < 5; ++index)
//            OracleHelper._Connections.Add(new OracleConnection());
//        }
//        OracleConnection conn = OracleHelper._Connections[OracleHelper._Random.Next(0, OracleHelper._Connections.Count - 1)];
//        OracleHelper.PrepareConnection(conn);
//        return conn;
//      }
//    }

//    private OracleHelper()
//    {
//    }

//    public static int ExecuteSql(string sql, OracleConnection conn)
//    {
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        using (OracleCommand oracleCommand = new OracleCommand(sql, conn))
//          return ((DbCommand) oracleCommand).ExecuteNonQuery();
//      }
//      catch (OracleException ex)
//      {
//        LogHelper.Error("SQL={0} Message={1}", sql, ((Exception) ex).Message);
//        ((DbConnection) conn).Close();
//        throw;
//      }
//    }

//    public static void ExecuteSqlTran(ArrayList sqlList, OracleConnection conn)
//    {
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        using (OracleCommand oracleCommand = new OracleCommand())
//        {
//          oracleCommand.Connection = conn;
//          OracleTransaction oracleTransaction = conn.BeginTransaction();
//          oracleCommand.Transaction = oracleTransaction;
//          for (int index = 0; index < sqlList.Count; ++index)
//          {
//            string str = sqlList[index].ToString();
//            try
//            {
//              if (!string.IsNullOrEmpty(str))
//              {
//                ((DbCommand) oracleCommand).CommandText = str;
//                ((DbCommand) oracleCommand).ExecuteNonQuery();
//              }
//            }
//            catch (OracleException ex)
//            {
//              LogHelper.Error("SQL={0} Message={1}", str, ((Exception) ex).Message);
//            }
//          }
//          ((DbTransaction) oracleTransaction).Commit();
//        }
//      }
//      catch (OracleException ex)
//      {
//        LogHelper.Error("SQL={0} Message={1}",  string.Empty, ((Exception) ex).Message);
//        ((DbConnection) conn).Close();
//        throw;
//      }
//    }

//    public static OracleDataReader ExecuteReader(string sql, OracleConnection conn)
//    {
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        using (OracleCommand oracleCommand = new OracleCommand(sql, conn))
//          return oracleCommand.ExecuteReader();
//      }
//      catch (OracleException ex)
//      {
//        LogHelper.Error("SQL={0} Message={1}", sql, ((Exception) ex).Message);
//        ((DbConnection) conn).Close();
//        throw;
//      }
//    }

//    public static int ExecuteSql(string sql, OracleConnection conn, params OracleParameter[] cmdParms)
//    {
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        using (OracleCommand cmd = new OracleCommand())
//        {
//          OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction) null, sql, cmdParms);
//          int num = ((DbCommand) cmd).ExecuteNonQuery();
//          ((DbParameterCollection) cmd.Parameters).Clear();
//          return num;
//        }
//      }
//      catch (OracleException ex)
//      {
//        ((DbConnection) conn).Close();
//        throw ex;
//      }
//    }

//    public static bool ExecuteSqlTran(List<KeyValuePair<string, OracleParameter[]>> list, OracleConnection conn)
//    {
//      if (list.Count == 0)
//        return true;
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        if (((DbConnection) conn).State != ConnectionState.Open)
//          ((DbConnection) conn).Open();
//        using (OracleTransaction trans = conn.BeginTransaction())
//        {
//          using (OracleCommand cmd = new OracleCommand())
//          {
//            using (List<KeyValuePair<string, OracleParameter[]>>.Enumerator enumerator = list.GetEnumerator())
//            {
//              while (enumerator.MoveNext())
//              {
//                KeyValuePair<string, OracleParameter[]> current = enumerator.Current;
//                string key = current.Key;
//                OracleParameter[] cmdParms = current.Value;
//                OracleHelper.PrepareCommand(cmd, conn, trans, key, cmdParms);
//                ((DbCommand) cmd).ExecuteNonQuery();
//                ((DbParameterCollection) cmd.Parameters).Clear();
//              }
//            }
//            ((DbTransaction) trans).Commit();
//            return true;
//          }
//        }
//      }
//      catch (OracleException ex)
//      {
//        LogHelper.Error("SQL={0} Message={1}", string.Empty, ((Exception) ex).Message);
//        ((DbConnection) conn).Close();
//        throw ex;
//      }
//    }

//    public static object ExecuteScalar(string sql, OracleConnection conn, params OracleParameter[] cmdParms)
//    {
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        OracleCommand cmd = new OracleCommand();
//        OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction) null, sql, cmdParms);
//        object obj = ((DbCommand) cmd).ExecuteScalar();
//        ((DbParameterCollection) cmd.Parameters).Clear();
//        return obj;
//      }
//      catch (OracleException ex)
//      {
//        LogHelper.Error("SQL={0} Message={1}", sql, ((Exception) ex).Message);
//        ((DbConnection) conn).Close();
//        throw ex;
//      }
//    }

//    public static OracleDataReader ExecuteReader(string sql, OracleConnection conn, params OracleParameter[] cmdParms)
//    {
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        OracleCommand cmd = new OracleCommand();
//        OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction) null, sql, cmdParms);
//        OracleDataReader oracleDataReader = cmd.ExecuteReader();
//        ((DbParameterCollection) cmd.Parameters).Clear();
//        return oracleDataReader;
//      }
//      catch (Exception ex)
//      {
//        LogHelper.Error(ex);
//        ((DbConnection) conn).Close();
//        throw new Exception(ex.Message + sql);
//      }
//    }

//    private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
//    {
//      if (((DbConnection) conn).State != ConnectionState.Open)
//        ((DbConnection) conn).Open();
//      cmd.Connection = conn;
//      ((DbCommand) cmd).CommandText = cmdText;
//      if (trans != null)
        
//        cmd.Transaction = trans;
//      cmd.BindByName = true;
//      ((DbCommand) cmd).CommandType = CommandType.Text;
//      if (cmdParms == null)
//        return;
//      foreach (OracleParameter oracleParameter in cmdParms)
//      {
//        if (oracleParameter != null)
//          cmd.Parameters.Add(oracleParameter);
//      }
//    }

   

//    private static void PrepareConnection(OracleConnection conn)
//    {
//      if (((DbConnection) conn).ConnectionString == null || ((DbConnection) conn).ConnectionString == "")
//      {
//        string str = ConfigurationManager.AppSettings["connectionString"];
//        if (string.IsNullOrEmpty(str))
//          throw new Exception("连接字符串尚未初始化！");
//        ((DbConnection) conn).ConnectionString = str;
//      }
//      if (((DbConnection) conn).State == ConnectionState.Open)
//        return;
//      ((DbConnection) conn).Open();
//    }

//    public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters, OracleConnection conn)
//    {
//      OracleHelper.PrepareConnection(conn);
//      if (((DbConnection) conn).State != ConnectionState.Open)
//        ((DbConnection) conn).Open();
//      OracleCommand oracleCommand = OracleHelper.BuildQueryCommand(conn, storedProcName, parameters);
//      ((DbCommand) oracleCommand).CommandType = CommandType.StoredProcedure;
//      return oracleCommand.ExecuteReader();
//    }

//    public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, OracleConnection conn)
//    {
//      OracleHelper.PrepareConnection(conn);
//      DataSet dataSet = new DataSet();
//      if (((DbConnection) conn).State != ConnectionState.Open)
//        ((DbConnection) conn).Open();
//      OracleDataAdapter oracleDataAdapter = new OracleDataAdapter();
//      oracleDataAdapter.SelectCommand = (OracleHelper.BuildQueryCommand(conn, storedProcName, parameters));
//      ((DbDataAdapter) oracleDataAdapter).Fill(dataSet, tableName);
//      ((DbConnection) conn).Close();
//      return dataSet;
//    }

//    private static OracleCommand BuildQueryCommand(OracleConnection conn, string storedProcName, IDataParameter[] parameters)
//    {
//      OracleHelper.PrepareConnection(conn);
//      OracleCommand oracleCommand = new OracleCommand(storedProcName, conn);
//      ((DbCommand) oracleCommand).CommandType = CommandType.StoredProcedure;
//      foreach (OracleParameter oracleParameter in parameters)
//        oracleCommand.Parameters.Add(oracleParameter);
//      return oracleCommand;
//    }

//    public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected, OracleConnection conn)
//    {
//        int result;//add
//      OracleHelper.PrepareConnection(conn);
//      if (((DbConnection) conn).State != ConnectionState.Open)
//        ((DbConnection) conn).Open();
//      OracleCommand oracleCommand = OracleHelper.BuildIntCommand(conn, storedProcName, parameters);
//      rowsAffected = oracleCommand.ExecuteNonQuery();
//     // return (int) ((DbParameter) oracleCommand.Parameters.get_Item("ReturnValue")).Value;
//       result = (int)oracleCommand.Parameters["ReturnValue"].Value;
//      ((DbConnection)conn).Close();//add
//      return result;//add

//    }


//    private static OracleCommand BuildIntCommand(OracleConnection conn, string storedProcName, IDataParameter[] parameters)
//    {
//      OracleHelper.PrepareConnection(conn);
//      OracleCommand oracleCommand = OracleHelper.BuildQueryCommand(conn, storedProcName, parameters);
//      oracleCommand.Parameters.Add(new OracleParameter("ReturnValue", (OracleDbType) 112, 4, ParameterDirection.ReturnValue, false, (byte) 0, (byte) 0, string.Empty, DataRowVersion.Default, (object) null));
//      return oracleCommand;
//    }

//    public static string ToCountSql(string sql)
//    {
//      Match match = Regex.Match(sql, "(.*?)(FROM.*)", RegexOptions.IgnoreCase);
//      if (!match.Success)
//        return string.Format("select count(*) from ({0})", (object) sql);
//      return string.Format("select count(1) {0}", match.Groups[2].Value);
//    }

//    public static string ToPagerSql(string sql, int pageIndex, int pageSize, int recordCount)
//    {
//      return string.Format("select * from (select rownum r,t.* from ({0}) t where rownum <={1}) where r > {2}", sql, (pageIndex * pageSize > recordCount ? recordCount : pageIndex * pageSize), ((pageIndex - 1) * pageSize));
//    }

//    public static DataTable Execute(string sql, OracleConnection conn, params OracleParameter[] cmdParams)
//    {
//      DataTable dataTable = new DataTable();
//      try
//      {
//        OracleHelper.PrepareConnection(conn);
//        OracleCommand cmd = new OracleCommand();
//        OracleHelper.PrepareCommand(cmd, conn, (OracleTransaction) null, sql, cmdParams);
//        ((DbDataAdapter) new OracleDataAdapter(cmd)).Fill(dataTable);
//        ((DbParameterCollection) cmd.Parameters).Clear();
//      }
//      catch (OracleException ex)
//      {
//        LogHelper.Error("SQL={0} Message={1}", sql, ((Exception) ex).Message);
//        ((DbConnection) conn).Close();
//        throw ex;
//      }
//      return dataTable;
//    }
//  }
//}