none
POCO and tracking changes? RRS feed

  • Question

  • Hi all,

    I think I'm going to use POCO with EF4 for my classes. One of the requirements is that changes done to any field of any table are saved in a 'history' table.

    Do I need to code my own stuff or is there a simplified way to achieve this?

    Thanks,

    Ivan

    Wednesday, May 9, 2012 6:16 AM

Answers

  • Hi Cryo75,

    Yes, you need to code manually. You can write a method to Insert records into History table, pass the changed properties and their values as parameters to the method. I wrote a demo, you can refer to it.

    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (TestEntities context = new TestEntities())
                {
                    //new record
                    Player p = new Player();
                    p.TeamID = 1;
                    p.PlayerName = "player2";
                    p.Age = 23;
                    context.Players.AddObject(p);
                    context.SaveChanges();
                    Dictionary<string, string> newEntity = new Dictionary<string, string>();
                    newEntity.Add("table_name", p.GetType().Name);
                    newEntity.Add("record_id", p.PlayerID.ToString());
                    newEntity.Add("action", "new");
                    newEntity.Add("fields", "PlayerID:" + p.PlayerID + "PlayerName:" + p.PlayerName + "TeamID:" + p.TeamID + "Age:" + p.Age);
                    WriteToHistory(newEntity);
    
                    //modify record
                    Player p2 = (from player in context.Players where player.PlayerID == 19 select player).SingleOrDefault();         
                    p2.PlayerName = "player3";
                    Dictionary<string, string> Original = new Dictionary<string, string>();
                    Dictionary<string, string> Modified = new Dictionary<string, string>();
                    Original.Add("table_name", p2.GetType().Name);
                    Original.Add("record_id", p2.PlayerID.ToString());
                    Original.Add("action", "modify");
                    Original.Add("fields", "PlayerName:" + context.ObjectStateManager.GetObjectStateEntry(p2).OriginalValues["PlayerName"].ToString());
                    Modified.Add("fields", "PlayerName:" + p2.PlayerName);
                    context.SaveChanges();
                    WriteToHistory(Original, Modified);
                    
                }
            }
    
            static void WriteToHistory(Dictionary<string,string> newEntity)
            {
                using (TestEntities context = new TestEntities())
                {
                    History his = new History();
                    his.table_name = newEntity["table_name"];
                    his.record_id = Convert.ToInt32(newEntity["record_id"]);
                    his.action = newEntity["action"];
                    his.fields = newEntity["fields"];
                    context.Histories.AddObject(his);
                    context.SaveChanges();
                }                    
            }
    
            static void WriteToHistory(Dictionary<string, string> Original, Dictionary<string, string> Modified)
            {
                using (TestEntities context = new TestEntities())
                {
                    History his = new History();
                    his.table_name = Original["table_name"];
                    his.record_id = Convert.ToInt32(Original["record_id"]);
                    his.action = Original["action"];
                    his.fields = Original["fields"] + Modified["fields"];
                    context.Histories.AddObject(his);
                    context.SaveChanges();
                }
            }
    
           
        }
    
        
    }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Cryo75 Wednesday, May 9, 2012 9:31 AM
    Wednesday, May 9, 2012 9:26 AM
    Moderator

All replies

  • Hi Cryo75,

    Welcome to MSDN Forum.

    I'm sorry, Im not clear about the question. Could you please post more details?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 9, 2012 7:48 AM
    Moderator
  • In my database, I have a table called 'history'. This table has the columns (table_name, record_id, action, fields). Now, for example I want to add a new record in the table 'titles', so I add a title name called 'Mr'. So upon saving this record, I need to create a new history record such as:

    table_name='titles'

    record_id=1

    action = 'new'

    fields '= name;;Mr'

    Afterwards I want to change my title from Mr to Mrs. Upon saving, I need to create a new history record as follows:

    table_name='titles'

    record_id=1

    action = 'edit'

    fields '= name;Mr;Mrs'

    Basically, I'm tracking every single change that is done to any field of any table in the database.

    Ivan


    • Edited by Cryo75 Wednesday, May 9, 2012 8:08 AM
    Wednesday, May 9, 2012 8:07 AM
  • Hi Cryo75,

    Yes, you need to code manually. You can write a method to Insert records into History table, pass the changed properties and their values as parameters to the method. I wrote a demo, you can refer to it.

    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (TestEntities context = new TestEntities())
                {
                    //new record
                    Player p = new Player();
                    p.TeamID = 1;
                    p.PlayerName = "player2";
                    p.Age = 23;
                    context.Players.AddObject(p);
                    context.SaveChanges();
                    Dictionary<string, string> newEntity = new Dictionary<string, string>();
                    newEntity.Add("table_name", p.GetType().Name);
                    newEntity.Add("record_id", p.PlayerID.ToString());
                    newEntity.Add("action", "new");
                    newEntity.Add("fields", "PlayerID:" + p.PlayerID + "PlayerName:" + p.PlayerName + "TeamID:" + p.TeamID + "Age:" + p.Age);
                    WriteToHistory(newEntity);
    
                    //modify record
                    Player p2 = (from player in context.Players where player.PlayerID == 19 select player).SingleOrDefault();         
                    p2.PlayerName = "player3";
                    Dictionary<string, string> Original = new Dictionary<string, string>();
                    Dictionary<string, string> Modified = new Dictionary<string, string>();
                    Original.Add("table_name", p2.GetType().Name);
                    Original.Add("record_id", p2.PlayerID.ToString());
                    Original.Add("action", "modify");
                    Original.Add("fields", "PlayerName:" + context.ObjectStateManager.GetObjectStateEntry(p2).OriginalValues["PlayerName"].ToString());
                    Modified.Add("fields", "PlayerName:" + p2.PlayerName);
                    context.SaveChanges();
                    WriteToHistory(Original, Modified);
                    
                }
            }
    
            static void WriteToHistory(Dictionary<string,string> newEntity)
            {
                using (TestEntities context = new TestEntities())
                {
                    History his = new History();
                    his.table_name = newEntity["table_name"];
                    his.record_id = Convert.ToInt32(newEntity["record_id"]);
                    his.action = newEntity["action"];
                    his.fields = newEntity["fields"];
                    context.Histories.AddObject(his);
                    context.SaveChanges();
                }                    
            }
    
            static void WriteToHistory(Dictionary<string, string> Original, Dictionary<string, string> Modified)
            {
                using (TestEntities context = new TestEntities())
                {
                    History his = new History();
                    his.table_name = Original["table_name"];
                    his.record_id = Convert.ToInt32(Original["record_id"]);
                    his.action = Original["action"];
                    his.fields = Original["fields"] + Modified["fields"];
                    context.Histories.AddObject(his);
                    context.SaveChanges();
                }
            }
    
           
        }
    
        
    }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Cryo75 Wednesday, May 9, 2012 9:31 AM
    Wednesday, May 9, 2012 9:26 AM
    Moderator