Revisiting the Dynamic Connection String
This article is intended as a follow up to one that was written a little over a couple of years ago on that perennial little annoyance, the strongly typed connection string in a project’s App.Config file. If you’ll recall the problem with the connection string that gets built by the visual studio wizard and appears in the projects settings is that it’s strongly typed and can’t be written to. The answer as we saw was to effect a workaround that allowed us to assign a new string to this strongly typed connection string at runtime. This we did by adding a read only property to the settings code in our project and using that to assign the connection string that we actually wanted to use at runtime. It still however left you with the matter of actually getting the new connection string that you want to use in the first place and at the time that meant building your own data connection dialog. Well since then Microsoft finally released the data connection dialog for public use, and even better (if you’re using Visual Studio 2010) you can make use of nuget to download the required files to your project. The sample attached to this article was done in VS2008 so that you can at least make use of the correct files that are enclosed within. And so to business… The attached sample contains a simple form on which there is a data grid displaying customers from our old friend Northwind traders. To get the data onto the form I created a dataset using following the standard procedure that has been well documented elsewhere (so I won’t bore you with it) and ended up with a connection string to Northwind embedded in my project. 
You can see that we have our connection string to the Northwind database which somewhat unoriginally has been called ‘NorthwindConnectionString’. Notice that there are a couple of other user settings that I added to this. Duplicate the same in your project. Now we need to add a write only property to the settings namespace. Click on the view code button (outlined in the illustration above) and in the code window that opens add the code shown below.
Namespace My
'This class allows you to handle specific events on the settings class:
' The SettingChanging event is raised before a setting's value is changed.
' The PropertyChanged event is raised after a setting's value is changed.
' The SettingsLoaded event is raised after the setting values are loaded.
' The SettingsSaving event is raised before the setting values are saved.
Partial Friend NotInheritable Class MySettings
' Fields...
Private _runtimeConnectionString As String
' Properties...
Public WriteOnly Property RuntimeConnectionString() As String
Set(ByVal Value As String)
My.Settings("NorthwindConnectionString") = Value
End Set
End Property
End Class
End Namespace
Now that you have done that we need to add a couple of references to our project. Specifically these are to enable us to use the Microsoft data connection wizard. They are included in the sample project or if you are working in Visual Studio 2010 you can use nugget to get them (simply type ‘ PM> Install-Package DataConnectionDialog’ in the nugget package manager). When don you should see the following in the projects references; 
Next create a new module or class in your project. I chose to call it Program but you could call it anything that you wanted) and add the following code to it.
Imports Microsoft.Data.ConnectionUI
Module Program
Private Sub CreateProductionConnectionString()
'This calls the Microsoft DataConnection Wizard.
'first we instantiate the dialog
Dim dcd As DataConnectionDialog = New DataConnectionDialog()
'now we limit the data providers it could use to just the sql data provider
Dim sqlDataSource As DataSource = New DataSource("MicrosoftSqlServer", "Microsoft SQL Server")
sqlDataSource.Providers.Add(DataProvider.SqlDataProvider)
dcd.DataSources.Add(sqlDataSource)
dcd.SelectedDataProvider = DataProvider.SqlDataProvider
dcd.SelectedDataSource = sqlDataSource
'now we call the dialog and allocate the generated string to the project setting we intend to use to store it
If DataConnectionDialog.Show(dcd) = DialogResult.OK Then
My.Settings.ProductionConnectionString = dcd.ConnectionString
End If
End Sub
<STAThread()> _
Public Sub Main()
Application.EnableVisualStyles()
Application.SetCompatibleTextRenderingDefault(False)
#If DEBUG Then
'do nothing as we are working in design time
#Else
'Carry user settings forward on application upgrade
If My.Settings.SettingsUpgraded = False Then
My.Settings.Upgrade()
My.Settings.SettingsUpgraded = True
End If
'now check to see if there is a connection stored in your settings
'and if not add it
If String.IsNullOrEmpty(My.Settings.ProductionConnectionString) Then
If DataMessage.ShowDialog = DialogResult.OK Then
CreateProductionConnectionString()
Else
Return
End If
End If
'Now assign our new connection string to replace the one stored in 'NorthwindConnectionString'
'of course if they simply cancelled their way out ot the data connection dialog then later on
' we will end up with an unhandled exception when we try to load data from a database that we
' can't connect to so we need to wrap this up in a get out of jail free card.
'
If String.IsNullOrEmpty(My.Settings.ProductionConnectionString) Then
Return
Else
My.Settings.RuntimeConnectionString = My.Settings.ProductionConnectionString
End if
#End If
Application.Run(New Form1)
End Sub
End Module
Hopefully the code above ought to be reasonably self-explanatory but let’s just take a look at a couple of points that might be of interest. In essence we have now created a sub main which we will call as the first thing to be executed in the application. So if you have not already done so return to the project’s properties and on the application tab ensure that the ‘Enable Application Framework’ is unchecked and that the start up object is set to Sub Main. 
The #If Debug construct simply ensures that we only really make use of our production connection string when we are actively running the application in a production environment rather than when we are working on it on our own machines. The first thing we then do is ensure that the settings are updated if we are running an upgraded version of the application. By design settings are version specific. This allows them to be carried over if the application is upgraded. The next thing that we do is check to see if there is anything stored in the user setting in which we intend to store our new connection string. If there isn’t we call the Microsoft Data Connection Dialog and save the result to the user setting in which we intend to store it. Finally we assign our newly saved (or the runtime string we had previously stored) to become the ‘new’ NorthwindConnectionString’. And that is it. A few lines of code, a couple of references and you have a very simple way to provide your application with a dynamic connection string. Finally (and most importantly) on the form that will be the main application form add the following to its Form Closing event so that the user settings are properly saved.
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
My.Settings.Save()
End Sub
There is one other advantage that being able to use the Microsoft Data Connection Dialog can bring to you. If you create reports for your application using C1 Reports then you’ll be aware of the fact that when you originally created them in the C1 Report Designer it used an OleDB connection string to makes its connection to the database, as you can see in this small section of a report definition file.
GrapeCity