Table Adapters and Generics in Visual Studio 2005

Introduction

This is an article by Eric Keller and Joel Etherton about using generics to minimize duplicate code when using Table Adapters in a Data Access Layer (DAL).

Background

Recently we had the need to perform the same logic on many different strongly typed table adapters. We wanted to do this without duplicating the code over and over for each table adapter created. Immediately we began tossing around the idea of generics as a solution to our problem. However as we began to test our idea, it was apparent that one very important issue stood in our way. There is no TableAdapter base class as table adapters inherit from the System.ComponentModel.Component class. Therefore, we could not directly apply the logic that we needed to the methods and properties of the table adapter via generics. See MSDN for more info on generics.

Below is an example of how to set the Connection property of many table adapters using a single generic method.

Using the code

Example Assumptions

1. You have created several table adapters and queries in a dataset (XSD) within a class assembly
2. You are accessing the table adapter methods created by Microsoft Visual Studio when you define the table adapters and queries via a custom Data Access Layer (DAL) class
3. Your DAL needs to assign a new connection to the connection property of the Table Adapter when it is called
4. You have a method in the DAL named GetDatabaseConnection that reads a connection string from the web.config file and returns a new database connection object

The way we solved the problem of the table adapter not having a base class was by creating our own in the DAL and then allowing the table adapters to inherit from our base class. Note: the custom base class must inherit from System.ComponentModel.Component. The base class for our example looks like this:

Imports SysData = System.Data
Public Class BaseTableAdapter
    Inherits System.ComponentModel.Component

    Private _sqlDbConnection As System.Data.SqlClient.SqlConnection

    Public Overridable Property Connection() _
                As SysData.SqlClient.SqlConnection
        Get
            Return _sqlDbConnection
        End Get

        Set(ByVal value As System.Data.SqlClient.SqlConnection)
            _sqlDbConnection = value
        End Set
    End Property

End Class 

Next we forced the table adapters to inherit from the custom base class that we had just created by changing the BaseClass property of the table adapter to our base class name.

The overridable property named Connection is now accessible in our generic method allowing us to set the property each time it is called from the DAL.

We created the generic method in the DAL. For our example here, this method will simply set the current connection object that is obtained from the GetDatabaseConnection method to the table adapter that is passed to the generic method. The generic method code looks something like this:

Private Function BuildGenericTableAdapter(Of adapterType As _
                {New, BaseTableAdapter})() _ 
As adapterType 
        Dim currentAdapter As New adapterType 
        currentAdapter.Connection = Me.GetDatabaseConnection 
        Return currentAdapter 
End Function 

Note: What makes this assignment possible is the BaseTableAdapter constraint in the generic declaration. It allows the compiler to assume that any properties or methods available in the base class will always be available.

Once we created the generic method, we were able to actually set the Connection property of a table adapter using code similar to the example below:

Public Overloads Function GetBasicContent(ByVal contentID As Integer) _
    As DataContentInfo.BasicContentDataTable 

    Dim typedDataTable As DataContentInfo.BasicContentDataTable
    Dim typedTableAdapter As _
    DataContentInfoTableAdapters.BasicContentTableAdapter _
        = BuildGenericTableAdapter( _
            Of DataContentInfoTableAdapters.BasicContentTableAdapter)()

    typedDataTable = typedTableAdapter.GetDataByContentID(contentID)
    Return typedDataTable
End Function

Public Overloads Function GetBasicContent(ByVal beginDate As Date, _
                        ByVal endDate As Date) _
        As DataContentInfo.BasicContentDataTable

    Dim typedDataTable As New DataContentInfo.BasicContentDataTable
    Dim typedTableAdapter _
        As DataContentInfoTableAdapters.BasicContentTableAdapter _
        = BuildGenericTableAdapter( _
            Of DataContentInfoTableAdapters.BasicContentTableAdapter)()

    typedDataTable = _
    typedTableAdapter.GetDataByDateRange_AllPages_AllVerStatus _
       (beginDate, endDate)
    Return typedDataTable
End Function

Public Overloads Function GetVersions(ByVal contentID As Integer) _
        As DataContentInfo.VersionsDataTable
    
    Dim typedDataTable As DataContentInfo.VersionsDataTable
    Dim typedTableAdapter As _
        DataContentInfoTableAdapters.VersionsTableAdapter _
        = BuildGenericTableAdapter( _
            Of DataContentInfoTableAdapters.VersionsTableAdapter)()
    typedDataTable = typedTableAdapter.GetDataByContentID(contentID)
    Return typedDataTable
End Function

As you can see, each of the overloaded methods was able to call a single generic method, pass a strongly typed table adapter to it, and get a strongly typed table adapter back with it's connection property set to a new connection object without having to box and unbox the types or bloat the code with overloads.