Use assemblies containing typed DataSets: How to handle the connection strings?

Remark 1: Found syntax- or language errors? Let me know!

Remark 2: I’d rather not get in discussion with people about the use of typed datasets, I’m not a fan of it, but I had to use it at a customer.

The problem
If the user of the application you’re making has to set the connection string in a config file of the application, the user had to define a connection string setting for each assembly that uses typed datasets.
Suppose I have two assemblies named Knrs.ConnectionStringsExample.Db.dll and Knrs.ConnectionStringsExample.Db2.dll, you’ll get something like this:


connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />

connectionString="Data Source=qsf;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />


Download the zip file containing a Visual Studio 2005 sln with the projects (+ setup project) STEP 1: Problem

What do we want?
We wanted one single connectionstring setting in our config file for all the assemblies!

The solution
Last week, we found a solution for this annoying problem. We studied the handling of connection string in the code that the typed dataset designer creates.

We had a look at the property Connection that the designer generated. It’s a internal property, meaning that the property can be get/set by a file in the same assembly. Because the Windows form project that the assemblies uses, is not the same assembly, we can not set/get the Connection property directly.

So we found out the concept: TableAdapterManager. We created a static class in each assembly that uses typed datasets:
public static class DbTableAdapterManager
{
///

/// Get a SuppliersTableAdapter object with a custom connection string
///

/// The connectionstring you want to assign to the table adapter
public static SuppliersTableAdapter GetSuppliersTableAdapter(string connectionString) {
SuppliersTableAdapter suppliersTableAdapter = new SuppliersTableAdapter();

//Make a SqlConnection with the given connection string
SqlConnection sqlConnection = new SqlConnection(connectionString);

//Assign the connection to the table adapter
suppliersTableAdapter.Connection = sqlConnection;

return suppliersTableAdapter;
}
}

(for full code, download the sln file!)

As you can see, it has a method called Get[tableadapternamehere](string connectionString) and it’s pretty obvious what it does: it just replaces the connection of a new instance of the table adapter class by a new connection using a connection string that you can pass in the method.

If you make such a class for each assembly that uses typed datasets, your problem is solved! You can just place 1 connectionstring in the config file of the application and pass this connectionstring in the method to get the desired table adapter!

The only little contra for this method is that you can not just make an instance of the table adapter class, but that you have to ask the TableAdapterManager class for an instance..

But it’s easier and cleaner for the users of your application to just define 1 connectionstring, instead of defining 2, 3, …, x connectionstrings.

Download the zip file containing a Visual Studio 2005 sln with the projects (+ setup project) STEP 2: Solution

Questions? koen [dot] roos [at] telenet [dot] be

Thanks to Mark Devos (calidos.be)