目之瞬间-专注于互联网平台

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的使用