Change data source at runtime in ASP.NET MVC EF Database First app

Question

I am trying to set my web.config's connectionString so it can be changed at runtime from "data source=MyDevDatabase" to "data source=MyQADatabase" using a dropdown. The app is built in ASP.NET MVC, and I am using an Entity Framework Database First approach, so I have an .edmx file in my Models folder.

Here is the closest answer I have found (scroll down to Database/Model First with connection string in app.config/web.config file).

But I am not sure how to implement it. Can someone give me an example?

I've never worked with database first. Is it possible to create an abstract base context and then 2 different contexts, that extend the base context, which point at different databases? That way, you can just use the different data context's to hit the separate dbs. But like you said, you have a .edmx file and I don't know how that works. — Neil Smith
– Neil Smith, Commented Feb 26, 2014 at 17:49
You can pass connection string into the constructor of EF Context — Andrei
– Andrei, Commented Feb 26, 2014 at 18:34

Community · Accepted Answer · 2017-05-23 12:28:44Z

2

Have 2 different connection strings in your web.config with 2 names. You have to feed the connection string manually every time your call the database.

Webconfig with 2 connectionstrings:

<configuration>
  <connectionStrings>
    <add name="ConnectionString1"
         connectionString="Data Source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|YourDatabaseFilename1.mdf;User Instance=True"
         providerName="System.Data.SqlClient" />
    <add name="ConnectionString2"
         connectionString="Data Source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|YourDatabaseFilename2.mdf;User Instance=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

here is how you feed the connection string:

public MyEntities (string connectionString)
    : base(connectionString)
{
}

See:

How to have different connection string for different users in Entity Framework

Setup Entity Framework For Dynamic Connection String

EntityFramework - Where is the connection string?

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Feb 26, 2014 at 18:42

user3311522

1,6683 gold badges20 silver badges34 bronze badges

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

6 Comments

Derek Over a year ago

For some reason this is not working for me. I created partial class MyEntities and set it up how you said. Then I modified the db at the top of the controller to be private MyEntities db = new MyEntities(connectionString);. But no luck.

user3311522 Over a year ago

I have implemented this in numerous project. please paste your code and I might be able to help further. Also please post your error in detail.

Derek Over a year ago

Top of controller: private DealerSitesMasterEntities db = new DealerSitesMasterEntities(connectionString); The connectionString is the full connectionString value in the web.config, except I changed the data source.

Derek Over a year ago

Class in Models folder:

partial class DealerSitesMasterEntities { public DealerSitesMasterEntities(string connectionString) : base(connectionString) { } }

user3311522 Over a year ago

Not sure exactly what is causing the problem but I can assure you the above code is the right approach.

|

Community · Accepted Answer · 2017-05-23 10:26:11Z

0

You can do that with web.config transforms (XML transforms). Basically, you will have a web.config release file that will have a different connection string and when you build in release mode, it will use that transformation to make the final web.config. See the Microsoft tutorial here.

You can add transforms by right clicking the web.config file and clicking 'Add Config Transforms'.

Edit: looks like you mean once you have deployed. See this answer.

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Feb 26, 2014 at 17:31

jle

9,5095 gold badges50 silver badges68 bronze badges

2 Comments

Derek Over a year ago

Yeah, I want to make it so a user can change what database they are connected to from a dropdown. And I am going to add more data-sources as well, such as "data source=MyProdDatabase".

Derek Over a year ago

Thanks for the link! Ok, let's assume I am starting with the dev database and wanting to change to the QA database at runtime. It runs the ChangeDatabase method, and when it's done, source.Database.Connection.ConnectionString has changed from "Data Source=MyDevDatabase" to "Data Source=MyQADatabase". However, when I check the current connection string in the view, it says the data source is still the dev database. I can confirm this when I make edits.

Collectives™ on Stack Overflow

Change data source at runtime in ASP.NET MVC EF Database First app

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related