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

• Download sample - 309 KB
• nHydrate ORM home page

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:

• NHydrate Step-by-Step
• Entity Framework with nHydrate
• Why implement an Entity Framework based DAL in nHydrate?

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.

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.

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

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

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.



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