Skip to content

Instantly share code, notes, and snippets.

@jsauve
Last active August 18, 2023 17:58
Show Gist options
  • Star 31 You must be signed in to star a gist
  • Fork 10 You must be signed in to fork a gist
  • Save jsauve/ffa2f0dc534aee3a3f16 to your computer and use it in GitHub Desktop.
Save jsauve/ffa2f0dc534aee3a3f16 to your computer and use it in GitHub Desktop.
Async Dapper Demo. Includes buffered and non-buffered connection helpers.
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();
});
}
}
@kiquenet
Copy link

Good! Any sample real world using EntityPOCORepository in ASP.NET application ?

"future post" in your blog ?
http://www.joesauve.com/async-dapper-and-async-sql-connection-management/

The only cautionary note is that, since it's all async, if your service or app starts getting hit too hard (this depends on your server architecture and resources), it may start returning HTTP 503 responses. So, you may need a throttler or a request queue or something. That's not something I've tackled yet, but maybe I'll do a future post about that.

@denniscpolley
Copy link

Do you have an example of this approach implementing "unit of work"?

@jsauve
Copy link
Author

jsauve commented Nov 29, 2020

Do you have an example of this approach implementing "unit of work"?

No. And this is some very old code of mine.

According to Microsoft, the concept of Unit Of Work is encapsulated in DbContext in EF Core:
https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-implementation-entity-framework-core#implement-custom-repositories-with-entity-framework-core

Using EF Core and Dapper will probably get you what you need.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment