分享一个拿即用的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="data source=192.168.0.2/orcl;user id=TEST;password=123456;persist security info=False;Pooling=False"/>
</appSettings>
</configuration>