Auditing with Castle ActiveRecord

Introduction

This article describes how to generate automatic audit information when using Castle ActiveRecord.

Background

First you need to create this table (or any you want):

CREATE TABLE auditoria
(
  id_auditoria bigint NOT NULL,
  fecha_hora timestamp without time zone NOT NULL,
  id_usuario integer NOT NULL,
  ip character varying(50) NOT NULL,
  tabla character varying(300),
  campo_pk character varying(300),
  id bigint,
  operacion character(1),
  observaciones character varying(500),
  CONSTRAINT auditoria_pkey1 PRIMARY KEY (id_auditoria ),
  CONSTRAINT auditoria_id_usuario_fkey1 FOREIGN KEY (id_usuario)
  REFERENCES usuarios (id_usuario) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)

In this table we will save all audit information.

Using the code

You need to extend ActiveRecordBase and override the following methods:

public class NFActiveRecord<T> : ActiveRecordBase<T>
{
    public override void Save()
    {
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        bool esAlta = id == null;

        base.Save();

        Auditoria auditoria = new Auditoria();

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = esAlta? "A" : "M";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void SaveAndFlush()
    {
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        bool esAlta = id == null;

        base.SaveAndFlush();

        Auditoria auditoria = new Auditoria();

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = esAlta ? "A" : "M";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void Update()
    {
        base.Update();

        Auditoria auditoria = new Auditoria();
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = "M";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void UpdateAndFlush()
    {
        base.UpdateAndFlush();

        Auditoria auditoria = new Auditoria();
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = "M";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void Create()
    {
        base.Create();

        Auditoria auditoria = new Auditoria();
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = "A";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void CreateAndFlush()
    {
        base.CreateAndFlush();

        Auditoria auditoria = new Auditoria();
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = "A";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void Delete()
    {
        base.Delete();

        Auditoria auditoria = new Auditoria();
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = "B";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    public override void DeleteAndFlush()
    {
        base.DeleteAndFlush();

        Auditoria auditoria = new Auditoria();
        int? id = null;
        string campoPK = "";

        GetID(this.GetType(), ref id, ref campoPK);
        auditoria.CampoPK = campoPK;
        auditoria.FechaHora = DateTime.Now;
        auditoria.Id = id;
        auditoria.IdUsuario = Seguridad.Usuario.IdUsuario;
        auditoria.IP = Seguridad.IPUsuario;
        auditoria.Operacion = "B";
        auditoria.Tabla = GetTable(this.GetType());
        auditoria.Save();
    }

    private string GetTable(Type type)
    {
        object[] tables = type.GetCustomAttributes(true);

        if (tables.Length == 1)
            return (tables[0] as ActiveRecordAttribute).Table;
        else
            return null;
    }

    private void GetID(Type type, ref int? id, ref string campo)
    {
        PropertyInfo[] properties = type.GetProperties();
        object[] keys;

        id = null;
        campo = "";
        foreach (PropertyInfo p in properties)
        {
            keys = p.GetCustomAttributes(typeof(PrimaryKeyAttribute), true);

            if (keys != null && keys.Length > 0)
            {
                campo = (keys[0] as PrimaryKeyAttribute).Column;
                if (p.GetValue(this, null) != null)
                    id = (int)p.GetValue(this, null);

                break;
            }
        }
    }
}

You also need this class:

[ActiveRecord("auditoria", Lazy = true)]
public class Auditoria : ActiveRecordBase<Auditoria>
{
    [PrimaryKey("id_auditoria", SequenceName = "auditoria_id_auditoria_seq")]
    public virtual long? IdAuditoria { get; set; }

    [Property("fecha_hora")]
    public virtual DateTime? FechaHora { get; set; }

    [Property("id_usuario")]
    public virtual int? IdUsuario { get; set; }

    [Property("ip")]
    public virtual string IP { get; set; }

    [Property("tabla")]
    public virtual string Tabla { get; set; }

    [Property("campo_pk")]
    public virtual string CampoPK { get; set; }

    [Property("id")]
    public virtual long? Id { get; set; }

    [Property("operacion")]
    public virtual string Operacion { get; set; }

    [Property("observaciones")]
    public virtual string Observaciones { get; set; }
}

And when you create an ActiveRecord class (except the Auditory one), you need to use the extended class NFActiveRecord (or the name you choose).

[ActiveRecord("turnos", Lazy = true)]
public class Turno : NFActiveRecord<Turno>
{
    [PrimaryKey("id_turno", SequenceName = "turnos_id_turno_seq")]
    public virtual int? IdTurno { get; set; }

    [BelongsTo("id_usuario")]
    public virtual Usuario Usuario { get; set; }

    [BelongsTo("id_paciente")]
    public virtual Paciente Paciente { get; set; }

    [BelongsTo("id_lugar_atencion")]
    public virtual LugarAtencion LugarAtencion { get; set; }

    [Property("hora_comienzo")]
    public virtual DateTime? HoraComienzo { get; set; }

    [Property("hora_fin")]
    public virtual DateTime? HoraFin { get; set; }

    [Property("comentario", ColumnType = "StringClob")]
    public virtual string Comentario { get; set; }
}

With this workaround, we will able to generate audit information when any class performs a Create, Update, Save, or Delete.