|
Dapper 簡介Dapper是.NET的一款輕量級ORM工具（GitHub），也可稱為簡單對象映射器。在速度方面擁有微型ORM之王的稱號。
它是半自動的，也就是說實體類和SQL語句都要自己寫，但它提供自動對象映射。是通過對IDbConnection接口的擴展來操作數(shù)據(jù)庫的。 優(yōu)點： 輕量，只有一個文件 性能高，Dapper的速度接近與IDataReader，取列表的數(shù)據(jù)超過了DataTable。 支持多種數(shù)據(jù)庫。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server 使用Dapper可以自動進(jìn)行對象映射，通過Emit反射IDataReader的序列隊列，來快速的得到和產(chǎn)生對象
使用 Dapper下面簡單創(chuàng)建一個Web API應(yīng)用并通過Dapper訪問MySQL數(shù)據(jù)。 創(chuàng)建MySQL測試數(shù)據(jù) 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);
創(chuàng)建Web API應(yīng)用并添加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類用于創(chuàng)建并返回數(shù)據(jù)庫連接 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;
}
}
創(chuàng)建簡單的倉儲接口和類 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里面調(diào)用倉儲方法 [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對存儲過程和事務(wù)的支持 存儲過程
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);
}
事務(wù)
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類添加數(shù)據(jù)注解 [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 | Get GetList GetListPaged Insert Update Delete DeleteList RecordCount | | Dapper Plus | Bulk Insert Bulk Delete Bulk Update Bulk Merge Bulk Action Async Bulk Also Action Bulk Then Action | | Dapper.FastCRUD | Get Find Insert Update BulkUpdate Delete BulkDelete Count | | Dapper.Mapper | Multi-mapping |
引入工作單元 Unit of Work倉儲模式往往需要工作單元模式的介入來負(fù)責(zé)一系列倉儲對象的持久化，確保數(shù)據(jù)完整性。網(wǎng)上關(guān)于工作單元模式的實現(xiàn)方式有多種，但其本質(zhì)都是工作單元類通過創(chuàng)建一個所有倉儲共享的數(shù)據(jù)庫上下文對象，來組織多個倉儲對象。 網(wǎng)上的一些實現(xiàn)方式： Implementing the Repository and Unit of Work Patterns in an ASP.NET MVC Application
微軟之前給出的一個示例，倉儲類做為工作單元的變量，并通過工作單元傳入一致的context參數(shù)創(chuàng)建。 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 領(lǐng)域驅(qū)動設(shè)計－談?wù)?Repository、IUnitOfWork 和 IDbContext 的實踐
博客園一位大神的總結(jié)，最終采用的方案是倉儲類負(fù)責(zé)查詢，工作單元類負(fù)責(zé)增刪改等數(shù)據(jù)持久化操作。
優(yōu)缺點不作討論，適合自己的就是最好的，這里采用了另外一種實現(xiàn)方式： 定義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);
}
}
定義服務(wù)擴展 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;
}
}
怎么使用 創(chuàng)建一個自己的Context并繼承DapperDBContext。下面測試的TestDBContext是采用MySQL數(shù)據(jù)庫并返回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里面注冊服務(wù) 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調(diào)用 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();
}
}
|
