VB.NET SQL Factory Pattern

What would be a very good way of implementing a factory pattern for SQL-Access? It should be easy maintainable in the future.

I run into trouble because of different tables having different columns.

I created a new Project (Windows Forms) in VB.NET and called it "DBAccess" so that you can follow me more easy instead of pasting my hugh project here.

• I added a new item (Folder) and called it "DBAccess"
• I added a new Item (Module) and called it "DBAccessFactory"
• I added a new Item (Class) and called it "DBAccessData"
• I added a new Item (Class) and called it "DBAccessUser"
• I added a new Item (Code File) and called it "IDBAccess"

The idea behind my factory is that the it returns an object depending on an Enum. In the future you can easily add a new class if needed and simply add a new initialisation to the factory. Each time when you call the "GetDBAccess" I exeute the "setConnectionString" method so that if in feature something changes with the connection string you only need to change it at one place.

Public Module DBAccessFactory

    Private _connectionString As String = ""

    Public Enum DatabaseTable
        Data = 0
        User = 1
    End Enum

    Function GetDBAccess(table As DatabaseTable) As IDBAccess
        Try
            Dim dbAccessor As IDBAccess = Nothing

            setConnectionString()

            ' Get the correct DBAccessor for the table depending on the Enum
            Select Case table
                Case DatabaseTable.Data
                    dbAccessor = New DBAccessData(DBAccessFactory._connectionString)
                Case DatabaseTable.User
                    dbAccessor = New DBAccessUser(DBAccessFactory._connectionString)
            End Select

            ' Return an object that can access the table in our database
            Return dbAccessor

        Catch ex As Exception
            LogManager.handleExceptionDefault(ex)
            Return Nothing
        End Try
    End Function

    Private Sub setConnectionString()
#If DEBUG Then
        DBAccessFactory._connectionString = My.MySettings.Default.ConnectionTESTDB
#Else
        DBAccessFactory._connectionString = My.MySettings.Default.ConnectionLIVEDB
#End If
    End Sub
End Module

My DBAccessUser looks similar to this.

The idea is that whenever you retrieve an object the connection string is always the same to create a SQL connection. If you need to change it you go to the factory class.

Public Class DBAccessUser : Implements IDBAccess

    Private _connectionString As String

    Sub New(connectionString As String)
        _connectionString = connectionString
    End Sub

    Public Function GetExportedDates() As Date() Implements IDBAccess.GetExportedDates
        Throw New NotImplementedException()
    End Function

    Public Function GetUser() As Object Implements IDBAccess.GetUser
        Dim user As New Object ' Userobject

        ' SELECT * FROM USER
        ' user.add(Name)
        ' user.add(E-Mail)
        Return user
    End Function
End Class

My DBAccessData looks similar to this.

Public Class DBAccessData : Implements IDBAccess

    Private _connectionString As String

    Sub New(connectionString As String)
        _connectionString = connectionString
    End Sub

    Public Function GetExportedDates() As Date() Implements IDBAccess.GetExportedDates
        Dim exportDates(5) As Date

        ' SELECT ExportedDates FROM Data
        ' exportDates(0 .. N) = Some Date
        Return exportDates
    End Function

    Public Function GetUser() As Object Implements IDBAccess.GetUser
        Throw New NotImplementedException()
    End Function
End Class

Both classes implement an Interface similar to this one

Public Interface IDBAccess

    Function GetExportedDates() As Date()

    Function GetUser() As Object 'Userobject with ID, Name, E-Mail etc...
End Interface

Now I can do this in my Client

Public Class Form1
    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

        Dim accessData As IDBAccess
        accessData = DBAccessFactory.GetDBAccess(DatabaseTable.Data)
        accessData.GetExportedDates()

        Dim accessUser As IDBAccess
        accessUser = DBAccessFactory.GetDBAccess(DatabaseTable.User)
        accessUser.GetUser()
    End Sub
End Class

1) Big problem is that I have about 20 tables each looking different 2) While I proceed like this I have to add more and more "NotImplemented" Exceptions because each table has to be accessed in a different way.

I tried Abstract classes and Interfaces but I don't get to a desired result.

How would you solve this issue?