How can I use multiple entity framework connection strings in a project?

Question

I've created a repository class and have the following code:

public class InventoryDA : Accident_Reporting_Entities
{

}

Web.config:

<add name="Accident_Reporting_Entities" connectionString="metadata=res://*/Models.IncidentModel.csdl|res://*/Models.IncidentModel.ssdl|res://*/Models.IncidentModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=server;initial catalog=Accident_Reporting;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

<add name="Accident_Reporting_Entities2" connectionString="metadata=res://*/Models.IncidentModel.csdl|res://*/Models.IncidentModel.ssdl|res://*/Models.IncidentModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=server;initial catalog=Accident_Reporting2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I'm wondering how I can have multiple connections and replace Accident_Reporting__Entities with another connection string. The connections are of different databases that have the same design. Is it possible to check for a session variable and change this connection according to the variable, and would this be the best way?

You didn't explain anything about your base type Accident_Reporting_Entities, so answers you can get may vary a lot. Anyway you can implement two "different" DbContext types which you setup using two different connection strings. And then your logic will decide which context to use. Will it be http session, http request, user input or something else, it's of course up to you :) — Darjan Bogdan
– Darjan Bogdan, Commented Feb 18, 2019 at 15:08
This sounds like the answer I'm looking for, but I'm not the most experienced with MVC. Could you give a simple example so I can see the syntax of using two DbContext types? — sjohn285
– sjohn285, Commented Feb 18, 2019 at 15:12

Fabiano França · Accepted Answer · 2019-02-18 21:33:00Z

2

You can use the same DbContext with multiple connection strings:

public partial class SchoolDBEntities : DbContext
{
    public SchoolDBEntities(string connectionString) : base(connectionString)
    {

    }
}

var db1 = new SchoolDBEntities("Server=myServerAddress;Database=myDataBase1;User Id=myUsername;Password=myPassword;");
var db2 = new SchoolDBEntities("Server=myServerAddress;Database=myDataBase2;User Id=myUsername;Password=myPassword;");

Or you can pass to the contructor the name of the key in the Web.config file:

<configuration>
    <connectionStrings>
        <add name="myDataBase1" connectionString="Server=myServerAddress;Database=myDataBase1;User Id=myUsername;Password=myPassword;" />
        <add name="myDataBase2" connectionString="Server=myServerAddress;Database=myDataBase2;User Id=myUsername;Password=myPassword;" />
    </connectionStrings>
</configuration>

var db1 = new SchoolDBEntities("myDataBase1");
var db2 = new SchoolDBEntities("myDataBase2");

edited Feb 18, 2019 at 21:33

answered Feb 18, 2019 at 16:20

Fabiano França

611 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sjohn285 Over a year ago

If I were to use a session variable to decide which connection string to use, would I need to do a check on the variable in every controller action?

Fabiano França Over a year ago

Yes, or you can use a factory to keep the logic in one place.

sjohn285 Over a year ago

Do you know of where I could see a good example of a factory?

Collectives™ on Stack Overflow

How can I use multiple entity framework connection strings in a project?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related