Entity Framework : Change connection string at runtime

Question

Assuming there is an ASP.NET MVC application that uses Entity Framework 6 with code-first approach and StructureMap as IoC.
Also It uses Unit Of Work pattern. Here are the codes :

Domain Class

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }        
 
    }

IUnitOfWork and DbContext :

    public interface IUnitOfWork
{
    IDbSet<TEntity> Set<TEntity>() where TEntity : class;
    int SaveChanges();
}

public class Sample07Context : DbContext, IUnitOfWork
{
    public DbSet<Product> Products { set; get; }

    #region IUnitOfWork Members

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

    #endregion
}

Business logic in service classes :

   public interface IProductService
{
    void AddNewProduct(Product product);
    IList<Product> GetAllProducts();
}


    public class ProductService : IProductService
    {
        IUnitOfWork _uow;
        IDbSet<Product> _products;
        public ProductService(IUnitOfWork uow)
        {
            _uow = uow;
            _products = _uow.Set<Product>();
        }
 
        public void AddNewProduct(Product product)
        {
            _products.Add(product);
        }
 
        public IList<Product> GetAllProducts()
        {
            return _products.Include(x => x.Category).ToList();
        }
    }

Injecting the service class in controller

        public class HomeController : Controller
    {
        private IProductService _productService;
        private IUnitOfWork _uow;

        public HomeController(IUnitOfWork uow, IProductService productService)
        {
            _productService = productService;

            _uow = uow;
        }

        [HttpGet]
        public ActionResult Index()
        {
            var list = _productService.GetAllProducts();
            return View(list);
        }
    }

StructureMap Configuration that we call in app_start :

       private static void initStructureMap()
        {
            ObjectFactory.Initialize(x =>
            {
                x.For<IUnitOfWork>().HttpContextScoped().Use(() => new Sample07Context());
                x.ForRequestedType<IProductService>().TheDefaultIsConcreteType<EfProductService>();
            });
            //Set current Controller factory as StructureMapControllerFactory
            ControllerBuilder.Current.SetControllerFactory(new StructureMapControllerFactory());
        }

Everything works fine with single database but in my scenario user can use multiple databases, I mean the user should be able to change the connection string at runtime. We create separate database per each project that user creates in the application.
Now the problem is that we inject DbContext to service and DbContext reads connection string from web.config so when user changes the database we cannot set new connection string to the DbContext.
What do you suggest?

Answer 1

In my experience, I used the Database First mode in EF 6. The DbContext would be generated like below when I add Entity Data Model.

public TestEntities()
            : base("name=TestEntities")
        {
        }

The TestEntities represent the ConnectionString element in the App.Config

<connectionStrings>   
<add name="TestEntities" connectionString="..." providerName="System.Data.EntityClient" />
</connectionStrings>

But you can change the default code to below.

public partial class TestEntities : DbContext
    {
        public TestEntities()
            : base("name=TestEntities")
        {
        }

        public TestEntities(string sConnectionString)
            : base(sConnectionString)
        {
        }

...}

So you got two options to getting DB connection.

1. using the default. The EF will find the connection string in the config file.

2. passing the connection string to DbContext.

The code look like below.

EntityConnection entityConn =DBConnectionHelper.BuildConnection();
using (var db = new TestEntities(entityConn.ConnectionString))
{
....
}

As to the question How to build a EntityConnection?. Please see MSDN EntityConnection.

Hope it is helpful.

Thanks.

Answer 2

By default the name of the connection string to use in Entity Framework is inferred from the name of you DbContext class. However you can pass the connection string as a constructor parameter:

public class MyDbContext : DbContext, IUnitOfWork
{
    public MyDbContext(string connectionString)
        : base(connectionString)
    {
    }
}

Then you can configure StructureMap to pass in the current connection string e.g.

For<IUnitOfWork>().Use(ctx => new MyDbContext(TheConnectionStringToUse));

This could come from a static value that you set in your code, the current session etc.