Code sharing for audit log in single table.

[ramveersgh]

Hi

I was having a requirement to generate report for work done by user on daily basis and the details of actions (Insert/Update/Delete) by user. Initially i was using ICaptureLogRow but i have so many table and i have to create a copy of each table and a log class for each module. My table also changed frequently so maintaining log table and log class was becoming pain for me. So i created custom behaviour to log all changes in single table.

I am sharing code by thinking that it may be helpful for someone else.
DBScripts for Table, Stored Procedure and Code file is attached.
DB Scripts
SQLServer_AuditScript.txt

Code File
AuditBehavior.txt

How To Use

1. Run the DBScript in your SQL Server Database
2. If there is already a column named Identity then inherit your RowClass with IAuditLog
   Ex:

public sealed class UserRow : LoggingRow, IIdRow, INameRow, IIsActiveRow, IAuditLog
{
}

1. If you want to save any other column value as RowId in AuditTable then use following

public sealed class UserRow : LoggingRow, IIdRow, INameRow, IIsActiveRow, IExAuditLog
{
         public Int32Field UserId
        {
            get { return Fields.IdField; }
        }
}

Here is screenshot of log table

Hope this will be helpful for someone. Please let me know if you find any improvement in this or have any suggestions.

[awesomegithubusername]

Thanks for sharing @ramveersgh. How do you know which row got deleted (Could field values be saved before deleting the row)?
P.S: Could you move this thread to the Wiki?

[ramveersgh]

@awesomegithubusername
I am inserting primary key in the log table for deleted row. Off course this primary key will have log for insert and updated too. So RowId is enough to have for delete log. I have attached an image for the log with Insert, Update and Delete.

I never created any page on wiki but i will try to add it in Wiki if @volkanceylan verify and allow me to add this.

[volkanceylan]

Thanks for sharing @ramveersgh. Actually yours is similar to AuditLog but i didn't test it for long time. You should share your solution in wiki. Another option is SQL Server Change Data Capture (CDC).

[ramveersgh]

Thanks @volkanceylan
I also thought that IAuditLogRow should be similar to what i did. I will share my solution to wiki and will close this.
I am using SQL Express version which does not support CDC.

[ramveersgh]

Added the Wiki Page for this.
Wiki Page

[awesomegithubusername]

Good job @ramveersgh. Much appreciated.

[ramveersgh]

Thanks @awesomegithubusername !!

[Qasem2022]

great effort
Thank you for posting the code

But when I apply the code, I see an error in the process of deleting and adding
(Object reference not set to an instance of an object)
can you help
Also, the class mentioned in Namespace (http://using IBS_ACOC.Modules.Common.Enums;) I don't know what it is / can it be published?
There is an error in Namespace (//using IBS_ACOC.DAL;) / Is it related to the error?

Once again thank you

hope for help

[nahidulislam-cse15]

@ramveersgh When this procedure will call ?or from where to call this procedure?