前言
相信大家在选择ORM(对象关系映射器)框架的时候,都有自己的比较跟个人习惯,目前.net国内主流的ORM有Entity Framework (EF) 、NHibernate、Dapper、SqlSuger等等。在选择一个适合自己的ORM的时候,我会考虑到几个方面,社区活跃度、功能完善度、支持多种SQL、性能方面,而Dapper在速度方面拥有Micro-ORM之王的称号,社区活跃度也还不错,功能也比较完善,因此本文选择Dapper作为我们项目的ORM框架来访问oracle数据库,选择访问oracle数据库是因为网上有很多访问mssql,Mysql的了,这里就演示访问oracle的。
开始
创建Asp.Net Core API项目
这步参照我的文章《.net core3.1项目部署到linux的docker》
NuGet添加依赖项
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);
}
}
运行截图