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

1、安装Dapper

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

2、创建Employee类和Repository

public class 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 GetByID(int id);
    Task> GetByDateOfBirth(DateTime dateOfBirth);
}
public class EmployeeRepository : IEmployeeRepository
{
    public async Task GetByID(int id)
    {
    }
    public async Task> GetByDateOfBirth(DateTime dateOfBirth)
    {
    }
}

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

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }
    public IConfiguration Configuration { get; }
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddTransient();
        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
    }
    public void Configure(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 class 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 class 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 class EmployeeRepository : IEmployeeRepository{    //...    public async Task 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(sQuery, new { ID = id });            return result.FirstOrDefault();        }    }  public async Task> 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(sQuery, new { DateOfBirth = dateOfBirth });            return result.ToList();        }    }}

6、实现异步的Controller

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

[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
    private readonly IEmployeeRepository _employeeRepo;
    public EmployeeController(IEmployeeRepository employeeRepo)
    {
        _employeeRepo = employeeRepo;
    }
    [HttpGet]
    [Route("{id}")]
    public async Task> GetByID(int id)
    {
        return await _employeeRepo.GetByID(id);
    }
    [HttpGet]
    [Route("dob/{dateOfBirth}")]
    public async Task>> 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的使用