CODEWITHSUNDEEP
c#asp.net-mvcdatabaseef-code-first
SDwarfs
SDwarfs

Reputation: 3239

History tables in .NET MVC code first approach?

I need to track a change history of some database objects in a MVC .NET application using the code first approach.

Here is what is meant by history table: http://database-programmer.blogspot.de/2008/07/history-tables.html

I would use a history table for it, if I would write the SQL queries myself. But in the code first approach the SQL is generated... and I would like to stick to this paradigm.

The goal is a structure that holds all "old" revisions of changed/deleted entries together with some additional information (e.g. timestamp, user who changed it, ...)

Any ideas?

Regards, Stefan

To be more specific - here is some code example:

public class Node {
 public int NodeID { get; set; }

 public string? data { get; set; }  // sample data
}


public class NodeHistory {
  public int NodeID { get; set; }
  public string? data { get; set; }

  public int UserID { get; set; }
  public DataTime timestamp { get; set; }
}

What I need is some "framework" assistance to be able to add an entry to NodeHistory whenever a change is -persisted- to table the Node structure.

That means: Just overriding the set-method isn't a solution, as it would also create an entry, if the change to a "Node" is not persisted at the end (e.g. roleback).

Upvotes: 2

Views: 2668

Answers (2)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

This is really something you should do with a trigger. Yes, you have to write some sql for it, but then history is updated no matter how the update occurs, either manually, or through some other means.

Upvotes: 1

JTMon
JTMon

Reputation: 3199

I think the best approach for me would be to use a repository pattern and do the insertion into the NodeHistory table on every operation on the Node object that you see fit to keep a history of.

EDIT: Some code

public class NodeRepository{
    public Node EditNode(Node toEdit, int userId){
        using(new TransactionScope())
        {            
            //Edit Node in NodeContext like you would anyway without repository
            NodeContext.NodeHistories.Add(new NodeHistory(){//initialise NodeHistory stuff here)
            NodeContext.SaveChagnes();
        }
    }
}
public class NodeContext:DbContext{
    public DbSet<Node> Nodes{get;set;}
    public DbSet<NodeHistory> NodeHistories{get;set;}
}

If you are looking for something simpler than this, then I have no idea what it might be.

Upvotes: 2

Related Questions