Recursive database actions

Question

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

• Randy: 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.

Answer 1

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.

Answer 2

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.

• KoolKabin: its greatful when we have some of your favourite site link for information on Entity Framework
• sansknwoledge: nuget.org/packages/EntityFramework/5.0.0