Using dapper.net and repository pattern in Azure websites

Question

I'm currently on Azure Websites & Azure SQL, some documentation recommends using Async as much as possible as cloud services higher latency and have chances of dropping traffic, but it's also cautioning that Async/Await have overhead.

Should I...

1. Implement an IStoreRepository interface (although I don't know if it's helpful to me)?
2. Use Async versions of Query and OpenConnection?
3. Am I repeating myself so much in different Repository classes like GetById, GetAll, etc? Is there a more elegant solution?

I am pretty new to dapper.net and I would like to have some feedback on my current implementation.

StoreRepository

public class StoreRepository
{
 public async Task<Store> GetById(int id)
 {
 using (var conn = await SqlHelper.GetOpenConnectionAsync())
 {
 const string sql = "Select * from Stores where Id = @Id";
 var res = await conn.QueryAsync<Store>(sql, new { Id = id });
 return res.FirstOrDefault();
 }
 }
 ...
}

StoreController

public async Task<ActionResult> Details(int id)
{
 var db = new StoreRepository();
 var store = await db.GetById(id);
 return View(store);
}

SQLHelper

internal static class SqlHelper
{
 public static readonly string connectionString = ConfigurationManager.ConnectionStrings["ttcn"].ConnectionString;
 public async static Task<DbConnection> GetOpenConnectionAsync()
 {
 return new ProfiledDbConnection(await GetOpenConnection(false), MiniProfiler.Current);
 }
 private async static Task<SqlConnection> GetOpenConnection(bool mars = false)
 {
 var cs = connectionString;
 if (mars)
 {
 SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(cs);
 scsb.MultipleActiveResultSets = true;
 cs = scsb.ConnectionString;
 }
 var connection = new SqlConnection(cs);
 await connection.OpenAsync();
 return connection;
 }
}

• Emil Lundin: I suggest naming your async methods "SomethingAsync". So "GetById" would be "GetByIdAsync". This in case you want to have a synchronous version of the method as well, but also to clarify to the caller that the method is asynchronous.
• Aluan Haddad: I do recommend async here its overhead is negligible relative to database access. When doing so, make sure that you pass TransactionScopeAsyncFlowOption.Enabled when explicitly beginning any transactions.

Answer 1

1. Always program to abstraction, meaning it is always preferred to write your code against interface or abstract class rather than specific implementation. So, IStoreRepository would be a good idea.

2. If you are not going to ever change your implementation, do not use abstract or interfaces. Make it simple and readable.

3. If you are considering using mapper and all, use DI container too so that you don't have to do this:

   var db = new StoreRepository();
   // creating instance like this is not a good idea, rather inject the dependencies 
   

   Same thing goes for your SQL helper class too. Inject as a dependency in your repository.

4. If you use EF/nhibernate, then your code will not be repeating as all code will have the same kind of code. You write a generic base class for them and pass the type of data you want to retrieve it from the database. But in case of hand-written SQL, that could not be done that easily as you need to write different SQL for different type. Again, that is your choice.

5. It is good to use async await for managing an SQL connection so you don't just block the currently-working thread. Overhead is small in overall picture.