10.2. Dapper 集成

10.2.1 关于 Dapper#

Dapper 是 .NET/C# 平台非常优秀的 微型 ORM 框架,主要是为 ADO.NET 操作对象提供拓展能力,推崇原生 sql 操作法。

Dapper 官方仓库地址:https://github.com/StackExchange/Dapper

10.2.2 如何集成#

Furion 框架中,已经推出 Dapper 拓展包 Furion.Extras.DatabaseAccessor.Dapper

10.2.2.1 注册 Dapper 服务#

使用非常简单,只需要在 Startup.cs 中添加 services.AddDapper(connStr, SqlProvider) 即可。如:

services.AddDapper("Data Source=./Furion.db", SqlProvider.Sqlite);

10.2.2.2 安装对应的数据库提供器#

  • SqlServerMicrosoft.Data.SqlClient
  • SqliteMicrosoft.Data.Sqlite
  • MySqlMySql.Data
  • NpgsqlNpgsql
  • OracleOracle.ManagedDataAccess.Core
  • FirebirdFirebirdSql.Data.FirebirdClient
安装拓展包位置

Furion 框架中,推荐将拓展包 Furion.Extras.DatabaseAccessor.Dapper 安装到 Furion.Core 层中。

10.2.3 基本使用#

在使用之前,我们可以通过构造函数注入 IDapperRepositoryIDapperRepository<TEntity> 接口,如:

  • 非泛型版本
private readonly IDapperRepository _dapperRepository;
public PersonService(IDapperRepository dapperRepository)
{
_dapperRepository = dapperRepository;
}
  • 泛型版本
private readonly IDapperRepository<Person> _personRepository;
public PersonService(IDapperRepository<Person> personRepository)
{
_personRepository = personRepository;
}

10.2.3.1 sql 操作#

var data = _dapperRepository.Query("select * from person");
var data = await _dapperRepository.QueryAsync("select * from person");
var data = _dapperRepository.Query<Person>("select * from person");
var guid = Guid.NewGuid();
var dog = _dapperRepository.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });
var count = _dapperRepository.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
);
var user = _dapperRepository.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).SingleOrDefault();

用法和官方一致,此处不再举更多例子。

10.2.3.2 <TEntity> 操作#

Furion 框架提供了 IDapperRepositoryIDapperRepository<TEntity> 两个操作仓储,后者继承前者。使用如下:

var person = personRepository.Get(1);
var persons = personRepository.GetAll();
var effects = personRepository.Insert(person);
var effects = personRepository.Update(person);
var effects = personRepository.Delete(person);
var effects = personRepository.Insert(persons); // 插入多个
var effects = personRepository.Update(persons); // 更新多个
var effects = personRepository.Delete(persons); // 删除多个
var effects = await personRepository.InsertAsync(person);

10.2.4 高级使用#

IDapperRepositoryIDapperRepository<TEntity> 仓储提供了 ContextDynamicContext 属性,该属性返回 IDbConnection 对象。

拿到该对象后,我们就可以操作 Dapper 提供的所有操作了,如:

10.2.4.1 查询一对一#

var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";
var data = dapperRepository.Context.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

10.2.4.2 查询多个结果#

var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";
using (var multi = dapperRepository.Context.QueryMultiple(sql, new {id=selectedId}))
{
var customer = multi.Read<Customer>().Single();
var orders = multi.Read<Order>().ToList();
var returns = multi.Read<Return>().ToList();
// ...
}

10.2.4.3 更多操作#

var shapes = new List<IShape>();
using (var reader = dapperRepository.Context.ExecuteReader("select * from Shapes"))
{
var circleParser = reader.GetRowParser<IShape>(typeof(Circle));
var squareParser = reader.GetRowParser<IShape>(typeof(Square));
var triangleParser = reader.GetRowParser<IShape>(typeof(Triangle));
var typeColumnIndex = reader.GetOrdinal("Type");
while (reader.Read())
{
IShape shape;
var type = (ShapeType)reader.GetInt32(typeColumnIndex);
switch (type)
{
case ShapeType.Circle:
shape = circleParser(reader);
break;
case ShapeType.Square:
shape = squareParser(reader);
break;
case ShapeType.Triangle:
shape = triangleParser(reader);
break;
default:
throw new NotImplementedException();
}
shapes.Add(shape);
}
}

10.2.5 反馈与建议#

与我们交流

给 Furion 提 Issue


了解更多

想了解更多 Dapper 知识可查阅 Dapper 官网