Jim's Blog Ramblings about novels, comics, programming, and other geek topics

How to use custom audit fields with SubSonic

SubSonic uses a few audit fields by default - you don't need to write any code. However, the field names that are used are coded into the source code. You can modify the source code and generate a new assembly, but if you're like me and would rather not edit the source code (so you don't need to update the code at every release), then you'll be looking for an alternative approach.

SubSonic default audit fields

First, by default SubSonic uses the following database field names:

• CreatedBy
• CreatedOn
• ModifiedBy
• ModifiedOn
• Deleted
• IsDeleted
• IsActive

I'm only going to focus on CreatedBy, CreatedOn, ModifiedBy, and ModifiedOn in this example, but you can insert the others into the code if you need.

What happens when you have differing field names?

The problem arises when you have database field names that differ from the above field names. In my database, I've very close but we use a different naming standard that uses underscores to make the field names more readable without relying on camel casing the field names.

Here's how my field names match up with SubSonic's defaults:

My field name	SubSonic default field name
Created_By	CreatedBy
Created_On	CreatedOn
Modified_By	ModifiedBy
Modified_On	ModifiedOn

 

The Three Options

1. Modify SubSonic source code

2. Use partial classes for each table

3. Create a custom template and base class

The advantages and disadvantages to each option is shown below. I'm sure there's a few more advantages and disadvantages, so if you think of one, let me know in the comments section.

 

Option 1: Modify SubSonic source code

You can modify the SubSonic source code to use your database field names.

Advantages	Disadvantages
Less coding	Requires maintaining project's source code Must maintain multiple copies of source code for each differing set of field names per application/database

Option 2: Use Partial classes for each table

Advantages	Disadvantages
Easy to code	Duplicate code for each table/class Must add partial classes and code for every table/class

Option 3: Create a custom template and base class

Advantages	Disadvantages
No code duplication Automatically configured for any new tables/classes	Must create a code generation template project

 

Option 1: Modifying the SubSonic source code

The first solution is the modified SubSonic's source code as stated by yitzchok:

Get the source code of SubSonic and modify SubSonicConstants.cs to the names you want

    public class ReservedColumnName
    {
        public const string CREATED_BY = "CreatedBy";
        public const string CREATED_ON = "CreatedOn";
        public const string DELETED = "Deleted";
        public const string IS_ACTIVE = "IsActive";
        public const string IS_DELETED = "IsDeleted";
        public const string MODIFIED_BY = "ModifiedBy";
        public const string MODIFIED_ON = "ModifiedOn";
    }

In this code, you would just insert your field names as the string literals. Then you would rebuild the project and use the new SubSonic modified assemblies.

If you choose to go this route, then you don't need to follow any further. I choose to go a different route, because I didn't want to modify and maintain the source (when updates are available) and have to worry about merging any of my changes back into the source.

 

Option 2: Using partial classes

Step 1: Creating your partial classes

Add a new partial class to your source code. For example, if your table was called "Product" and you had generated a SubSonic class called "Product", then your partial class would look something like:

namespace MyProject.DAL
{
    public partial class Product
    {

    }

}

The namespace and class should match exactly what is generated by SubSonic. You are just adding another source code file to your DAL project. I usually structure my SubSonic DAL project as such:

/MyProject/DAL

/MyProject/DAL/Custom

/MyProject/DAL/Generated

The automatically generated class files go into "DAL/Generated" and my partial classes go into "DAL/Custom".

Step 2: Overriding BeforeInsert and BeforeUpdate

To automatically set the created_on, created_by, modified_on, and modified_by values. I need to override the BeforeInsert and BeforeUpdate methods. You can paste the below code within the Product class above. You may need to modify parts of the code to fit your database design.

Here, I check to see if this table has a database field named "Created_On" and if it does, then I set the database column to DateTime.Now. Then I just repeat that same logic for the other fields.

protected override void BeforeInsert()
{
   string userName = string.Empty;
   if (HttpContext.Current != null)
      userName = HttpContext.Current.User.Identity.Name;
   if (this.GetSchema().Columns.Contains("Created_On"))
      this.SetColumnValue("Created_On", DateTime.Now);
   if (this.GetSchema().Columns.Contains("Created_By"))
      this.SetColumnValue("Created_By", userName);
   base.BeforeInsert();
}

protected override void BeforeUpdate()
{
   string userName = string.Empty;
   if (HttpContext.Current != null)
      userName = HttpContext.Current.User.Identity.Name;
   if (this.GetSchema().Columns.Contains("Modified_On"))
      this.SetColumnValue("Modified_On", DateTime.Now);
   if (this.GetSchema().Columns.Contains("Modified_By"))
      this.SetColumnValue("Modified_By", userName);
   base.BeforeUpdate();
}

You can also replace the string literals used above ("Created_By", etc.) with constants.

Step 3: The end

You'll need to create partial classes and override the two methods in each of your tables that you want to automatically maintain your audit fields.

Finally, build and test your code.

 

Option 3: Creating code templates

Step 1

First, make a copy of the templates provided with the SubSonic installation. We are going to modify those templates to inherit from a custom class that we'll create.

The templates are located in your installation folder and consists of several ASPX files. The SubSonic web site shows you how to create a template project and get the build process to work. So you should make sure that you have a project with the ASPX files and you are able to do a successful build before continuing.

• Webcast: Creating  Your Own Code Gen Templates by Rob Connery
• Code Generation: How To Customize Our Templates

 

Step 2: Modifying the template

Open the CS_ClassTemplate.aspx file.

Scroll down about 25 lines and insert the following bold code (middle line with comment) between the shown lines:

  string baseClass = tbl.Provider.TableBaseClass;
  string baseClassCustomized = baseClass; // this is new!
  string collectionBaseClass = String.Format("List<{0}>", className);

Here we are adding a new variable named "baseClassCustomized" to store the name of our new base class that we'll implement later.

Next, scroll down to about line 40 and insert the following bold code (last line with comment) between the shown lines:

{
 baseSuffix = "IActiveRecord";
 collectionBaseClass = String.Format("ActiveList<{0}, {0}Collection>", className);
 baseClassCustomized =  String.Format(
     "MyProject.DAL.ActiveRecordCustomized<{0}>, {1}", 
     className, 
     baseSuffix); // this is new! 
}

I'm just updating the base class for ActiveRecord. You will need to use the full class name with namespace to the class that we haven't created yet.  In this example, our newly created class name will be "MyProject.DAL.ActiveRecordCustomized." You should put your namespace and class name here.

Finally, scroll down to around line 117 and update the code to generate the class information as shown below. We are changing baseClass to use baseCusomizedClass.

  public partial class <%=className%> : <%=baseCustomizedClass%>

You'll need to update your web.config and/or app.config as shown by Rob in the webcast and SubSonic documentation linked above in Option 2: Step 1. Also, make sure you can build the template project as shown in the webcast.

 

Step 3: Adding our new customized base class

Create a new class within the namespace used for the previous step. You must make sure this class and namespace match what we've used in the previous step or you will get build errors.

namespace MyProject.DAL
{
    public class ActiveRecordCustomized<T> : ActiveRecord<T>
        where T : ReadOnlyRecord<T>, new()
    {

    }

}

Step 4: Override BeforeInsert and BeforeUpdate

Finally, you just need to override the BeforeInsert and BeforeUpdate methods to add in your audit field data.

See Option 2: Step 2 for source code.

 

Step 5: The end

Finally, build and test your code.