首页 > 代码库 > 分享一个oraclehelper

分享一个oraclehelper

分享一个拿即用的oraclehelper

首先要引用本机中的oralce access,如果是64位的话,也必须是64位运行,不然会报连接为空connection 等于null.

using Oracle.DataAccess;using Oracle.DataAccess.Client;

  

public class OraHelper    {        public static string connectionString;        private static OracleConnection conn;        static OraHelper()        {            connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnetionString"].ToString();//读取app.cofig中的数据库配置                        conn = new OracleConnection(connectionString);        }        /// <summary>        /// 获取默认的连接        /// </summary>        /// <returns></returns>        public static OracleConnection GetConnection()        {            return conn;        }        /// <summary>        /// 创建新的连接(用于事务操作)        /// </summary>        /// <returns></returns>        public static OracleConnection CreateConnection()        {            return new OracleConnection(connectionString);        }        public static bool ConnectionTest()        {            bool ret = false;            try            {                conn.Open();                ret = true;            }            catch { ret = false; }            finally { conn.Close(); }            return ret;        }        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)        {            int val = 0;            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);            try            {                val = cmd.ExecuteNonQuery();            }            catch { val = -1; }            finally            {                cmd.Parameters.Clear();                conn.Close();            }            return val;        }        public static int ExecuteNonQuery(string cmdText)        {            return ExecuteNonQuery(CommandType.Text, cmdText);        }        public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)        {            return ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);        }        /// <summary>        /// 以事务的方式执行(多用于多条SQL语句执行)        /// </summary>        public static int ExecuteNonQueryByTransaction(string cmdText, params OracleParameter[] commandParameters)        {            int val = 0;            OracleCommand cmd = new OracleCommand();            OracleTransaction trans = conn.BeginTransaction();            PrepareCommand(cmd, trans, CommandType.Text, cmdText, commandParameters);            try            {                val = cmd.ExecuteNonQuery();                trans.Commit();            }            catch            {                val = -1;                trans.Rollback();            }            finally            {                cmd.Parameters.Clear();                conn.Close();            }            return val;        }        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)        {            int val = 0;            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, trans, cmdType, cmdText, commandParameters);            try            {                val = cmd.ExecuteNonQuery();            }            catch            {                val = -1;            }            finally            {                cmd.Parameters.Clear();            }            return val;        }        public static OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)        {            OracleDataReader rdr;            OracleCommand cmd = new OracleCommand();            try            {                PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                cmd.Parameters.Clear();            }            catch { rdr = null; }            finally            {                cmd.Parameters.Clear();                conn.Close();            }            return rdr;        }        public static DataTable ExecuteDataTable(string cmdText)        {            DataTable dt = new DataTable();            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, CommandType.Text, cmdText);            try            {                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)                    dt = ds.Tables[0];                else                    dt = null;            }            catch { dt = null; }            finally { conn.Close(); }            return dt;        }        public static DataTable ExecuteDataTable(string cmdText, params OracleParameter[] commandParameters)        {            DataTable dt = new DataTable();            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, CommandType.Text, cmdText, commandParameters);            try            {                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)                    dt = ds.Tables[0];                else                    dt = null;            }            catch { dt = null; }            finally { conn.Close(); }            return dt;        }        public static DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)        {            DataTable dt = new DataTable();            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);            try            {                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)                    dt = ds.Tables[0];                else                    dt = null;            }            catch { dt = null; }            finally { conn.Close(); }            return dt;        }        public static DataSet ExecuteDataSet(string cmdText)        {            DataSet ds = new DataSet();            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, CommandType.Text, cmdText);            try            {                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                adapter.Fill(ds);            }            catch { ds = null; }            finally { conn.Close(); }            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;            else return ds;        }        public static DataSet ExecuteDataSet(string cmdText, params OracleParameter[] commandParameters)        {            DataSet ds = new DataSet();            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, CommandType.Text, cmdText, commandParameters);            try            {                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                adapter.Fill(ds);            }            catch { ds = null; }            finally { conn.Close(); }            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;            else return ds;        }        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)        {            DataSet ds = new DataSet();            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);            try            {                OracleDataAdapter adapter = new OracleDataAdapter(cmd);                adapter.Fill(ds);            }            catch { ds = null; }            finally { conn.Close(); }            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;            else return ds;        }        public static object ExecuteScalar(string cmdText)        {            return ExecuteScalar(CommandType.Text, cmdText);        }        public static object ExecuteScalar(string cmdText, params OracleParameter[] commandParameters)        {            return ExecuteScalar(CommandType.Text, cmdText, commandParameters);        }        public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)        {            object val = null;            OracleCommand cmd = new OracleCommand();            PrepareCommand(cmd, null, cmdType, cmdText, commandParameters);            try            {                val = cmd.ExecuteScalar();            }            catch { val = null; }            finally            {                cmd.Parameters.Clear();                conn.Close();            }            return val;        }        private static void PrepareCommand(OracleCommand cmd, OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)        {            if (trans != null)            {                if (trans.Connection.State != ConnectionState.Open)                    trans.Connection.Open();                cmd.Connection = trans.Connection;            }            else            {                if (conn.State != ConnectionState.Open)                    conn.Open();                cmd.Connection = conn;            }            cmd.CommandText = cmdText;            cmd.CommandType = cmdType;            if (cmdParms != null)            {                foreach (OracleParameter parm in cmdParms)                    cmd.Parameters.Add(parm);            }        }    }

  在app.config中添加oracle 的连接,代码如下:

<configuration>    <startup>         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />    </startup>  <appSettings>    <add key="ConnetionString" value=http://www.mamicode.com/"data source=192.168.0.2/orcl;user id=TEST;password=123456;persist security info=False;Pooling=False"/>  </appSettings></configuration>

 

分享一个oraclehelper