Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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

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?

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;
    }
}
share|improve this question

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.