Reasons Not to Use TableAdapters in .NET 2.0

Introduction

We have decided to ban the use of TableAdapters on our project for the time being. This is because there is no way that we can see of doing centralized connection management using them. Every TableAdapter is capable of creating its own new Connection object. There’s no easy way of overriding that so that whenever a TableAdapter is instantiated it will call out to use a Connection provided by another component. In general we don’t want connections to be available in the TableAdapters component at all.

If anyone can help with this it would be appreciated.

We can get the behaviour we want with DataAdapters (indeed we already do have this in place). I will explain in more detail why this is below.

Why is Connection Management Important?

I am personally paranoid about this because several years ago I was given responsibility for a project that had a number of fundamental problems. The most entertaining of these was that immediately after logging on to the system any given user would have twelve database connections. This number then grew over time. Users could have hundreds of simultaneous connections. You can imagine how well the application ran.

This was exceedingly difficult to address. Connections would be cached and then just re-used assuming they were open. So closing one could easily mean an exception later in execution when the code tried to access the database. We had to attempt to trawl through the code finding every place the database was accessed. This could be almost anywhere because the layering was so poor.

Current solution with Data Adapters

Ever since then I’ve made sure that the projects I’m working on have only one database connection at most open at any given time. The best way to do this is not to let developers anywhere near the connection string, but to give them a series of methods that handle it for them. So we have a separate library that does connection management. This exposes public methods like ExecuteScalar and ExecuteNonQuery which work exactly like the .NET equivalents, except that no connection is necessary.

Whenever this library gets a call of this kind it gets the connection string (which involves decrypting a password in the configuration file), creates a connection, executes the SQL passed in, and closes the connection. Neither the connection nor the connection string is exposed outside of the library.

Similarly we have public methods called FillDataSetUsingDataAdapter and UpdateDataSetUsingDataAdapter that take a DataSet and a DataAdapter and fill or update on the correct database connection regardless of what connections are on the adapter. We can pass the generic DataSet type in to these methods even though our DataSets are strongly-typed because the strongly-typed DataSets inherit from System.Data.DataSet. All our DataAdapters are of type OleDbDataAdapter and we can work with that in the methods.

Advantages of this Approach

Note that there’s no SQL at all in the connection management library. All the SQL is written in a separate ‘data access’ library, which, as mentioned, has access to these methods but not to the correct connection to the database. This means developers are not tempted to just instantiate a connection and use it.

Furthermore, developers can create DataAdapters and typed DataSets using Microsoft’s code generation tools against any copy of the database. Although these will get cached in the data access library, in production code the connections will be redirected to point at the production database. There’s no danger of mistakenly accessing a development database in production.

Finally all of our calls to set up connections go through one method, and all of our database calls go through one small set of routines. This lets us run specific code whenever we connect to a database. For example we have a read-only database. If the code tries to write to it then the exception is intercepted and a sensible error message put into it so the user will know what’s going on. We can also do database logging, and ensure that all connections are immediately closed after they are used (assuming that’s what we want: we can just as easily turn this off).

Table Adapters

At present we can’t see how we can do a similar thing with table adapters, at least not without writing custom code every time we set one up (which rather defeats the point of them).

Firstly they are types generated on the fly (inheriting System.Component) so we can’t pass them into a separate library in the same way as Data Adapters. We could give them a custom interface and pass them in, or just extract the underlying adapter and pass that in, but this isn’t giving us any advantages over a data adapter and involves more code.

So we’re being pushed towards making the connection string available in the data access component where they will be set up, which isn’t very satisfactory. In my earlier article I was discussing the difficulty of doing custom password decryption in this scenario, but in fact we can achieve this by a call at application start up that constructs the connection string and sets it on the Settings class (which needs to be extended with a setter to do this).

There is an alternative to making the connection string generally available to database code as above. This is to extract the table adapters into yet another library that does have access to the connection string, whilst leaving the existing data access component alone. This doesn’t get rid of the problem that our connections are not now all set up in the connection manager though.

Conclusion

Table adapters don’t seem to be giving us many advantages when compared with data adapters, and are giving us problems with our connection management. For the time being we’ll leave them alone.