Change DB in the runtime using Generic Repository and IoC

Question

I'm developing an N-Tier web application which uses a database for each client with Simple Injector for IoC and generic repository pattern.

When I tried to change the db connection I found that the injector runs in Application_Start in Glopal.asax where I still can't figure out who is the current user to set the connection string, I need to change it in the runtime not in the Application_Start, any ideas?

---

Sample of my Repository Class

public class Repository<T> : IRepository<T> where T : BaseEntity
{
    private readonly IDBContext _context;
    private IDbSet<T> _entities;

    public Repository(IDBContext context)
    {
        _context = context;
    }

    private IDbSet<T> Entities
    {
        get
        {
            if (_entities == null)
            {
                _entities = _context.Set<T>();
            }
            return _entities;
        }
    }
}

---

Sample of DBContxt Class

public class DBContext: DbContext, IDBContext
{
    public DBContext()
        : base("Name=DbConnectionString")
    {
        Database.SetInitializer<DBContext>(null);
    }

    //public DBContext(ConnectionStr connection)
    //    : base(connection.conn)
    //{
    //    Database.SetInitializer<DBContext>(null);
    //}

    public new IDbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity
    {
        return base.Set<TEntity>();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()
            .Where(type => !String.IsNullOrEmpty(type.Namespace))
            .Where(
                type =>
                    type.BaseType != null && type.BaseType.IsGenericType &&
                    type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
        foreach (var type in typesToRegister)
        {
            dynamic configurationInstance = Activator.CreateInstance(type);
            modelBuilder.Configurations.Add(configurationInstance);
        }
        base.OnModelCreating(modelBuilder);
    }
}

Answer 1

First of all, you will have a serious problem here, because to do this, you will have to know the password of each user, or make it plain somehow. I'm not sure if it can be done. If it can, the code below will give you a good starting point.

If you still want to do this, you need a UserContext service which returns the current User from the HttpContext. And build your connectionstring from this.

This could or should look something like this:

public interface IUserContext
{
    string CurrentUser { get; } 
}

public class HttpUserContext : IUserContext
{
    public string CurrentUser
    {
        get { return HttpContext.Current.User.Identity.Name; }
    }
}

public class ConnectionStringFactory
{
    private readonly IUserContext userContext;
    private readonly string partialConnectionString;

    public ConnectionStringFactory(
        IUserContext userContext, 
        string partialConnectionString)
    {
        this.userContext = userContext;
        this.partialConnectionString = partialConnectionString;
    }

    public string GetConnectionString()
    {
            var builder = new SqlConnectionStringBuilder(partialConnectionString);
            builder.UserID = this.userContext.CurrentUser;
            return builder.ConnectionString;
    }
}

//register like this
var connectionStringFactory = new ConnectionStringFactory(
         new HttpUserContext(), 
         "yourconnectionString");
container.RegisterSingle(connectionStringFactory);

container.RegisterPerWebRequest<YourDbContext>(() =>
{
    var connectionFactory = container.GetInstance<ConnectionStringFactory>();
    var entityConnectionString = connectionFactory.GetConnectionString;
    return new YourDbContext(entityConnectionString);
});

NOTE: For Entity Framework you will probably have to tweak the ConnectionFactory.

A little better solution would be to use integrated security with SQL server and let your AppPool impersonate the current user.

A far better approach would be to connect to your database with a single user account (the AppPool identity) and control authentication within your application by adding some extra tables to your model and add something like an IAuthorizedRepository on top of your IRepository interface. Take a look at this discussion around this subject: How to implement row based security

Answer 2

You're going to run into a problem - how do you know the user is valid against whatever database they choose?

Do they select a database from a drop down, then enter credentials? (Probably a bad idea) Do you have a master DB that has username // password // db mappings?

Once you figure that out, one way to do it would be to rewrite the connection string in the web.config http://www.beansoftware.com/ASP.NET-Tutorials/Modify-Web.Config-Run-Time.aspx I don't much like this idea.

A better approach could be to pass your connection string when you create your DB context: Pass connection string to code-first DbContext