Log record changes in SQL server in an audit table

Visual Studio Entity Framework Core
CREATE TABLE [dbo].[AuditLog](
[AuditLogId] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](100) NOT NULL,
[AuditActionId] [char](1) NOT NULL,
[RecordId] [varchar](100) NULL,
[AuditDateTime] [datetime] NOT NULL,
[UserId] [varchar](20) NOT NULL,
CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED
(
[AuditLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[AuditLogItem](
[AuditLogItemId] [int] IDENTITY(1,1) NOT NULL,
[AuditLogId] [int] NOT NULL,
[FieldName] [varchar](100) NOT NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
CONSTRAINT [PK_MT_AuditLogItem] PRIMARY KEY CLUSTERED
(
[AuditLogItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

AuditLog

In your DataClasses insert this code:

public partial class YourDataClassesDataContext : System.Data.Linq.DataContext 
{
#region " RossiniAudit "
public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{
var changeSet = this.GetChangeSet();
foreach (var update in changeSet.Updates)
{
if (update as AuditLogItem == null && update as AuditLog == null)
{
var table = this.GetTable(update.GetType());
var a = this.Mapping.GetTable(update.GetType());
var b = a.RowType.DataMembers.SingleOrDefault(x => x.IsPrimaryKey);
var PrimaryKey = update.GetType().GetProperty(b.Name).GetValue(update, null);
var modifications = table.GetModifiedMembers(update);
AuditLog header = new AuditLog();
AuditLogs.InsertOnSubmit(header);
header.UserId = UserLoggedOn;
header.AuditDateTime = DateTime.Now;
header.TableName = update.GetType().Name;
header.RecordId = PrimaryKey.ToString();
header.AuditActionId = 'E';

foreach (var modification in modifications)
{
AuditLogItem log = new AuditLogItem();
log.FieldName = modification.Member.Name;
log.OldValue = modification.OriginalValue.ToString();
log.NewValue = modification.CurrentValue.ToString();
header.AuditLogItems.Add(log);
}
}
}

base.SubmitChanges(failureMode);
}

public string UserLoggedOn
{
get { return HttpContext.Current.User.Identity.Name; }
}
#endregion
}

One thought on “Log record changes in SQL server in an audit table

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.