ASP.NET Core 2.1 中异步使用Dapper总结

1、安装Dapper

通过Nuget安装Dapper,直接搜索dapper,安装包名就叫Dapper,就安装这一个就行。

2、创建Employee类和Repository

public >Employee
{
  public int ID { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public DateTime DateOfBirth { get; set; }
}
public interface IEmployeeRepository
{
  Task<Employee> GetByID(int id);
  Task<List<Employee>> GetByDateOfBirth(DateTime dateOfBirth);
}
public >EmployeeRepository : IEmployeeRepository
{
  public async Task<Employee> GetByID(int id)
  {
  }
  public async Task<List<Employee>> GetByDateOfBirth(DateTime dateOfBirth)
  {
  }
}

我们还需要更新项目的Startup文件，在服务层中包含我们写的Repository

public >Startup
{
  public Startup(IConfiguration configuration)
  {
    Configuration = configuration;
  }
  public IConfiguration Configuration { get; }
  public void ConpServices(IServiceCollection services)
  {
    services.AddTransient<IEmployeeRepository, EmployeeRepository>();
    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
  }
  public void Conp(IApplicationBuilder app, IHostingEnvironment env)
  {
    //...
  }
}

3、注入 IConfiguration

在ASP.NET Core项目中的appSettings.json文件中添加连接字符串，具体如下：

{
 "Logging": {
  "LogLevel": {
   "Default": "Debug",
   "System": "Information",
   "Microsoft": "Information"
  }
 },
 "ConnectionStrings": {
  "MyConnectionString": "MyConnectionString"
 }
}

ASP.NET Core引入了一个可以注入其他类的IConfiguration对象。注入的实例将包含一个方法GetConnectionString，我们可以使用该方法从appSettings.json文件中获取连接字符串。具体代码如下，

官方文档：https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/?view=aspnetcore-2.1&tabs=ba...

public >EmployeeRepository : IEmployeeRepository
{
  private readonly IConfiguration _config;
  public EmployeeRepository(IConfiguration config)
  {
    _config = config;
  }
 
  //Remainder of file is unchanged
}

4、创建SqlConnection连接对象

注入IConfiguration对象可用于我们的Repository，我们可以创建一个支持Dapper的SqlConnection对象，我们所有的Repository方法都可以使用它。

public >EmployeeRepository : IEmployeeRepository
{
  private readonly IConfiguration _config;
  public EmployeeRepository(IConfiguration config)
  {
    _config = config;
  }
  public IDbConnection Connection
  {
    get
    {
      return new SqlConnection(_config.GetConnectionString("MyConnectionString"));
    }
  }
 
  //Remainder of file is unchanged
}

5、Dapper异步的查询方法

public >EmployeeRepository : IEmployeeRepository
{
    //...
    public async Task<Employee> GetByID(int id)
    {
        using (IDbConnection conn = Connection)
        {
            string sQuery = "SELECT ID, FirstName, LastName, DateOfBirth FROM Employee WHERE ID = @ID";
            conn.Open();
            var result = await conn.QueryAsync<Employee>(sQuery, new { ID = id });
            return result.FirstOrDefault();
        }
    }
  public async Task<List<Employee>> GetByDateOfBirth(DateTime dateOfBirth)
    {
        using (IDbConnection conn = Connection)
        {
            string sQuery = "SELECT ID, FirstName, LastName, DateOfBirth FROM Employee WHERE DateOfBirth = @DateOfBirth";
            conn.Open();
            var result = await conn.QueryAsync<Employee>(sQuery, new { DateOfBirth = dateOfBirth });
            return result.ToList();
        }
    }
}

6、实现异步的Controller

创建一个EmployeeRepository可以注入的控制器，代码如下：

[Route("api/[controller]")]
[ApiController]
public >EmployeeController : ControllerBase
{
  private readonly IEmployeeRepository _employeeRepo;
  public EmployeeController(IEmployeeRepository employeeRepo)
  {
    _employeeRepo = employeeRepo;
  }
  [HttpGet]
  [Route("{id}")]
  public async Task<ActionResult<Employee>> GetByID(int id)
  {
    return await _employeeRepo.GetByID(id);
  }
  [HttpGet]
  [Route("dob/{dateOfBirth}")]
  public async Task<ActionResult<List<Employee>>> GetByID(DateTime dateOfBirth)
  {
    return await _employeeRepo.GetByDateOfBirth(dateOfBirth);
  }
}

项目代码：https://github.com/exceptionnotfound/AspNetCoreDapperAsyncDemo

参考文档：https://exceptionnotfound.net/using-dapper-asynchronously-in-asp-net-core-2-1/

相关文档：.NET Core Dapper返回存储过程输出(output)参数

.NET Core Dapper(ORM) 执行sql语句和事物(Transaction)

VS(Visual Studio)中Nuget的使用