Click here to Skip to main content
Click here to Skip to main content

Features of the nHydrate DAL and Entity Framework generators: Part 1 - Auditing

, , 22 Jul 2010 Ms-PL
Rate this:
Please Sign up or sign in to vote.
This article details how to use nHydrate to add an auditing framework to the nHydrate DAL and Entity Framework DAL.

Overview

The aim of this article will be to explain the auditing features available in nHydrate. Providing the ability to audit database changes is a useful feature for many systems. As such, nHydrate has provided an implementation that makes implementing an auditing solution pain free. The examples provided in the article will work on both the traditional nHydrate Data Access Layer (NHDAL) as well as the nHydrate Entity Framework Data Access Layer (EFDAL).

As a precursor to this document, you may want to check out the following articles:

What's in the Model?

Let us start by taking a look at the properties in the model that deal with auditing.

Audit Field Names

The first is on the database node of the nHydrate model. Here, you can identify what names you want to apply to your audit fields. These fields will be added to entities that specify they wish to be audited. The following properties are provided: CreatedByColumnName, CreatedDateColumnName, ModifiedByColumnName, and ModifiedDateColumnName.

index.001.png

Table Level Audit Settings

For each table in the model, nHydrate provides the ability to turn on or off table level auditing. There are three settings to consider:

  • AllowCreateAudit - This is a true/false setting. When the value is true, the table will have two columns placed on it: created_by and created_date. These fields are set the first time a user inserts the record.
  • AllowModifyAudit - This is a true/false setting. When the value is true, the table will have two columns placed on it: modified_by and modified_date. These fields are reset each time a user updates the record
  • AllowAuditTracking - This is a true/false setting. When true, it identifies that a new database table will be created to hold historical audit information.

index.002.png

What's in the API?

Although the generated frameworks for nHydrate DAL and Entity Framework are slightly different, they both provide means for implementing the auditing features.

In the examples below, pay particular attention to the following:

  • First, we will identify how to set the identity of the user performing the changes. This will allow the framework to setup the modifiedby and createdby columns without requiring the developer to set it on every object.
  • There is also a convenience method added to every object that allows you to pull back a history of the modifications. In this way, it is very easy to manage rollbacks or present object histories from the API. The convenience method used in this example brings back all the audit records (not shown). This method has been overloaded to deal with large audit sets.
    • instance.GetAuditRecords() - All audit records for the instance.
    • instance.GetAuditRecords(int pageOffset, int recordsPerPage) - Paginated records for the instance.
    • instance.GetAuditRecords(int pageOffset, int recordsPerPage, DateTime? startDate, DateTime? endDate) - Paginated records for the instance between dates. recordsPerPage=0, pageOffset=0: returns all records between the dates.

nHydrateDAL - Example

// Add a couple of customer objects. You will notice that during
// the creation of the customer collection  we pass the modifier. 
// We are also not setting modified_by, modified_on, created_by or created_on
// fields. These are implemented by the framework.
CustomerCollection customerCollection = new CustomerCollection("User14");
 
//Create a simple customer
//When persisted create record will be added to the audit table 
Customer simpleCustomer = customerCollection.NewItem();
simpleCustomer.Name = "Simple Customer";
customerCollection.AddItem(simpleCustomer);
 
//Create another customer
//When persisted Create record will be added to the audit table
Customer customer = customerCollection.NewItem();
customer.Name = "Test Name 1";
customerCollection.AddItem(customer);
 
//Persist both customers they will both have the modifier or User14
customerCollection.Persist();
 
//Update the name. Updated record will be added to audit table
customer.Name = "Test Name 2";
customerCollection.Persist();
 
//Lets look at what the create a modify produced
//Retrieve customer from database that we just saved.
Customer auditedCustomer = 
   Customer.SelectUsingPK(customer.CustomerId, "User15");
 
//Write Audit Records. There will be two records. 
//The first record will represent the creation.
//The second record will represent the modification.
foreach (CustomerAudit customerAudit in auditedCustomer.GetAuditRecords())
{
  Console.WriteLine("AuditDate: " + customerAudit.AuditDate.ToString());
  Console.WriteLine("AuditType: " + customerAudit.AuditType.ToString());
  Console.WriteLine("CustomerId: " + customerAudit.CustomerId.ToString());
  Console.WriteLine("Name: " + customerAudit.Name);
  Console.WriteLine("ModifiedBy: " + customerAudit.ModifiedBy);
}

EFDAL - Example

Guid createdCustomerID = Guid.Empty;
 
// Add a couple of customer objects.
// You will notice that during the creation of the ObjectContext
// (AuditExampleEntities). We provide a context startup
// object that specifies the modifying user
// We are also not setting modified_by, modified_on,
// created_by or created_on fields. These are 
// implemented by the framework.
ContextStartup user14Startup = new ContextStartup("User14");
using (AuditExampleEntities context = new AuditExampleEntities(user14Startup))
{
  //Create a simple customer
  //When persisted create record will be added to the audit table 
  Customer simpleCustomer = new Customer();
  simpleCustomer.Name = "Simple Customer";
  context.AddItem(simpleCustomer);

  //Create another customer
  //When persisted Create record will be added to the audit table
  Customer customer = new Customer();
  customer.Name = "Test Name 1";
  context.AddItem(customer);

  //Persist both customers they will both have the modifier or User14
  context.SaveChanges();

  //Update the name. Updated record will be added to audit table
  customer.Name = "Test Name 2";
  context.SaveChanges();

  createdCustomerID = customer.CustomerId;
}
 
//Lets look at what the create a modify produced
using (AuditExampleEntities context = new AuditExampleEntities(user14Startup))
{
  //Retrieve customer from database that we just saved.
  Customer customer = context.Customer.
                Single(cust => cust.CustomerId == createdCustomerID);

  //Write Audit Records. There will be two records. 
  //The first record will represent the creation.
  //The second record will represent the modification.
  foreach (CustomerAudit customerAudit in customer.GetAuditRecords())
  {
    Console.WriteLine("AuditDate: " + customerAudit.AuditDate.ToString());
    Console.WriteLine("AuditType: " + customerAudit.AuditType.ToString());
    Console.WriteLine("CustomerId: " + customerAudit.CustomerId.ToString());
    Console.WriteLine("Name: " + customerAudit.Name);
    Console.WriteLine("ModifiedBy: " + customerAudit.ModifiedBy);
  }
}

What's in the database?

Within the database, additional columns are added to the tables when AllowCreateAudit or AllowModifyAudit are set to true. Taking the customer table as an example, we see the existence of CreatedBy, CreatedOn, ModifiedBy, and ModifiedOn.

The next thing that you will notice is, a new table has been created in the database schema. This table is where the audit records are kept. This is a result of specifying AllowAuditTracking to true on the customer table settings.

Database diagram for Audit model

index.003.png

Now we can look at the results from running our code. Within the database, we will notice that auditing fields have data for both of the customers we added. This occurred without the overhead of a developer expressly setting them on every object that is stored. We will also see that the audit records have been established in the __AUDIT__Customer database table.

Customer and __Audit__Customer results

index.004.png

Miscellaneous

  • The framework can set ModifiedDate, CreatedDate, and __insertdate as UTC or Local values. Depending on the UseUTCTime setting, all of these times are established on the database server.
  • index.005.png

  • Manually setting ModifiedDate or CreatedDate will override the values set by the framework.
  • The database stores an integer to identify the audit actions. 1 = Create, 2 = Update, 3 = Delete.
  • Fields that are of type text, ntext, or image are not available in the audit tables.

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Authors

Christopher R Davis
Software Developer (Senior) Hewlett Packard
United States United States
I currently work in security at HP and create developer tools. I have been working with technology since the VIC 20. I am currently co-authoring the nHydrate code generator (and other projects) with Michael Knight in Atlanta, GA. We have built a faceted navigation engine that allows you to list any set of objects (cars, jobs, apartments, etc) on a website that gives users the click-through experience. My goal is no more advanced search pages.
 
I use nHydrate at my day job every day and use it manage a a complex system for HP.
 
Check out the current nHydrate site on Codeplex. http://nhydrate.codeplex.com
 


My LinkedIn Profile

LinkedIn nHydrate Group
Follow on   Twitter

Micheal Knight

United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 3 PinprotectorMarc Clifton24-Jul-10 1:19 
GeneralRe: My vote of 3 PinmemberMichael S Knight24-Jul-10 5:52 
Marc thank you for spending the time to review this.
 
Let me start by saying this is a code project article and does not reflect the full extend of what is available. I was writting it as an introduction to a framework that exists. Let me take your points individually.
 
your use of a GUID for the primary key: within the project you can use a guid, database identity, or custom primary key. THis is not a restriction.
 
Creating an audit table for every table you want audited. The article clearly states that auditing can be turned on or off at the table level.
 
Reproducing every column in the auditing table - This is true. We have not implemented an audit setting at the individual column level. However that is not a difficult thing to add should people need it.
 
Audit changes made to a view instead of a table. In this case the changes will be audited. However, you are correct in the fact that the change package will take a little work to re-construct.
 
Reconstruct the original record when not only the FK changes: The foreign key is available. for you to identify the record. I agree that it would take a little more logic to fully reconstruct the tables that are related to it. However, the functionality is readily available to do so. Since you can get audits by records prior to a specific date. You just need to request from the parent table the record that exists with a date prior to the audit record you are considering. Single call to another table.
GeneralRe: My vote of 3 PinprotectorMarc Clifton24-Jul-10 7:56 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 22 Jul 2010
Article Copyright 2010 by Christopher R Davis, Micheal Knight
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid