基于委托的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,Func<OracleCommand,T> func)
	{
		using (OracleConnection conn = new OracleConnection(connectionString))
		{
			using (OracleCommand cmd = new OracleCommand(sql, conn))
			{
				conn.Open();
				OracleTransaction transaction = conn.BeginTransaction();
				try
				{                        
					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 ExecuteScalar(string sql)
	{
		Func<OracleCommand, object> func = new Func<OracleCommand, object>(
			cmd => {
				return cmd.ExecuteScalar();
			}
		);
		return Execute<object>(sql,func);
	}

	public static int ExecuteNonQuery(string sql)
	{
		Func<OracleCommand, int> func = new Func<OracleCommand, int>(
			cmd => {
				return cmd.ExecuteNonQuery();
			}
		);
		return Execute<int>(sql, func);
	}
	/// <summary>
	/// 查询数据
	/// </summary>
	/// <param name="sql"></param>
	/// <returns></returns>
	public static DataSet Query(string sql)
	{
		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, func);
	}
}

4、测试

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

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

// 测试查询
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();