Rich Newman

March 4, 2007

Problems with Table Adapters in .NET 2.0

Filed under: .net, c#, dataset, dotnet, table adapter, tableadapter, technology — richnewman @ 6:11 pm

There is now second article on the problems with table adapters in .NET.


In Visual Studio 2005 Microsoft have effectively deprecated the use of the separate DataAdapter components (OleDbDataAdapter, SQLDataAdapter). They have replaced them with TableAdapters that are code-generated with the DataSet itself, in the same code module. However, we are struggling with how to do connection management with table adapters. A couple of initial problems are described here.

Problem 1: Custom Connection String Set-Up

One problem is that within our organization we are not permitted to use Microsoft’s integrated security. Furthermore, we are expected to use generic database accounts to connect to databases (so all users use the same account). We have been storing the password for this database account in the app.config file on the client workstation. When we do this we encrypt it using 256-bit AES encryption.

So if we are going to use table adapters we need to decrypt the password and set it on the table adapter. We can’t directly use the configuration file password in the way Microsoft clearly intend you to. It is possible that we could use Protected Configuration for this, of course.

Problem 2: Development vs Production Databases

We also need a method of ensuring that for release builds the connection strings on all table adapters are pointing at the production database. Developers can set up table adapters to point at any database they like (and of course this will be a development database) and this connection is cached and will be re-used. Obviously you don’t want your live system accessing a development database for certain data.


To solve both of these problems we need to be able to set the connection string used by all the table adapters to the same correct value at system start up. Further, we need to be able to construct the connection string in code and then set it.

To do this:

  1. Set up a new data source using the Data Source Configuration Wizard. This can point at any database, but when the wizard asks if you want to save the connection string to the applicaition configuration file tick ‘Yes’ and give the connection a sensible name (e.g. MainConnectionString).
  2. This has the effect of adding the string to both the application configuration file AND to the Settings class (under Properties in Solution Explorer). You’re not interested in the settings in the application configuration file and can delete them. For most projects the table adapters won’t be in the start up component in any case, which means an application configuration file won’t have any effect.
  3. Now extend the Settings class by adding a new partial class to your project as below. This should be in the same namespace as the existing settings class (since it is the same class). As shown, expose a method to set MainConnectionString (or whatever you have called it) on this class.
  4. Now write code that will run at start up, construct your connection string, and set it on the Settings class using your new method.
    internal sealed partial class Settings
         internal void SetMainConnectionString(string value)
             this["MainConnectionString"] = value;

All developers must now use your new named connection to connect any table adapter to the database. However, they can freely edit it to point at any development database they like, because the start up code will redirect it in production. This editing can easily be done in the Settings screen. Obviously you still can’t easily stop developers using the wrong named connection by mistake. You could in theory write a unit test using reflection to find any table adapters that didn’t have their connection correctly set (although this might be tricky since TableAdapter isn’t actually a type).


The problems with table adapters outlined here can be solved by using the Settings class.

However, currently in our project we are still using ‘old-style’ OleDbDataAdapters for all our data access. We upgraded our project to .NET 2.0 at the back end of last year, but have not yet moved to using TableAdapters due to time constraints, and because of the problems above and the problems outlined in my second article – Reasons Not to Use TableAdapters in .NET 2.0.

Day 1

Filed under: general — richnewman @ 3:59 pm

Welcome to my blog.

Create a free website or blog at