How do I effectively store a connection string in machine.config only?

Question

We are moving to an environment with multiple engines of SQL Server running on the same server (a test engine and a production engine). We also have separate test and production web servers, and would like for our asp.net applications to "magically" use the test database engine on the test web server and the production database engine on the production web servers. We would like to store the connection strings in machine.config rather than in web.config, but when we put it in machine.config, Visual Studio's IDE (particularly with datasets) does not recognize that the machine.config contains the connection.

Does anyone know of a solution for displaying these machine.config connection strings in visual studio, or of a different solution that would accommodate for this?

Thanks.

Answer 1

(Note I have never actually tried or tested this but I believe it will work. Let me know if it does.) You could add your connection strings to the machine.config, then add a connectionstring.config file to your project at the root level. In properties of this file choose not to deploy

The Contents of the connectionstring.config file:

<?xml version="1.0"?>
  <connectionStrings>
    <add name="someName" connectionString="yourString" />
    <add name="someName2" connectionString="yourString2" />
    <add name="someName3" connectionString="yourString3" />
  <connectionStrings>

finally in your web.config for your project modify the connectionstring section like this

<connectionStrings file="connectionstring.config">
</connectionStrings>

Basically what you are doing is setting up the dev enviornment to read your connection strings from a specific file. When deployed to a test or production server the connectionstring.config file will not be present therefore it should revert back to using the connection strings specified in the machine config.

Answer 2

This may help...