0

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?

Derek
  • 5,137
  • 9
  • 28
  • 39
  • 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 Feb 26 '14 at 17:49
  • 1
    You can pass connection string into the constructor of EF Context – Andrei Feb 26 '14 at 18:34

2 Answers2

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?

Community
  • 1
  • 1
user3311522
  • 1,638
  • 3
  • 19
  • 33
  • 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. – Derek Feb 26 '14 at 21:28
  • 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. – user3311522 Feb 26 '14 at 21:30
  • 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 Feb 26 '14 at 21:46
  • Class in Models folder: `partial class DealerSitesMasterEntities { public DealerSitesMasterEntities(string connectionString) : base(connectionString) { } }` – Derek Feb 26 '14 at 21:47
  • Not sure exactly what is causing the problem but I can assure you the above code is the right approach. – user3311522 Feb 26 '14 at 21:50
  • It worked! I used `public DealerSitesMasterEntities(string name) : base("name=DealerSitesMasterEntitiesQA") { }` – Derek Feb 26 '14 at 22:17
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.

Community
  • 1
  • 1
jle
  • 9,316
  • 5
  • 48
  • 67
  • 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 Feb 26 '14 at 17:37
  • 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. – Derek Feb 26 '14 at 18:21