相信大家在选择ORM（对象关系映射器）框架的时候，都有自己的比较跟个人习惯，目前.net国内主流的ORM有Entity Framework (EF) 、NHibernate、Dapper、SqlSuger等等。在选择一个适合自己的ORM的时候，我会考虑到几个方面，社区活跃度、功能完善度、支持多种SQL、性能方面，而Dapper在速度方面拥有Micro-ORM之王的称号，社区活跃度也还不错，功能也比较完善，因此本文选择Dapper作为我们项目的ORM框架来访问oracle数据库，选择访问oracle数据库是因为网上有很多访问mssql，Mysql的了，这里就演示访问oracle的。
这步参照我的文章《.net core3.1项目部署到linux的docker》
TestAPI.csproj
<ItemGroup> <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="2.19.91" /> <PackageReference Include="Dapper" Version="2.0.35" /> <PackageReference Include="Newtonsoft.Json" Version="12.0.3" /> </ItemGroup>appsettings.json文件
{ "Logging": { "IncludeScopes": false, "LogLevel": { "Default": "Trace", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Information" } }, "AllowedHosts": "*", "ConnectionStrings": { "OracleConnectionString": "data source=192.168.1.230:1521/xfdb;password=password;user id=test;Incr Pool Size=5;Decr Pool Size=2;" } }用于管理我们的数据库连接字符串以及其他数据库的连接配置 ConnectionConfig.cs
public class ConnectionConfig { public string ConnectionString { get; set; } public DbStoreType DbType { get; set; } } public enum DbStoreType { MySql = 0, SqlServer = 1, Sqlite = 2, Oracle = 3 } public class DapperFactoryOptions { public IList<Action<ConnectionConfig>> DapperActions { get; } = new List<Action<ConnectionConfig>>(); }新建一个类，自行封装一些增删改查的方法 DapperClient.cs
public class DapperClient { public ConnectionConfig CurrentConnectionConfig { get; set; } public DapperClient(IOptionsMonitor<ConnectionConfig> config) { CurrentConnectionConfig = config.CurrentValue; } public DapperClient(ConnectionConfig config) { CurrentConnectionConfig = config; } IDbConnection _connection = null; public IDbConnection Connection { get { switch (CurrentConnectionConfig.DbType) { //case DbStoreType.MySql: // _connection = new MySql.Data.MySqlClient.MySqlConnection(CurrentConnectionConfig.ConnectionString); // break; //case DbStoreType.Sqlite: // _connection = new SQLiteConnection(CurrentConnectionConfig.ConnectionString); // break; //case DbStoreType.SqlServer: // _connection = new System.Data.SqlClient.SqlConnection(CurrentConnectionConfig.ConnectionString); // break; case DbStoreType.Oracle: _connection = new Oracle.ManagedDataAccess.Client.OracleConnection(CurrentConnectionConfig.ConnectionString); break; default: throw new Exception("未指定数据库类型！"); } return _connection; } } /// <summary> /// 执行SQL返回集合 /// </summary> /// <param name="strSql">sql语句</param> /// <returns></returns> public virtual List<T> Query<T>(string strSql) { using (IDbConnection conn = Connection) { return conn.Query<T>(strSql, null).ToList(); } } /// <summary> /// 执行SQL返回集合 /// </summary> /// <param name="strSql">SQL语句</param> /// <param name="obj">参数model</param> /// <returns></returns> public virtual List<T> Query<T>(string strSql, object param) { using (IDbConnection conn = Connection) { return conn.Query<T>(strSql, param).ToList(); } } /// <summary> /// 执行SQL返回一个对象 /// </summary> /// <param name="strSql">SQL语句</param> /// <returns></returns> public virtual T QueryFirst<T>(string strSql) { using (IDbConnection conn = Connection) { return conn.Query<T>(strSql).FirstOrDefault<T>(); } } /// <summary> /// 执行SQL返回一个对象 /// </summary> /// <param name="strSql">SQL语句</param> /// <returns></returns> public virtual async Task<T> QueryFirstAsync<T>(string strSql) { using (IDbConnection conn = Connection) { var res = await conn.QueryAsync<T>(strSql); return res.FirstOrDefault<T>(); } } /// <summary> /// 执行SQL返回一个对象 /// </summary> /// <param name="strSql">SQL语句</param> /// <param name="obj">参数model</param> /// <returns></returns> public virtual T QueryFirst<T>(string strSql, object param) { using (IDbConnection conn = Connection) { return conn.Query<T>(strSql, param).FirstOrDefault<T>(); } } /// <summary> /// 执行SQL /// </summary> /// <param name="strSql">SQL语句</param> /// <param name="param">参数</param> /// <returns>0成功，-1执行失败</returns> public virtual int Execute(string strSql, object param) { using (IDbConnection conn = Connection) { try { return conn.Execute(strSql, param) > 0 ? 0 : -1; } catch (Exception ex) { throw ex; } } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="strProcedure">过程名</param> /// <returns></returns> public virtual int ExecuteStoredProcedure(string strProcedure) { using (IDbConnection conn = Connection) { try { return conn.Execute(strProcedure, null, null, null, CommandType.StoredProcedure) == 0 ? 0 : -1; } catch (Exception ex) { throw ex; } } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="strProcedure">过程名</param> /// <param name="param">参数</param> /// <returns></returns> public virtual int ExecuteStoredProcedure(string strProcedure, object param) { using (IDbConnection conn = Connection) { try { return conn.Execute(strProcedure, param, null, null, CommandType.StoredProcedure) == 0 ? 0 : -1; } catch (Exception ex) { throw ex; } } } }新增一个接口类，这里就写了一个CreateClient方法 IDapperFactory.cs
public interface IDapperFactory { DapperClient CreateClient(string name); }实现接口 DefaultDapperFactory.cs
public class DefaultDapperFactory : IDapperFactory { private readonly IServiceProvider _services; private readonly IOptionsMonitor<DapperFactoryOptions> _optionsMonitor; public DefaultDapperFactory(IServiceProvider services, IOptionsMonitor<DapperFactoryOptions> optionsMonitor) { _services = services ?? throw new ArgumentNullException(nameof(services)); _optionsMonitor = optionsMonitor ?? throw new ArgumentNullException(nameof(optionsMonitor)); } public DapperClient CreateClient(string name) { if (name == null) throw new ArgumentNullException(nameof(name)); var client = new DapperClient(new ConnectionConfig { }); var option = _optionsMonitor.Get(name).DapperActions.FirstOrDefault(); if (option != null) option(client.CurrentConnectionConfig); else throw new ArgumentNullException(nameof(option)); return client; } }依赖注入入口
DapperFactoryCollectionExtensions.cs
public static class DapperFactoryCollectionExtensions { public static IServiceCollection AddDapper(this IServiceCollection services) { if (services == null) throw new ArgumentNullException(nameof(services)); services.AddLogging(); services.AddOptions(); services.AddSingleton<DefaultDapperFactory>(); services.TryAddSingleton<IDapperFactory>(serviceProvider => serviceProvider.GetRequiredService<DefaultDapperFactory>()); return services; } public static IDapperFactoryBuilder AddDapper(this IServiceCollection services, string name, Action<ConnectionConfig> configureClient) { if (services == null) throw new ArgumentNullException(nameof(services)); if (name == null) throw new ArgumentNullException(nameof(name)); if (configureClient == null) throw new ArgumentNullException(nameof(configureClient)); AddDapper(services); var builder = new DefaultDapperFactoryBuilder(services, name); builder.ConfigureDapper(configureClient); return builder; } public static IDapperFactoryBuilder ConfigureDapper(this IDapperFactoryBuilder builder, Action<ConnectionConfig> configureClient) { if (builder == null) throw new ArgumentNullException(nameof(builder)); if (configureClient == null) throw new ArgumentNullException(nameof(configureClient)); builder.Services.Configure<DapperFactoryOptions>(builder.Name, options => options.DapperActions.Add(configureClient)); return builder; } } public interface IDapperFactoryBuilder { string Name { get; } IServiceCollection Services { get; } } internal class DefaultDapperFactoryBuilder : IDapperFactoryBuilder { public DefaultDapperFactoryBuilder(IServiceCollection services, string name) { Services = services; Name = name; } public string Name { get; } public IServiceCollection Services { get; } }如果要在控制器或仓储类中使用依赖项的话，我们必须配置或者说在Startup类的ConfigureServices方法中为我们的接口注册我们的依赖项类。
Startup.cs
public void ConfigureServices(IServiceCollection services) { 连接sqlserver //services.AddDapper("SqlDb", m => //{ // m.ConnectionString = Configuration.GetConnectionString("DefaultConnection"); // m.DbType = DbStoreType.SqlServer; //}); //连接Oracle services.AddDapper("OracleConnection", m => { m.ConnectionString = Configuration.GetConnectionString("OracleConnectionString"); m.DbType = DbStoreType.Oracle; }); services.AddControllers(); }写个简单的控制器，测试我们的封装的方法 UserController.cs
[Route("api/[controller]")] [ApiController] public class UserController : ControllerBase { //private readonly DapperClient _SqlDB; private readonly DapperClient _OracleDB; public UserController(IDapperFactory dapperFactory) { _OracleDB = dapperFactory.CreateClient("OracleConnection"); //_SqlDB = dapperFactory.CreateClient("SqlDb"); } [HttpGet("getuser")] public string GetUser() { var testQuery = _OracleDB.Query<dynamic>(@"SELECT * FROM TEST_USER where ROWNUM<=5"); //var result = _SqlDB.Query<dynamic>(@"select * from [UserInfo](nolock)"); return JsonConvert.SerializeObject(testQuery); } }运行截图
