Product Info

Generated Apps

Technical Materials

Set Audit Fields for All Tables

Write custom code once by setting audit fields for all tables in your application.
- Jing Ding, Senior Systems Consultant of The Ohio State University Medical Center

October 31, 2008
Iron Speed Designer V5.X

Introduction

Iron Speed Designer provides two Code Customizaton Wizard examples to set a table audit fields while inserting or updating records. The examples add two event handlers (InsertingRecord and UpdatingRecord), in the business layer record class. These event handlers are similar to database triggers.

This is ideal for applications in which only a small number of tables have audit fields. However, for big projects with hundreds of audited tables, repeating the same code again and again is not an elegant solution. In this article I will demonstrate how to write custom code once by setting audit fields for all tables in your Iron Speed Designer generated application.

Solution

Since business layer record classes are derived from BaseClasses.Data.BaseRecord, we will apply our auditing code here. First, let’s address several issues:

Since the audit columns are defined as properties in record classes how will the BaseRecord classes know and modify its subclass properties.

How will the layer record classes handle non-standard tables without auditing fields?

Since the customization is applied in BaseClasses, it will effect all future projects. Can the customization be configured to be turned off?

How can will we handle column name changes from project to project (for example, "ChangedOn" -> "LastModifyDate")?

The implementation described below leverages the built-in flexibility in BaseRecord’s to address the concerns outlined above.

Implementation

Open the BaseClasses200x.vbproj (located in C:\Program Files\Iron Speed\Designer v5.x.x\BaseClasses\) in Visual Studio. Open the BaseRecord.vb under ~\Data\. Insert the following code in OnInsertingRecord() and OnUpdatingRecord() methods. Save and build the project. Copy the BaseClasses.dll to your existing projects’ BIN folder.

Visual Basic .NET:

Protected Overridable Sub OnInsertingRecord(ByVal e As System.ComponentModel.CancelEventArgs)
  Dim dtColName As String = ConfigurationManager.AppSettings("InsertAuditDateColumn")
  If Not String.IsNullOrEmpty(dtColName) Then
    Dim dtCol As BaseColumn = TableAccess.TableDefinition.ColumnList.GetByInternalName
    (dtColName)
    If Not IsNothing(dtCol) Then
        Parse(DateTime.Now, dtCol)
    End If
  End If

  Dim userColName As String = ConfigurationManager.AppSettings("InsertAuditUserColumn")
  If Not String.IsNullOrEmpty(userColName) Then
    Dim userCol As BaseColumn = TableAccess.TableDefinition.ColumnList.GetByInternalName
    (userColName)
    If Not IsNothing(userCol) Then
        Parse(DataAccessSettings.Current.SignedInUserId, userCol)
    End If
  End If

  RaiseEvent InsertingRecord(Me, e)
End Sub
Protected Overridable Sub OnUpdatingRecord(ByVal e As System.ComponentModel.CancelEventArgs)
  Dim dtColName As String = ConfigurationManager.AppSettings("UpdateAuditDateColumn")
  If Not String.IsNullOrEmpty(dtColName) Then
    Dim dtCol As BaseColumn = TableAccess.TableDefinition.ColumnList.GetByInternalName
    (dtColName)
    If Not IsNothing(dtCol) Then
        Parse(DateTime.Now, dtCol)
    End If
  End If

  Dim userColName As String = ConfigurationManager.AppSettings("UpdateAuditUserColumn")
  If No String.IsNullOrEmpty(userColName) Then
    Dim userCol As BaseColumn = TableAccess.TableDefinition.ColumnList.GetByInternalName
    (userColName)
    If Not IsNothing(userCol) Then
        Parse(DataAccessSettings.Current.SignedInUserId, userCol)
    End If
  End If

  RaiseEvent UpdatingRecord(Me, e)
End Sub

Next, insert the following key-value pairs in your project’s web.config.

Please note the code uses BaseRecord.TableAccess.TableDefinition to find the auditing fields, and BaseRecord.Parse() method to modify them. This addresses Issue No. 1. For Issue No. 2, the code checks whether an auditing field exists before modifying it. Since the auditing column names are defined in web.config, they are easy to configure from project to project. If the columns are not defined in web.config, the feature is effectively turned off. This addresses the concerns of Issue No. 3.

Conclusion

Iron Speed Designer’s Code Customization Wizard provides a way to customize your code without repetition.

About the Author

Jing Ding has a PhD in Computer Engineering, Bioinformatics and Computational Biology, and an M.S. in Toxicology from Iowa State University. He received his B.S. in biophysics from Fundan University in Shanghai, China. He is a self-taught programmer who "played" with assembly, C and C++ in the 1990s. He took a break from programming from 1997 to 2000. When he picked it up again in 2001, he worked with Java. Jing began working with C# and .NET in 2006.



	Copyright © 2005-2009, Iron Speed, Inc. Iron Speed® is a registered trademark. All rights reserved.	Privacy Statement