Wednesday, July 11, 2007

How to move a Typed Dataset to its own Project and Namespace - Part 2 - Deployment and ConnectionStrings

Just in case you missed part 1 please read that adventure before proceeding.

I thought my adventure was over with these ridiculous typed datasets but today I got a rude surprise. It reminded me of a baseball game that I once played. I was playing shortstop and the go ahead run was at 3rd base so we brought our infielders in on the grass. This guy hit a rocket right at me and the ball bounced up and hit me right in the mouth. That's about how fun this adventure was.

So, what was today's baseball in the choppers you ask? Well, it appears that the geniuses at Microsoft (and there are many I am sure) happened to overlook the fact that the database connectionstring information that is used when you are designing a typed dataset just might not be the connectionstring that you will want to use when you deploy your application. I know in a perfect world (for hackers at least) every database would have the same name, user name and password but I live in an imperfect world and typed datasets are just another shining example.

First of all, thank heavens for a posting on Channel 9 or else I would have never gotten the ideas that were required to workaround this huge oversight by Microsoft.

Basically, the situation is that typed datasets hard code connection string information in them. It can either be burned into the code via app.config or via the settings.settings file underneath the "My Project" folder in the Solution Explorer (you may need to do a "Show All Files" to see this file).

App.Config is great in general but if you have many projects within a solution and each of those project have an app.config each of those files contains their own connectionstring then maintaining & synchronizing the same database connectionstring information in all of the different .config files can really be a nuisance. There appears to be no easy way to convince typed dataset projects to read from the main EXE's app.config or web.config. Keep in mind that if you decide to go with this hard to maintain "many .config file" approach the .config file will not be created on "Debug" builds of your solution. You will need to go to Project Properties Compile Tab and select "Release" from the "Configuration" combobox at the top of the screen. This approach is not bad if you have a small project and a single database but it probably is not an acceptable solution for an enterprise software product. Then again, I am starting to believe that typed datasets are not acceptable for enterprise software products.

The other approach is to place your database connectionstring in the settings.settings file. This was ultimately my preferred approach although at first glance it looked like it was an even worse idea than app.config. Here's why settings seemed like a dead end. The setting that Visual Studio maintains for a connectionstring is generated as readonly. If you try to manually change this by editing the contents of Settings.Designer.vb Visual Studio will reset the property to readonly. There seemed to be no way to fool Visual Studio.

Here's how I solved the problem:
1) I added a new Module to my project and named it DBConnectionString.vb.

Option Strict On
Option Explicit On
'''<summary>Shared module that allows for setting the database connection at runtime.
''' <history>Created by kmccaa on 07.11.07</history>

Public Module DBConnectionString
Private m_RunTimeConnectionString As String = String.Empty
''' <summary>Sets the connection string for all of the XSDs in the project at runtime.
''' Because this is a shared class (module) setting this value once when the application
''' starts (e.g. in global.asax Application_OnStart for web projects) and then all the
''' XSDs will use this runtime connectionstring rather than the connectionstring that was
''' burned into the source files at design time.
''' </summary>
''' <history>Created by kmccaa on 07.11.07</history>

Public Property RunTimeConnectionString() As String
    Return m_RunTimeConnectionString
  End Get
  Set(ByVal value As String)
    m_RunTimeConnectionString = value
  End Set
End Property
End Module

2) If you do not have a connectionstring property setup in your DAL project yet do the following. Go to Project Properties Settings tab.
Add a new setting.
Name it "ConnectionString"
Choose ConnectionString as the "Type"
Choose Application as the "Scope"
Type in a valid database connectionstring for the "Value".
Save your settings and close the property screen.

3) I manually edited the contents of Settings.Designer.vb. These changes will get overwritten any time you change the settings.settings in your project but unlike when I tried to change the property to a read/write property I was able to fool Visual Studio so that it would not change the internals of the property at compile time.

Public ReadOnly Property ConnectionString() As String
    If DBConnectionString.RunTimeConnectionString.Length > 0 Then
      Return DBConnectionString.RunTimeConnectionString
      Return CType(Me("ConnectionString"), String)
    End If
  End Get
End Property

Keep in mind that if you ever add/edit/delete any settings from with Visual Studio the ConnectionString property will probably get overwritten and reverted back to the following.

Public ReadOnly Property ConnectionString() As String
    Return CType(Me("ConnectionString"), String)
  End Get
End Property

For that reason, you should save the snippet above somewhere handy. I would recommend as a code snippet in your toolbox. If you have never added a code snippet to your toolbox before just select the text you like and drag it to your toolbox. Then right click on it and choose "Rename Item". By having it in your toolbox you can restore it to the "custom" way quickly by dragging it out of the toolbox and into the code window.

4) Then I had to make sure that none of my XSDs were reading their connectionstring from app.config. So I had to replace this:

Me._connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

With this (insert your project name where it says <ProjectName>):

Me._connection.ConnectionString = Global.<ProjectName>.My.MySettings.Default.ConnectionString

For example, if your project name was ABCInc.Employees.DAL then you would use this:

Me._connection.ConnectionString = Global.ABCInc.Employees.DAL.My.MySettings.Default.ConnectionString

5) Delete any connectionstring information that you may have in app.config for your DAL project. Perhaps you can delete the whole app.config file. This just helps to make sure you don't have a stray reference to app.config in your code.

6) Compile your DAL project.

7) Within your main EXE of website you now have to set the DBConnectionString.RunTimeConnectionString value in your DAL project. My work was for a web project so I opened global.asax and added this (of course you have to put your own value in for the <ProjectName>:

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
  <ProjectName>.DBConnectionString.RunTimeConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
End Sub

By the way for those that are not used to using modules instead of classes here is the advantage. Because I created the DBConnectionString.vb as a module rather than a class that allows me to set the RunTimeConnectionString once at application startup and not have to worry about setting it each time I try to hit the database. This can have a downside too though if I wanted each user to connect using a different connectionstring then by having a shared property that would mean that all users within the website would all be sharing that single value. In Oracle there is a workaround for this called "Proxy Users" but that is a topic for another day.

8) Set your main web.config or app.config connectionstring to something other than the connectionstring that you used while developing your XSD.

9) Run your project to verify that the queries are indeed running against this alternate database.

10) Send me a Thank You note if it works. Send me a question if it doesn't.

Thanks for sharing this adventure with me and keep your eyes (and not your mouth) on the ball.


Mark said...

I have what I think is a better way. I have posted it on CodeProject at

Klaus said...

A solution that worked for me on the deployment server was to remove the connectionstring setting from the app.config int the DAL and but it in the web.config. After I manually edidted the connection string to the new server.

Clint said...

still cant see the page properly because this box is in theway

Anonymous said...

Your Best Choice! wow power leveling and wow gold wow gold