Working with Dynamic Connection Strings in VB.NET 2005 WinForms Applications
When Microsoft released their latest edition of Visual Studio 2005, they got a lot of things right. The data binding is finally ready for use in real projects, click once is a wonderful feature, and the support for connecting and editing data sources is great. One real problem with all this automation is that when something goes wrong, it really goes wrong. Do you scrap the automated features that can save you so much time? Hopefully this article will mean you wont have to when it comes to dynamic connection strings.
In the past, we used to define our connection strings to our database either right in the code or in a config file. Because we defined it and manually assigned it to the connection object, we could modify the connection string as required. It was a simple task to put a new server address in at run time or a new file path to an access database. The problem was that it was time consuming to re-write the connection code each time we needed to use it. There were several solutions that could ease our pain, but we were still doing things by hand.
When Microsoft added support for partial classes, they allowed much of the plumbing code to be seperated from the logic code. Now, for a form, there are 3 files involved. Much of the connection code is placed in the designer file where it is tucked away nicely. The problem is that this designer file is refactored (rewritten) when certain changes are made so you can’t gaurentee that any changes you make to the designer file will be there later. Manipulating the connection string here puts us back into manual mode and has to be done for each form/connection. What we really want is to just have our connection work and take advantage of the time saving features of drag and drop and data binding.
With all these great tools, we have lost control of the connection string. To really take advantage of the time saving tools in visual studio, we setup a connection in our server explorer. If that is the exact connection properties that will be in use when the project is live, then there is no issue. You can view tables, views, stored procedures, etc.. right from the server explorer. You can drop tables onto your forms and it will automatically setup connections, data adaptors, data sets, navigation bars, etc.. It makes setting up a basic data form extremely simple and fast. When you use a database server such as MS SQL or MySQL, you probly wont notice this issue until you decide to run your app off of two different servers (one for development and one for production). If you use MS Acces for your database, then you are probly reading this because you already have the problem.
With MS Access, your connection string includes the path to your MDB file. Lets say for this example that the MDB file is in your project directory. You could use a full path(c:\projects\myproject\data.mdb) which would work fine while you are developing your application. What about when you send the app off for your friends or coworkers to use? The chances that your program will be in the exact same folder as on your computer can be slim and you don’t want to have to recompile your app each time some one runs it in a different directory (if you even knew what that directory was!).
The easy solution is to use a relative path. In this case, you could just specify the MDB file name without any path. This works great in the IDE when you are dragging and dropping things, but as soon as you push the play button, you compile and create either a debug or release version of your app. This app is then placed in the bin/debug or bin/release directory. Suddenly your MDB file is no longer in the current directory (it is two levels back in the project directory) and your app can’t find it.
Shucks, I will just add my MDB file to the project, then when it is built, it will copy the MDB file over to the bin/debug directory. This actually works, except that each time you build your MDB file will be copied over again meaning that any data you enter while the program is running will be over written (this actually had me confused for hours and I though changes werent sticking and I was trying to debug my save code). You can set the properties of the MDB file in the project to “Copy if newer” which will help, but if you make changes to the main MDB file, then it will copy over the on in the bin/debug folder.
It would be nice for the connection string to be smart enough to use the project folder as the main folder all the time. You can actually mess with this by having your builds reside in the project folder. This almost solves most of your problems. This solution clutters up your folder and while it might work for certain instances, it isn’t the best solution.
Now that we have address most of the issues you come across while developing and testing your software, lets talk about issues you face when you deploy it.
In my case, I had yet another problem. When deployed, the user could pick from more than one database. They had the option of browsing to an MDB file and choosing the current data to work with. How do you put in this new data path without writing custom code in every single form or location where you use a connection string without messing up the configuration while you are in the development environment?
First, go to “My Project” and go to the Settings tab. Click the View Code button on top. Add the event “Settings Loaded”.
This event fires after the settings have been loaded. When you setup connections in your server explorer, it adds connection strings to your settings area. These strings are specifically marked as a connection string. What we can do here is loop through our settings and find the connection strings. This happens early in the application and allows us to set the connection string before they are even utilized by the connection objects in the application. By changing them here, the rest of the application can just work without knowing that anything changed.
You could specifically change one connection string instead of looping, but I found that as I worked on my project that the IDE liked to add CString1, CString2, CString3, and so on. A new string (identical to the first) was added every time the project needed to reconnect to the database. Simply changing the connection string name or deleting it causes problems with data sets later on as the data sets specifically reference the connection strings by name. The easiest method for me was just to update all connection strings to point to the new location.
The advantage here is that the changes can be made in one location and you can put in the code to handle running in the IDE or deployed.
Here is the actual code:
Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
' Find the current file...
If (Debugger.IsAttached) Then
' Don't mess with the path if we are running in the IDE
ValidConnection = True
Return
End If
' Grab the last MDB file we opened and try to get it
Dim fname As String
fname = My.Settings.LastDBFile
' Try and load the file..
' CheckDataFile checks to see if the file exists...
' UpdateCStrings modifies all the connection strings to use the new file name
If (CheckDataFile(fname)) Then
' Good file.. lets use it...
UpdateCStrings(fname)
ValidConnection = True
Return
End If
' If the last db file cannot be found, start looking in the current directory, then start looking in parent directories
' Load the correct database directory
Dim data_directory As String = ""
data_directory = Windows.Forms.Application.StartupPath
data_directory = data_directory.Replace("/", "")
If (Right(data_directory, 1) <> "") Then
data_directory += ""
End If
Dim p_dir As String
p_dir = System.IO.Directory.GetParent(data_directory).Parent.Parent.FullName + ""
' Use this in case your MDB file isn't copied to your bin/debug folder when you push play
' Look for: dbfile, ..\..\dbfile
Dim last_db_file As String = ""
last_db_file = My.Settings.LastDBFile
' Check the current directory
If (CheckDataFile(data_directory + last_db_file)) Then
UpdateCStrings(data_directory + last_db_file)
ValidConnection = True
Return
End If
' Check the parent directory (../../dbfile)
If (CheckDataFile(p_dir + last_db_file)) Then
UpdateCStrings(p_dir + last_db_file)
ValidConnection = True
Return
End If
' Those others didn't work, lets try other common locations.
' Try to open from my documents...
data_directory = My.Computer.FileSystem.SpecialDirectories.MyDocuments.TrimEnd("") + ""
If (CheckDataFile(data_directory + last_db_file)) Then
UpdateCStrings(p_dir + last_db_file)
ValidConnection = True
Return
End If
' Couldn't open any db file
' Show some kind of error....
End Sub
The code is really long because it accounts for several different options. First, if we are in the debugger (IDE or playing from the IDE) it doesn’t do anything as the connection strings should work fine already from that location. Next, it tries the currently saved file path (we save the last used file path as a user setting). Next, it tries looking in the current directory, the parent directory (../../dbfile.mdb) to account for your application running in the bin/debug folder. Next it tries to load the db file from the MyDocuments folder.
You can adjust your code any way you like so that it works for you. The CheckDataFile function just uses the File.Exists method to see if the MDB file exists. In our case, if it does exist, it also tries to load it and check a version. That way we can update the MDB file if changes were made to the structure when it is loaded (eg: I add a new table to my MDB file while developing, so I can update the clients MDB file also when it is loaded). You can do whatever you want with the CheckDataFile function. It just takes the current path and checks things out, then returns a true or false based on its ability to find and load the file. I won’t list it here since it is mostly very specific to my project.
The next function that is important is UpdateCStrings. This takes the new file path and loops through all the connection string items in your settings updating them to point to the new path. I will list that here:
Public Sub UpdateCStrings(ByVal fname As String)
Dim item As System.Configuration.SettingsProperty
' Loop through each property (each setting)...
For Each item In My.Settings.Properties
If (item.PropertyType Is GetType(String)) Then
Dim keys As System.Collections.ICollection
keys = item.Attributes.Keys
Dim attr As System.Configuration.SpecialSettingAttribute
Dim key As Object
' Each property has multiple attributes, we are looking for the connectionstring special attribute
For Each key In keys
If (item.Attributes.Item(key).GetType() Is GetType(System.Configuration.SpecialSettingAttribute)) Then
attr = item.Attributes.Item(key)
If (attr.SpecialSetting = Configuration.SpecialSetting.ConnectionString) Then
Dim tmp As String
' Found a connection string... convert the connection string...
tmp = ConvertConnectionString(fname, item.DefaultValue)
' Assign the new connection string back to the setting
My.Settings.Item(item.Name) = tmp
End If
End If
Next
End If
Next
End Sub
Public Function ConvertConnectionString(ByVal fname As String, Optional ByVal cstring As String = "") As String
If (cstring = "") Then
cstring = My.Settings.MyConnectionString
End If
If (fname <> "") Then
Return cstring.Replace("|DataDirectory|\mydb.data", fname)
End If
Return cstring
End Function
The first function does the looping and is responsible for identifying which settings are connection strings. Once it finds one, it sends the item to the ConvertConnectionString to change the path in the cstring. One thing that you may notices is that Visual Studio likes to use |DataDirectory| for your file path if the MDB file is in a specific locaiton. The mydata.mdb file is the name of the file I used during development. Thus, all connection strings should point there to start with. The easiest method is to simply replace that with the new path. Note that we call the ConvertConnectionString function using the DefalutValue rather than the current connection string as it could have already been modified and the |DataDirectory| value may no longer be present. You could use fancier parsing techniques, but I have found that the simple string replace method works great for me.
The hardest part by far is the looping through the properties to identify which settings are actually connection strings. There is little real documentation on this and I had to hack it out by hand. Hopefully this will save others a great deal of time. I know that on other projects, being able to drop this in will cut of many hours and allow me to still take full advantage of the built in drag and drop databinding features in Visual Studio .NET 2005.
In our app, we use the UpdateCStrings function later on. The user can browse for a new DB file at any time and the UpdateCStrings function can be called using the new supplied path. The settings event helps to make sure that the connection strings are all set as the application starts.
Ray Pulsipher
Owner
Computer Magic And Software Design
