首页 > 代码库 > 分享一个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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。