Ordeal: Strongly Typed DataSet Connection String

When designing Strongly Typed DataSets, Visual Studio’s designer wants you to specify a database Connection String, which is then shared by all TableAdapters for database connectivity. The connection string gets stored as a read-only property in the Application Settings configuration. You cannot change it at run-time if you want to connect to a different data source. In the real-world, even the simplest application needs to connect to different data sources – development, test and production. While we certainly need the RAD and IntelliSense benefits of using strongly typed datasets during development, we also need the ability to change the connection at run-time, whenever we need to. Here we shall see how to work around this inadequacy. This is Ideal Ordeal.

Applies to: .NET 3.5, Visual Studio 2008

Contents
1____In the DataSet Designer
1.1________Hierarchical Update
1.2________Connection String
1.3________TableAdapter Properties
2____In the Code Editor
2.1________Add Runtime Connection String to Application Settings
2.2________Data Tier
3____That’s all folks!

---

In the DataSet Designer

Hierarchical Update

.........

Create a new DataSet and bring up the DataSet designer. View the Properties list and make sure the Hierarchical Update property is set to True. This tells the designer to create the TableAdapterManager class.

TableAdapterManager is a new class in .NET 3.5 and the Visual Studio 2008 DataSet designer creates this class in a new namespace under your application’s root namespace.

I have named my root namespace ConfigHack and my DataSet, MyDataSet. The DataSet designer defines the TableAdapterManager class in the ConfigHack.MyDataSetTableAdapters namespace. We will be using this class to change the connection string at run-time.

For this walk-through, I am using a local Access database, MyDatabase.mdb, and then illustrate how we can change the connection string at run-time to connect to a remote database server. The database contains two tables, Customer and BlackList.

Customer		BlackList
ID	<==>	CustomerID
Name		StartDate
Email		EndDate
LastUsedIP		IPAddress
		Reason

We will add these two tables to our strongly typed dataset.

Right-click on the DataSet designer and choose “Add>TableAdapter…”. This will bring up the “TableAdapter Configuration Wizard”. As the fist step, you will be asked to specify a Connection String. Go ahead and create a new connection string pointing to the local Access database file, MyDatabase.mdb. Click next.

Connection String

In the second step you will be asked to save the connection string to the application’s configuration file. Enter a name for the connection string and click next. I named mine “ConnectionString”, which is made available as a read-only Application Settings property:

ConfigHack.Properties.Settings.Default.ConnectionString

.........

The person who built the wizard interface for the designer seems to have had the right idea, as you can see in this figure, where it is says, “Storing connection strings in your application configuration file eases maintenance and deployment”.

Nice.

But it is probably a different person who decided to store the connection string as a read-only application scope property.

How does it “ease maintenance and deployment” if it can never be changed dynamically at run-time?

Click next and move on to specify a query as shown below and complete the setup for the Customer table.

SELECT ID, Name FROM Customer
ORDER BY Name

Repeat the above steps, reuse the same connection string and add a TableAdapter for the BlackList table. In the query let it search for a given Customer ID.

SELECT * FROM BlackList
WHERE CustomerID = @customerid
AND EndDate > GetDate()
ORDER BY StartDate

Now back in the designer, you can setup the relationship between the two tables.

TableAdapter Properties

Click and select the Customer TableAdapter and look at its Properties list. You can see the connection string and all the SQL commands. I renamed the Fill() method to GetCustomers() which will populate the MyDataSet.Customer table.

.........

In the Code Editor

Add Runtime Connection String to Application Settings

Depending on the needs of an application, the choice of connecting to data sources could be driven by various run-time factors. Information from input parameters, user permissions & preferences, environment variables, registry settings, run-time availability of a data source, business rules – one or a combination of these and other factors might determine the appropriate connection string at run time.

In this walk-through, I have a simple console application, ConfigHack.exe, which gets the connection information from command line parameters. We can construct the entire connection string from these parameter values, for example, to a Sybase server, like shown here:

Provider=Sybase.ASEOLEDBProvider.2;Persist Security Info=True;Initial Catalog=devDB;Password=Pa$$w0rd;User ID=user007;Server Name=devServer;Server Port Address=108

In a somewhat more complicated scenario, you may not want to build entirely new connection string, but just modify certain values within the currently used connection string. The source code provided with this posting deals with such a scenario. Once the connection string is ready, it is added to Application Settings:

Properties.Settings.Default.Properties.Add(
    new System.Configuration.SettingsProperty(
        "RuntimeConnectionString",
        String.Empty.GetType(),
        new System.Configuration.LocalFileSettingsProvider(),
        false,
        newConnectionString,
        System.Configuration.SettingsSerializeAs.String,
        new System.Configuration.SettingsAttributeDictionary(),
        false,
        false));

New property RuntimeConnectionString is added to the Application Settings. In line 7, newConnectionString is a StringBuilder which contains the run-time connection string value. Also notice in line 6, I specified false for the isReadOnly boolean parameter.

NOTE: There is a bug in the SettingsProperty constructor or Properties.Add() method, which basically ignores the isReadOnly parameter. That is, you will be able to modify a read-only setting!

Data Tier

In the application’s data tier, once the TableAdapterManager is instantiated, I call the private SetConnectionString() method, which reads the new Application Settings RuntimeConnectionString value and assigns it to the TableAdapterManager.Connection.ConnectionString property:

using ConfigHack.MyDataSetTableAdapters;

// ...

// Read the runtime connection string value
string connectionString = Properties.Settings.Default.Properties["RuntimeConnectionString"].DefaultValue.ToString();

// Update table adapter manager's connection string
this.tableAdapterManager.Connection.ConnectionString = connectionString;

NOTE: Remember to include the namespace as as shown above in line 1.

Now that we have changed the TableAdapterManager’s connection string, all tables should connect and work just fine, right?

No. Not yet.

.NET 3.5 TableAdapterManager has a bug. Changing the connection string for the Manager is supposed to change it for all underlying TableAdapters. But it is changed only for the very first TableAdapter, other TableAdapters still use the old connection. So, we will have to change the connection string for every TableAdapter, until Microsoft fixes this bug.

this.tableAdapterManager.CustomerTableAdapter.Connection.ConnectionString = connectionString;
this.tableAdapterManager.BlackListTableAdapter.Connection.ConnectionString = connectionString;

That’s all folks!

The work around explained here avoids several “problematic solutions” – like directly editing designer generated code, modifying the .config file at run-time etc. Also using this work around you can change the connection anytime you want to. Here we basically have a two-step strategy, once you dynamically build your connection string at run-time:

1. Add a new property in the Application Settings to store the run-time connection string.
2. Read this new property in your data tier and assign to the TableAdapterManager before accessing the DataSet.

Also, keep in mind, anytime the application Reload()s the properties from config file, you may have to redo adding the run-time connection string property.

For the complete source code, please see below. WordPress does not allow uploading of ZIP files, so I am not able to publish the entire application. I hope this information was useful to you. If you have questions or if you notice any errors, please let me know.

Source Code
C#:	ConfigHack-C#.pdf
VB:	Coming Soon

Bug Tracking:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=510914
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=519333
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=519338

Related links:
http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7483b816-be7a-4204-a4d3-cfb14b2aae26/
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/392e5774-3f43-486a-a5c9-5bb26772f489/
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=113388
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305701
http://weblogs.asp.net/rajbk/archive/2007/05/26/changing-the-connectionstring-of-a-wizard-generated-tableadapter-at-runtime-from-an-objectdatasource.aspx

---