ASP.NET Core 中的 ORM 之 Dapper
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了ASP.NET Core 中的 ORM 之 Dapper，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16845字，纯文字阅读大概需要25分钟。
内容图文
目录
Dapper 简介
Dapper是.NET的一款轻量级ORM工具（GitHub），也可称为简单对象映射器。在速度方面拥有微型ORM之王的称号。
它是半自动的，也就是说实体类和SQL语句都要自己写，但它提供自动对象映射。是通过对IDbConnection接口的扩展来操作数据库的。
优点：
- 轻量，只有一个文件
- 性能高，Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。
- 支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
- 使用Dapper可以自动进行对象映射，通过Emit反射IDataReader的序列队列，来快速的得到和产生对象
使用 Dapper
下面简单创建一个Web API应用并通过Dapper访问MySQL数据。
-
创建MySQL测试数据
CREATE SCHEMA `ormdemo` ; CREATE TABLE `ormdemo`.`category` ( `Id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`Id`)); CREATE TABLE `ormdemo`.`product` ( `Id` INT NOT NULL AUTO_INCREMENT, `Name` VARCHAR(45) NOT NULL, `Price` DECIMAL(19,2) NULL, `Quantity` INT NULL, `CategoryId` INT NOT NULL, PRIMARY KEY (`Id`), INDEX `fk_product_category_idx` (`CategoryId` ASC), CONSTRAINT `fk_product_category` FOREIGN KEY (`CategoryId`) REFERENCES `ormdemo`.`category` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION); INSERT INTO `ormdemo`.`category` (`Name`) VALUES("Phones"); INSERT INTO `ormdemo`.`category` (`Name`) VALUES("Computers"); INSERT INTO `ormdemo`.`product` (`Name`,`Price`,`Quantity`,`CategoryId`) VALUES("iPhone8",4999.99,10,1); INSERT INTO `ormdemo`.`product` (`Name`,`Price`,`Quantity`,`CategoryId`) VALUES("iPhone7",2999.99,10,1); INSERT INTO `ormdemo`.`product` (`Name`,`Price`,`Quantity`,`CategoryId`) VALUES("HP750",6000.00,5,2); INSERT INTO `ormdemo`.`product` (`Name`,`Price`,`Quantity`,`CategoryId`) VALUES("HP5000",12000.00,10,2); -
创建Web API应用并添加NuGet引用
Install-Package MySql.Data Install-Package Dapper -
新建一个Product类
public class Category { public int Id { get; set; } public string Name { get; set; } } public class Product { public int Id { get; set; } public string Name { get; set; } public int Quantity { get; set; } public decimal Price { get; set; } public int CategoryId { get; set; } public virtual Category Category { get; set; } } -
新建一个DBConfig类用于创建并返回数据库连接
using MySql.Data.MySqlClient; using System.Data; using System.Configuration; public class DBConfig { //ConfigurationManager.ConnectionStrings["Connection"].ConnectionString; private static string DefaultSqlConnectionString = @"server=127.0.0.1;database=ormdemo;uid=root;pwd=Open0001;SslMode=none;"; public static IDbConnection GetSqlConnection(string sqlConnectionString = null) { if (string.IsNullOrWhiteSpace(sqlConnectionString)) { sqlConnectionString = DefaultSqlConnectionString; } IDbConnection conn = new MySqlConnection(sqlConnectionString); conn.Open(); return conn; } } -
创建简单的仓储接口和类
public interface IProductRepository { Task<bool> AddAsync(Product prod); Task<IEnumerable<Product>> GetAllAsync(); Task<Product> GetByIDAsync(int id); Task<bool> DeleteAsync(int id); Task<bool> UpdateAsync(Product prod); }public class ProductRepository : IProductRepository { public async Task<IEnumerable<Product>> GetAllAsync() { using (IDbConnection conn = DBConfig.GetSqlConnection()) { return await conn.QueryAsync<Product>(@"SELECT Id ,Name ,Quantity ,Price ,CategoryId FROM Product"); } } public async Task<Product> GetByIDAsync(int id) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { string sql = @"SELECT Id ,Name ,Quantity ,Price ,CategoryId FROM Product WHERE Id = @Id"; return await conn.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id }); } } public async Task<bool> AddAsync(Product prod) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { string sql = @"INSERT INTO Product (Name ,Quantity ,Price ,CategoryId) VALUES (@Name ,@Quantity ,@Price ,@CategoryId)"; return await conn.ExecuteAsync(sql, prod) > 0; } } public async Task<bool> UpdateAsync(Product prod) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { string sql = @"UPDATE Product SET Name = @Name ,Quantity = @Quantity ,Price= @Price ,CategoryId= @CategoryId WHERE Id = @Id"; return await conn.ExecuteAsync(sql, prod) > 0; } } public async Task<bool> DeleteAsync(int id) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { string sql = @"DELETE FROM Product WHERE Id = @Id"; return await conn.ExecuteAsync(sql, new { Id = id }) > 0; } } }在Startup ConfigureServices方法里面配置依赖注入
public void ConfigureServices(IServiceCollection services) { services.AddTransient<IProductRepository, ProductRepository>(); services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1); } -
在Controller里面调用仓储方法
[Route("api/[controller]")] [ApiController] public class ProductController : ControllerBase { private readonly IProductRepository _productRepository; public ProductController(IProductRepository productRepository) { _productRepository = productRepository; } [HttpGet] public async Task<IActionResult> Get() { var data = await _productRepository.GetAllAsync(); return Ok(data); } [HttpGet("{id}")] public async Task<IActionResult> Get(int id) { var data = await _productRepository.GetByIDAsync(id); return Ok(data); } [HttpPost] public async Task<IActionResult> Post([FromBody] Product prod) { if (!ModelState.IsValid) { return BadRequest(ModelState); } await _productRepository.AddAsync(prod); return NoContent(); } [HttpPut("{id}")] public async Task<IActionResult> Put(int id, [FromBody] Product prod) { if (!ModelState.IsValid) { return BadRequest(ModelState); } var model = await _productRepository.GetByIDAsync(id); model.Name = prod.Name; model.Quantity = prod.Quantity; model.Price = prod.Price; await _productRepository.UpdateAsync(model); return NoContent(); } [HttpDelete("{id}")] public async Task<IActionResult> Delete(int id) { await _productRepository.DeleteAsync(id); return NoContent(); } } -
测试API是否可以正常工作
-
Dapper对存储过程和事务的支持
存储过程using (var connection = My.ConnectionFactory()) { connection.Open(); var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure); My.Result.Show(affectedRows); }事务using (var connection = My.ConnectionFactory()) { connection.Open(); using (var transaction = connection.BeginTransaction()) { var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"}, transaction: transaction); transaction.Commit(); } } -
Dapper对多表映射的支持
var selectAllProductWithCategorySQL = @"select * from product p inner join category c on c.Id = p.CategoryId Order by p.Id"; var allProductWithCategory = connection.Query<Product, Category, Product>(selectAllProductWithCategorySQL, (prod, cg) => { prod.Category = cg; return prod; });
使用 Dapper Contrib 或其他扩展
Dapper Contrib扩展Dapper提供了CRUD的方法
- Get
- GetAll
- Insert
- Update
- Delete
- DeleteAll
-
添加NuGet引用Dapper.Contrib
Install-Package Dapper.Contrib -
为Product类添加数据注解
[Table("Product")] public class Product { [Key] public int Id { get; set; } public string Name { get; set; } public int Quantity { get; set; } public decimal Price { get; set; } public int CategoryId { get; set; } public virtual Category Category { get; set; } } -
增加一个新的仓储类继承
public class ContribProductRepository : IProductRepository { public async Task<bool> AddAsync(Product prod) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { return await conn.InsertAsync(prod) > 0; } } public async Task<IEnumerable<Product>> GetAllAsync() { using (IDbConnection conn = DBConfig.GetSqlConnection()) { return await conn.GetAllAsync<Product>(); } } public async Task<Product> GetByIDAsync(int id) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { return await conn.GetAsync<Product>(id); } } public async Task<bool> DeleteAsync(int id) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { var entity = await conn.GetAsync<Product>(id); return await conn.DeleteAsync(entity); } } public async Task<bool> UpdateAsync(Product prod) { using (IDbConnection conn = DBConfig.GetSqlConnection()) { return await conn.UpdateAsync(prod); } } }修改Startup ConfigureServices方法里面配置依赖注入
services.AddTransient<IProductRepository, ContribProductRepository>();测试，这样可以少写了不少基本的SQL语句。
-
其他一些开源的Dapper扩展
类库 提供的方法 Dapper.SimpleCRUD GetGetListGetListPagedInsertUpdateDeleteDeleteListRecordCountDapper Plus Bulk InsertBulk DeleteBulk UpdateBulk MergeBulk Action AsyncBulk Also ActionBulk Then ActionDapper.FastCRUD GetFindInsertUpdateBulkUpdateDeleteBulkDeleteCountDapper.Mapper Multi-mapping
引入工作单元 Unit of Work
仓储模式往往需要工作单元模式的介入来负责一系列仓储对象的持久化，确保数据完整性。网上关于工作单元模式的实现方式有多种，但其本质都是工作单元类通过创建一个所有仓储共享的数据库上下文对象，来组织多个仓储对象。
网上的一些实现方式：
-
Implementing the Repository and Unit of Work Patterns in an ASP.NET MVC Application
微软之前给出的一个示例，仓储类做为工作单元的变量，并通过工作单元传入一致的context参数创建。public class UnitOfWork : IDisposable { private SchoolContext context = new SchoolContext(); private GenericRepository<Department> departmentRepository; private GenericRepository<Course> courseRepository; public GenericRepository<Department> DepartmentRepository { get { if (this.departmentRepository == null) { this.departmentRepository = new GenericRepository<Department>(context); } return departmentRepository; } } public GenericRepository<Course> CourseRepository { get { if (this.courseRepository == null) { this.courseRepository = new GenericRepository<Course>(context); } return courseRepository; } } public void Save() { context.SaveChanges(); } private bool disposed = false; protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { context.Dispose(); } } this.disposed = true; } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } } -
DDD 领域驱动设计－谈谈 Repository、IUnitOfWork 和 IDbContext 的实践
博客园一位大神的总结，最终采用的方案是仓储类负责查询，工作单元类负责增删改等数据持久化操作。
优缺点不作讨论，适合自己的就是最好的，这里采用了另外一种实现方式：
-
定义DapperDBContext
public interface IContext : IDisposable { bool IsTransactionStarted { get; } void BeginTransaction(); void Commit(); void Rollback(); } public abstract class DapperDBContext : IContext { private IDbConnection _connection; private IDbTransaction _transaction; private int? _commandTimeout = null; private readonly DapperDBContextOptions _options; public bool IsTransactionStarted { get; private set; } protected abstract IDbConnection CreateConnection(string connectionString); protected DapperDBContext(IOptions<DapperDBContextOptions> optionsAccessor) { _options = optionsAccessor.Value; _connection = CreateConnection(_options.Configuration); _connection.Open(); DebugPrint("Connection started."); } #region Transaction public void BeginTransaction() { if (IsTransactionStarted) throw new InvalidOperationException("Transaction is already started."); _transaction = _connection.BeginTransaction(); IsTransactionStarted = true; DebugPrint("Transaction started."); } public void Commit() { if (!IsTransactionStarted) throw new InvalidOperationException("No transaction started."); _transaction.Commit(); _transaction = null; IsTransactionStarted = false; DebugPrint("Transaction committed."); } public void Rollback() { if (!IsTransactionStarted) throw new InvalidOperationException("No transaction started."); _transaction.Rollback(); _transaction.Dispose(); _transaction = null; IsTransactionStarted = false; DebugPrint("Transaction rollbacked and disposed."); } #endregion Transaction #region Dapper Execute & Query public async Task<int> ExecuteAsync(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, commandType); } public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.QueryAsync<T>(sql, param, _transaction, _commandTimeout, commandType); } public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text) { return await _connection.QueryFirstOrDefaultAsync<T>(sql, param, _transaction, _commandTimeout, commandType); } public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text) { return await _connection.QueryAsync(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType); } #endregion Dapper Execute & Query public void Dispose() { if (IsTransactionStarted) Rollback(); _connection.Close(); _connection.Dispose(); _connection = null; DebugPrint("Connection closed and disposed."); } private void DebugPrint(string message) { #if DEBUG Debug.Print(">>> UnitOfWorkWithDapper - Thread {0}: {1}", Thread.CurrentThread.ManagedThreadId, message); #endif } } -
定义UnitOfWork
public interface IUnitOfWork : IDisposable { void SaveChanges(); } public class UnitOfWork : IUnitOfWork { private readonly IContext _context; public UnitOfWork(IContext context) { _context = context; _context.BeginTransaction(); } public void SaveChanges() { if (!_context.IsTransactionStarted) throw new InvalidOperationException("Transaction have already been commited or disposed."); _context.Commit(); } public void Dispose() { if (_context.IsTransactionStarted) _context.Rollback(); } } -
定义UnitOfWorkFactory
public interface IUnitOfWorkFactory { IUnitOfWork Create(); } public class DapperUnitOfWorkFactory : IUnitOfWorkFactory { private readonly DapperDBContext _context; public DapperUnitOfWorkFactory(DapperDBContext context) { _context = context; } public IUnitOfWork Create() { return new UnitOfWork(_context); } } -
定义服务扩展
public class DapperDBContextOptions : IOptions<DapperDBContextOptions> { public string Configuration { get; set; } DapperDBContextOptions IOptions<DapperDBContextOptions>.Value { get { return this; } } } public static class DapperDBContextServiceCollectionExtensions { public static IServiceCollection AddDapperDBContext<T>(this IServiceCollection services, Action<DapperDBContextOptions> setupAction) where T : DapperDBContext { if (services == null) { throw new ArgumentNullException(nameof(services)); } if (setupAction == null) { throw new ArgumentNullException(nameof(setupAction)); } services.AddOptions(); services.Configure(setupAction); services.AddScoped<DapperDBContext, T>(); services.AddScoped<IUnitOfWorkFactory, DapperUnitOfWorkFactory>(); return services; } } - 怎么使用
-
创建一个自己的Context并继承DapperDBContext。下面测试的TestDBContext是采用MySQL数据库并返回MySqlConnection。
public class TestDBContext : DapperDBContext { public TestDBContext(IOptions<DapperDBContextOptions> optionsAccessor) : base(optionsAccessor) { } protected override IDbConnection CreateConnection(string connectionString) { IDbConnection conn = new MySqlConnection(connectionString); return conn; } } -
仓储类里面添加DapperDBContext引用
public class UowProductRepository : IProductRepository { private readonly DapperDBContext _context; public UowProductRepository(DapperDBContext context) { _context = context; } public async Task<Product> GetByIDAsync(int id) { string sql = @"SELECT Id ,Name ,Quantity ,Price ,CategoryId FROM Product WHERE Id = @Id"; return await _context.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id }); } public async Task<bool> AddAsync(Product prod) { string sql = @"INSERT INTO Product (Name ,Quantity ,Price ,CategoryId) VALUES (@Name ,@Quantity ,@Price ,@CategoryId)"; return await _context.ExecuteAsync(sql, prod) > 0; } } -
Startup里面注册服务
public void ConfigureServices(IServiceCollection services) { services.AddDapperDBContext<TestDBContext>(options => { options.Configuration = @"server=127.0.0.1;database=ormdemo;uid=root;pwd=password;SslMode=none;"; }); services.AddTransient<IProductRepository, UowProductRepository>(); services.AddTransient<ICategoryRepository, UowCategoryRepository>(); services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1); } -
Controller调用
public class ProductController : ControllerBase { private readonly IUnitOfWorkFactory _uowFactory; private readonly IProductRepository _productRepository; private readonly ICategoryRepository _categoryRepository; public ProductController(IUnitOfWorkFactory uowFactory, IProductRepository productRepository, ICategoryRepository categoryRepository) { _uowFactory = uowFactory; _productRepository = productRepository; _categoryRepository = categoryRepository; } [HttpGet("{id}")] public async Task<IActionResult> Get(int id) { var data = await _productRepository.GetByIDAsync(id); return Ok(data); } [HttpPost] public async Task<IActionResult> Post([FromBody] Product prod) { if (!ModelState.IsValid) { return BadRequest(ModelState); } //await _productRepository.AddAsync(prod); using (var uow = _uowFactory.Create()) { await _productRepository.AddAsync(prod); uow.SaveChanges(); } return NoContent(); } }
-
源代码
参考
来源：https://www.cnblogs.com/royzshare/p/9522127.html
内容总结
以上是互联网集市为您收集整理的ASP.NET Core 中的 ORM 之 Dapper全部内容，希望文章能够帮你解决ASP.NET Core 中的 ORM 之 Dapper所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】