CODEWITHSUNDEEP
c#asp.netentity-framework
Rune Hansen
Rune Hansen

Reputation: 1024

How to switch between multiple, same schema, databases in my .edmx EF model?

(I have edited this question, so that it's now different from the original question.)

I have different, but same schema, databases. I have modeled this schema with a .edmx Entity Framework model in my application

So to sum up, the same database schema living on multiple SQL Servers, and being able, at run time, to change the database connection of my .edmx model, so I can switch between accessing these various databases.

Now I would like to switch between these different databases at runtime. Preferably in a simple way. How, precisely, do I do that?

Step by step tutorial or precise, all comprising, code example would be appreciated.

Below is an example of how my connection strings looks in the Web.config:

<add name="MyModelEntities" connectionString="metadata=res://*/Dal.ToolA.ToolATestOne.csdl|res://*/Dal.ToolA.ToolATestOne.ssdl|res://*/Dal.ToolA.ToolATestOne.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MyServer\MySqlServer;initial catalog=MyDatabase;user id=MyUser;password=MyPassword;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Upvotes: 3

Views: 2741

Answers (4)

Rune Hansen
Rune Hansen

Reputation: 1024

Thank you for your response. Sorry for my unclarity about the question.

I have now figured out how to do it. I found these two great links.

Multiple-database-support-with-Entity-Framework

entity-framework-dynamic-schema-changes-using-database-first-approach

So I am making a constructor which takes a connection string, as a partial class with the same name.

public partial class MyDatabaseEntities : DbContext
{
    public MyDatabaseEntities(string connectionString)
        : base(connectionString)
    {
    }
}

And then I am dynamically generating a connection string to different databases. Thus I am able to switch database runtime :)

Upvotes: 1

CodeCaster
CodeCaster

Reputation: 151738

The DbContext class has a constructor parameter nameOrConnectionString. Depending on how you initialize your DbContext, you can pass a different connection string name. If you for example store them on country name, like:

<connectionStrings>
  <add name="YourApplication_UK" connectionString="..."/>
  <add name="YourApplication_Norway" connectionString="..."/>
  <add name="YourApplication_Sweden" connectionString="..."/>
</connectionStrings>

Then from code, you can construct the appropriate connection string name:

string connectionStringName = "YourApplication_" + myApplicationLocation;
var dbContext = new DbContext(connectionStringName);

Upvotes: 5

Satii
Satii

Reputation: 1

You can use something as shown below to change the connection string http://tutorials.csharp-online.net/Connect_Data_ADO_NET%E2%80%94Change_Database_Open_Connection

When you want to switch DB, use a method as 

    EntityInstance_ReviewEntities.GetContext(GetConnectionString(country));

In the country value you can pass the current country and get the connection string and assign it to context as well.

Upvotes: 0

Corey Adler
Corey Adler

Reputation: 16149

All you would need to do is to switch the default connection string depending on what country they wanted to access. Just store the matching connection string as a static variable after the user picks a country, and then override your DbContext's constructor to always use that variable.

Like so:

For the connection string:

public static class StaticVariables
{
  public static string ConnectionString { get; set; }
}

For your DbContext constructor:

public DbContext()
{
  this(StaticVariables.ConnectionString);
}

Upvotes: 2

Related Questions