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 App.Logger.Targets.ObjectHistoryLogger;
using System.Data.Common;
using System.Data.SqlClient;
using App.Logger.Targets.ObjectHistoryLogger.Data.DBScripts.OHSqlClient.Queries;

namespace App.Logger.Targets.ObjectHistoryLogger.Data
{
	public class OHSqlClient : OHDbBaseClient
	{

		public OHSqlClient(OHTarget target)
			: base(target)
		{
			
		}


		public override bool DbSchemaUpdated 
		{
			get
			{
				bool result = false;

				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;
				string query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN ('{0}', '{1}', '{2}', '{3}', '{4}');";
				query = string.Format(query, tbl_EventLogs, tbl_Levels, tbl_ObjectLogDetails, tbl_ObjectLogs, tbl_Operations);

				comm.CommandText = query;
				conn.Open();
				object qResult = comm.ExecuteScalar();
				conn.Close();

				int count = 0;
				int.TryParse(qResult.ToString(), out count);

				result = (count == 5);	// *** CHANGE THIS IF THE NUMBER OF THE EXPECTED COLUMNS HAVE CHANGED ***

				return result;
			}
		}

		public override void SaveObject(OHInfo objInfo, OHTarget target)
		{
			// get the object last version
			OHSqlClient sqlClient = new OHSqlClient(target);
			var keyPropertyValue = objInfo.LoggableProperties.Find(p => p.Name.Equals(objInfo.KeyPropertyName)).Value;
			List<OHResultItem> lastVersion = sqlClient.GetLastVersion(objInfo.ObjectType, keyPropertyValue).AsList();

			// Save the modified properties
			DbProviderFactory dbFact = DbProviderFactories.GetFactory(target.DbProviderInvariantName);

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

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



			string script = string.Empty;
			script += "DECLARE @OperationID INT;\n";
			script += "DECLARE @ObjectLogID INT;\n";

			script += "INSERT INTO [{0}Operations] ([User], TimeStamp, KeyValue) VALUES (@User, @TimeStamp, @KeyValue); SELECT @OperationID = SCOPE_IDENTITY();\n";

			script += "SELECT @ObjectLogID = ObjectLogID FROM [{0}ObjectLogs] WHERE Name LIKE @ObjectName AND ObjectType LIKE @ObjectType;\n";
			script += "IF(@ObjectLogID IS NULL)\n";
			script += "BEGIN\n";
			script += "INSERT INTO [{0}ObjectLogs] ([Name],[ObjectType]) VALUES (@ObjectName, @ObjectType); SELECT @ObjectLogID = SCOPE_IDENTITY();\n";
			script += "END;\n";

			// Save the properties that were modified
			bool hasChanges = false;
			foreach (var prop in objInfo.LoggableProperties)
			{
				OHResultItem LastVersionPropInfo = lastVersion.Find(p => p.PropertyName.Equals(prop.Name));
				string oldValue = LastVersionPropInfo == null ? null : LastVersionPropInfo.PropertyValue;
				string newValue = prop.Value;

				if (objInfo.AppendType == AppendTypes.Incremental || newValue != oldValue)
				{
					hasChanges = true;
					string dbValue = prop.Value == null ? "NULL" : string.Format("'{0}'", prop.Value);
					script += "INSERT INTO [{0}ObjectLogDetails] ([ObjectLogID] ,[OperationID] ,[LevelID] ,[PropertyName] ,[PropertyValue], [PropertyType]) VALUES (@ObjectLogID, @OperationID, @LevelID, '" + prop.Name + "', " + dbValue + ", '" + prop.Type + "');\n";
				}
			}

			// if no changes found then there's nothing to do...
			if (!hasChanges) return;

			// Apply the configured tables prefix (if any)
			comm.CommandText = string.Format(script, target.DBTablesPrefix);
			
			// Parameters
			comm.Parameters.Add(new SqlParameter("@KeyValue", objInfo.LoggableProperties.Find(p => p.Name.Equals(objInfo.KeyPropertyName)).Value));
			comm.Parameters.Add(new SqlParameter("@User", string.IsNullOrEmpty(objInfo.User) ? string.Empty : objInfo.User));
			comm.Parameters.Add(new SqlParameter("@TimeStamp", objInfo.TimeStamp));
			comm.Parameters.Add(new SqlParameter("@ObjectName", objInfo.ObjectName));
			comm.Parameters.Add(new SqlParameter("@ObjectType", objInfo.ObjectType.ToString()));
			comm.Parameters.Add(new SqlParameter("@LevelID", 1));

			conn.Open();
			comm.ExecuteNonQuery();
			conn.Close();
		}



		/// <summary>
		/// Gets the last version of the object.
		/// </summary>
		/// <param name="objInfo">The obj info.</param>
		/// <returns></returns>
		public override OHDbBaseClient.OHResultList GetLastVersion(Type objType, string KeyValue)
		{
			OHInfo objInfo = new OHInfo(objType, target);
			DbProviderFactory dbFact = DbProviderFactories.GetFactory(target.DbProviderInvariantName);

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

			DbCommand comm = conn.CreateCommand();
			comm.CommandType = System.Data.CommandType.Text;
			comm.CommandText = 
				"SELECT tbl.* FROM (" +
				string.Format(SqlClientQueriesResource.GetObjectsLastVersion, this.target.DBTablesPrefix) +
				") tbl WHERE tbl.ObjectName = '{0}' AND tbl.ObjectType = '{1}' AND tbl.KeyValue = '{2}'";

			comm.CommandText = string.Format(
									comm.CommandText, 
									objInfo.ObjectName, 
									objInfo.ObjectType.ToString(),
									KeyValue.ToString());

			conn.Open();
			OHDbBaseClient.OHResultList list = new OHResultList(objInfo, comm.ExecuteReader(), true);
			conn.Close();

			return list;
		}

		public  override OHResultList GetVersion(Type objType, string keyValue, DateTime date)
		{
			OHInfo objInfo = new OHInfo(objType, target);
			DbProviderFactory dbFact = DbProviderFactories.GetFactory(target.DbProviderInvariantName);

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

			DbCommand comm = conn.CreateCommand();
			comm.CommandType = System.Data.CommandType.Text;
			comm.CommandText =
				"SELECT tbl.* FROM (" +
				string.Format(SqlClientQueriesResource.GetObjectsVersion, this.target.DBTablesPrefix) +
				") tbl WHERE tbl.ObjectName = '{0}' AND tbl.ObjectType = '{1}' AND tbl.KeyValue = '{2}'";

			comm.CommandText = string.Format(
									comm.CommandText,
									objInfo.ObjectName,
									objInfo.ObjectType.ToString(),
									keyValue.ToString());

			DbParameter paramMaxDate = dbFact.CreateParameter();
			paramMaxDate.ParameterName = "MaxDate";
			paramMaxDate.Value = date;
			comm.Parameters.Add(paramMaxDate);

			conn.Open();
			OHDbBaseClient.OHResultList list = new OHResultList(objInfo, comm.ExecuteReader(), true);
			conn.Close();

			return list;
		}

		/// <summary>
		/// Gets the history list of all modifications made on the object.
		/// </summary>
		/// <param name="objInfo">The obj info.</param>
		/// <returns></returns>
		public override OHDbBaseClient.OHResultList GetHistory(Type objType, string keyValue)
		{
			OHInfo objInfo = new OHInfo(objType, target);
			DbProviderFactory dbFact = DbProviderFactories.GetFactory(target.DbProviderInvariantName);

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

			DbCommand comm = conn.CreateCommand();
			comm.CommandType = System.Data.CommandType.Text;
			comm.CommandText =
				"SELECT tbl.* FROM (" +
				string.Format(SqlClientQueriesResource.GetAllObjectsHistory, target.DBTablesPrefix) +
				") tbl WHERE tbl.ObjectName = '{0}' AND tbl.ObjectType = '{1}' AND tbl.KeyValue = '{2}'";

			comm.CommandText = string.Format(
								comm.CommandText, 
								objInfo.ObjectName,
								objInfo.ObjectType.ToString(),
								keyValue);

			conn.Open();
			OHDbBaseClient.OHResultList list = new OHResultList(objInfo, comm.ExecuteReader(), false);
			conn.Close();

			return list;
		}

		public override string DbScriptsContainerPath
		{
			get { return "App.Logger.Targets.ObjectHistoryLogger.Data.DBScripts"; }
		}
	}
}

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
Web01 | 2.8.1411023.1 | Last Updated 26 May 2010
Article Copyright 2010 by AlexCode
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid