Using dapper.net and repository pattern in Azure websites

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...

• Implement an IStoreRepository interface (although I don't know if

it's helpful to me)?

• Use Async versions of Query and OpenConnection?

• 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 GetById(int id)
    {
        using (var conn = await SqlHelper.GetOpenConnectionAsync())
        {
            const string sql = "Select * from Stores where Id = @Id";
            var res = await conn.QueryAsync(sql, new { Id = id });
            return res.FirstOrDefault();
        }
    }
    ...
}

StoreController

public async Task 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 GetOpenConnectionAsync()
    {
        return new ProfiledDbConnection(await GetOpenConnection(false), MiniProfiler.Current);
    }
    private async static Task 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;
    }
}

-
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.

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

-
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.

-
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.

-
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.

StackExchange Code Review Q#58337, answer score: 2