Introduction
Having been programming in some other multi-value based database programming languages for some years, I have always found it very handy to have the ability to define a dictionary of fields in a database that are not just stored fields, but have the ability to use calculated fields with constants and variables based on the stored fields in the rows...
With a strong client base in the .NET Framework, this led me to develop an inherited class that could provide me with some of the features that I am used to using in the other multi-value languages.
The Theory
My current projects involve managing data in a database system (in my case, I commonly use MySQL). My database structure is usually based around a Data Layer that communicates with the database, a Table Collection class that stores the rows, and a Row class that stores the values for the rows.
I have created a simple example set of classes for the demonstration below consisting of a CustomerCollection
class which will load the data and store an ICollection
of CustomerItem
classes for use (the collection class would normally contain all the search methods required, as well as the code to communicate with the DataLayer
class to update a row, delete a row, etc.).
NB: The loading of the data could also be done asynchronously by passing itself to another thread; but in this example, I will keep it simple.
Customer Item
Imports DataLayer
Public Class CustomerCollection
Private _DataLayer As DataLayer
Public Customer As ICollection(Of CustomerItem)
Public Sub New()
_DataLayer = New DataLayer
End Sub
Public Sub LoadCustomers(Optional ByVal sQuery As String = "")
Dim dtCustomers As DataTable = _DataLayer.ExecuteSQL("SELECT * FROM customers;")
End Sub
End Class
Customer Collection
Public Class CustomerItem
Public ID As Integer
Public Name As String
Public Address As String
Public Phone As String
Public Country as String
Public Email As String
Public Website As String
Public WeeklyRepaymentAmount As Decimal
Public RepaymentFrequency As enumRepaymentFrequecy
Public Enum enumRepaymentFrequecy
repayWeekly = 1
repayFortnightly = 2
repayMonthly = 3
repayQuarterly = 4
repayAnnually = 5
End Enum
End Class
How It Would Normally Work
For the point of this exercise, we want to know how much the customers need to repay on their account. In the CustomerItem
class, this value is stored in the database variable WeeklyRepaymentAmount
.
The customer might not necessarily want to pay back the account on a weekly basis, however; sometimes fortnightly, some monthly, and others annually all in one lump sum. An additional discount if they do pay it back in one lump sum of 10% applies to their payment.
This is all well and good, but I wanted to be able to keep the business rules in the business layer of the program. Several of my clients use a business layer that is located in an external DLL file and consumed by a WebService or other programs as well as the main application.
Another handy feature is the ease of maintenance as the calculated columns will be able to be stored as a runtime changeable list stored in a table for each class. More on that later though...
So in this example, we want to calculate the customer's repayment amount; here's a code snippet to do that...
Sub Main()
Dim Customer As CustomerItem
Customer = New CustomerItem
Customer.Name = "Bob The Builder"
Customer.Address = "1 Bobtown Way"
Customer.Country = "AU"
Customer.Phone = "(55) 123-4567"
Customer.WeeklyRepaymentAmount = 515.25
Customer.RepaymentFrequency = _
CustomerItem.enumRepaymentFrequecy.repayMonthly
Dim dAmount As Decimal
Select Case Customer.RepaymentFrequency
Case CustomerItem.enumRepaymentFrequecy.repayWeekly
dAmount = Customer.WeeklyRepaymentAmount
Case CustomerItem.enumRepaymentFrequecy.repayFortnightly
dAmount = Customer.WeeklyRepaymentAmount * 2
Case CustomerItem.enumRepaymentFrequecy.repayMonthly
dAmount = Customer.WeeklyRepaymentAmount * 52 / 12
Case CustomerItem.enumRepaymentFrequecy.repayQuarterly
dAmount = Customer.WeeklyRepaymentAmount * 52 / 4
Case CustomerItem.enumRepaymentFrequecy.repayAnnually
dAmount = Customer.WeeklyRepaymentAmount * 52 * 0.9
End Select
Console.WriteLine(Customer.Name & "'s Repayment is $" & FormatNumber(dAmount, 2))
Console.ReadKey()
End Sub
"Now why can't you just put that as a function or property in the CustomerItem
class" I hear you say...
Well, you could, and here's the example of that...
Public Function RepaymentAmount() As Decimal
Dim dAmount As Decimal
Select Case Me.RepaymentFrequency
Case enumRepaymentFrequecy.repayWeekly
dAmount = Me.WeeklyRepaymentAmount
Case enumRepaymentFrequecy.repayFortnightly
dAmount = Me.WeeklyRepaymentAmount * 2
Case enumRepaymentFrequecy.repayMonthly
dAmount = Me.WeeklyRepaymentAmount * 52 / 12
Case enumRepaymentFrequecy.repayQuarterly
dAmount = Me.WeeklyRepaymentAmount * 52 / 4
Case enumRepaymentFrequecy.repayAnnually
dAmount = Me.WeeklyRepaymentAmount * 52 * 0.9
Case Else
dAmount = 0
End Select
Return dAmount
End Function
and the modified program to get the result...
Console.WriteLine(Customer.Name & "'s Repayment is $" & _
FormatNumber(Customer.RepaymentAmount, 2))
Both yielding the same result of:
Bob The Builder's Repayment is $2,232.75
So What's This New Idea of Yours?
Well, I thought you would never ask...
I call it ICalculated
, an inherited class to allow easier access to the class variables and also allows the use of calculated columns to enrich the data. It utilises System.Reflector
to access the structure of the class, and a sample from CodeProject called Eval3
to parse the formulas.
Link to Eval3 project: http://www.codeproject.com/KB/recipes/eval3.aspx.
The theory is pretty simple; there were a few things I wanted to be able to achieve with the CustomerItem
class:
- To be able to programmatically access the fields stored in the
CustomerItem
class as if it was a collection of values - To be able to create custom functions at runtime or in the class that could also be accessed as if it was just another field in the class
Here's How it Works
First, we get the CustomerItem
class to inherit ICalculated
.
Public Class CustomerItem
Inherits ICalculated
...
That's it. Now we can use all the extra features, so I'll just throw together a sample of how it works in the code and then go through it...
Customer.DICT_AddField("FortnightlyRepaymentAmount", "WeeklyRepaymentAmount * 2")
Customer.DICT_AddField("MonthlyRepaymentAmount", "WeeklyRepaymentAmount * 52 / 12")
Customer.DICT_AddField("QuarterlyRepaymentAmount", "WeeklyRepaymentAmount * 52 / 4")
Customer.DICT_AddField("AnnualRepaymentAmount", "WeeklyRepaymentAmount * 52 * 0.9")
Console.WriteLine(Customer.Name & "'s Repayment Options:")
Console.WriteLine(" - Weekly: " & Customer("WeeklyRepaymentAmount"))
Console.WriteLine(" - Fortnightly: " & Customer("FortnightlyRepaymentAmount"))
Console.WriteLine(" - Monthly: " & Customer("MonthlyRepaymentAmount"))
Console.WriteLine(" - Quarterly: " & Customer("QuarterlyRepaymentAmount"))
Console.WriteLine(" - Annual: " & Customer("AnnualRepaymentAmount"))
Now this returns the following output:
Bob The Builder's Repayment Options:
- Weekly: 515.25
- Fortnightly: 1030.5
- Monthly: 2232.75
- Quarterly: 6698.25
- Annual: 24113.7
Notice that we could access the fields of the CustomerItem
class using Customer("FieldName")
, but the big factor is that it also includes the calculated columns in that list. The first amount that is printed to the screen is the one from the variable WeeklyRepaymentAmount
; the rest are calculated fields.
The ICalculated
class also contains a few variables that could come in handy, such as DICT_FieldList
that contains a list of all the public fields in the class stored with the value
of their data type.
There is also a variable called ClassName
that returns the name of the calling class; in this example, it would result in returning "CustomerItem
".
An advantage can be instantly seen with the features of the ICalculated
class. For example:
- Defining a "field prefix" variable or some sort of pairing system that the
CustomerItem
class in the program could use to match the database fields with the fields in the class would mean that a simple loop could import the fields with no need for hard-coding of the field names.
Dim dTable As DataTable
For Each dRow As DataRow In dTable.Rows
For Each dCol As DataColumn In dTable.Columns
Customer(dCol.ColumnName) = dr(Customer.TablePrefix & dCol.ColumnName)
Next
Next
The Code
Imports Eval3
Imports System.Reflection
Public MustInherit Class ICalculated
Public DICT_CalculatedList As Dictionary(Of String, String)
Public DICT_FieldList As Dictionary(Of String, String)
Public ClassName As String
Private DictEval As Evaluator
Default Public Property DICT_Fields(ByVal sFieldName As String) As String
Get
Try
Return CallByName(Me, sFieldName, CallType.Get)
Catch ex As Exception
If DICT_CalculatedList.ContainsKey(sFieldName.ToLower) Then
Return DictEval.Parse(DICT_CalculatedList(sFieldName.ToLower)).value
Else
Throw New Exception("Field '" & _
sFieldName.ToLower & "' does not exist in dictionary.")
End If
End Try
End Get
Set(ByVal value As String)
Try
CallByName(Me, sFieldName, CallType.Let, value)
Catch ex As Exception
If Not DICT_FieldList.ContainsKey(sFieldName.ToLower) Then
If DICT_CalculatedList.ContainsKey(sFieldName.ToLower) Then
Throw New Exception("Field '" & sFieldName.ToLower & "
' is a calculated field and cannot be assigned a value.")
Else
Throw New Exception("Field '" & sFieldName.ToLower & "
' does not exist in dictionary.")
End If
End If
Throw New Exception("Dictionary Parse Error: " & ex.Message)
End Try
End Set
End Property
Public Function DICT_TestEval(ByVal sFunc As String) As String
Try
Return DictEval.Parse(sFunc).value
Catch ex As Exception
Throw New Exception("Dictionary Parse Error: " & ex.Message)
End Try
End Function
Public Sub DICT_AddField(ByVal sName As String, ByVal sCalc As String)
If Not DICT_CalculatedList.ContainsKey(sName.ToLower) Then
If Not DICT_FieldList.ContainsKey(sName.ToLower) Then
DICT_CalculatedList.Add(sName.ToLower, sCalc)
Else
Throw New Exception("Cannot add calculated field " & _
"with name of existing field '" & sName.ToLower & "'")
End If
Else
Throw New Exception("Cannot add calculated field '" & _
sName.ToLower & "', already exists in dictionary.")
End If
End Sub
Public Sub DICT_RemoveField(ByVal sName As String)
DICT_CalculatedList.Remove(sName)
End Sub
Public Sub New()
DICT_CalculatedList = New Dictionary(Of String, String)
ClassName = Me.GetType.Name
DICT_LoadFieldList(Me.GetType)
DictEval = New Evaluator(eParserSyntax.Vb, False)
DictEval.AddEnvironmentFunctions(Me)
End Sub
Private Sub DICT_LoadFieldList(ByVal t As Type)
DICT_FieldList = New Dictionary(Of String, String)
Dim m As MemberInfo
For Each m In t.GetMembers
If m.MemberType = MemberTypes.Field And m.DeclaringType Is t Then
DICT_FieldList.Add(m.Name, Field(m.ToString.ToLower, " ", 1))
End If
Next
End Sub
Shared Function Field(ByVal sString As String, _
ByVal sDelim As String, ByVal iField As Integer)
If InStr(sString, sDelim) = -1 Then Return sString
Dim aString() As String = Split(sString, sDelim)
Dim lCount As Long = aString.Length
If iField <= lCount Then
Return Trim(aString(iField - 1))
End If
Return ""
End Function
Protected Overrides Sub Finalize()
DictEval = Nothing
MyBase.Finalize()
End Sub
End Class
Finally
I hope you enjoy my first article on CodeProject. Having gained so much knowledge over the years from this site, I thought it time to give back some to the community...
Been programming for 20+ years, from the early days of GWBasic all the way to current .NET, OpenInsight, Advanced Revelations and Pick based programming also. Started out as a hobby but now run my own Software and Consulting company. Specialties include VB, C#, JavaScript, JQuery, AJAX, Node.js, PHP, Python, Knockout.JS, Raspberry Pi, iOS and Android, Industrial Automation, HTML, CSS, Magento, and whatever else I need to learn to complete a project really...