Click here to Skip to main content
14,304,886 members

The Story of ICalculated... the Calculated Column for a Class and More

Rate this:
4.88 (8 votes)
Please Sign up or sign in to vote.
4.88 (8 votes)
7 Sep 2010CPOL
Custom calculated columns for classes as well as programmatic access, and list of all public variables read/write.


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 = "")
        'Talk to the database layer here to load the customer information

        Dim dtCustomers As DataTable = _DataLayer.ExecuteSQL("SELECT * FROM customers;")

        'Process customers here...

    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()

    'Define a sample customer for the point of the excercise.

    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 = _

    'Code to calculate repayment amount:

    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))


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:

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)

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

                Return CallByName(Me, sFieldName, CallType.Get)
            Catch ex As Exception

                'Might not exist, try the calculated columns to see if it is there
                If DICT_CalculatedList.ContainsKey(sFieldName.ToLower) Then

                    Return DictEval.Parse(DICT_CalculatedList(sFieldName.ToLower)).value

                    Throw New Exception("Field '" & _
                      sFieldName.ToLower & "' does not exist in dictionary.")
                End If
            End Try
        End Get
        Set(ByVal value As String)
                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.")
                        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

            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)
                Throw New Exception("Cannot add calculated field " & _ 
                      "with name of existing field '" & sName.ToLower & "'")
            End If

            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)
    End Sub

    Public Sub New()
        DICT_CalculatedList = New Dictionary(Of String, String)

        ClassName = Me.GetType.Name


        DictEval = New Evaluator(eParserSyntax.Vb, False)
    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

    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
    End Sub
End Class


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...


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Aaron @ Think Software
Software Developer Think Software
Australia Australia
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...
Group type: Organisation (No members)

Comments and Discussions

QuestionNice article, thanks a lot Pin
Leif Monnike14-Nov-12 1:07
memberLeif Monnike14-Nov-12 1:07 
GeneralMy vote of 5 Pin
Erich Deckert7-Sep-10 22:59
memberErich Deckert7-Sep-10 22:59 
GeneralMy vote of 3 Pin
William Winner3-Sep-10 10:24
memberWilliam Winner3-Sep-10 10:24 
RantCan't really rate this article Pin
William Winner1-Sep-10 6:32
memberWilliam Winner1-Sep-10 6:32 
GeneralNothing to do with your article. Pin
Euhemerus26-Aug-10 4:35
memberEuhemerus26-Aug-10 4:35 
GeneralRe: Nothing to do with your article. Pin
Aaron @ Think Software26-Aug-10 14:24
professionalAaron @ Think Software26-Aug-10 14:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 24 Aug 2010


13 bookmarked