Is wrapping the service layer worthwhile? 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&lt;IDbConnection, T&gt;(Func&lt;IDbConnection, T&gt; query)
    {
         using(var connection = CreateConnection())
         {
             return query(connection);
         }
    }

    protected T ExecuteTransaction&lt;T&gt;(Func&lt;IDbConnection, IDbTransaction, T&gt; query, IsolationLevel level)
    {
        return Execute(c =&gt;
                            {
                                using (var transaction = c.BeginTransaction(level))
                                {
                                    try
                                    {
                                        var result = query(c, transaction);
                                        transaction.Commit();

                                        return result;
                                    }
                                    catch (SqlException)
                                    {
                                        transaction.Rollback();

                                        throw;
                                    }
                                }
                            });
    }
}

public interface ICanGetById&lt;TEntity, TKey&gt;
{
    TEntity GetById(TKey id)
}

public interface IOrderService : ICanGetById&lt;OrderDTO, int&gt;
{
}

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 =&gt; c.Query&lt;OrderDTO&gt;(
                                            "usp_OrderSelect",
                                            dynamicParameters,
                                            commandType: CommandType.StoredProcedure));
    }
}
The drawback is that you are limiting the functionality of your service to perform one query per connection and you have no "batch mode"

But maybe you can implement a query batching method!

For this reason, the user of the database service perhaps should have the power to open and close the connection. Your SQLService could expose Open, Close, and Dispose.