Skip to main content Skip to footer

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")  
        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()  


#If DEBUG Then  
        'do nothing as we are working in design time  

        'Carry user settings forward on application upgrade  
        If My.Settings.SettingsUpgraded = False Then  
            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  
            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  
            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  
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. Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WestLondonSecurityData;Data Source=SMAUG\SQLEXPRESS Once again (irrespective of the fact that there are some quite obvious differences between a SQL Connection String and an OleDB Connection String) it is quite clearly hard coded to the database server and database against which the report(s) were designed. Left as they are then unless your end user has exactly the same configuration as you do then these reports simply won’t render. We need a dynamic connection string for our C1Reports. If you look back to the code in the Program Module (and specifically at the CreateProductionConnectionString method) you’ll see that the Dialog was pre-configured to only create a sql connection string. With a couple of small changes it can be configured to only produce an OleDB Connection String. With the new OleDB Connection string you can then assign that to your C1 Reports when they are run. More information on how to do that can be found in the relevant documentation on C1 Reports. The sample that is attached to this blog demonstrates this and in addition implements a means of allowing the end user to get a little help about the Data Connection Dialog before being presented with it completely unawares.


comments powered by Disqus