Change connString dynamically

user_11622065 0
Viewed 972

I am currently working on an ASP.NET MVC project with Entity Framework. The solution includes the ASP.NET MVC application and a DataProvider project. The project only includes an EF model, as I have a partner who shares a database with me.

The problem is in that, that we don't actually share the same database. What I mean is that the actual server of the database is not the same. We have the same tables. But the connection string has to be changed every single time we pull from our git repository. The string changes in the web.config file of the ASP.NET MVC project and in the app.config file of the EF library.

My question is - if there is a way for our system to detect which PC it is running on and modify the connection string dynamically? Now I do it manually.

<!--PC NICO CEI-->

<!--
<connectionStrings>
    <add name="dbShubertEntities" 
         connectionString="metadata=res://*/ModelDbShubert.csdl|res://*/ModelDbShubert.ssdl|res://*/ModelDbShubert.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=E04\SQLEXPRESS;initial catalog=dbShubert;user id=gereisma17;password=12321;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>
-->

<!--PC NICO Laptop-->

<!--
<connectionStrings>
    <add name="dbShubertEntities" 
         connectionString="metadata=res://*/ModelDbShubert.csdl|res://*/ModelDbShubert.ssdl|res://*/ModelDbShubert.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-I62BLOC;initial catalog=dbShubert;user id=user;password=1234;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>
-->

This is what I do now. I am commenting the connection string. I want to automate the whole thing.

sql c#
4 Answers

You could overload the DbContext's constructor with the dynamic connection string

public class MasterDal : DbContext
    {
        public MasterDal(string nameOrConnectionString) : base(nameOrConnectionString)
        {

        }
       // DbSet &  OnModelCreating etc 
    }

& then invoke dal in Controller as required

if (logicToGetPc.equal("CEI"))
 { 
    MasterDal dal = new MasterDal("dbShubertEntitiesCEI");
   // do  mumbai related whatever 
 }
 else if (logicToGetPc.equal("Laptop"))
 {
     MasterDal dal = new MasterDal("dbShubertEntitiesCEILaptop");
 }
user_45883020

Store the connection string as an environment variable on each PC.

When creating the connection to the database, retrieve the connection string using Environment.GetEnvironmentVariable method instead.

user_19305180

if you are using aspnet core there is a way to configure environment variable as override for the app settings this way you'll have a "default" appsettings.json that contains all the common settings, then on the different machine you can configure environmnet variables that replaces (override) the connection string in different way for each machine

i suppose a similar approach is possible for aspnet for the full framework as well

as your connection string have already a quite specific name "dbShubertEntities" it sould be quite effective to override and you should not risk to have connection string collisions in case you work on different projects.

as reference see https://joonasw.net/view/asp-net-core-1-configuration-deep-dive the part where it uses .AddEnvironmentVariables()

user_7749860

(at time of writing, there's no code)

This is what appsettings.json, (or it's counterparts), was made for.

As i perceive your predicament, (you need more whitespace in your writing to make it easier to read), you are basically working in two different environments, (similar to dev versus test). A simple configuration change would be the simplest way.

As an aside: Reading between the lines, it seems like you are hard-coding connection strings, and I have a two-word advice for you: STOP IT! (I used to do that, and It's a bad habit that can be hard to shake off)

user_104581650
Related