NLog Log and Audit Advanced Target

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"; }
		}
	}
}