基于委托的Oracle数据库连接

基于委托的Oracle数据库连接

1、引入包

Oracle.ManagedDataAccess.dll
System.Configuration
Newtonsoft.Json

2、App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
  <appSettings>
    <add key="oracleServer" value="localhost"/>
    <add key="database" value="net"/>
    <add key="user" value="system"/>
    <add key="pwd" value="manager"/>
  </appSettings>
</configuration>

3、OracleDbHelper

public class OracleDbHelper
{
	public static readonly string oracleServer = ConfigurationManager.AppSettings["oracleServer"];
	public static readonly string dataBase = ConfigurationManager.AppSettings["database"];
	public static readonly string user = ConfigurationManager.AppSettings["user"];
	public static readonly string password = ConfigurationManager.AppSettings["pwd"];
	//数据库连接字符串(app.config来配置)，可以动态更改connectionString支持多数据库.
	//public static readonly string connectionString = ConfigurationManager.AppSettings["OraConnString"];
	public static readonly string connectionString = $"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={oracleServer})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={dataBase})));User Id = {user}; Password={password};";
	/// <summary>
	/// 利用委托封装的Oracle访问通用类
	/// </summary>
	/// <typeparam name="T"></typeparam>
	/// <param name="sql"></param>
	/// <param name="func"></param>
	/// <returns></returns>
	private static T Execute<T>(string sql, Dictionary<string, string> keys, Func<OracleCommand,T> func)
	{
		using (OracleConnection conn = new OracleConnection(connectionString))
		{
			using (OracleCommand cmd = new OracleCommand(sql, conn))
			{
				conn.Open();
				OracleTransaction transaction = conn.BeginTransaction();
				try
				{
					// 设置事务
					if (transaction != null)
					{
						cmd.Transaction = transaction;
						cmd.CommandType = CommandType.Text;
					}
					if (keys != null)
					{
						foreach (string key in keys.Keys)
						{
							OracleParameter parm = new OracleParameter(key, keys[key]);
							cmd.Parameters.Add(parm);
						}                                
					}
					T tResult = func.Invoke(cmd);
					// 提交数据
					transaction.Commit();
					return tResult;
				}
				catch (OracleException ex)
				{
					// 出现异常是回滚
					transaction.Rollback();
					throw new Exception(ex.Message);
				}
			}
		}
	}
	/// <summary>
	/// 执行一条计算查询结果语句，返回查询结果
	/// </summary>
	/// <param name="sql"></param>
	/// <returns></returns>
	public static object QueryOne(string sql,Dictionary<string,string> keys)
	{
		Func<OracleCommand, object> func = new Func<OracleCommand, object>(
			cmd => {
				return cmd.ExecuteScalar();
			}
		);            
		return Execute<object>(sql, keys, func);
	}
	/// <summary>
	/// 执行一条增删改SQL语句。
	/// </summary>
	/// <param name="sql"></param>
	/// <param name="keys"></param>
	/// <returns></returns>
	public static int ExecuteSql(string sql, Dictionary<string, string> keys)
	{
		Func<OracleCommand, int> func = new Func<OracleCommand, int>(
			cmd => {
				return cmd.ExecuteNonQuery();
			}
		);
		return Execute<int>(sql, keys,func);
	}
	/// <summary>
	/// 执行多条SQL语句，实现数据库事务。
	/// </summary>
	/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的Dictionary<string, string>）</param>
	public static int ExecuteMuchSql(Hashtable SQLStringList)
	{
		Func<OracleCommand, int> func = new Func<OracleCommand, int>(
			cmd => {
				return cmd.ExecuteNonQuery();
			}
		);
		int rows = 0;
		//循环
		foreach (DictionaryEntry myDE in SQLStringList)
		{                
			rows += Execute<int>(myDE.Key.ToString(), myDE.Value as Dictionary<string, string>, func);               
		}
		return rows;
	}
	/// <summary>
	/// 查询数据
	/// </summary>
	/// <param name="sql"></param>
	/// <returns></returns>
	public static DataSet Query(string sql, Dictionary<string, string> keys)
	{
		Func<OracleCommand, DataSet> func = new Func<OracleCommand, DataSet>(
			cmd => {
				DataSet ds = new DataSet();
				OracleDataAdapter adapter = new OracleDataAdapter(cmd);
				adapter.Fill(ds, "data");
				return ds;
			}
		);
		return Execute<DataSet>(sql, keys, func);
	}
}

4、测试

// 1、执行一条计算查询结果语句，返回查询结果
string sql = $"select count(1) from staff_dict";
object rows = OracleDbHelper.QueryOne(sql,null);
Console.WriteLine(rows);
Console.ReadKey();

// 测试更新
string sql = "update staff_dict t set t.status = 1 where t.emp_no = '0650'";
int rows = OracleDbHelper.ExecuteSql(sql,null);
Console.WriteLine(rows);
Console.ReadKey();

// 带参数的更新
string sql = "update staff_dict t set t.status = 1 where t.emp_no = :id";
int rows = OracleDbHelper.ExecuteSql(sql,new Dictionary<string, string>() { { "id", "0650" } });

// 测试查询
string sql = "select * from staff_dict t where t.emp_no like '065%'";
DataSet data = OracleDbHelper.Query(sql);
Console.WriteLine(JsonConvert.SerializeObject(data));
Console.ReadKey();

// 带参数查询
string sql = $"select * from staff_dict t where t.emp_no = :id";
DataSet ds = OracleDbHelper.Query(sql, new Dictionary<string, string>() { {"id","0650" } });