How to use a single SQL DB Connection string?

Question

Hello,

I am developing an ASP .NET application on my PC and deploying it on a host.

The application uses a Microsoft SQL DB, I have two databases one local and one on a server.

How should the application be configured so it could connect to both DBs without any code changes? (the 2 databases have identical names and structure)

Thanks

Answer 1

Store the connection string in web.config:

<configuration>
    <connectionStrings>
        <add name="Name" connectionString="..." />
    </connectionStrings>
</configuration>

Then in your code:

string connStr = ConfigurationManager.ConnectionStrings["Name"].ConnectionString;

Answer 2

and a nice website which specifies the connection string for most DBs

Answer 3

If you need to store all of them in one config file, you can also do some indirection like this:

string name = ConfigurationManager.AppSettings["DB"]
string connStr = ConfigurationManager.ConnectionStrings[name].ConnectionString;

<configuration>
 <appSettings>
  <add key="DB" name="Prod" />
 </appSettings>    
 <connectionStrings>        
   <add name="Prod" connectionString="..." />    
   <add name="Test" connectionString="..." />    
   <add name="Dev" connectionString="..." />    
 </connectionStrings>
</configuration>

It comes down to who can edit config in produciton and how you're allowed to deploy software. Sometimes this is a viable solution with the actual option to use being specified somewhere else altogether.

Answer 4

The problem with using the web.config is that you have to change it when you deploy.

I have a post arguing for machine.config here.

For places where you can't edit the machine.config I show a way to have it in a config file that is not under your web site's directory here.