About the author

Steven HarmanSteven Harman is a passionate developer who believes that writing great software isn't just a job, its a craft.

ASP.NET MVP

For recent posts and more about me, scroll to the bottom.

Sponsors

Subscribe

  • Subscribe to my feed. via RSS
  • Subscribe via email via email

Jobs

Badges

  • Subtext Project
  • Support Subtext
  • HiddenNetwork.com Banner
| Home |

Tip: Put Connection Strings in Their Own Configuration File

asp.net 2.0 introduced a new section in the web.config file -ConnectionStrings. This new configuration section allows you to add connection strings in your web.config, like we always have, or in a different data source.

In Subtext 2.0 we're using this new feature to split the connection string out into a new configuration file, user.config. Doing this should make day to day development easier for our development team by reducing the number of merge conflicts in web.config. Since most every developer has a different database configuration, it makes sense to keep this information in its own file and away from the other more static configuration settings.

Show me how!

I'm a visual type of guy and typically get more out of a demo or code sample than from some author's rambling writing...

Subtext's web.config

<configuration>
  ...
  <connectionStrings configSource="user.config">
    <!-- you can still add connection strings here -->
  </connectionStrings>
  ...

As John pointed out in the comments, when using a configSource you must move all values under the configuration section into the external file. Which is why I've crossed out the comment in the above code sample.

The user.config

<connectionStrings>
  <add name="subtextData"
    connectionString="Server=.;Database=myData;Trusted_Connection=True;" />
</connectionStrings>

Then each developer modifies their user.config file for their setup. No more merging nor conflict issues with that sucker in web.config.

I realize you could still run into some issues where another developer accidentally checks in their user.config and then SVN tries to merge the changes into your file. But since the only thing in that file is the connection string settings, you can just revert the other developer's changes without the worry of losing other, intended, changes.

Connection string resources

In the above code you might have noticed that I set my Server equal to . (dot). I believe that is just shorthand for localhost, but I don't remember where I picked that up. Does anyone out there know?

In trying to find out where I first learned that syntax, I used the following resources that might help you get your connection string just right:

What others are saying.

# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Simone
Jun 07, 2007
And if someone is wondering who came out with that idea to put the connection sting in a different file:
Managing application configurations in development teams
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Steve Harman
Jun 07, 2007
@Simo: Wow... I totally forgot you wrote that post - and I even commented on it. Boy do I feel dumb now. :)
# Links (6/7/2007)
Gravatar Member Blogs
Jun 07, 2007
.NET Synchronize up to 7 folders with System.IO.FileSystemWatcher NET C#3.0 Session at TechEd2007 - Code
# Tip: Put Connection Strings in Their Own Configuration File
Gravatar DotNetKicks.com
Jun 07, 2007
You've been kicked (a good thing) - Trackback from DotNetKicks.com
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Dan Hounshell
Jun 08, 2007
Steve, I usually just use the . for localhost as well (or ./instancename for a named instance). I'm not sure where I picked it up either.

We're now shipping Community Server with the connection strings in a separate config file, surprisingly called ConnectionStrings.config :)
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Si Philp
Jun 08, 2007
So simple yet so effective. I think there are times where we over complicate things without realising it and not taking time to think about the small simple things :S
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Dave Ward
Jun 08, 2007
I have always struggled with web.config sync issues between development and production environments, due to differing connection strings. Thanks for the great tip.
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar ajdotnet
Jun 15, 2007
We just had the same problem (every developer has its own SQL server instance, thus its own connection string). We solved it with SQL Server 2005 aliases (available in "SQL Server Configuration Manager"). This does only adress the server name, though.

Anyway, I'm a friend of getting as much information out of the web.config as possible. It serves too many purposes.
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar John
Jul 12, 2007
You can't add connection strings to a connectionStrings node where you've specified a value for "configSource" - I get an error "A section using 'configSource' may contain no other attributes or elements." if I do this.

How come it can't work the same way as appSettings and just override the set values with the ones from the external file?


# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Steve Harman
Jul 13, 2007
@John: I'm not sure I follow what you're saying. Can you post some snippets to demonstrate?
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar John
Jul 13, 2007
Sure - configuring connectionStrings as below with both an external file and specified value causes the error I mentioned before:

<connectionStrings configSource="connectionStrings.config">
<add name="DB" connectionString="Database=DB;Server=0.0.0.0;User Id=x;Password=y;" providerName="System.Data.SqlClient"/>

</connectionStrings>

So where it says "You can still add connection strings here" in the example unfortunately doesn't seem to be true. It's a shame if there's no way to do this the same way as you can override web.config settings in a user.config file.
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Steve Harman
Jul 13, 2007
@John: You are indeed correct, sir! It even says that right in the MSDN docs:

When you use the configSource attribute, you must move the entire section to a separate file because there is no merging of element settings.


I really need to start checking my facts before running my mouth. :)

But in my defense, I've not actually tried using the configSource element and adding values under that config section as shown in the example. Instead I just assumed it would work like appSettings and the file attribute.

This really is unfortunate as it would ease a lot of the pain we feel when running our applications across several environments... and think about how nice it would be for large (and distributed) development teams. Like say, and Open Source project.
# re: Tip: Put Connection Strings in Their Own Configuration File
Gravatar Greg Hawk
Feb 29, 2008
You must always set this user.config file in vs to "copy to output" or you will always get a "could not find file" error.
Comments have been closed on this topic.