DbConnection resolver for both transaction and no-transaction scenarios.

IDbConnectionResolver.cs

// using System;
// using System.Data.Common;

public interface IDbConnectionResolver
{
    IDbConnectionWrapper Resolve();
}

public interface IDbConnectionWrapper : IDisposable
{
    DbConnection Connection { get; }
}

LongLivingSqlConnectionResolver.cs

// using System;
// using System.Data.Common;

/// <summary>
/// Should be used inside transaction, new instance should be created for each transaction.
/// Creates single connection and returns it on each <see cref="Resolve"/> invocation.
/// Disposes the connection on <see cref="Dispose"/>.
/// <see cref="ConnectionWrapper.Dispose"/> does nothing.
/// </summary>
public class LongLivingSqlConnectionResolver : IDbConnectionResolver, IDisposable
{
    private class ConnectionWrapper : IDbConnectionWrapper
    {
        public ConnectionWrapper(
            DbConnection connection)
        {
            Connection = connection;
        }

        public DbConnection Connection { get; }

        public void Dispose() { }
    }

    private readonly Lazy<DbConnection> _connection;

    public LongLivingSqlConnectionResolver(
        Func<DbConnection> connectionFactory)
    {
        _connection = new Lazy<DbConnection>(connectionFactory);
    }

    public IDbConnectionWrapper Resolve()
    {
        return new ConnectionWrapper(_connection.Value);
    }

    public void Dispose()
    {
        if (_connection.IsValueCreated)
        {
            _connection.Value.Dispose();
        }
    }
}

ShortLivingSqlConnectionResolver.cs

// using System;
// using System.Data.Common;

/// <summary>
/// Should be used outside transaction, can be singleton.
/// Returns new connection on each <see cref="Resolve"/> invocation.
/// The connection is disposed on <see cref="ConnectionWrapper.Dispose"/>.
/// </summary>
public class ShortLivingSqlConnectionResolver : IDbConnectionResolver
{
    private class ConnectionWrapper : IDbConnectionWrapper
    {
        public ConnectionWrapper(
            DbConnection connection)
        {
            Connection = connection;
        }

        public DbConnection Connection { get; }

        public void Dispose() => Connection.Dispose();
    }

    private readonly Func<DbConnection> _connectionFactory;

    public ShortLivingSqlConnectionResolver(
        Func<DbConnection> connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public IDbConnectionWrapper Resolve()
    {
        var connection = _connectionFactory();

        return new ConnectionWrapper(connection);
    }
}