递归数据库操作

Question

在我的应用程序中，我一直在使用递归数据库操作的代码模式。

我创建了一个数据库表的两个类对象:单数类对象(例如代理)，用于保存包含所有字段定义的单个记录；为记录的数据库操作创建复数类对象(例如代理)，例如，选择、插入、删除、更新等。我发现使用代码模式很容易。

但是，随着时间的推移，我发现在不同的类中定义相同的数据库操作函数有点费劲，只是数据类型不同。

我如何才能使它变得更好，避免一次又一次地定义它？

表示类定义的类文件的示例代码：

Imports EssenceDBLayer

Public Class Booking
#Region "Constants"
    Public Shared _Pre As String = "bk01"
    Public Shared _Table As String = "bookings"
#End Region

#Region " Instance Variables "
    Private _UIN As Integer = 0
    Private _Title As String = ""
    Private _Email As String = ""
    Private _contactPerson As String = ""
    Private _Telephone As String = ""
    Private _Mobile As String = ""
    Private _Address As String = ""
    Private _LastBalance As Double = 0
#End Region

#Region " Constructor "
    Public Sub New()
        'Do nothing as all private variables has been initiated'
    End Sub

    Public Sub New(ByVal DataRow As DataRow)
        _UIN = CInt(DataRow.Item(_Pre & "UIN"))
        _Title = CStr(DataRow.Item(_Pre & "Title"))
        _Email = CStr(DataRow.Item(_Pre & "Email"))
        _contactPerson = CStr(DataRow.Item(_Pre & "contact_person"))
        _Telephone = CStr(DataRow.Item(_Pre & "Telephone"))
        _Mobile = CStr(DataRow.Item(_Pre & "Mobile"))
        _Address = CStr(DataRow.Item(_Pre & "Address"))
        _LastBalance = CDbl(DataRow.Item(_Pre & "Last_Balance"))
    End Sub
#End Region

#Region " Properties "
    Public Property UIN() As Integer
        Get
            Return _UIN
        End Get
        Set(ByVal value As Integer)
            _UIN = value
        End Set
    End Property

    Public Property Title() As String
        Get
            Return _Title
        End Get
        Set(ByVal value As String)
            _Title = value
        End Set
    End Property

    Public Property Email() As String
        Get
            Return _Email
        End Get
        Set(ByVal value As String)
            _Email = value
        End Set
    End Property

    Public Property ContactPerson() As String
        Get
            Return _contactPerson
        End Get
        Set(ByVal value As String)
            _contactPerson = value
        End Set
    End Property

    Public Property Telephone() As String
        Get
            Return _Telephone
        End Get
        Set(ByVal value As String)
            _Telephone = value
        End Set
    End Property

    Public Property Mobile() As String
        Get
            Return _Mobile
        End Get
        Set(ByVal value As String)
            _Mobile = value
        End Set
    End Property

    Public Property Address() As String
        Get
            Return _Address
        End Get
        Set(ByVal value As String)
            _Address = value
        End Set
    End Property

    Public Property LastBalance() As Double
        Get
            Return _LastBalance
        End Get
        Set(ByVal value As Double)
            _LastBalance = value
        End Set
    End Property

#End Region

#Region " Methods "
    Public Sub [Get](ByRef DataRow As DataRow)
        DataRow(_Pre & "Title") = _Title
        DataRow(_Pre & "Email") = _Email
        DataRow(_Pre & "Contact_person") = _contactPerson
        DataRow(_Pre & "Telephone") = _Telephone
        DataRow(_Pre & "Mobile") = _Mobile
        DataRow(_Pre & "Address") = _Address
        DataRow(_Pre & "last_balance") = _LastBalance
    End Sub
#End Region
End Class

Public Class Bookings
    Inherits DBLayer

#Region "Constants"
    Public Shared _Pre As String = "bk01"
    Public Shared _Table As String = "bookings"
#End Region

#Region " Standard Methods "
    Public Shared Function GetData() As List(Of Booking)
        Dim QueryString As String = String.Format("SELECT * FROM {0}{1} ORDER BY {0}UIN;", _Pre, _Table)
        Dim Dataset As DataSet = New DataSet()
        Dim DataList As List(Of Booking) = New List(Of Booking)

        Try
            Dataset = Query(QueryString)
            For Each DataRow As DataRow In Dataset.Tables(0).Rows
                DataList.Add(New Booking(DataRow))
            Next
        Catch ex As Exception
            DataList = Nothing
            SystemErrors.Create(New SystemError(ex.Message, ex.StackTrace))
        End Try
        Return DataList
    End Function

    Public Shared Function GetData(ByVal uin As String) As Booking
        Dim QueryString As String = String.Format("SELECT * FROM {0}{1} WHERE {0}uin = {2};", _Pre, _Table, uin)
        Dim Dataset As DataSet = New DataSet()
        Dim Data As Booking = New Booking()

        Try
            Dataset = Query(QueryString)
            If Dataset.Tables(0).Rows.Count = 1 Then
                Data = New Booking(Dataset.Tables(0).Rows(0))
            Else
                Data = Nothing
            End If
        Catch ex As Exception
            Data = Nothing
            SystemErrors.Create(New SystemError(ex.Message, ex.StackTrace))
        End Try
        Return Data
    End Function

    Public Shared Function Create(ByVal Data As Booking) As Boolean
        Dim QueryString As String = String.Format("SELECT * FROM {0}{1} WHERE {0}uin = Null;", _Pre, _Table)
        Dim Dataset As DataSet = New DataSet()
        Dim Datarow As DataRow
        Dim Result As Boolean = False

        Try
            Dataset = Query(QueryString)
            If Dataset.Tables(0).Rows.Count = 0 Then
                Datarow = Dataset.Tables(0).NewRow()
                Data.Get(Datarow)
                Dataset.Tables(0).Rows.Add(Datarow)
                Result = UpdateDB(QueryString, Dataset)
            Else
                Result = False
            End If
        Catch ex As Exception
            Result = False
            SystemErrors.Create(New SystemError(ex.Message, ex.StackTrace))
        End Try
        Return Result
    End Function

    Public Shared Function Update(ByVal Data As Booking) As Boolean
        Dim QueryString As String = String.Format("SELECT * FROM {0}{1} WHERE {0}uin = {2};", _Pre, _Table, Data.UIN)
        Dim Dataset As DataSet = New DataSet()
        Dim Result As Boolean = False
        Dim DataRow As DataRow = Nothing

        Try
            Dataset = Query(QueryString)
            If Dataset.Tables(0).Rows.Count = 1 Then
                DataRow = Dataset.Tables(0).Rows(0)
                Data.Get(DataRow)
                Result = UpdateDB(QueryString, Dataset)
            Else
                Result = False
            End If
        Catch ex As Exception
            Result = False
            SystemErrors.Create(New SystemError(ex.Message, ex.StackTrace))
        End Try
        Return Result
    End Function

    Public Shared Function UpdateBulk(ByRef DataList As List(Of Booking)) As Boolean
        Dim Result As Boolean = False

        Try
            For Each Data As Booking In DataList
                Update(Data)
            Next
            Result = True
        Catch ex As Exception
            SystemErrors.Create(New SystemError(ex.Message, ex.StackTrace))
        End Try

        Return Result
    End Function

    Public Shared Function FillGrid() As List(Of Booking)
        Return GetData()
    End Function
#End Region
End Class

Answer 1

你说的是所谓的对象-关系映射。

你可以这样做，但这将是相当大的努力。幸运的是，很多人都遇到过同样的问题，回答了这个问题，并且开源了这个解决方案。我建议考虑使用这些解决方案之一。

nHibernate只是一个例子，但它是一个流行和成熟的解决方案。

编辑:更准确地说，对象-关系映射是将字段映射到列，将对象映射到表，将对象关系映射到表关系，因此它完全满足您的需要，而且(可选)更多。

Answer 2

为了给PDR的答案添加一点，因为您已经提到了VB.NET，如果您使用的是.NET 3.5或更高版本，您可以使用实体框架生成这些类的所有基本类和支持的CRUD方法。