Friday 4 January 2013

Comparing two Datasets using LINQ

let there be two  datasets (oldds, newds) having similar columns and data, we need compare the two and detect whether any new records has been added in the new dataset, whether any data has been deleted from the new dataset and whether there is any modification in the data of the new dataset. To do so I have used LINQ to compare the datasets seperately for different scenarios, then convert the result of the LINQ back to a dataset to bind it with the gridview. The code is as follows:

private DataSet CompareDatasets(DataSet Oldds, DataSet Newds)
{
//Result Dataset

DataSet res= new DataSet();DataTable dt = new DataTable();//Create the structure of the datatable same as the source table
dt = CreatTableStructure();

DataTable dtOld = new DataTable();
dtOld = dt.Clone();

  try
{
//make the datasets enumerable to loop through itvar orig = Oldds.Tables[0].AsEnumerable();
var updated = Newds.Tables[0].AsEnumerable();
//Using LINQ to loop through the data//Any New Record Addedvar newRec = from u in updated
where !(from o in orig
select o.Field<double>("Primary Key")).Contains(u.Field<
double>("Primary Key"))
select u;
//Data Updated var updRec = from u in updated
join o in orig
on u.Field<doublePrimary Key")
equals o.Field<double>("Primary Key")
where (u.Field<double>("Project ID") !=o.Field<
double>("Project ID")) ||(u.Field<
string>("Description") !=o.Field<
string>("Description")) ||(u.Field<
string>("City") !=o.Field<
string>("City"))
//|| (u.Field<DateTime>("*Start Date") !=//o.Field<DateTime>("*Start Date")) ||//(u.Field<DateTime>("*End Date") !=//o.Field<DateTime>("*End Date"))select u;
//Any Record deletedvar delRec = from o in orig
where !(from u in updated
select u.Field<double>("Primary Key")).Contains(o.Field<
double>("Primary Key"))
select o;
//Transfer the values to datatable and check which data has been updated :foreach (var item in updRec){
dt.Rows.Add(item.Field<double>("Project ID"), item.Field<double>("*Team Member"), item.Field<string>("Name"), item.Field<string>("Description"), item.Field<string>("Cust Ref#"), item.Field<string>("City"), item.Field<DateTime>("*Start Date"), item.Field<DateTime>("*End Date"), "Updated");}

//Transfer the added records to a datatable :foreach (var item in newRec){
dt.Rows.Add(item.Field<
double>("Project ID"), item.Field<double>("*Team Member"), item.Field<string>("Name"), item.Field<string>("Description"), item.Field<string>("Cust Ref#"), item.Field<string>("City"), item.Field<DateTime>("*Start Date"), item.Field<DateTime>("*End Date"), "Added");// item.rec_type);}
//Transfer the deleted records to a datatable :foreach (var item in delRec){
dt.Rows.Add(item.Field<
double>("Project ID"), item.Field<double>("*Team Member"), item.Field<string>("Name"), item.Field<string>("Description"), item.Field<string>("Cust Ref#"), item.Field<string>("City"), item.Field<DateTime>("*Start Date"), item.Field<DateTime>("*End Date"), "Deleted");// item.rec_type);}
res.Tables.Add(dt);
return res;}

catch{
throw;}
}


private DataTable CreatTableStructure()
{
DataTable t = new DataTable();
t.Columns.Add(
"Project ID", typeof(double));
t.Columns.Add(
"Primary Key", typeof(double));
t.Columns.Add(
"Name", typeof(string));
t.Columns.Add(
"Description", typeof(string));
t.Columns.Add(
"Cust Ref#", typeof(string));
t.Columns.Add(
"City", typeof(string));
t.Columns.Add(
"Start Date", typeof(DateTime));
t.Columns.Add(
"End Date", typeof(DateTime));
t.Columns.Add(
"Rec_Type", typeof(string));
return t;}



No comments:

Post a Comment