Practical ASP.NET

Accessing Connection Strings Reliably

Peter provides an alternative to the default mechanism for accessing connection string -- and plugs his next book.

• By Peter Vogel
• 01/22/2009

<connectionStrings>
  <add name="Northwind"  connectionString="..."
             providerName="System.Data.SqlClient"  />
 </connectionStrings>

When retrieving connection strings from the config file, you use syntax that looks something like this.

However, there are lots of problems with this syntax. The absence of IntelliSense support means that you have to switch back to your configuration file in order to find what connection strings you have and what you called them. More importantly, if you mistype the name of the connection string, you won't find that out until runtime (probably when someone who has input to your appraisal is looking over your shoulder).

But don't take my word that this syntax is error-prone: Did you catch the misspelling of "Northwnd" in the sample code? It should have been "Northwind" but that problem won't show up until the code executes.

There's a better solution and it's modeled in another part of ASP.NET: personalization. While you define personalization properties in the Web.config file, you access them through the Profile class. The Profile class has properties (available through IntelliSense) representing each of the entries in the Web.config file. The same model can be used with connection strings.

Connection Manager Class
The ideal solution would be a class called ConnectionManager with a property for each connection string. With that class in place, I could retrieve a connection string with code like this.

This solution isn't free, of course: You need to add the ConnectionManager class to your App_Code folder when you start your project. On an ongoing basis, you need to add a new property each time you add a connection string to the Web.config file (and delete properties if you ever remove a connection string). You also need to copy the class to each project or Web site where you want to use it in because the code is specific to each project.

Because of the costs involved with this solution, it's essentially limited. It would, for instance, be useful to be able to perform update operations like this possible:

ConnectionManager.Northwind.ConnectionString = "...";

If you wanted to the full nine yards, you'd even provide a collection of ConnectionString objects to iterate through.

But the cost of maintaining this sophisticated a solution would be too high to be worthwhile because of the number of changes required every time a connection string is added or deleted. It would also just move around the problems that I discussed at the start of this column: Instead of creating errors when accessing connection strings, you'd be creating errors trying to update the ConnectionManager.

Code Generation
The right answer is to use code generation: a Visual Studio add-in that will detect changes to the ConnectionStrings element in the Web.config file and regenerate the ConnectionManager class. That's what I use and the ConnectionManager code that I showed before is the output from my add-in.

Developers don't use code generation enough, which is too bad. All the necessary tools are built into .NET and just waiting for you to take advantage of. Unfortunately, most developers aren't aware of the code generation tools available to them and what those tools can do for them. So, here's the plug: I'm writing a book for Addison-Wesley to fix that knowledge gap by providing one-stop shopping for the typical .NET developer (we're still vacillating on the title -- we're torn between Practical Code Generation for .NET and The .NET Code Generation Toolkit).

When it comes out, you should buy it -- my kids need new shoes. And if you'd like a copy of the add-in, send me an e-mail at [email protected] and I'll send you a copy. Just don't ask for support.