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