This question is a bit academic now as my contract at this place is coming rapidly to a close. But for future reference, I'd be very interested in the answer. We're talking about .Net 2.0 and Visual Studio 2005. Don't limit your responses to this platform though; I'm interested in best practice rather than hacky work-arounds for an elderly platform.
The company where I've been working runs different DEV, SIT, UAT and PROD databases. They have connection strings for all four databases in their app.config file, but only one is uncommented at any given time. To do a SIT build, you have to uncomment the SIT connection string, comment out the DEV string, build, then revert app.config. This seems very clumsy.
I played with creating four separate build configurations Configuration Manager and then using the configuration variables in app.config. For example using something like <add name="DBKEY" connectionString="$(Configuration)" ProviderName="System.Data.SqlClient" />
This didn't work and the connection string was returned as a literal.
I also found the tag in MyProject.vbproj: <None Include="app.config" />. I changed this to <None Include="app_$(Configuration).config" /> with some success. I could build the solution and run the exe from the target folder. But maintaining four separate replicats of app.config with the connection string as the only difference was worse than the problem I was trying to resolve. It also didn't work with the "Setup and Deployment" project.
I've come to the conclusion that adding a custom different constant to each build type in the "advanced compile options" for each build type in the main project would be a good approach. The difficulty is that each project has a copy of the connection strings and it becomes a maintenance issue. The up-side of app.config is that there is only one place where connection strings need to be maintained.
Does anybody else have a nice, elegant solution?