Check out my new blog at

Monday, March 31, 2008

Microsoft .Net 2.0 - Strongly Typed Datasets, App.Config, and Overriding Connection Strings

imageWe use strongly typed datasets in our data access layer (DAL) with various levels of satisfaction.  The DAL is implemented in a class library.   Connection strings for the data sets are configurable at design time through the dataset designer and are stored in an app.config file.  Class libraries are packaged as DLL's and therefore don't have a config file (.Net doesn't support myFile.dll.config).   The reason for this is pretty simple:  one config file per app domain.  When the class library is complied into a DLL, the app.config file gets munched and converted into a resource.

So, how do you change the connection string at run-time?

Method One: Set Connection Programmatically

In strongly typed datasets, the connection string is a property of the table adapter.  For every table adapter creation, you could do something like the following:

using (AssessmentTableAdapter assessmentAdapter = new AssessmentTableAdapter())
    assessmentAdapter.Connection.ConnectionString = Properties.Settings.Default.TSDConnectionString;

If your system is like ours, this is absolutely unworkable. 

Method Two: Override Connection Strings in App.Config

If you rummage round in your strong typed dataset's designer file, you can see how the connection string is resolved:

private void InitConnection() {
    this._connection = new global::System.Data.SqlClient.SqlConnection();
    this._connection.ConnectionString = global::IIL.TSD.Applications.ProductManager.Properties.Settings.Default.TSDConnectionString;

Notice the "global" keyword, which is the global namespace qualifier.  Because the connection is resolved through global, the precedence of embedded (as is the case when a class library is given a app.config file--it becomes an embedded resource in the resultant DLL) settings is lower than that of the app domain's app.config file.  This means that I can override the connection string information buried in the DLL by creating a connection string with the same name (in this case, IIL.TSD.Applications.ProductManager.Properties.Settings.Default.TSDConnectionString) in the app.config:

<add name="IIL.TSD.Applications.ProductManager.Properties.Settings.TSDConnectionString"
    connectionString="Data Source=yo.yo.yo.yo;Initial Catalog=&quot;360° Assessment Framework&quot;;Integrated Security=True;Connect Timeout=30;"
    providerName="System.Data.SqlClient" />

This is better, but not great...  I have several class libraries that access the database for a variety of reasons:  one library is the core domain logic, while another subsystem is security, and yet another implements notifications--each of these subsystems perform data access operations .  That means that each has an embedded connection string.  I have to override each connection string in the app.config (or web.config), and that means the database connection string will be repeated multiple times.  There may be a better way to do it, but I haven't figured it out yet.

Overwriting Connection Strings at Run-Time

image I need to be able to switch between different databases at run-time, and I have the above scenario where there are multiple class libraries at various levels in the architecture.  I created a database connection dialog by lifting the SqlConnectionUIControl and SqlFileConnectionProperties functionality from the Microsoft.Data.ConnectionUI namespace, which is implemented in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Microsoft.Data.ConnectionUI.dll and C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Microsoft.Data.ConnectionUI.Dialog.dll (adjust your paths according to your install path).  A screen shot is to the right.  If you want to know how to do this, post a comment asking for it and I will blog it in the future.

After collecting the database connection string form the form, I cycle through all the connection strings in the app.config file and replace the connection strings with the new one.

In .Net 2.0, a new class called ConfigurationManager gives very good control over configuration information. To use this class, create a reference to System.Configuration (System.Configuration.DLL) and include "using System.Configuration" in your source file.

Your first attempt might use the static methods of the ConfigurationManager class.  This will not work as it offers read only access to the configuration information.  You must open the config and explicitly save it out again.

Here is what a very simple implementation looks like.  Note that I restart my application after the configuration is saved.

DataConnectionForm connectionForm = new DataConnectionForm();
if (connectionForm.ShowDialog(this) == DialogResult.OK)
    string connectionString = connectionForm.ConnectionString;
    string configLocation = Assembly.GetExecutingAssembly().Location;
    Configuration config = ConfigurationManager.OpenExeConfiguration(configLocation);
    for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++)
        ConnectionStringSettings connection = new ConnectionStringSettings(ConfigurationManager.ConnectionStrings[i].Name, connectionString);
        connection.ProviderName = ConfigurationManager.ConnectionStrings[i].ProviderName;

Hope this was helpful!


Anonymous said...

Awesome! That's what I needed for the connection string change at runtime. Thanks.

Colby Africa said...

You are most welcome!

Anonymous said...

Thak you for the post, but can you make and aditional post with the code to use the DataConnectionDialog?..

Colby Africa said...

Send me an email...use the contact me form and I will dig it up for you.

Marc W said...

This is a MS DLL, can we distribute it?

Colby Africa said...

I don't know but I will try to find out.


Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.