I have been trying to design a good data layer that will eventually be generated. I am wondering if I have missed anything. The basic architecture contains a service class that handles connecting and eventually transactions. I wrap the IDbConnection in the service class to make sure it gets disposed properly so that the user will not need to worry about that. Are there any drawbacks to wrapping the IDbConnection in a using statement?
Sample code:
public abstract class SqlService
{
private readonly string _connectionString;
protected Service(string connectionString)
{
_connectionString = connectionString;
}
private IDbConnection CreateConnection()
{
var connection = new SqlConnection(_connectionString);
connection.Open();
return connection;
}
protected T Execute<IDbConnection, T>(Func<IDbConnection, T> query)
{
using(var connection = CreateConnection())
{
return query(connection);
}
}
protected T ExecuteTransaction<T>(Func<IDbConnection, IDbTransaction, T> query, IsolationLevel level)
{
return Execute(c =>
{
using (var transaction = c.BeginTransaction(level))
{
try
{
var result = query(c, transaction);
transaction.Commit();
return result;
}
catch (SqlException)
{
transaction.Rollback();
throw;
}
}
});
}
}
public interface ICanGetById<TEntity, TKey>
{
TEntity GetById(TKey id)
}
public interface IOrderService : ICanGetById<OrderDTO, int>
{
}
public sealed partial class OrderService : SqlService, IOrderService
{
public OrderService (string connectionString) : base(connectionString) { }
public OrderDTO GetById(int id)
{
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@Id", id);
return Execute(c => c.Query<OrderDTO>(
"usp_OrderSelect",
dynamicParameters,
commandType: CommandType.StoredProcedure));
}
}
