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...
- Implement an IStoreRepository interface (although I don't know if it's helpful to me)?
- Use Async versions of
Query
andOpenConnection
? - 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;
}
}