We are developing the new audit history mechanism. SQL triggers were added. They do the shadow copy of added/deleted/updated data in important tables such as user story, project. The problem is that we need the custom context in these triggers implementation such as logged user and client date. In other words we need to set some custom info into the connection session before any change.

The code below shows how to set and extract context on MS SQL side:

GO

CREATE PROCEDURE setTpCnt
       @userID INT,
       @clientDate DATETIME
AS

DECLARE @BinVar varbinary(128)

SET @BinVar = CAST(CAST(@userID as nvarchar(20)) +
       '_' + CONVERT(nvarchar(100), @clientDate, 13)
              + '_' AS varbinary(128))

SET CONTEXT_INFO @BinVar

GO

CREATE FUNCTION f_GetLoggedUserID()
       RETURNS INT
       AS
BEGIN
       DECLARE @CONTEXT AS NVARCHAR(120)

       SET @CONTEXT = NULL
   
       SELECT @CONTEXT = CAST(CONTEXT_INFO AS NVARCHAR(120))
                     FROM master.dbo.sysprocesses WHERE spid = @@spid

       IF (@CONTEXT IS NULL)
              RETURN NULL
   
   
       RETURN CAST(SUBSTRING(@CONTEXT, 0, CHARINDEX('_', @CONTEXT)) as INT)
END

GO

CREATE FUNCTION f_GetClientTime()
       RETURNS DATETIME
       AS
BEGIN
       DECLARE @PAD_INDEX AS INT
       DECLARE @CONTEXT AS NVARCHAR(120)

       SET @CONTEXT = NULL
   
       SELECT @CONTEXT = CAST(CONTEXT_INFO AS NVARCHAR(120))
                     FROM master.dbo.sysprocesses WHERE spid = @@spid

       IF (@CONTEXT IS NULL)
              RETURN NULL
   
       SET @PAD_INDEX = CHARINDEX('_', @CONTEXT)
       SET @CONTEXT = SUBSTRING(@CONTEXT, @PAD_INDEX + 1, LEN(@CONTEXT) - @PAD_INDEX)
       SET @CONTEXT = SUBSTRING(@CONTEXT, 0, CHARINDEX('_', @CONTEXT))
   
       RETURN CONVERT(DATETIME, @CONTEXT, 13)
END

GO

We can do the following things with the procedure and functions above

• We can set the context using stored procedure setTpCnt
• We can get the logged user anywhere using function f_GetLoggedUserID
• We can get the client time using the function f_GetClientTime

Now we need to set the context from our client. Only our client knows the logged user id and the date. We need somehow to the call of stored procedure setTpCnt in our C# client. We are using NHibernate. So we need to figure out how to pass the custom context information into every connection which is created by NHibernate. Please find the solution below. We created the custom driver for NHibernate to make a call to stored procedure with setting required value:

#region

using System;
using System.Data;
using System.Data.SqlClient;
using NHibernate.Driver;
using NHibernate.SqlCommand;
using NHibernate.SqlTypes;
using Tp.BusinessObjects.Components.Authentication;

#endregion

namespace Tp.BusinessObjects.Data
{
    public class Driver : SqlClientDriver
    {
        private bool _isSecurityInjected;

        public override IDbConnection CreateConnection()
        {
            var connection = base.CreateConnection();
            _isSecurityInjected = false;
            return connection;
        }

        public override IDbCommand GenerateCommand(CommandType type, 
                SqlString sqlString, SqlType[] parameterTypes)
        {
            var command = base.GenerateCommand(type, sqlString, parameterTypes);

            if (_isSecurityInjected)
                return command;

            var commandText = command.CommandText;

            if (string.IsNullOrEmpty(commandText))
                return command;


            if ((commandText.IndexOf("INSERT ", 
                    StringComparison.InvariantCultureIgnoreCase) < 0)
                && (commandText.IndexOf("UPDATE ", 
                    StringComparison.InvariantCultureIgnoreCase) < 0)
                && (commandText.IndexOf("DELETE ", 
                    StringComparison.InvariantCultureIgnoreCase) < 0))
            {
                return command;
            }

            var userID = UserAuthentication.UserID;

            if (userID != null)
            {
                _isSecurityInjected = true;
                var text = @"EXEC setTpCnt @cnt_userID, @cnt_ClientDate" + Environment.NewLine;
                command.CommandText = text + commandText;
                command.Parameters.Add(new SqlParameter("@cnt_userID", userID));
                command.Parameters.Add(new SqlParameter("@cnt_ClientDate", CurrentDate.Value));
            }

            return command;
        }
    }
}

Now we need to improve NHibernate configuration to include the driver created above. It should be done in the following way

Will keep you informed about other tricks (if have time for sure).