Rich Newman

April 22, 2007

Reasons Not to Use TableAdapters in .NET 2.0

Filed under: .net, c#, dataset, dotnet, table adapter, technology — richnewman @ 8:06 pm

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.

About these ads

11 Comments »

  1. Hi,

    This is my solution to customizing TableAdapters to take in different connections.

    A typed dataset’s TableAdapter will be defined as a partial class by the code generated by the designer automatically. We can create a new constructor for the TableAdapter class in another partial class declaration. For example…

    I have a TableAdapter called FloorPlanDS.FloorPlanTableAdapter, I can just declare another partial class with:

    public partial class FloorPlanTableAdapter : System.ComponentModel.Component
    {
    [System.Diagnostics.DebuggerNonUserCodeAttribute()]
    public FloorPlanTableAdapter(SqlConnection sConn)
    {
    this.ClearBeforeFill = true;
    this._connection = sConn;
    }
    }

    By doing this, I will call new FloorPlanTableAdapter(sConn) everytime I instantiate a TableAdapter object, which will in turn use my SqlConnection object, instead of the ones specified by the built-in code.

    Hope that solves your problem.

    Comment by VT — May 22, 2007 @ 6:37 am

  2. The 5/22 comment is excellent. I am creating database unit tests and using table adapters to speed up the process. Having the connection strings locked up in the config files in the class library containing the typed datasets and the table adapters would be a problem. Adding the partial class with the overloaded constructor appears to have solved the problem in a vary nice, clean, elegant manner. With this perhaps table adapters are not all bad. Thank you for your help.

    Comment by str_test — January 18, 2008 @ 7:29 pm

  3. Guys

    I clearly haven’t made my point all that well, which is that we don’t want developers setting up TableAdapters to have access to connection strings, but want our connections handled centrally. We can do this with DataAdapters, but not with TableAdapters.

    I’ll put together some code examples to clarify.

    Rich

    Comment by richnewman — January 19, 2008 @ 1:53 pm

  4. Rich is correct. This is very limiting. I will stick with a class that uses my typed dataset, but then will use a dataadapter to do the updates of the data to the database. This means I will have to have an update function in my class that will define my stored proc and the corresponding columns in the typed dataset to the sp parameters, but it is way better than having to use a connection when defining my dataset.

    Comment by Bob — March 1, 2008 @ 12:41 am

  5. I have never had problems with TableAdapters and SQL connections, and an enormous amount of connections being opened.

    TableAdapters do open connections through a DataAdapter. You can create a partial class of a TableAdapter in a dataset, and expose the DataAdapter of the TableAdapter. I do this to expose the RowUpdated event of the DataAdapter.

    Example:

    If MyTableTableAdapter exists, you can do the following:

    Partial Class MyTableTableAdapter
    {

    public SqlDataAdapter MyTableAdapter
    {
    if (this._adapter == null)
    {
    this.InitAdapter();
    }
    return this._adapter;
    }

    }

    End Class

    Comment by Chuck — March 18, 2008 @ 9:51 pm

  6. I too, have never had a problem. What Rich needs to realise is that ADO.NET performs database connection pooling itself unless switched off via connection string (not a good idea) so even if he writes code to check he is being strict about opening and closing every connection, ADO.NET (yes, TableAdapter [wraps a DataAdapter [wraps a DataReader]]) will open multiple conenctions if it feels like it. Open and Close simply leases and returns conenctions to the pool. Holding a connection open for a long time is a bad idea, because it negates the benefits of having a pool in the first place.

    Open it, use it, close it. If you use TableAdapters exclusively then they actually do this open/close for you so are strict about it, and you dont encounter hundreds of open connections making your app run like a dog. Remember that ADO.NET is pooling based on the conenction string so conceptually you’d be able to have many apps in .NET all open on one machine, all using the same database, and all sharing one connection.

    To expose the DataAdapter a TableAdapter uses (primarily so you can get at its connection, right?), you dont even have to go as complex as most of the posters here recommend, putting partial classes in. Just go to the properties of the TableAdapter and set the ConnectionModifier to something accessible in your context. By default it is Internal. This property is only available on TAs that fill a datatable. For TAs containing no datatable related methods (no Select, Update, Insert or Delete commands) you must expand the Connection property for each command and set the connection’s Modifier status as you please

    Also, take a look at the designer generated code for a TA; it’s quite clever in that it only closes a connection that it opened. If you opened the conenction yourself, the TA will leave it open.. Naturally this is then open to the abuse you complain of, of hundreds of useless open conenctions lying around.. But that’s what you get when you allow fickle, forgetful humans to meddle in perfectly good, reliable, code-generated-code

    The other things you complained of:

    Connection string password security -> yep, settings files can be encrypted!

    They inherit System.Component -> ahh, one of my biggest bugbears with them.. Until I realised that the class they descend from can also be specified as a property, so now its easy to pass them around and refer to them generically (not that I need to very often)

    “My projects have only one open DB conenction” -> you dont write high performance multithreaded programs then?

    Mistakenly accessing a dev db in prod -> Yep, and one simple #if !DEBUG if(settings.ConnectionString.Contains(“DEVEL”) throw Exception(“This release program appears to be connected to the development database”); solved that one for me

    all of our calls to set up connections go through one method -> This would be the little old OO concept of encapsulation, coupling related methods together in one place.. Mmm.. Something like a TableAdapter does, don’t you think? Everything related to accessing and pushing/pulling a db table all in one place.. multiple queries allowed ensure you dont end up with strSQL = “SELECT …” in a button handler..

    In short.. Congratulations on your solution that uses DataAdapters; it sounds exactly like what TableAdapters do for me, and the IDE helps me build the DAL.. It took me a long time to get really into liking TAs and how they work, but now that they are the devil I know, I can see the wisdom of them. I think htey may still be at the “devil you dont” know stage for you, which is why there is a bit of fear and loathing.. ;)

    Comment by cjard — June 10, 2008 @ 10:55 am

  7. You can also change your connection string in the DataAccess project that contains the datasets.
    It is locked but you can doing something like:

    foreach (SettingsProperty p in Properties.Settings.Default.Properties)
    Properties.Settings.Default[p.Name] = myConnectionString;

    also can add a check to do it only con connection strings…
    I’m using it in a asp.net project and its executed in the global.asax once on application load.

    Comment by Aridane Álamo — December 11, 2008 @ 5:44 pm

  8. I have the same problem that I need my connectors to “seemlessly” connect to other databases. The solution I have used is to override the my.settings. Basically, I use one setting e.g. DevConnString for the adapters that points to my fixed development database. Using system configuration information, I repoint this to the appropriate database at run time. e.g.

    Option Strict On
    Option Explicit On

    Namespace My
    Partial Friend NotInheritable Class MySettings
    Inherits Global.System.Configuration.ApplicationSettingsBase

    Public Enum DynamSystem
    DefForWebSite = 0
    UxOnline = 1
    UxIntranet = 2
    End Enum

    Private ConfOverrides() As String = { _
    “UxOnlineConnectionString”, _
    “UxIntranetConnectionString”, _
    “UxDynamConnectionString”, _
    “UxConnectConnectionString” _
    }

    Private g_CurrSystem As DynamSystem = DynamSystem.DefForWebSite ‘ Sets dynmic configuration string

    Private Sub userOverride_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) _
    Handles Me.SettingsLoaded
    ‘ Used to intercept requests for application properties and if “ours” replace them with the
    ‘ system dependent ones
    Dim appProperty As String
    Dim DefSystem As String

    Using Conf As UxConfig = New UxConfig
    For Each appProperty In ConfOverrides
    Select Case appProperty
    Case “UxOnlineConnectionString”
    ‘ request for Uxonline connection string
    Me(appProperty) = Conf.GetNetConnString(SystemName.Online)
    Case “UxIntranetConnectionString”
    ‘ request for Intranet connection string
    Me(appProperty) = Conf.GetNetConnString(SystemName.Intranet)
    Case “UxConnectConnectionString”
    ‘ request for Connect connection string
    Me(appProperty) = Conf.GetNetConnString(SystemName.Connect)
    Case “UxDynamConnectionString”
    ‘ use the connection string for the current dynamic system
    If g_CurrSystem = DynamSystem.DefForWebSite Then
    ‘ Default for web site set to appropriate value
    DefSystem = Conf.GetNetValue(“System”)
    If DefSystem = DynamSystem.UxIntranet.ToString Then
    g_CurrSystem = DynamSystem.UxIntranet
    ElseIf DefSystem = DynamSystem.UxOnline.ToString Then
    g_CurrSystem = DynamSystem.UxOnline
    Else
    Throw New Exception(“Unrecognised configured dynamic system: ” & DefSystem)
    End If
    End If

    Select Case g_CurrSystem
    Case DynamSystem.UxIntranet
    Me(appProperty) = Conf.GetNetConnString(SystemName.Intranet)
    Case DynamSystem.UxOnline
    Me(appProperty) = Conf.GetNetConnString(SystemName.Online)
    Case Else
    Throw New Exception(“Unrecognised dynamic system: ” & g_CurrSystem.ToString)
    End Select

    Case Else
    ‘ request for a value
    Me(appProperty) = Conf.GetNetValue(appProperty)
    End Select
    Next
    End Using
    End Sub

    Private Sub userOverride_SettingsSaving(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles Me.SettingsSaving
    ‘ Settings are read only
    End Sub

    end class

    Comment by Paul Ireland — February 2, 2009 @ 12:48 pm

  9. I was reading through your site – the top part was very interesting as I also had the problem of trying to set my Wizard Created forms, Adapters, Connections to different databases (with the same structure on different servers)

    I did this with simple code and it was not more than 5 lines of VB code.

    What is it that you need done ?

    Comment by Dyfin — May 19, 2009 @ 8:13 pm

  10. I would be very interested to see some sample code….

    – Andy

    Comment by Andrew Heys — September 16, 2009 @ 1:01 pm


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: