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.

Introduction

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.

Solution

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).

Conclusion

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.

http://blogs.msdn.com/vbteam/archive/2004/07/19/187953.aspx#214433

About these ads

2 Comments »

  1. how do I implement same method for prod vs stage database?

    Comment by xyz — July 18, 2008 @ 3:52 pm

  2. And if you are trying to set this from different assembly, you must also implement litle helper class:

    public class DBSettingClassHelper
    {
    public static void SetMainDBConnectionString(string value)
    {
    Settings.Default.SetMainDBConnectionString(value);
    }
    }

    (internal means Settings class is oly visible to local assembly)

    Comment by Tommi Rouvali — August 28, 2008 @ 6:08 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: