The other day at work, I had to work on an old solution (some C# web API with an SQL) that a colleague had created a long time ago. Part of this solution was to create a kind of version history of objects and who had made the individual changes.
As I opened up the project, I saw some of the wildest code (in my opinion) I’ve seen in a long time to solve an otherwise simple problem😮. Without going too deep into what was done, I can say that the person who created it was very fond of reflection in C#.
How would I have done it?
Since I’ve worked a lot with SQL, it would be obvious for me to look at how to solve this directly in the database, rather than having to create a lot of generic C# code that almost no one would be able to read afterwards. So the obvious choice would be to create an SQL trigger on the exact table that should have a version history.
So let’s take an example
Let’s say we have a Person table like this:
CREATE TABLE [dbo].[Persons](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](255) NOT NULL,
[Lastname] [nvarchar](255) NOT NULL,
[Email] [nvarchar](255) NULL,
[Phone] [int] NULL,
[Address] [nvarchar](255) NULL,
[ModifiedAt] [datetime] NULL,
[ModifiedBy] [nvarchar](255) NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
( [ID] ASC )
) ON [PRIMARY]
From this table, we want to log every time a change occurs, not just that a change happens, but also what individual value changes from and to. This should, of course, be stored in a table as well, and such a table could look like this:
CREATE TABLE [dbo].[Persons_Changes](
[TriggerID] [int] NOT NULL,
[Firstname_Old] [nvarchar](255) NULL,
[Firstname_New] [nvarchar](255) NULL,
[Lastname_Old] [nvarchar](255) NULL,
[Lastname_New] [nvarchar](255) NULL,
[Email_Old] [nvarchar](255) NULL,
[Email_New] [nvarchar](255) NULL,
[Phone_Old] [int] NULL,
[Phone_New] [int] NULL,
[Address_Old] [nvarchar](255) NULL,
[Address_New] [nvarchar](255) NULL,
[ModifiedBy] [nvarchar](255) NULL,
[ModifiedAt] [datetime] NULL
) ON [PRIMARY]
Now for the fun part🎉 - namely, the logic that triggers each time a row in the Person table is updated. The trigger is designed to only save the values that are actually changed; the others that remain unchanged are simply set to NULL.
Trigger code
CREATE TRIGGER TriggerPerson_AfterUpdate
ON dbo.Persons
AFTER UPDATE
AS
BEGIN
DECLARE @ColumnOrdinalTotal INT = 0;
SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal
+ POWER (
2
, COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
)
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE s.name = 'dbo'
AND t.name = 'Persons'
AND c.name IN (
'Firstname',
'Lastname',
'Email',
'Phone',
'Address',
'ModifiedAt',
'ModifiedBy'
);
IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
BEGIN
INSERT INTO dbo.Persons_Changes
(
dbo.Persons_Changes.TriggerID,
dbo.Persons_Changes.Firstname_Old,
dbo.Persons_Changes.Firstname_New,
dbo.Persons_Changes.Lastname_Old,
dbo.Persons_Changes.Lastname_New,
dbo.Persons_Changes.Email_Old,
dbo.Persons_Changes.Email_New,
dbo.Persons_Changes.ModifiedBy,
dbo.Persons_Changes.ModifiedAt
)
SELECT d.ID
, CASE WHEN d.Firstname = i.Firstname THEN NULL ELSE d.Firstname END
, CASE WHEN d.Firstname = i.Firstname THEN NULL ELSE i.Firstname END
, CASE WHEN d.Lastname = i.Lastname THEN NULL ELSE d.Lastname END
, CASE WHEN d.Lastname = i.Lastname THEN NULL ELSE i.Lastname END
, CASE WHEN d.Email = i.Email THEN NULL ELSE d.Email END
, CASE WHEN d.Email = i.Email THEN NULL ELSE i.Email END
, i.ModifiedBy
, i.ModifiedAt
FROM inserted i
LEFT JOIN deleted d ON i.ID = d.ID;
END
END
GO
Now there should be a row like these every time a Person is updated
| TriggerID | Firstname_Old | Firstname_New | Lastname_Old | Lastname_New | … | ModifiedBy | ModifiedAt |
|---|---|---|---|---|---|---|---|
| 1 | Jeppe1 | Jeppe | NULL | NULL | … | JSC | 2024-02-03 11:30:19 |
| 1 | NULL | NULL | Spanggaard | Christensen | … | JSC | 2024-02-03 11:35:23 |
And now just for fun - how can this be used in C#? First of all, we need to create the DTO classes, one for the Persons table and one for Persons_Changes. Unlike many, I hardly ever use scaffold with Entity Framework; I create the models myself.
Persons class:
[Table("Persons", Schema = "dbo")]
internal class Person
{
[Column("ID")]
public int Id { get; set; }
[Column("Firstname")]
public string? Firstname { get; set; }
[Column("Lastname")]
public string? Lastname { get; set; }
[Column("Email")]
public string? Email { get; set; }
[Column("Phone")]
public int? Phone { get; set; }
[Column("Address")]
public string? Address { get; set; }
[Column("ModifiedAt")]
public DateTime? ModifiedAt { get; set; }
[Column("ModifiedBy")]
public string? ModifiedBy { get; set; }
}
Persons_Changes:
[Table("Persons_Changes", Schema = "dbo")]
internal class PersonChange
{
[Column("TriggerID")]
public int Id { get; set; }
[Column("Firstname_Old")]
public string? FirstnameOld { get; set; }
[Column("Firstname_New")]
public string? FirstnameNew { get; set; }
[Column("Lastname_Old")]
public string? LastnameOld { get; set; }
[Column("Lastname_New")]
public string? LastnameNew { get; set; }
[Column("Email_Old")]
public string? EmailOld { get; set; }
[Column("Email_New")]
public string? EmailNew { get; set; }
[Column("Phone_Old")]
public int? PhoneOld { get; set; }
[Column("Phone_New")]
public int? PhoneNew { get; set; }
[Column("Address_Old")]
public string? AddressOld { get; set; }
[Column("Address_New")]
public string? AddressNew { get; set; }
[Column("ModifiedAt")]
public DateTime? ModifiedAt { get; set; }
[Column("ModifiedBy")]
public string? ModifiedBy { get; set; }
}
DbContext:
internal class TestDbContext : DbContext
{
public DbSet<Person> Persons { get; set; }
public DbSet<PersonChange> PersonChanges { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("CONNECTION_STRING");
}
protected void OnModelCreation(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
}
Once we have this in place, we can start playing with the data in C#, and for fun, I’m thinking of creating a ’nice’ display of who and when the individual values were changed. And even though I started joking about reflection, I use it myself (when it makes sense)😜.
internal static string FormatChange<T>(T changeObj)
{
List<Tuple<string,string?,string?>> changes = new List<Tuple<string, string?, string?>>();
var properties = typeof(T).GetProperties();
var filteredProperties = properties
.Where(x => x.Name.Contains("Old") || x.Name.Contains("New"));
var distinctProperties = filteredProperties
.Select(x => x.Name.Substring(0, x.Name.Length - 3))
.Distinct();
foreach (var property in distinctProperties)
{
var oldValueProperty = typeof(T).GetProperty($"{property}Old");
var newValueProperty = typeof(T).GetProperty($"{property}New");
if (oldValueProperty != null && newValueProperty != null)
{
var oldValue = oldValueProperty.GetValue(changeObj);
var newValue = newValueProperty.GetValue(changeObj);
if (oldValue != null && newValue != null)
{
changes.Add(
new Tuple<string, string?, string?>(
property,
oldValue?.ToString(),
newValue?.ToString()
));
}
}
}
StringBuilder sb = new StringBuilder();
foreach (var change in changes)
{
if (change.Equals(changes.Last()))
{
sb.Append($"{change.Item1} from '{change.Item2}' to '{change.Item3}'");
break;
}
sb.Append($"{change.Item1} from '{change.Item2}' to '{change.Item3}' and ");
}
return sb.ToString();
}
When we bring it all together in a test application like this:
static void Main(string[] args)
{
Console.WriteLine("Hello, World!");
TestDbContext dbContext = new TestDbContext();
List<Person> persons = dbContext.Persons.ToList();
Person person1 = persons.First();
List<PersonChange> personChanges = dbContext.PersonChanges
.Where(x => x.Id == person1.Id)
.OrderBy(x => x.ModifiedAt)
.AsNoTracking()
.ToList();
foreach (PersonChange change in personChanges)
{
Console.WriteLine(
$"[{change.ModifiedAt}] {change.ModifiedBy} " +
"updated the the properties " +
FormatChange<PersonChange>(change));
}
}
We get this output:
[2024-02-03 11:30:19] JSC updated the the properties Firstname from 'Jeppe1' to 'Jeppe'
[2024-02-03 11:35:23] JSC updated the the properties Lastname from 'Spanggaard' to 'Christensen'
Is it then a better solution?
I can’t answer that, but for me, it is - and that’s because I believe something like this belongs in a database and not in code. Also, for me, it’s much easier to read a trigger than a bunch of reflection code😏.
Jeppe Spanggaard
A passionate software developer. I love to build software that makes a difference!