Another quickie this month! This month topic is useful to both Windows and Web developers.

It is about a special section of the configuration file (app.config for Windows developers or web.config for web developers). This section is named ConnectionStrings. The code is exactly the same whatever the platform you are working on.

This section was introduced with the .Net Framework 2.0 but remained hidden (at least for me) since then.

Downloadable example

The code you can download for this article was created using Visual Studio 2008 SP1. If you still use VS2005, it is exactly the same code as nothing is specific to the latest version of the .Net Framework.

Oh surprise, the solution contains both a VB and a C# project! I often have request for the C# version of my demos (and don’t know why!). This month, I include the example in both languages. I cannot promise that it will always be the case but I will do my best.

The configuration file

I hope that you never hardcode any data that could change over the time. I have already written articles on how to use the configuration file (see AppSettings revisited from March 2007).

This file lets you store any data (not code) that your application requires. The biggest advantage is that you can modify that file and your application can react to it without having to recompile it.

So what’s new?

Not much. Only that when I first wrote the article on application settings in March 2007, I wasn’t aware that the connection strings were having their own dedicated section. If you have a simple connection string for your application, there is no real added value to the tip I will demonstrate here. But if you want to handle a dynamic number of connection strings, this month article becomes very handy.

Since the connection strings have their own dedicated section in the configuration file, you can easily loops trough that collection and the .Net Framework already wrapped that collection for your convenience.

My demo App.Config file

To start this month demo application, I have created a new Windows application to which I have added a configuration file like shown in figure 1.

Figure 1: Adding a configuration file to your project

Once the file is added to your project, you can start editing it either manually or through the dialog as I have shown in my previous article. Let say you edit it manually, add a new section called “connectionStrings” (intellisense should help you typing correctly) right into the configuration main group.

Into that new section, you can start adding your different connection strings. It is important that you use the correct keywords (and intellisense should help you once again). You need to start with the add verb, provide a value for the name, providerName, and the ConnectionString attributes. An example is shown here:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<clear/>
		<add name="Dev"
			 providerName="System.Data.SqlClient"
			 connectionString="Data Source=moer-i1520\sql1008; Initial Catalog=DevDB; Integrated Security=SSPI"
	/>
		<add name="QA"
			 providerName="System.Data.SqlClient"
			 connectionString="Data Source=moer-i1520\sql1008; Initial Catalog=QADB; Integrated Security=SSPI"
	/>
		<add name="Prod"
			 providerName="System.Data.SqlClient"
			 connectionString="Data Source=moer-i1520\sql1008; Initial Catalog=ProdDB; Integrated Security=SSPI"
	/>
	</connectionStrings>
</configuration>

Reading the connection strings’ collection

Now that we have the configuration file, you will want to read it and probably let the user pick one of the available connections. This is where the built-in collection comes handy. If you followed the syntax to create your connections into the configuration file, the .Net Framework lets you dig into it very easily.

Before being able to use the special wrapper provided by the .Net Framework, you need to add a reference to the System.Configuration component as shown in figure 2.

Figure 2: Adding a reference to the System.Configuration component

Now that we have this reference, we will be able to use the ConnectionStringSettingsCollection class that will do the entire job for us. See this method:

Imports System.Configuration

Private Sub GetAllConnectionStrings()
    'Clear the listbox
    Me.ListBox1.Items.Clear()

    'Declare a collection that will contains all the connection strings 
    'retrieved from the app.config file
    Dim collCS As ConnectionStringSettingsCollection
    Try
        collCS = ConfigurationManager.ConnectionStrings
    Catch ex As Exception
        'the section is surely not found!
        collCS = Nothing
    End Try

    'If the collection is not empty
    If collCS IsNot Nothing Then
        'Loop through all settings
        For Each cs As ConnectionStringSettings In collCS
            'Add the name of the connection string to the listbox
            Me.ListBox1.Items.Add(cs.Name)
        Next
    End If

    collCS = Nothing
End Sub

This code loop through the connections strings found in the according section of the configuration file and adds them to a ListBox control to let the user pick the one he wants. If you read carefully at the code, you will find that the ConnectonStringSettingsCollection object offers ConnectionStringSettings class which is strongly type object that exposes the properties of a connection string. This is why we have access to the Name property that easily (and also to the ConnectionString and the ProviderName we will use later).

Figure 3: The demo application listing the available connections

After the user picked one on the available item in the ListBox control, you can use the name to directly pick the correct entry from the collection and access the other properties like the ProviderName and the ConnectionString. This is what is done when you click the button at the bottom of the form as this code is showing:

'The name will become the key
Dim strCSName As String = Me.ListBox1.SelectedItem.ToString
Dim strCS As String
Dim strProvider As String
'extract data from the app.config file
With ConfigurationManager.ConnectionStrings(strCSName)
    strCS = .ConnectionString()
    strProvider = .ProviderName
End With

'Display the required information
MessageBox.Show("You have selected this connection string:" + Environment.NewLine + _
                "     Name     = " + strCSName + Environment.NewLine + _
                "     Provider = " + strProvider + Environment.NewLine + _
                "     Connection string = " + strCS, _
                "Your connection string", _
                MessageBoxButtons.OK, _
                MessageBoxIcon.Information)

Securing connection strings

In a Windows application, copying the app.config file to your users’ computer will provide them the connection string to your database which is not a good idea (especially if you are not using integrated security).

As a bonus, the downloadable demo application includes a class that can encrypt and decrypt strings. It is the same class I used in my March 2007 article.

Web applications

And I told you in the introduction that this tip works for web applications as well. The Web.Config file as exactly the same section containing the connection strings. The exact same code would let you retrieve and handle those connection strings.

Conclusion

Providing a dynamic environment for the user to select its database environment from a configuration couldn’t be easier.