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
