Getting rid of the DTC using TableAdapters

DTC is not good for your performance, and it's a pain in the ass to get rid of..

An interesting link is http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx, this solution uses Reflection to set the SqlTransaction object to the commands of your TableAdapter.

But, we wanted to use the (new in .NET 2.0) TransactionScope object instead of the SqlTransaction object to control our transactions.. If I just use the TableAdapters of my typed DataSets as such, i got the DTC running, which isn't good for your performance. So I tried some stuff and I concluded that I had to control the connections of the TableAdapters.

Instead of letting the TableAdapters create their own SqlConnection objects, I had to give them the reference to one and the same SqlConnection object for each TableAdapter.

If you look at my blogpost "Use assemblies containing typed DataSets: How to handle the connection strings?" you can see how I use a Factory class to control my TableAdapters. When invoking the methods to ask the Factory for your desired TableAdapter, you'll have to pass the ConnectionString.
Well, now I made it so that I have to pass a SqlConnection object.

Conclusion: To get rid of the DTC, you'll have to use 1 and the same SqlConnection object for all your TableAdapters AND you'll have to Open() the connection before you use a TransactionScope and you'll have to Close() the connection after the use of a TransactionScope object.

Maybe I'll post an example program, but I haven't got much time.. If you want one, please mail me, I'll do my best..

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)