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