Tuesday, May 27, 2008

Windows Dev: Storing and Changing SQL Connection String from Configuration File

When writing a .NET application with SQL database, we always need to store a configuration string somewhere. This can be done in many ways. I happened to like the Application Configuration capability that started to appear in Visual Studio 2005 with .NET Framework 2.0

This is a very typical thing we do, but at least in .NET Framework 2.0 there still is not an easy solution for this, and using the App.config approach, you will soon realize that you run into two problems (I am not going into the security aspects of it.)

  1. You could store the entire connection string as the Application level configuration parameter in app.config but if you do that then you cannot change and save the string from anything in the applications section.
  2. But you have to change the instance of that string in order to easily propagate the connection string to all TableAdapters
Well, it turns out that there is simple and easy way of doing this. You can modify the memory instance of the application connection string using the array indexer with the name rather than calling as a member of the property. If you can successfully make the change in the connection string in the constructor of your application in the following manner, the changed configuration string will propagate to all of the TableAdapters.

public Form1()
string s = Properties.Settings.Default.MyConnectionString;
s = s.Replace("(local)", ""); // This can come from another user configuration string.
Properties.Settings.Default["MyConnectionString"] = s;

No comments: