Partilhar via

LINQ to SQL : Using ChangeSet for Audit Trail

  • Artigo

Most of the transactional scenario, you may need to add the transaction to some table for ref. many apps does it for admin perspective.

Problem

You can do it in LINQ to SQL with ChangeSet but the problem is with Inserts. It does not give you the Identity field value until and unless you call SubmitChanges. But after SubmitChages() you cannot get the objects. So the trick is as below,

//Add new Emp

Emp emp1 = new Emp() { Name = "E : "+DateTime.Now.ToString() };

//Add new Dept

Dept dep1 = new Dept() { DeptName = "D : " + DateTime.Now.ToString() };

db.Emps.InsertOnSubmit(emp1);

db.Depts.InsertOnSubmit(dep1);

//Update Employee

Emp eUpd = (from em in db.Emps

            where em.Id == 170

            select em).First();

eUpd.Name = DateTime.Now.ToString();

//Track the changed objects

ChangeSet changedOnes = db.GetChangeSet();

//Insert

/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 * Since we do not get the Identity column value before SubmitChanges()

 * We need to store them in offline List<T> and then find the Id column after commit

 * This is tricky as we cannot get the Object after calling SubmitChanges()

 * +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 */

List<Emp> lstEmp = new List<Emp>();

List<Dept> lstDept = new List<Dept>();

foreach (var obj in changedOnes.Inserts)

{

    Type ty = obj.GetType();

    if (ty.Name == "Emp")

    {

        lstEmp.Add((Emp)obj);

    }

    if (ty.Name == "Dept")

    {

        lstDept.Add((Dept)obj);

    }

}

string sData = "Summary : " + changedOnes.ToString() + "\r\n";

//Track Updates

foreach (var obj in changedOnes.Updates)

{

    Type ty = obj.GetType();

    sData += "Entity Updated : " + ty.Name + "\r\n";

}

//Save the changes

db.SubmitChanges();

//Now find out the Identity Column value

foreach (var sEmp in lstEmp)

{

    sData += String.Format("New Emp Id {0} for {1} ", sEmp.Id.ToString(), "Emp") + "\r\n";

}

foreach (var sDept in lstDept)

{

    sData += String.Format("New Dept Id {0} for {1} ", sDept.DeptId.ToString(), "Dept") + "\r\n";

}

MessageBox.Show(sData);

Namoskar!!!

Comments

  • Anonymous
    April 03, 2009
    PingBack from http://tune-up-pc.com/blog/?p=898

  • Anonymous
    April 04, 2009
    Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Anonymous
    April 05, 2009
    Hi, a more robust alternative would be using a determinstic datatype for your primary keys. The unique identifier is perfect for scenarios where data are truly disconnected from the datastore and you typically want to know the primary key in advance of persistence.

  • Anonymous
    October 21, 2010
    Nicely written in a very simple format.  Very helpful