1. 引入库类
Oracle.ManagedDataAccess.dll
using Oracle.ManagedDataAccess.Client;
2. 粗暴封装
namespace xx
{
public class OracleSqlDao
{
public OracleSqlDao(string connStr)
{
ConnectionString = connStr;
}
/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectionString;
/// <summary>
/// 使用固定连接进行数据库的操作，默认false，表示每次操作都使用新的连接
/// </summary>
public bool UseSpecifiedConn = false;
/// <summary>
/// 使用指定连接执行数据库操作时所用的数据库连接，使用前需设置UseSpecifiedConn
/// </summary>
public OracleConnection OrasqlConn;
/// <summary>
/// 打开数据库连接
/// </summary>
/// <returns></returns>
public bool OpenConn()
{
try
{
if (OrasqlConn.State == ConnectionState.Open)
{
return true;
}
else
{
OrasqlConn.Open();
return OrasqlConn.State == ConnectionState.Open;
}
}
catch (Exception ex)
{
Common.WriteLog.Error("打开数据库连接出错!", ex);
return false;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns></returns>
public bool CloseConn()
{
try
{
OrasqlConn.Close();
return OrasqlConn.State == ConnectionState.Closed;
}
catch (Exception ex)
{
Common.WriteLog.Error("关闭数据库连接出错!", ex);
return false;
}
}
/// <summary>
/// 检查数据库连接是否正常
/// </summary>
/// <returns></returns>
public bool TryOpenConn()
{
bool result = false;
using (OracleConnection Conn = new OracleConnection(ConnectionString))
{
try
{
Conn.Open();
result = Conn.State == ConnectionState.Open;
}
catch (Exception ex)
{
Common.WriteLog.Error("打开数据库连接出错!", ex);
}
}
return result;
}
/// <summary>
/// 查询 postgre 数据库，返回 DataTable 数据
/// </summary>
/// <param name="sqlText">sql查询语句</param>
/// <returns></returns>
public DataTable ExecuteQuery(string sqlText)
{
return ExecuteQuery(sqlText, null);
}
/// <summary>
/// 查询 postgre 数据库，返回 DataTable 数据
/// </summary>
/// <param name="sqlText">sql查询语句</param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public DataTable ExecuteQuery(string sqlText, OracleParameter[] param)
{
var dt = new DataTable();
OracleConnection conn = UseSpecifiedConn ? OrasqlConn : new OracleConnection(ConnectionString);
using (var nda = new OracleDataAdapter(sqlText, conn))
{
try
{
if (param != null)
{
SetParmDBNull(param);
nda.SelectCommand.Parameters.AddRange(param);
}
var ds = new DataSet();
nda.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
Common.WriteLog.Error("查询数据库出错!查询语句:" + sqlText, ex);
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
return dt;
}
/// <summary>
/// 查询唯一值（用来查总记录数等等..）
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int QueryOnly(string sqlText)
{
OracleConnection conn = UseSpecifiedConn ? OrasqlConn : new OracleConnection(ConnectionString);
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleCommand objCommand = new OracleCommand(sqlText, conn);
int count = Convert.ToInt32(objCommand.ExecuteScalar());
return count;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return 0;
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
/// <summary>
/// 增删改 postgre 数据库，返回是否成功标识
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlText)
{
return ExecuteNonQuery(sqlText, null);
}
/// <summary>
/// 增删改 postgre 数据库，返回是否成功标识
/// </summary>
/// <param name="sqlText"></param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlText, OracleParameter[] param)
{
int result = int.MinValue;
OracleConnection conn = UseSpecifiedConn ? OrasqlConn : new OracleConnection(ConnectionString);
using (var nSqlCommand = new OracleCommand(sqlText, conn))
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
// 添加参数
if (param != null)
{
SetParmDBNull(param);
nSqlCommand.Parameters.AddRange(param);
}
// 执行查询并返回受影响的行数
result = nSqlCommand.ExecuteNonQuery();
}
catch (OracleException ex)
{
Common.WriteLog.Error("执行数据库语句出错!执行语句:" + sqlText, ex);
if (ex != null)
{
result = ex.ErrorCode;
}
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
return result;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public bool ExecuteTransSQL(List<String> SQLStringList)
{
using (OracleConnection conn = UseSpecifiedConn ? OrasqlConn : new OracleConnection(ConnectionString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return true;
}
catch
{
tx.Rollback();
return false;
}
finally
{
if (!UseSpecifiedConn)
{
conn.Dispose();
}
}
}
}
/// <summary>
/// 将参数中null值转换为DBNull
/// </summary>
/// <param name="param"></param>
public void SetParmDBNull(OracleParameter[] param)
{
foreach (var p in param)
{
if (p.Value == null)
{
p.Value = DBNull.Value;
}
}
}
/// <summary>
/// DataTable转换List，反射机制
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public IList<T> DataTableToList<T>(DataTable dt) where T : new()
{
IList<T> list = new List<T>();// 定义集合
Type type = typeof(T); // 获得此模型的类型
var tempName = string.Empty;
foreach (DataRow dr in dt.Rows)
{
T t = new T();
PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
if (dt.Columns.Contains(tempName))
{
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
list.Add(t);
}
return list;
}
}
}
3. 代码实例
/// <summary>
/// oracle
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOra_Click(object sender, RoutedEventArgs e)
{
OracleSqlDao ora = new OracleSqlDao("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.37.175)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=c3mits)));Persist Security Info=True;User ID=gosun;Password=video;");
var result=ora.TryOpenConn();
var table=ora.ExecuteQuery("select * from yunxingtest");
}