C# - How to return different instances of SqlConnection using Autofac

Question

I have two databases: DbX and DbY. I have to get data from DbX, do something with it and save it to DbY. Since Models are the same for both I created abstract SqlDataAccess class, which looks like this:

public abstract class SqlDataAccess : IDataAccess
    {
        protected readonly IDbConnection _connection;

        protected SqlDataAccess(IDbConnection connection)
        {
            _connection = connection;
        }

        public virtual long Add<ModelType>(ModelType model) where ModelType : class
        {
            _connection.Open();
            var output = _connection.Insert(model);
            _connection.Close();

            return output;
        }

        public virtual ICollection<ModelType> Get<ModelType>(string sql, DynamicParameters parameters = null, Tenant tenant = null) where ModelType : class
        {
            _connection.Open();
            if (tenant != null) _connection.ChangeDatabase(tenant.DbName);
            var output = _connection.Query<ModelType>(sql, parameters).ToList();
            _connection.Close();

            return output;
        }

        public bool Update<ModelType>(ModelType model) where ModelType : class
        {
            _connection.Open();
            var output = _connection.Update(model);
            _connection.Close();

            return output;
        }

        public bool Delete<ModelType>(ModelType model) where ModelType : class
        {
            _connection.Open();
            var output = _connection.Delete(model);
            _connection.Close();

            return output;
        }
    } 

I also have XDataAccess, YDataAccess and interfaces for them:

public interface IXDataAccess: IDataAccess
    {
    }


public interface IYDataAccess: IDataAccess
    {
    }

I also created IXDbConnection and IYDbConnection.

public interface IXDbConnection: IDbConnection
    {
    }


public interface IYDbConnection: IDbConnection
    {
    }

So I end up with:

public class XDataAccess : SqlDataAccess, IXDataAccess
    {
        public XDataAccess(IXDbConnection connection)
            : base(connection)
        {
        }
    }

public class YDataAccess : SqlDataAccess, IYDataAccess
    {
        public YDataAccess(IYDbConnection connection)
            : base(connection)
        {
        }
    }

I would like to use it in a service like this:

public class AccountService : IAccountService
    {
        private readonly IXDataAccess _xDataAccess;
        private readonly IYAccess _yDataAccess;

        public AccountService(IXDataAccess xDataAccess, IYDataAccess yDataAccess)
        {
            _xDataAccess = xDataAccess;
            _yDataAccess = yDataAccess;
        }

        public ICollection<Account> GetFromDbX(DateTime time, Tenant tenant)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@SomeParameter", time);

            string sql = $@"SELECT * FROM Accounts WHERE SomeParameter = @SomeParameter";

            var output = _xDataAccess.Get<Account>(sql, parameters, tenant);

            return output;
        }

        public ICollection<Account> GetFromDbY(DateTime time)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@SomeParameter", time);

            string sql = $@"SELECT * FROM Accounts WHERE SomeParameter = @SomeParameter";

            var output = _yDataAccess.Get<Account>(sql, parameters);

            return output;
        }
    }

Now when i try to register everything in Autofac

builder.RegisterType<AccountService>().As<IAccountService>();
builder.RegisterType<XDataAccess>().As<IXDataAccess>();
builder.RegisterType<YDataAccess>().As<IYDataAccess>();
builder.Register(c => ConnectionFactory.Create("DbX")).As<IXDbConnection>();
builder.Register(c => ConnectionFactory.Create("DbY")).As<IYDbConnection>();

I get this error: System.ArgumentException: 'The type 'System.Data.IDbConnection' is not assignable to service 'SomeNamespace.DataAccess.IXDbConnection'.

ConnectionFactory.Create() returns SqlConnection.

Is it possible for it to work or am I doing something wrong? Is there a better way to do this?

Answer 1

You can't register non implemented interface. I can suggest you a solution.

Change constructers of DataAccess classes that expect IDbConnecttion

public class XDataAccess : SqlDataAccess, IXDataAccess
{
    public XDataAccess(IDbConnection connection)
        : base(connection)
    {
    }
}

 public class YDataAccess : SqlDataAccess, IYDataAccess
{
    public YDataAccess(IDbConnection connection)
        : base(connection)
    {
    }
}

Then register them creating new instances. You don't need IXDbConnection and IYDbConnection.

builder.RegisterType<AccountService>().As<IAccountService>();
builder.Register(c => new XDataAccess(ConnectionFactory.Create("DbX"))).As<IXDataAccess>();
builder.Register(c => new YDataAccess(ConnectionFactory.Create("DbY"))).As<IYDataAccess>();