Entity Framework : Change connection string at runtime

Question

Assuming there is an ASP.NET MVC application that uses Entity Framework 6 with a code-first approach and StructureMap as IoC.

It also uses the Unit Of Work pattern.

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 the 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 a separate database for each project that the user creates in the application. Now the problem is that we inject DbContext into the service and DbContext reads the connection string from the web.config so when user changes the database we cannot set a new connection string to the DbContext.

What do you suggest?

and how did you solve finally?! (it`s 2017 :) ) – sepehr Dec 28 '16 at 12:42 — sepehr, Dec 28 '16 at 12:42

score 19 · Answer 1 · answered Oct 21 '14 at 07:28

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.

using the default. The EF will find the connection string in the config file.
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.

Not sure how relevant with Code first, but DB first, stick the added method in a different class file so does not get removed on updates. — Maze, Mar 30 '15 at 08:37
by another class - i meant that public partial class, with method taking string passing to base put into it own cs file, that way if the entity framework CS file changes, the added runtime set connection string constructor is not lost. — Maze, Apr 02 '15 at 17:37
Man, that's amazing. Those partial classes save life sometimes! :) — Oleg, Dec 22 '15 at 00:22
Maze: I didnt understand this. I'm adding costructor to Model1.Context.cs file and whenever model updates, it is lost, where to put that constructor that takes connection string parameter. — Can, Jan 25 '17 at 07:59

score 4 · Answer 2 · answered Mar 08 '14 at 10:36

4

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.

answered Mar 08 '14 at 10:36

Ben Foster

34,340
40
176
285

1

The DBContext base class constructor calls for a parameter called nameOrConnectionString. The T4-generated class which inherits from DBContext calls the base, passing base("name=MyDbContext"), so you'd think that if you want to pass the connection string, you'd use base("ConnectionString=" + SomeConnectionString), but apparantly, that only generates an exception: Keyword not supported: 'connectionstring' – as9876 Sep 07 '14 at 07:09

score 1 · Answer 3 · answered Mar 08 '14 at 12:16

I am going to suggest a completely different path. Assuming you have your connection strings set up in your web.config, which you say you do, why wouldn't you use web.debug.config and web.release.config transforrms to set your connection strings appropriately?

i.e. in web.debug.config

<connectionStrings>
    <add name="FooEntities" connectionString="metadata=res://*/;provider=System.Data.SqlClient;provider connection string="Data Source=IP,PORT\Instancename;
    Initial Catalog=Foo;Persist Security Info=True;User ID=admin;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"/>
</connectionStrings>

and a web.release.config as such

<connectionStrings xdt:Transform="Replace">
    <add name="FooEntities" connectionString="metadata=res://*/;provider=System.Data.SqlClient;provider connection string="Data Source=LIVEIP,PORT\Instancename;
    Initial Catalog=Foo;Persist Security Info=True;User ID=admin;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"/>
</connectionStrings>

A very thorough explanation is available here

score 0 · Answer 4 · edited Apr 03 '17 at 08:32

 public partial class YourDBContextClass
 {
    // Add a constructor to allow the connection string name to be changed
 public YourDBContextClass(string connectionStringNameInConfig)
        : base("name=" + connectionStringNameInConfig)
    {
    }
}

Add multiple connection strings to your web or app.config file.

in your program code:

YourDBContextClass dbcontext = new YourDBContextClass("alternateconnectionstringname");

score 0 · Answer 5 · edited Apr 05 '17 at 03:26

The two approaches are good for two different situations:

The transform is good for deploying a connection string that only changes for the different evironments (test, production).
The approach of adding a constructor (which takes the connection string name) in a separate file to extend the partial dbcontext class allows the connection to be switched at runtime.

score -4 · Answer 6 · answered Aug 25 '16 at 05:13

Add two different Connection String in App.Config File using different Name.

Set Current connection String Name in Entity Constructor using Overloading.

In Code File

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

    public ASM_DBEntities(string conn)
        : base("name=ASM_DBEntities1")
    {

    }

When we pass string with object then is use different connection string.

Entity Framework : Change connection string at runtime

6 Answers6

Linked