재우니의 블로그


asp.net mvc 에서 dapper.net 을 통해 async await 로 처리하는 방법


https://gist.github.com/jsauve/ffa2f0dc534aee3a3f16


using System;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;

public class Program
{
    public static void Main()
    {
        var connectionString = "your connection string";
        PersonRepository personRepo = new PersonRepository(connectionString);
        Person person = null;
        Guid Id = new Guid("{82B31BB2-85BF-480F-8927-BB2AB71CE2B3}");
        
        // Typically, you'd be doing this inside of an async Web API controller, // not the main method of a console app.
        // So, we're just using Task.Factory to simulate an async Web API call.
        var task = new Task(async () =>
        {
            person = await personRepo.GetPersonById(Id);
        });
        
        // This just prevents the console app from exiting before the async work completes.
        Task.WaitAll(task);
    }
}

// Just a simple POCO model
public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

// Yes, I know this doesn't fit definition of a generic repository,
// but the assumption is that I have no idea how you want to get
// your data. That's up to you. This Base repo exists for the
// sole purpoose of providing SQL connection management.
public abstract class BaseRepository
{
    private readonly string _ConnectionString;
    protected BaseRepository(string connectionString)
    {
        _ConnectionString = connectionString;
    }

    // use for buffered queries that return a type
    protected async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData)
    {
        try
        {
            using (var connection = new SqlConnection(_ConnectionString))
            {
                await connection.OpenAsync();
                return await getData(connection);
            }
        }
        catch (TimeoutException ex)
        {
            throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
        }
        catch (SqlException ex)
        {
            throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
        }
    }
    
    // use for buffered queries that do not return a type
    protected async Task WithConnection(Func<IDbConnection, Task> getData)
{
try
{
using (var connection = new SqlConnection(_ConnectionString))
{
await connection.OpenAsync();
await getData(connection);
}
}
catch (TimeoutException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
}
catch (SqlException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
}
}

    // use for non-buffered queries that return a type
    protected async Task<TResult> WithConnection<TRead, TResult>(Func<IDbConnection, Task<TRead>> getData, Func<TRead, Task<TResult>> process)
    {
        try
        {
            using (var connection = new SqlConnection(_ConnectionString))
            {
                await connection.OpenAsync();
                var data = await getData(connection);
                return await process(data);
            }
        }
        catch (TimeoutException ex)
        {
            throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
        }
        catch (SqlException ex)
        {
            throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
        }
    }
    
    
}

public class PersonRepository : BaseRepository
{
    public PersonRepository(string connectionString): base (connectionString)
    {
    }

    // Assumes you have a Person table in your DB that
    // aligns with the Person POCO model.
    //
    // Assumes you have an exsiting SQL sproc in your DB
    // with @Id UNIQUEIDENTIFIER as a parameter. The sproc
    // returns rows from the Person table.
    public async Task<Person> GetPersonById(Guid Id)
    {
        return await WithConnection(async c =>
        {
            var p = new DynamicParameters();
            p.Add("Id", Id, DbType.Guid);
            var people = await c.QueryAsync<Person>(sql: "sp_Person_GetById", param: p, commandType: CommandType.StoredProcedure);
            return people.FirstOrDefault();
        });
    }
}