什么是Dapper？
Dapper是Stack Overflow团队开发的Micro-ORM框架，Dapper是一个简单对象mapper对于Net并且在速度方面有King of Micro ORM称号，几乎与原生ADO.NET data reader一样。ORM是一个对象关系映射,负责映射database和应用程序对象。
Dapper比Entity Framework快的原因是Dapper简洁，不像EF有复杂的逻辑，例如数据追踪，不高效的数据查询，支持懒加载等。
当然EF Core也是相当快的，问题取决于你是倾向于写更多的sql 还是linq。
Dapper工作原理
Dapper扩展IDbConnection接口提供扩展方法来查询数据库，它使用动态方式生成，使其能够直接从查询结果映射POCO，也允许直接从database columns映射到POCO 属性。
Dapper是大小写不敏感的。
Dapper支持多种数据类型
Anonymous：Useful for simple queries where you don't need to create a separate class to represent your data.
Dynamic： Useful for when you need to create a dynamic list of parameters, or when you need to dynamically change the value of a parameter.
List: This allows you to specify multiple parameters on an IN clause by using a list.
String: Useful when working with SQL Server stored procedures that accept varchar input parameters.
Dapper Plus是一个高性能的micro-ORM框架，提供了简单的方式映射class到数据库table and vice versa，Dapper plus也提供了bulk 操作，提高应用程序的性能，Dapper plus扩展了IDbConnection 接口支持bulk：
Bulk Insert
Bulk Update
Bulk Delete
Bulk Merge
不幸的是Dapper Plus是收费的。
Dapper -Transaction
Dapper支持管理Transaction，Transaction允许你将多个操作分组到一个工作单元中，之后可以作为一个单元执行committed或rolled back。
下面通过Code详细解析在项目中的应用场景：
首先创建Dapper Library应用
1、创建2张表，Company&User，Company与User为1:N的关系
CREATE TABLE [dbo].[Company](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[User](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NULL,
[DisplayName] [nvarchar](50) NULL,
[CompanyId] [uniqueidentifier] NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([Id])
sql
FK_<tablename>_<tablename>, where the first tablename is the name of the foreign key table, and the second tablename is the name of the primary key table. This is simply a default and common naming convention for the (Name) field of the foreign key object.
2、创建DB table映射的实体类
public abstract class Entity<TKey>
{
[ExplicitKey]
public virtual TKey Id { get; set; }
}
[TableAttribute("Company")]
public class Company : Entity<Guid>
{
public string Name { get; set; }
public string Location { get; set; }
}
[TableAttribute("User")]
public class User : Entity<Guid>
{
public string Name { get; set; }
public string DisplayName { get; set; }
public Guid CompanyId { get; set; }
}
Entity
3、Nuget Package Manager添加package引用
Dapper.Contrib(2.0.78)
System.Data.SqlClient(4.8.5)
4、自定义BaseRepository及Service Repository
using Dapper;
using Dapper.Contrib.Extensions;
using System.Data;
namespace API.Dapper.Repository;
public abstract class BaseRepository<T> where T : class, new()
{
protected IDbTransaction _transaction;
protected IDbConnection _connection
{
get { return _transaction?.Connection; }
}
protected Int32 _commandTimeout { get; set; } = 30;
public BaseRepository(IDbTransaction transaction)
{
this._transaction = transaction;
}
public async Task<IEnumerable<T>> GetAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
return await _connection.QueryAsync<T>(sql, param, _transaction, _commandTimeout, CommandType.Text);
}
public async Task AddAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, CommandType.Text);
}
public Task<bool> DeleteAsync(T t)
{
return _connection.DeleteAsync(t, _transaction, commandTimeout: _commandTimeout);
}
public async Task DeleteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, CommandType.Text);
}
public async Task<Int32> UpdateAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout,CommandType.Text);
}
}
Base Repository
using API.Dapper.Entity;
using System.Data;
namespace API.Dapper.Repository;
public class CompanyRepository : BaseRepository<Company>
{
public CompanyRepository(IDbTransaction transaction)
: base(transaction)
{
}
}
public class UserRepository: BaseRepository<User>
{
public UserRepository(IDbTransaction transaction)
: base(transaction)
{
}
}
Service Repository
5、创建Dapper 工作单元，工作单元的作用是为了保证数据的一致性，当一个request需要操作多个table，可以将多个操作放在一个unitOfWork中，只有多个操作全部执行成功才执行Committed，否则执行Rollback,保证操作的原子性从而保证数据的一致性及完整性。
public interface IUnitOfWork : IDisposable
{
IDbConnection DbConnection { get; set; }
IDbTransaction DbTransaction { get; set; }
void Commit();
void Rollback();
}
IUnitOfWork
using System.Data;
using System.Data.SqlClient;
namespace API.Dapper.UnitOfWork;
public class UnitOfWork : IUnitOfWork
{
public IDbConnection DbConnection { get; set; }
public IDbTransaction DbTransaction { get; set; }
public UnitOfWork(string connectionString)
: this(connectionString, IsolationLevel.Unspecified)
{
}
public UnitOfWork(string connectionString, IsolationLevel isolationLevel)
{
DbConnection = new SqlConnection(connectionString);
DbConnection.Open();
DbTransaction = DbConnection.BeginTransaction(isolationLevel);
}
public void Commit()
{
try
{
DbTransaction.Commit();
}
catch
{
DbTransaction.Rollback();
throw;
}
finally
{
DbTransaction.Dispose();
DbTransaction = DbConnection.BeginTransaction();
}
}
public void Dispose()
{
if (DbTransaction != null)
{
DbTransaction.Dispose();
DbTransaction = null;
}
if (DbConnection != null)
{
DbConnection.Dispose();
DbConnection = null;
}
}
public void Rollback()
{
DbTransaction.Rollback();
}
public async Task<IEnumerable<T>> BatchQuery<T>(IEnumerable<Guid> objs, Func<List<Guid>, Task<IEnumerable<T>>> func, Int32 batchSize = 2100)
{
var result = new List<T>();
var skip = 0;
List<Guid> pageObjs;
do
{
pageObjs = objs.Skip(skip * batchSize).Take(batchSize).ToList();
var pageResult = await func(pageObjs);
result.AddRange(pageResult);
skip++;
} while (pageObjs.Count == batchSize);
return result;
}
public async Task<Int32> RunBatch<T>(IEnumerable<T> objs, Func<List<T>, Task<Int32>> func, Int32 batchSize = 2100)
{
var affectedRows = 0;
var skip = 0;
List<T> pageObjs;
do
{
pageObjs = objs.Skip(skip * batchSize).Take(batchSize).ToList();
var pageAffectedRows = await func(pageObjs);
affectedRows += pageAffectedRows;
skip++;
} while (pageObjs.Count == batchSize);
return affectedRows;
}
}
UnitOfWork
using API.Dapper.Repository;
namespace API.Dapper.UnitOfWork;
public class UserCompanyUnitOfWork : UnitOfWork
{
public UserRepository UserRepository { get; }
public CompanyRepository CompanyRepository { get; }
public UserCompanyUnitOfWork(string connectionString)
: base(connectionString)
{
UserRepository = new UserRepository(DbTransaction);
CompanyRepository = new CompanyRepository(DbTransaction);
}
}
service UnitOfWork
然后创建NetCore web project，添加Dapper Library的引用
1、配置文件指定数据库连接字符串
"ConnectionString": "Server=localhost;Database=API.Demo;User Id=sa;Password=1qaz2wsxE;Trusted_Connection=False;"
2、创建UserService操作User table
using API.Dapper.Entity;
using API.Dapper.UnitOfWork;
using Dapper;
namespace API.ServiceA.Service;
public class UserService
{
private readonly IConfiguration _configuration;
public UserService(IConfiguration configuration)
{
_configuration = configuration;
}
public async Task<IEnumerable<User>> GetAllAsync()
{
using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
var sql = $"SELECT * FROM [{typeof(User).Name}]";
return await unitOfWork.UserRepository.GetAsync(sql);
}
public async Task<IEnumerable<User>> GetAsync(IEnumerable<Guid> ids, IEnumerable<string> selectedProperties)
{
var properties = typeof(User).GetProperties().Select(p => p.Name);
var selectClause = string.Join(",", properties.Intersect(selectedProperties));
using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
return await unitOfWork.BatchQuery<User>(ids, async pageIds =>
{
var sql = $"SELECT {selectClause} FROM [{typeof(User).Name}] WHERE Id IN @Ids";
var sqlParams = new DynamicParameters();
sqlParams.Add("Ids", ids);
return await unitOfWork.UserRepository.GetAsync(sql, sqlParams);
}, 2100);
}
public async Task AddAsync(User user)
{
try
{
using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
var properties = typeof(User).GetProperties().Select(p => p.Name);
var sql = $"INSERT INTO [{typeof(User).Name}]({string.Join(",", properties)}) VALUES({string.Join(",", properties.Select(p => $"@{p}"))})";
await unitOfWork.UserRepository.AddAsync(sql, user);
unitOfWork.Commit();
}
catch (Exception)
{
throw;
}
}
public async Task UpdateAsync(IEnumerable<User> users, IEnumerable<string> updateProperties, IEnumerable<string> whereProperties)
{
try
{
var properties = typeof(User).GetProperties().Select(p => p.Name);
var updateClause = string.Join(",", properties.Intersect(updateProperties).Select(p => $"{p}=@{p}"));
var whereClause = string.Join(" AND ", properties.Intersect(whereProperties).Select(p => $"{p}=@{p}"));
var sql = $"UPDATE [dbo].[{typeof(User).Name}] SET {updateClause} WHERE {whereClause}";
using UserCompanyUnitOfWork unitOfWork = new UserCompanyUnitOfWork(_configuration["ConnectionString"]);
await unitOfWork.UserRepository.UpdateAsync(sql, users);
unitOfWork.Commit();
}
catch (Exception)
{
throw;
}
}
}
UserService
3、添加测试controller
private readonly UserService _userService;
public ServiceAController(UserService userService)
{
_userService = userService;
}
[HttpPost("user")]
public async Task<IActionResult> AddUser([FromBody] User user)
{
await _userService.AddAsync(user);
return new JsonResult("OK");
}
[HttpPatch("updateuser")]
public async Task<IActionResult> UpadateUser()
{
var users = await _userService.GetAllAsync();
users = await _userService.GetAsync(users.Select(u => u.Id), typeof(User).GetProperties().Select(p => p.Name));
Array.ForEach(users.ToArray(),
user =>
{
user.DisplayName = $"{user.DisplayName}_updated";
});
await _userService.UpdateAsync(users, new List<string> { "DisplayName" }, new List<string> { "Id" });
return new JsonResult("OK");
}
controller
4、添加default company record，原因是添加user record有外键依赖company
标签：transaction,User,var,Dapper,null,public From： https://www.cnblogs.com/qindy/p/17163633.html