Dynamic Connection Strings


This blog post details a solution to the common problem of dynamically setting connection strings. The approach used allows end-users to change database connections at runtime through a friendly user interface, and save the connection for future uses. Provided with this post is source code for the connection form which accesses all SQL servers and databases on a computer and its networks, builds a connection string based upon user selection, and also saves this to an xml file in local data storage (see bottom of post for download).

Connection Wizard Form

Scenario:


You create a project in Visual Studio which utilizes a backend database. Typically, you first create your database, and then create a connection string in Visual Studio to link to your database, and then you complete the rest of your project. You store the database connection string in the settings of your project.

Project Settings

The Problem:


Once you are happy with the application you package and distribute it to your end users. This is when the fun starts as your end users start to say that they can't make a connection to the database on their server. The reason for this is really quite simple. The connection string that you have created has Application scope and is strongly typed. You need to allow the connection string to be set on the users end.

Because the connection string is strongly typed, you can do something like:

[C#] string myString = Properties.Settings.Default.MyConnectionString;
[VB.NET] Dim MyString As String = My.Settings.MyConnectionString
But if you try and reverse the logic:

[C#] Properties.Settings.Default.MyConnectionString = "abc";
[VB.NET] My.Settings.MyConnectionString = "abc"
You get an error.

Connection Error

The problem, of course, lies in the strongly typed connection string that you created when you designed the application. The real problem is that it is strongly typed.

So how can you get around this?  It's something that you would think has a ready solution available, but strangely there isn't one. A web search for 'How do I dynamically reassign the connection string' will return hundreds of results. The realization is that you are not alone.

The Solution:


Well, it turns out that you can actually play with the settings, but to do this you need to access the settings events. If you return to the Settings page of your project you'll see a button that allows you to view the code.

Project Settings View Code

Now, you still can't directly assign the connection string here in the code page (because it's still strongly typed), but you can make use of late binding. The following code is possible (in the Settings Load event):

[C#] this["MyConnectionString"] = "abc";
[VB.NET] Me.Item("MyConnectionString") = "abc"
This is the key to solving the problem. And now that you know that you can change the connection string at runtime, you can expand this process by letting the user build the connection string rather than you, the developer, always being responsible for knowing it. To do this you can create a property in your Settings.cs or Settings.vb page.

[C#] public string RuntimeSqlConnectionString {
set
{
this["MyConnectionString"] = value;
}
}
[VB.NET] Public WriteOnly Property RuntimeSqlConnectionString() As String
Set(ByVal value As String)
My.Settings("MyConnectionString") = value
End Set
End Property
The rest, as they say, is just a case of applying a little logic to your new found freedom.  The easiest approach (for you) is just to present the end user with a text box and have them fill in their connection string and assign that value. But to avoid all those future support calls, you should give them as much help as possible.  To achieve this you should display a form that actually searches the end user's computer/network for active SQL Servers, and when they select one, automatically list all of the databases available.

When they have made a successful test connection you write the information out to an xml file in the user's application data directory. This approach allows each user to have their own connection string (each user could have different passwords!).

Finally, on application startup, you read the xml file and dynamically assign the connection string to the property we created earlier. It's that simple.

The same logic can be used for other database connection strings, such as OleDb if you're using C1Reports. Just add another connection string setting and another property in your Settings code.

Download Sample:


Download source for this sample:

This sample provides the code for accessing all SQL servers and databases on a computer and its networks. It builds a connection string with the option for a password, and also saves the connection string to an xml file in local data storage. It may not look like a "wizard" but you could certainly take the logic used and apply it within a wizard of your own.

Connection Wizard Form

-Dom Sinclair (http://www.viewtolearn.net/)