Wriju's BLOG

.NET, Cloud and everything

LINQ to SQL : Using ChangeSet for Audit Trail

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!!!