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,
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...
I see one big issue with your proposal.
The first is that I often see programmers want to build a database help with methods that resemble this form:
DataTable GetData(string sql)
The important point here is that the method accepts an sql string, with no provision for query parameters. This is wrong. It is an anti-pattern, because it encourages you (pretty much forces you) to encode sql data as part of the query code. You must provide some mechanism for the correct passing of query parameters as separate data. Arrays are common, but that's not the only way. I also normally do not even provide an overload that accepts only the string, as it inevitably leads to abuse. If you want to send a query with no parameter, then pass an empty array. This way it is clear that is what you intend, and anyone who uses your database helper will be encouraged to learn to use parameters the right way.
There are other things that could be improved as well, but this to my mind is the main issue.
