很早就想写博客,又因为一些原因一直脱到现在,应公司项目要求写了一个oracleHelper并附带一些日志的输入

 

public class OracleHelper    {        protected TSunLog Log = CLog.GetInstance();        private OracleConnection _Conn;        private COracleParameter _server;        public OracleHelper(COracleParameter server)        {            _server = server;        }        public OracleConnection Conn        {            get            {                if (_Conn == null)                {                    if (ConnectToOracle())                        return _Conn;                }                else if (_Conn.State == ConnectionState.Closed)                    _Conn.Open();                return _Conn;            }            set            {                _Conn = value;            }        }        private string ConnectionString        {            get            {                return string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));Persist Security Info=True;User ID={3};Password={4};", (object)_server.ServerIp, (object)_server.Port, (object)_server.DbName, (object)_server.UserName, (object)_server.Password);            }        }        private bool ConnectToOracle()        {            bool flag;            try            {                _Conn = new OracleConnection(ConnectionString);                _Conn.Open();                flag = true;                Log.Debug("[CDbOracle][ConnectToOracle]:连接数据库成功!");            }            catch (Exception ex)            {                Log.Debug(string.Format("[CDbOracle][ConnectToOracle]:连接数据库失败!{0}", (object)ex.Message));                flag = false;            }            return flag;        }        public void Close()        {            if (Conn == null)                return;            Conn.Close();            Log.Debug("[CDbOracle][Close]:关闭数据库成功!");        }        private void CheckConnection()        {            if (Conn.State != ConnectionState.Closed)                return;            Conn.Open();        }        public int ExecuteMutliQuery(string commandText, string[][] dtData)        {            CheckConnection();            int num = 0;            using (OracleTransaction tran = Conn.BeginTransaction())            {                try                {                    foreach (string[] strArray in dtData)                        num += ExecuteNonQuery(tran, commandText, (object[])strArray);                    tran.Commit();                }                catch (Exception ex)                {                    Log.Debug("[CDbOracle][ExecuteMutliQuery]:" + ex.Message);                    tran.Rollback();                }                finally                {                    Conn.Close();                }            }            return num;        }        private int ExecuteNonQuery(OracleTransaction tran, string commandText, params object[] paramList)        {            if (tran == null)            {                Log.Debug("[CDbOracle][ExecuteNonQuery]:tran is null");                return 0;            }            if (tran.Connection == null)            {                Log.Debug("[CDbOracle][ExecuteNonQuery]:tran.Connection is null");                return 0;            }            using (IDbCommand command = (IDbCommand)tran.Connection.CreateCommand())            {                command.CommandText = commandText.Replace("@", ":");                AttachParameters((OracleCommand)command, command.CommandText, paramList);                if (tran.Connection.State == ConnectionState.Closed)                    tran.Connection.Open();                return command.ExecuteNonQuery();            }        }        private OracleParameterCollection AttachParameters(OracleCommand cmd, string commandText, object[] paramList)        {            if (paramList == null || paramList.Length == 0)                return (OracleParameterCollection)null;            OracleParameterCollection parameters = cmd.Parameters;            MatchCollection matchCollection = new Regex("(:)\\S*(.*?)\\b", RegexOptions.IgnoreCase).Matches(commandText.Substring(commandText.IndexOf(":")).Replace(",", " ,"));            string[] strArray = new string[matchCollection.Count];            int index1 = 0;            foreach (Match match in matchCollection)            {                strArray[index1] = match.Value;                ++index1;            }            int index2 = 0;            foreach (object obj in paramList)            {                Type type = obj.GetType();                OracleParameter oracleParameter = new OracleParameter();                switch (type.ToString())                {                    case "DBNull":                    case "Char":                    case "SByte":                    case "UInt16":                    case "UInt32":                    case "UInt64":                        throw new SystemException("Invalid data type");                    case "System.String":                        oracleParameter.DbType = DbType.String;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)(string)paramList[index2];                        parameters.Add(oracleParameter);                        break;                    case "System.Byte[]":                        oracleParameter.DbType = DbType.Binary;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)(byte[])paramList[index2];                        parameters.Add(oracleParameter);                        break;                    case "System.Int32":                        oracleParameter.DbType = DbType.Int32;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)(int)paramList[index2];                        parameters.Add(oracleParameter);                        break;                    case "System.Int64":                        oracleParameter.DbType = DbType.Int32;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)Convert.ToInt32(paramList[index2]);                        parameters.Add(oracleParameter);                        break;                    case "System.Boolean":                        oracleParameter.DbType = DbType.Boolean;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)(bool)paramList[index2];                        parameters.Add(oracleParameter);                        break;                    case "System.DateTime":                        oracleParameter.DbType = DbType.DateTime;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)Convert.ToDateTime(paramList[index2]);                        parameters.Add(oracleParameter);                        break;                    case "System.Double":                        oracleParameter.DbType = DbType.Double;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)Convert.ToDouble(paramList[index2]);                        parameters.Add(oracleParameter);                        break;                    case "System.Decimal":                        oracleParameter.DbType = DbType.Decimal;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)Convert.ToDecimal(paramList[index2]);                        break;                    case "System.Guid":                        oracleParameter.DbType = DbType.Guid;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = (object)(Guid)paramList[index2];                        break;                    case "System.Object":                        oracleParameter.DbType = DbType.Object;                        oracleParameter.ParameterName = strArray[index2];                        oracleParameter.Value = paramList[index2];                        parameters.Add(oracleParameter);                        break;                    default:                        throw new SystemException("Value is of unknown data type");                }                ++index2;            }            return parameters;        }        public int ExecuteNonSql(string strSql)        {            CheckConnection();            OracleCommand command = Conn.CreateCommand();            command.CommandText = strSql;            int num = command.ExecuteNonQuery();            command.Dispose();            Conn.Close();            return num;        }        public int ExecuteNonSql(string strSql, object[] parameters)        {            CheckConnection();            OracleCommand command = Conn.CreateCommand();            command.CommandText = strSql.Replace("@", ":");            AttachParameters(command, command.CommandText, parameters);            int num = command.ExecuteNonQuery();            command.Dispose();            Conn.Close();            return num;        }        public bool ExecuteSql(List
     
       strSqls)        {            CheckConnection();            foreach (string strSql in strSqls)                Log.Debug("[CDbOracle][ExecuteSql]:" + strSql);            int num = 0;            using (OracleCommand oracleCommand = new OracleCommand())            {                oracleCommand.Connection = Conn;                using (OracleTransaction oracleTransaction = Conn.BeginTransaction())                {                    oracleCommand.Transaction = oracleTransaction;                    try                    {                        for (int index = 0; index < strSqls.Count; ++index)                        {                            string str = strSqls[index].ToString();                            if (str.Trim().Length > 1)                            {                                oracleCommand.CommandText = str;                                num += oracleCommand.ExecuteNonQuery();                            }                        }                        oracleTransaction.Commit();                    }                    catch (Exception ex)                    {                        oracleTransaction.Rollback();                        Log.Debug("[CDbOracle][ExecuteSql][Error]:" + ex.Message);                    }                }            }            return num > 0;        }        public bool ExecuteSql(string strSql)        {            CheckConnection();            Log.Debug("[CDbOracle][ExecuteSql]:" + strSql);            OracleCommand command = Conn.CreateCommand();            command.CommandText = strSql;            int num = command.ExecuteNonQuery();            command.Dispose();            Conn.Close();            return num > 0;        }        public bool ExecuteSql(string strSql, object[] parameters)        {            return ExecuteNonSql(strSql, parameters) > 0;        }        public object GetObject(string strSql)        {            CheckConnection();            Log.Debug("[CDbOracle][GetObject]:" + strSql);            OracleCommand command = Conn.CreateCommand();            command.CommandText = strSql;            object obj = command.ExecuteScalar();            command.Dispose();            Conn.Close();            Log.Debug(obj.ToString());            return obj;        }        public string GetString(string strSql)        {            string str = GetObject(strSql).ToString();            Log.Debug("[CDbOracle][GetString]:" + str);            return str;        }        public List
      
        GetStrList(string sql)        {            Log.Debug(sql);            List
       
         stringList = new List
        
         ();            OracleCommand command = Conn.CreateCommand();            command.CommandText = sql;            IDataReader dataReader = (IDataReader)command.ExecuteReader();            while (dataReader.Read())            {                string sText = dataReader.GetString(0);                Log.Debug(sText);                stringList.Add(sText);            }            dataReader.Close();            command.Dispose();            return stringList;        }        public DataTable QuerySql(string sSql)        {            CheckConnection();            Log.Debug("[CDbOracle][QuerySql]:" + sSql);            OracleCommand command = Conn.CreateCommand();            command.CommandText = sSql;            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(command);            DataSet dataSet = new DataSet();            oracleDataAdapter.Fill(dataSet);            oracleDataAdapter.Dispose();            command.Dispose();            return dataSet.Tables[0];        }        public DataTable QueryTable(string sql)        {            return QuerySql(sql);        }        public string ExecProcess(string processName, Dictionary
         
           dicValue)        {            CheckConnection();            string str;            try            {                OracleCommand command = Conn.CreateCommand();                command.CommandType = CommandType.StoredProcedure;                command.CommandText = processName;                foreach (KeyValuePair
          
            keyValuePair in dicValue) { if (keyValuePair.Value != null) { command.Parameters.Add(keyValuePair.Key, OracleDbType.NVarchar2).Direction = ParameterDirection.Input; command.Parameters[keyValuePair.Key].Value = (object)keyValuePair.Value; } else command.Parameters.Add(keyValuePair.Key, OracleDbType.Int32).Direction = ParameterDirection.Output; } command.ExecuteNonQuery(); str = command.Parameters["res"].Value.ToString(); } catch (Exception ex) { Log.Debug(ex); str = "-1"; } return str; } }