.net C# SqlHelper for Oracle

.net C# SqlHelper for Oracle

适用于Oracle的sqlhelper

需要使用ODP.Net，引用Oracle.Dataaccess.dll　　推荐安装ODAC

代码如下：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Reflection;using System.Data;using System.Configuration;using Oracle.DataAccess.Client;namespace DAL{    public static class SqlHelper    {        #region 样本        //标准连接-SSPI        PRivate static readonly string defaultConnectString = "Data Source=ORCL;Integrated Security=SSPI;";        //标准连接        //private static readonly string defaultConnectString = "Data Source=ORCL;User Id=UPDM;PassWord=1234;";        //标准链接        //private static readonly string defaultConnectString = "Data Source=(DESCRipTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User Id=system;Password=1234;";        #endregion        #region 变量        private static OracleConnection _con = null;          public static string _constr = ConfigurationManager.ConnectionStrings["orcl"].ToString();                #endregion            #region 属性            public static string constr        {            get            {                if (_constr == null||_constr.Equals(String.Empty))                {                    _constr = defaultConnectString;                }                return _constr;            }            set            {                _constr = value;            }        }        /// <summary>          /// 获取或设置数据库连接对象          /// </summary>          public static OracleConnection Con          {            get              {                                if (SqlHelper._con == null)                  {                    SqlHelper._con = new OracleConnection();                  }                if (SqlHelper._con.ConnectionString == null || SqlHelper._con.ConnectionString.Equals(string.Empty))                {                    SqlHelper._con.ConnectionString = SqlHelper.constr;                }                return SqlHelper._con;              }              set              {                  SqlHelper._con = value;              }          }          #endregion           #region 方法           /// <summary>            /// 执行并返回第一行第一列的数据库操作          /// </summary>          /// <param name="commandText">Sql语句或存储过程名</param>          /// <param name="commandType">Sql命令类型</param>          /// <param name="param">Oracle命令参数数组</param>          /// <returns>第一行第一列的记录</returns>          public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)          {               int result = 0;              try{                using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))                  {                      try                      {                          cmd.CommandType = commandType;                          if (param!=null)                          {                              cmd.Parameters.AddRange(param);                          }                          SqlHelper.Con.Open();                        string x = cmd.CommandText;                        result = Convert.ToInt32(cmd.ExecuteScalar());                      }                    catch                    {                        result = -1;                    }                  }              }            finally            {                if (SqlHelper.Con.State != ConnectionState.Closed)                {                    SqlHelper.Con.Close();                }            }            return result;        }          /// <summary>          /// 执行不查询的数据库操作          /// </summary>          /// <param name="commandText">Oracle语句或存储过程名</param>          /// <param name="commandType">Oracle命令类型</param>          /// <param name="param">Oracle命令参数数组</param>          /// <returns>受影响的行数</returns>          public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)          {              int result = 0;            try            {                  using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))                  {                      try                      {                          cmd.CommandType = commandType;                          if (param!=null)                          {                              cmd.Parameters.AddRange(param);                          }                        SqlHelper.Con.Open();                          result = cmd.ExecuteNonQuery();                      }                      catch                    {                        result = -1;                    }                  }            }            finally            {                if (SqlHelper.Con.State != ConnectionState.Closed)                {                    SqlHelper.Con.Close();                }            }            return result;          }        /// <summary>        /// 获取数据表        /// </summary>        /// <param name="commandText">select命令</param>        /// <param name="param">参数表</param>        /// <returns></returns>        public static DataTable GetDataTable(string commandText,params OracleParameter[] param)        {            DataTable result = new DataTable();            try            {                using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))                {                    cmd.Parameters.AddRange(param);                    try {                                                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                        adapter.Fill(result);                    }                    catch                    {                        result = null;                    }                }            }            finally            {                if (SqlHelper.Con.State != ConnectionState.Closed)                {                    SqlHelper.Con.Close();                }            }            return result;        }        public static int GetNextValueInSequence(string sequenceName)        {            if (ExecuteScalar("select count(*) from user_objects where OBJECT_NAME=:seqName", CommandType.Text, new OracleParameter(":seqName",sequenceName)) > 0)            {                return ExecuteScalar("select " + sequenceName + ".nextval from dual", CommandType.Text);            }            else            {                return -1;            }        }        /// <summary>        /// 事务模式执行多行非查询语句        /// </summary>        /// <param name="commandText">sql语句</param>        /// <param name="param">参数</param>        /// <returns>受影响行数</returns>        public static int ExecuteNonQueryTransaction(string commandText, List<OracleParameter[]> param)        {            int result = 0;            try            {                using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con))                {                    SqlHelper.Con.Open();                    cmd.Transaction = cmd.Connection.BeginTransaction();                    try                    {                        foreach (OracleParameter[] par in param)                        {                            cmd.Parameters.Clear();                            cmd.Parameters.AddRange(par);                            result += cmd.ExecuteNonQuery();                        }                        cmd.Transaction.Commit();                    }                    catch                    {                        result = -1;                        try                        {                            cmd.Transaction.Rollback();                        }                        catch                        {                            result = -2;                        }                    }                }            }            finally            {                if (SqlHelper.Con.State != ConnectionState.Closed)