A question about connection strings

Question

User1304912589 posted
I have an idea about connecting to different databases that I would like an opinion on (be nice). I have several connections strings. One points to development, another test and another production. Each of these live in my web.config file. When I’m in development I pass in the hardcoded key “DEVELOPMENT”. In test I pass in the hardcoded key “TEST”. And when I’m in production I pass in the hardcoded key “PRODUCTION”. These hard coded values are listed in a custom connection helper class. The net result is that this class has to be changed from environment to environment. I don’t like that. I could use configuration transformation to switch these values, but that doesn’t feel right. It also may be an issue because I’m using a JavaScript framework for the front end to call my backend WebApi code.

I am considering creating a text file that I would distribute with each environment. That text file would contain one line:
Environment=PRODUCTION

or whatever the environment is. My GetConnectionString method in my custom connection helper class would read this text file and use the environment’s value to point to the correct database. This way I would have identical code between environments except for this one configuration file. Doing this would easily allow me to create new environments and eliminate accidental changes when re releasing code to production.

I’m curious if this makes sense or if there’s a better way to point to different databases depending on the environment.

Thanks

Answer 1

User1454365735 posted

For the debug mode you can use preprocessing directive : 

#if DEBUG
#end if

Aside that, I'm not sure it'a a better alternative, but why not comment connection strings in web config...

Answer 2

User1304912589 posted

The way that I currently handle connection strings is as follows:

Given the fictitious snippet from a web.config file

<appSettings>
<add key="Environment" value="Production"/>
</appSettings>

<connectionStrings>
<add name="Development" connectionString="...." />
<add name="Production" connectionString="...." />
</connectionStrings>

I used the Environment string from appSettings to determine which database connection string to use.  In this example I would grab the environment variable value of Production and then connect to my Production SQL Server connection. 

If my web.config never changed, this would be fine.  Unfortunately there have been times when we changed the web.config and a new web.config was moved to production.  On a few occasions the person doing the install did not change the value in appSettings

from:

<appSettings>
<add key="Environment" value="Development"/>
</appSettings>

To:

<appSettings>
<add key="Environment" value="Production"/>
</appSettings>

The result was that we connected to the wrong database.

I had hoped to create a new mechanism (say a text file) that contained  something like

DatabaseConnection=Production

and that file would never been released to production again.  This way we could modify our web.config without fear that we weren't pointing to the correct database.

I am curious if there is a better way to do this?

Thanks

Answer 3

User1454365735 posted

For me it doesn't seems to be a bad idea.

What I did in the past with a project is to hardcode the environment type (Test,demo,prod) in the Main class.

With a switch statement I got the connection string from the config file. Each environment had a different name and a different "look". It doesn't matter if the config file has changed, in the worse case, it doesn't work :-)

Another way would be to store the connection string (at least db) in a dedicated table of the db, and as an exemple use the build number to retrieve the correct db to use, but it seems to be a mess... 

Answer 4

User1304912589 posted
Thanks. I’m looking at different options. That’s one I hadn’t considered

Answer 5

User283571144 posted

Hi NewToDotNet,

As far as I know, we could use Web.config  transformation feature to achieve your requirement.

From MSDN article:

When you deploy a Web site, you often want some settings in the deployed application's Web.config file to be different from the development Web.config file. For example, you might want to disable debug options and change connection strings so that they point to different databases. This topic explains how to set up a Web.config transform file that is applied automatically during deployment in order to make changes to the deployed versions of Web.config files.Web.config transforms are part of a broader group of settings that you can configure to automate the deployment process.

You could modify the web.debug.config & web.release.config.

<?xml version="1.0" encoding="utf-8"?>

<!-- For more information on using web.config transformation visit https://go.microsoft.com/fwlink/?LinkId=125889 -->

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <!--
    In the example below, the "SetAttributes" transform will change the value of 
    "connectionString" to use "ReleaseSQLServer" only when the "Match" locator 
    finds an attribute "name" that has a value of "MyDB". -->
    
    <connectionStrings>
      <add name="MyDB" 
        connectionString="Data Source=ReleaseSQLServer;Initial Catalog=MyReleaseDB;Integrated Security=True" 
        xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    </connectionStrings>
 
  <system.web>
    <!--
      In the example below, the "Replace" transform will replace the entire 
      <customErrors> section of your web.config file.
      Note that because there is only one customErrors section under the 
      <system.web> node, there is no need to use the "xdt:Locator" attribute.
      
      <customErrors defaultRedirect="GenericError.htm"
        mode="RemoteOnly" xdt:Transform="Replace">
        <error statusCode="500" redirect="InternalError.htm"/>
      </customErrors>
    -->
  </system.web>
</configuration>

Best Regards,

Brando

Answer 6

User1304912589 posted

Thanks. I know it might sound silly, but I was hoping to avoid the configuration transformation for a few reasons.  One of them is that I like to keep production and what I will call Frozen (which is a direct mirror of production so that I can test production problems without affecting production) 100% in synch. Different web.configs defeat this or at least could possibly interject an unintended difference.  To further complicate this my app consists of both an MVC front end and a phone UI that use WebApi to access my business logic and database.

My plan is to run the following at app startup. I am creating a helper class that reads a text file to determine which of the connection strings from my web.config to use (production, development, frozen...etc).  Then using the connection manager set the proper connection string).  Later my back end code will invoke a method on this helper class to return the proper connection string.

This means that with the exception of a one line text file, my environments remain in 100% synch.

I can't think of a better way to do this.

Answer 7

User475983607 posted

The web.config transforms use the solution setting pick the correct transform and build the web.config.  There is only ever one web config and you get to pick it from the solution configuraiton.

My plan is to run the following at app startup. I am creating a helper class that reads a text file to determine which of the connection strings from my web.config to use (production, development, frozen...etc).  Then using the connection manager set the proper connection string).  Later my back end code will invoke a method on this helper class to return the proper connection string.

Using a file works too.   ASP Core uses environment variables. I imagine you use the same approach in ASP.NET.

https://docs.microsoft.com/en-us/dotnet/api/system.environment.getenvironmentvariable?view=netframework-4.7.2

Answer 8

User1304912589 posted

Thanks. I am going to have to look into the GetEnvironmentVariable.

Answer 9

User753101303 posted

Hi,

What I have done once is to use the host name for naming connection strings. That way "site-test.company.com" is just using the "site-test.company.com" connection string, "localhost" is using "localhost" and so on...

Answer 10

User1304912589 posted

Initially I posted the following:

That's a pretty good idea. I'm going to have to think about that one.

After giving it some thought, I am not sure how that solves my problem.  Using your methodology don't you wind up with a different web.config per machine?  That's what I am trying to avoid?  Are you possibly doing the same thing that I am planning on doing with a text file, but instead of using a text file to provide the name of the connection, you are using the machine name?  If so, then your method does make more sense then mine as long as you know the machine name where you app is going to be running.

Thanks

Answer 11

User753101303 posted

Yes, the web config had all of them. This is not really the machine name but the "host header name" ie the name of the site used in your browser. It was basically a multi-tenant application each tenant having its own host name and database. It could be installed as separate sites or as a single site with multiple host header names and depending on which host name is used in the browser address bar it was using the corresponding database.

Answer 12

User1304912589 posted

That makes a lot of sense. It's a better idea than I was planning.

Thanks