Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

NLog Log and Audit Advanced Target

, 26 May 2010 CPOL
A way to audit your business objects using NLog.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Reflection;

namespace App.Logger.Targets.ObjectHistoryLogger.Data
{
	/// <summary>
	/// Base class to add support to different database engines.
	/// </summary>
	public abstract class OHDbBaseClient
	{
		public readonly OHTarget target = null;
		protected string tbl_ObjectLogs
		{
			get 
			{
				string objectsPrefix = string.IsNullOrEmpty(target.DBTablesPrefix) ? "" : target.DBTablesPrefix;
				return objectsPrefix + "ObjectLogs";
			}
		}
		protected string tbl_ObjectLogDetails
		{
			get
			{
				string objectsPrefix = string.IsNullOrEmpty(target.DBTablesPrefix) ? "" : target.DBTablesPrefix;
				return objectsPrefix + "ObjectLogDetails";
			}
		}
		protected string tbl_EventLogs
		{
			get 
			{
				string objectsPrefix = string.IsNullOrEmpty(target.DBTablesPrefix) ? "" : target.DBTablesPrefix;
				return objectsPrefix + "EventLogs";
			}
		}
		protected string tbl_Operations
		{
			get
			{
				string objectsPrefix = string.IsNullOrEmpty(target.DBTablesPrefix) ? "" : target.DBTablesPrefix;
				return objectsPrefix + "Operations";
			}
		}
		protected string tbl_Levels
		{
			get 
			{
				string objectsPrefix = string.IsNullOrEmpty(target.DBTablesPrefix) ? "" : target.DBTablesPrefix;
				return objectsPrefix + "Levels";
			}
		}

		public OHDbBaseClient(OHTarget target)
		{
			this.target = target;
		}

		public abstract string DbScriptsContainerPath { get; }
		public abstract bool DbSchemaUpdated { get; }
		public abstract void SaveObject(OHInfo objInfo, OHTarget target);
		public abstract OHResultList GetLastVersion(Type objType, string keyValue);
		public abstract OHResultList GetVersion(Type objType, string keyValue, DateTime date);
		public abstract OHResultList GetHistory(Type objType, string keyValue);

		public virtual void CreateDBSchema()
		{
			// skip this if the schema is up to date
			if (DbSchemaUpdated) return;

			string objectsPrefix = string.IsNullOrEmpty(target.DBTablesPrefix) ? "" : target.DBTablesPrefix;
			DbProviderFactory dbFact = DbProviderFactories.GetFactory(target.DbProviderInvariantName);

			DbConnection conn = dbFact.CreateConnection();
			conn.ConnectionString = target.ConnectionString;

			DbCommand comm = conn.CreateCommand();
			comm.CommandType = System.Data.CommandType.Text;

			conn.Open();

			// execute table scripts
			List<string> scripts = DBScripts.OHScriptManager.GetTablesScripts(this);
			foreach (var script in scripts)
			{
				if (!string.IsNullOrEmpty(script.Trim()))
				{
					comm.CommandText = script;
					comm.ExecuteNonQuery();
				}
			}

			// execute procedures scripts
			scripts = DBScripts.OHScriptManager.GetProceduresScripts(this);
			foreach (var script in scripts)
			{
				if (!string.IsNullOrEmpty(script.Trim()))
				{
					comm.CommandText = script;
					comm.ExecuteNonQuery();
				}
			}

			// execute views scripts
			scripts = DBScripts.OHScriptManager.GetViewsScripts(this);
			foreach (var script in scripts)
			{
				if (!string.IsNullOrEmpty(script.Trim()))
				{
					comm.CommandText = script;
					comm.ExecuteNonQuery();
				}
			}

			// execute data scripts
			scripts = DBScripts.OHScriptManager.GetDataScripts(this);
			foreach (var script in scripts)
			{
				if (!string.IsNullOrEmpty(script.Trim()))
				{
					comm.CommandText = script;
					comm.ExecuteNonQuery();
				}
			}

			conn.Close();
		}


		public class OHResultList
		{
			List<OHResultItem> Items = null;
			OHInfo objInfo = null;
			bool Merge = false;


			/// <summary>
			/// Initializes a new instance of the <see cref="OHResultList"/> class.
			/// The reader must have the following columns:
			/// PropertyName; PropertyValue; PropertyType; ObjectName; ObjectType; TimeStamp; User; Level
			/// </summary>
			/// <param name="reader">The reader.</param>
			protected internal OHResultList(OHInfo objInfo, DbDataReader reader, bool mergeResults)
			{
				LoadData(reader);
				this.objInfo = objInfo;
				Merge = mergeResults;
			}


			/// <summary>
			/// Loads the data infering the mappings by the name of the columns.
			/// 
			/// </summary>
			/// <param name="reader">The reader.</param>
			/// <returns></returns>
			private void LoadData(DbDataReader reader)
			{
				Items = new List<OHResultItem>();

				while (reader.Read())
				{
					OHResultItem item = new OHResultItem();

					item.OperationID = int.Parse(reader["OperationID"].ToString());

					item.PropertyName = reader["PropertyName"].ToString();
					item.PropertyValue = reader["PropertyValue"].ToString();
					item.PropertyType = reader["PropertyType"].ToString();

					item.ObjectName = reader["ObjectName"].ToString();
					item.ObjectType = reader["ObjectType"].ToString();

					item.TimeStamp = DateTime.Parse(reader["LastModified"].ToString());
					item.User = reader["User"].ToString();

					item.Level = reader["Level"].ToString();

					Items.Add(item);
				}
			}

			/// <summary>
			/// Returns a list in wich each line is one property and its value.
			/// </summary>
			/// <returns></returns>
			public List<OHResultItem> AsList()
			{
				return Items;
			}

			/// <summary>
			/// Retuns a DataTable that pivots the data on the db to show each property as a column.
			/// </summary>
			/// <returns></returns>
			public DataTable AsDataTable()
			{
				var table = new DataTable();
				
				var list = AsList();
				var columns = list.Select(p => p.PropertyName).Distinct();
				var operations = list.Select(p => p.OperationID).Distinct();
				
				// create schema
				foreach (var col in columns)
				{
					table.Columns.Add(col, typeof(string));
				}
				table.Columns.Add("User", typeof(string));
				table.Columns.Add("TimeStamp", typeof(string));


				// add data
				if (Merge)
				{
					DataRow nrow = table.NewRow();
					foreach (var property in columns)
					{
						nrow[property] = list.Find(p => p.PropertyName == property).PropertyValue;
					}
				
					// merge results may contain information commited by different users at different instants in time
					// so here will be shown the last user and the last time
					OHResultItem tItem = list.Find(p => p.TimeStamp == list.Max(ml => ml.TimeStamp));
					nrow["User"] = tItem.User;
					nrow["TimeStamp"] = tItem.TimeStamp;

					table.Rows.Add(nrow);
				}
				else
				{
					foreach (var operation in operations)
					{
						DataRow nrow = table.NewRow();

						var changes = list.FindAll(p => p.OperationID == operation).ToList();
						foreach (var change in changes)
						{
							nrow[change.PropertyName] = change.PropertyValue;
						}

						OHResultItem tItem = list.Find(p => p.OperationID == operation);
						nrow["User"] = tItem.User;
						nrow["TimeStamp"] = tItem.TimeStamp;

						table.Rows.Add(nrow);
					}
				}


				return table;
			}

			/// <summary>
			/// Tries to cteate an instance of the 
			/// </summary>
			/// <returns></returns>
			public Object AsObject(Type type)
			{
				throw new NotImplementedException();

				// this isn't working yet.
				// for this to work it needs to reveive only one item row.
				// the code must also consider that logged property names may have been customized on the attribute.

				object obj = Activator.CreateInstance(type);

				foreach (var item in AsList())
				{
					PropertyInfo pInfo = obj.GetType().GetProperty(item.PropertyName);
					if (pInfo != null && pInfo.PropertyType.ToString() == item.PropertyType)
					{
						pInfo.SetValue(obj, item.PropertyValue, null);
					}
				}

				return obj;
			}
		}

		public class OHResultItem
		{
			public int OperationID { get; set; }

			public string PropertyName { get; set; }
			public string PropertyValue { get; set; }
			public string PropertyType { get; set; }

			public string ObjectName { get; set; }
			public string ObjectType { get; set; }

			public DateTime TimeStamp { get; set; }
			public string User { get; set; }

			public string Level { get; set; }
		}

	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AlexCode
Architect
Switzerland Switzerland
Senior IT Consultant working in Switzerland as Senior Software Engineer.
 
Find more at on my blog.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150302.1 | Last Updated 26 May 2010
Article Copyright 2010 by AlexCode
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid