[ORM] Using Dapper async in asp.net core 2.1

Dapper 是一套輕量化 ORM 套件，有優秀的效能，支援物件對應，與快速查詢，...，等。

Dapper 優點

1. 查詢快速
2. 優秀的效能
3. 支援物件映射
4. 支援預存程序 SP
5. 支援 Bulk 處理
6. 支援強型別
7. 支援動態綁定
8. 支援參數化查詢
9. 支援例舉

Packages

Please install Dapper

Nuget
PM> Install-Package Dapper -Version 1.50.5

建立 Model

public class Employee
{
    public int Id {get;set;}
    public string Name {get;set;}
    public Datetime birthday {get;set;}
}

Repository Pattern

IRepository

    public interface IRepository<T>
    {
        Task<List<T>> GetAll();
        Task<T> GetById(int id);
    }

XXXRepository

public class EmployeeRepository : IRepository<Employee>
    {
        readonly IConfiguration _config;

        public EmployeeRepository(IConfiguration config)
        {
            this._config = config;
        }
        public IDbConnection Connection
        {
            get
            {
                return new SqlConnection(_config.GetConnectionString("MyConnectionString"));
            }
        }

        public async Task<List<Employee>> GetAll()
        {
            using (IDbConnection conn = Connection)
            {
                string sQuery = "SELECT * FROM Employee";
                conn.Open();
                var result = await conn.QueryAsync<Employee>(sQuery);
                return result.ToList<Employee>();
            }
        }

        public async Task<Employee> GetByIdAsync(int id)
        {
            using (IDbConnection conn = Connection)
            {
                string sQuery = "SELECT ID, Name, Birthday FROM Employee WHERE ID = @ID";
                conn.Open();
                var result = await conn.QueryAsync<Employee>(sQuery, new { ID = id });
                return result.FirstOrDefault();
            }
        }
    }

註冊到容器

將 Repository 註冊到容器裡。另外，由於相容性的問題，在 ConfigureServices 裡，需要新增 CompatibilityVersion.Version_2_1。

public void ConfigureServices(IServiceCollection services)
{
...
services.AddTransient<IRepository<Employee>, EmployeeRepository>();
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
...
}

Reference

1. Dapper
2. Dapper-Git
3. Using Dapper Asynchronously in ASP.NET Core 2.1