设计决策：（VB.NET）我应该创建一个类还是模块来轻松连接到多个数据库中的一个？ [英] Design decision: (VB.NET) Should I create a class or module to easily connect to one of many databases?

问题描述

基本上，我们有三个数据库来抓取数据。一个是SQL Server数据库，一个是Access数据库（这是特别讨厌连接，因为我们必须映射网络驱动器等），最后一个将是一个Oracle数据库，当IT终于给了我们的权利。 p>

我正在考虑创建一个帮助函数，使任何一个数据库的查询尽可能简单。理想情况下，我想创建一个二维数组

  Dim myEasyResultArray（10,10）as String 
 myEasyResultArray = DatabaseHelper（Access，SELECT * FROM Employee）
  

此外，我如何可以使数组的大小合适？

Dim myEasyResultArray = DatabaseHelper（Access，SELECT * FROM Employee）

这应该是模块还是类？我真的不需要共享变量，

解决方案

我会尝试把所有我的数据访问逻辑到数据访问层。理想情况下，这将在一个单独的库和命名空间，但它不一定是。我将使用类，通常每个表/实体一个和所有的类设计为无状态（所以你不必重复使用相同的数据访问对象的实例，你可以只是实例化一个新的，任何时候你需要访问DB）。

我将返回数据对象（通常称为DTO的数据传输对象）而不是返回数组。我将保持DTO类尽可能干净，只包含公共属性和没有方法，如果可能的话。数据访问类应该都实现接口，以便可以创建每个接口的多个版本。一个用于Access，一个用于Oracle，一个用于SQL等。然后，无论何时我需要访问数据库（希望只在我的业务层，而不是在我的UI层），我将请求适当的数据访问对象，通用接口类型（因此需要一个工厂类来将正确的具体数据访问对象类型注入到我的业务对象中）。

这是一个DTO的简单示例： / p>

 公共类员工
 Public Id As Guid 
 Public Name As String 
 Public StartDate As Date 
结束类
  

p>

 公共接口IEmployeeDataAccess 
函数GetEmployee（id As Guid）as Employee 
 Function GetEmployees（）作为列表（员工）
结束接口
  

访问类：

 公共类SqlEmployeeDataAccess 
 Inherits IEmployeeDataAccess 
 
公共函数GetEmployee（id As Guid）作为员工实现IEmployeeDataAccess.GetEmployee 
 Dim employee As New Employee（）
'连接到SQL DB并填充员工DTO对象
返回employee 
结束函数
 
公共函数GetEmployees（）作为列表（员工）实现IEmployeeDataAccess.GetEmployees 
 Dim employees作为新列表（Employee）（）
'连接到SQL DB和填充员工DTO对象的列表
返回员工
结束函数
结束接口
  

然后您可以创建类似的类 AccessEmployeeDataAccess 和 OracleEmployeeDataAccess ，它们也实现了IEmployeeDataAccess接口。然后，在数据访问层，我将为每个支持的DB提供程序创建一个工厂类。我将使所有的DataAccess工厂实现相同的接口，像这样：

 公共接口IDataAccessFactory 
函数NewEmployeeDataAccess（）As IEmployeeDataAccess 
结束接口
 
公共类SqlDataAccessFactory 
实现IDataAccessFactory 
 
公共函数NewEmployeeDataAccess（）As IEmployeeDataAccess 
返回新的SqlEmployeeDataAccess（）
结束函数
结束类
  

我的业务类，我可能会这样做：

 公共类EmployeeBusiness 
 Sub New（employeeDataAccess As IEmployeeDataAcess）
 _employeeDataAccess = employeeDataAccess 
 End Sub 
 
私有_employeeDataAccess作为IEmployeeDataAcess 
 
公共函数GetEmployee（id As Guid）as Employee 
返回_employeeDataAccess.GetEmployee（id）
结束函数
结束类
  

然后在我的商业工厂，我会这样做：

  BusinessFactory 
 Public Sub New（）
选择案例dataAccessType 
案例SQL
 _dataAccessFactory =新的SqlDataAccessFactory（）
案例Oracle
 _dataAccessFactory = OracleDataAccessFactory（）
案例Access
 _dataAccessFactory =新的AccessDataAccessFactory（）
结束选择
结束Sub 
 
 _dataAccessFactory作为IDataAccessFactory 
 
公共函数NewEmployeeBusiness（）作为IEmployeeBusiness 
返回新的EmployeeBusiness（_dataAccessFactory.NewEmployeeDataAccess（））
结束函数
结束类
  

这可以通过拥有一组与任何数据库提供程序一起工作的数据访问对象来大大简化。要做到这一点，你只需要使用基本的ADO类型，如IDbConnection，而不是SqlConnection，和IDbCommand，而不是SqlCommand。然后你的数据访问对象可以要求一个DB提供程序工厂，它可以创建一个新的连接等，每当DataAccess类需要一个。但是，当你简单地调用存储过程，或者什么，更容易做。通常，特别是当您在代码中动态构建SQL语句时，提供程序之间的差异太大，因此您不能对所有数据库提供程序使用相同的DataAccess类。

但是，这只是我...

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...

这篇关于设计决策：（VB.NET）我应该创建一个类还是模块来轻松连接到多个数据库中的一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！