Design decision: (VB.NET) Should I create a class or module to easily connect to one of many databases?

database, vb.net

Solution

I would try to put all my data access logic into a data access layer. Ideally this would be in a separate library and namespace, but it doesn't have to be. I would use classes, typically one per table/entity and design all the classes to be stateless (so you don't have to ever reuse the same instance of the data access object, you can just instantiate a new one any time you need to access the DB).

Instead of having it return arrays, I would have it return data objects (often called DTO's - data transfer objects). I would keep the DTO classes as clean as possible containing only public properties and no methods, if possible. The data access classes should all implement interfaces so that multiple versions of each one can be created. One for Access, one for Oracle, one for SQL, etc. Then, wherever I needed to access the database (hopefully only in my business layer, not ever in my UI layer), I would request the appropriate data access objects by their "generic" interface type (thereby requiring a factory class to inject the correct concrete data access object type into my business object).

Here's a real simple example of a DTO:

Public Class Employee
    Public Id As Guid
    Public Name As String
    Public StartDate As Date
End Class

Here's an example Data Access interface

Public Interface IEmployeeDataAccess
    Function GetEmployee(id As Guid) As Employee
    Function GetEmployees() As List(Of Employee)
End Interface

Here's an example of an data access class:

Public Class SqlEmployeeDataAccess
    Inherits IEmployeeDataAccess

    Public Function GetEmployee(id As Guid) As Employee Implements IEmployeeDataAccess.GetEmployee
        Dim employee As New Employee()
        ' Connect to SQL DB and populate employee DTO object
        Return employee
    End Function

    Public Function GetEmployees() As List(Of Employee) Implements IEmployeeDataAccess.GetEmployees
        Dim employees As New List(Of Employee)()
        ' Connect to SQL DB and populate list of employee DTO objects
        Return employees
    End Function
End Interface

You might then make similar classes called `AccessEmployeeDataAccess` and `OracleEmployeeDataAccess` which also implement the IEmployeeDataAccess interface. Then, also in the data access layer, I would create a factory class for each supported DB provider. I would make all the DataAccess factories implement the same interface, like this:

Public Interface IDataAccessFactory
    Function NewEmployeeDataAccess() As IEmployeeDataAccess
End Interface

Public Class SqlDataAccessFactory
    Implements IDataAccessFactory

    Public Function NewEmployeeDataAccess() As IEmployeeDataAccess
        Return New SqlEmployeeDataAccess()
    End Function
End Class

Then, in my business class, I might do something like this:

Public Class EmployeeBusiness
    Public Sub New(employeeDataAccess As IEmployeeDataAcess)
        _employeeDataAccess = employeeDataAccess
    End Sub

    Private _employeeDataAccess As IEmployeeDataAcess

    Public Function GetEmployee(id As Guid) As Employee
        Return _employeeDataAccess.GetEmployee(id)
    End Function
End Class

And then in my business factory, I'd do something like this:

Public Class BusinessFactory
    Public Sub New()
        Select Case dataAccessType
            Case "SQL"
                _dataAccessFactory = New SqlDataAccessFactory()
            Case "Oracle"
                _dataAccessFactory = New OracleDataAccessFactory()
            Case "Access"
                _dataAccessFactory = New AccessDataAccessFactory()
        End Select
    End Sub

    _dataAccessFactory As IDataAccessFactory

    Public Function NewEmployeeBusiness() As IEmployeeBusiness
        Return New EmployeeBusiness(_dataAccessFactory.NewEmployeeDataAccess())
    End Function
End Class

This could be simplified a great deal by having a single set of data access objects that work with any database provider. To do that, you'd need to use only base ADO types like IDbConnection instead of SqlConnection, and IDbCommand instead of SqlCommand. Then your data access objects could ask for a DB Provider factory which could create a new connection, etc., whenever the DataAccess class needs one. This, however, is easier to do when you are simply calling stored procedures, or something. Often, especially when you are dynamically building your SQL sentences in code, there are too many differences between providers so you can't just use the same DataAccess class for all DB providers.

But, that's just me...

Problem

Basically, we have three databases to grab data from. One is a SQL Server database, one is an Access database (which is particularly annoying to connect to because we have to map a network drive and such), and the final one will be an Oracle database when IT finally gives us rights. I'm thinking about creating a helper function that makes querying any one of these databases as easy as possible. Ideally, I want to create a two-dimensional array ``` Dim myEasyResultArray(10,10) as String myEasyResultArray = DatabaseHelper("Access", "SELECT * FROM Employee") ``` Is this a good design decision? Also, how can I have the array be the right size? Can I just do this? `Dim myEasyResultArray = DatabaseHelper("Access", "SELECT * FROM Employee")` Should this be a module or a class? I don't really need to share variables,

Original source