Using entity framework on multiple databases

Question

I am writing a payroll system that will integrate with a pre-existing system. The original system had a master database that handled user management and some global configuration, below that there are multiple databases each identical in structure, basically each database is one companies payroll database, all these are tied to the main database because it belongs to a parent company who has many subsidiaries each with their own HR department.

What I was wondering is if there is any way that I can, based on either a cookie or another method that stores what company they wish to connect to, dynamically change the entity frameworks target database based on their input using a filter?

Here's an example:

User A logs in to the site, page loads with available companies that the user has permission to access, user will then select a company, they have admin privileges in that company, they add an employee, before that action is run, asp.net will switch the connection string to the appropriate database then add the record.

At most you will end up with 2 contexts, or there are some workarounds but very less practical and EF6 is expected to support multiple database. You can check the beta, by the time your app will be ready, EF6 will be released. But it all DBs are part of one organization then merging them with additional foreign key for CompanyID will make one DB and will make reporting easier. — Akash Kava, Jul 30 '13 at 08:20
Ah I see, the problem is that the the person who made the first payroll system is very adamant about us keeping the database structure the same, he says its so that companies will be more willing to upgrade to a newer version if they don't need to do any database migrations. — clifford.duke, Jul 30 '13 at 08:31
Well it is maintenance nightmare, you will not be able to do cross db queries with joins. I am not sure about EF6 but you can check at codeplex. — Akash Kava, Jul 30 '13 at 08:40
I'm not too worried about cross db queries as subsidiary companies shouldn't be accessing each others databases. thanks for the information though, I'll take a look at EF6 — clifford.duke, Jul 30 '13 at 08:52

score 27 · Answer 1 · edited Feb 04 '14 at 20:11

It is very simple. I have:

public WMSEntities() : base("name=WMSEntities") //WMSEntities is conection string name in     web.config also the name of Entitiframework
{
}

already in autogenerated Model.Context.cs of edmx folder

To connect to multiple database in runtime, I created another constructor that takes connection string as parameter like below in same file Model.Context.cs

public WMSEntities(string connStringName)
    : base("name=" + connStringName)
{
}

Now I added other connection string in Web.Config for example

<add name="WMSEntities31" connectionString="data source=TESTDBSERVER_NAME;initial catalog=TESTDB;userid=TestUser;password=TestUserPW/>

<add name="WMSEntities" connectionString="data source=TESTDBSERVER_NAME12;initial catalog=TESTDB12;userid=TestUser12;password=TestUserPW12/>

Then, when connecting to database I call below method passing connetionString name as parameter

public static List<v_POVendor> GetPOVendorList(string connectionStringName)
{
    using (WMSEntities db = new WMSEntities(connectionStringName))
    {               
        vendorList = db.v_POVendor.ToList();                 
    }
}

Hi,,this look great....my query is, if it any database model change, how it will be impact. it will be maintenance nightmare? — nikudale, Sep 18 '16 at 15:40
What is we re-generate the model.cs file, than again we have to add the configuration `public WMSEntities(string connStringName) : base("name=" + connStringName) { }` — Narendra Singh Rathore, Mar 10 '17 at 09:01

score 7 · Accepted Answer · edited May 23 '17 at 12:00

EF6 has better support for multiple DB access from Same context. Here is a snippet from EF5. Managing the database initializer setting prior is important. You may not want to trigger ANY migrations. i.e, use this before

Database.SetInitializer(new ContextInitializerNone<MyDbContext>());

but to answer the question: Yes you can

var conn = GetSqlConn4DbName(dataSource,dbName );
var ctx = new MyDbContext(conn,true);



public DbConnection GetSqlConn4DbName(string dataSource, string dbName) {
        var sqlConnStringBuilder = new SqlConnectionStringBuilder();
        sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;
        sqlConnStringBuilder.IntegratedSecurity = true;
        sqlConnStringBuilder.MultipleActiveResultSets = true;

        var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }


 public class ContextInitializerNone<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext
{
    public void InitializeDatabase(TContext context) {  }
}

Also see StackOverflow answer using migration, sample code, and dynamic db connection

Using entity framework on multiple databases

2 Answers2

Linked

Related