6
\$\begingroup\$

I am been using a code pattern for recursive database actions in my applications.

I create two class objects of a database table, singular one (e.g Agent) for holding single record with all fields definition, plural one (e.g Agents) for database actions of that records like, select, insert, delete, update etc. I find it easy using the code pattern.

But as the time runs I find it somewhat laborious to define same database action functions in different classes only differing in datatype.

How can I make it good and avoid defining it again and again?

Sample code of a class file representing the class definition:

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
\$\endgroup\$
  • 1
    \$\begingroup\$ Just for clean up: 1. to see how to do generics in VB.Net ( see msdn.microsoft.com/en-us/library/w256ka79(v=vs.100).aspx ) and 2. if you would KoolKabin, if you believe pdr's answer about ORM is the solution to your question, please click accept answer. \$\endgroup\$ – Randy Apr 2 '12 at 13:58
4
\$\begingroup\$

What you're talking about is called object-relational mapping.

You could do this, but it will be a fair amount of effort. Luckily many people have run into this same question before, answered it and open-sourced that solution. I suggest looking at using one of those solutions.

nHibernate is just one example but is a popular and mature solution.

Edit: More accurately, object-relational mapping is mapping fields to columns, objects to tables and object relationships to table relationships, so it does exactly what you want and (optionally) much more.

\$\endgroup\$
3
\$\begingroup\$

To add a little to PDR's answer, since you have mentioned VB.NET, if you're using .NET 3.5 or higher you can use the Entity Framework to generate all of the basic classes and the supporting CRUD methods of those classes.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.