如果我有两个结构相同的数据表,相同的主键和相同的列数。
如何比较它们的content,并检测出两个数据表中不同的单元格?
例:
TB_Offline
emp_num(key) salary ov
455 3000 67.891
677 5000 89.112
778 6000 12.672 TB_Online
emp_num(key) salary ov
455 3000 67.891
677 5000 50.113
778 5500 12.672 我想得到这样的结果(或者用某种结构来显示不同之处):
emp_num(key)| salary_off |salary_on|s1 | ov_off | ov_on |s2
677 | 5000 | 5000 | 1 | 89.112 | 50.113 | 0
778 | 6000 | 5500 | 0 | 12.672 | 12.672 | 1 注:
结果中不存在455,因为它是两个数据表中所有列中的确切值。
发布于 2016-03-08 09:06:01
根据您的新要求,您可以尝试这样做。
它首先获取主键名-- Unique列,然后是其他列名,用新列名创建一个新的DataTable,填充这些值,并创建一个表达式来比较它们:
void Main()
{
// create some test data
var dt1 = new DataTable();
dt1.Columns.Add("emp_num", typeof(int));
dt1.Columns.Add("salary", typeof(int));
dt1.Columns.Add("ov", typeof(double));
dt1.Columns[0].Unique = true;
dt1.Rows.Add(455, 3000, 67.891);
dt1.Rows.Add(677, 5000, 89.112);
dt1.Rows.Add(778, 6000, 12.672);
var dt2 = new DataTable();
dt2.Columns.Add("emp_num", typeof(int));
dt2.Columns.Add("salary", typeof(int));
dt2.Columns.Add("ov", typeof(double));
dt2.Columns[0].Unique = true;
dt2.Rows.Add(455, 3000, 67.891);
dt2.Rows.Add(677, 5000, 50.113);
dt2.Rows.Add(778, 5500, 12.672);
dt2.Rows.Add(779, 5500, 12.672);
var result = CompareDataTables(dt1, dt2);
result.Dump("Result");
}CompareDataTables方法:
static DataTable CompareDataTables(DataTable dt1, DataTable dt2)
{
var keyName = dt1.Columns.Cast<DataColumn>().Single (x => x.Unique).ColumnName;
var dt1Cols = dt1.Columns.Cast<DataColumn>().Where (x => !x.Unique).Select (x =>x.ColumnName );
var dt2Cols = dt1.Columns.Cast<DataColumn>().Where (x => !x.Unique).Select (x =>x.ColumnName );
// get keys from both data tables
var keys = new HashSet<int>(dt1.AsEnumerable().Select (x => (int)x[keyName]));
keys.UnionWith(dt2.AsEnumerable().Select (x => (int)x[keyName]));
keys.Dump("keys");
// create a new data table that will hold the results
var result = new DataTable();
result.Columns.Add(keyName, typeof(int));
result.Columns[0].Unique = true;
// initialize data and comparison columns
foreach (var name in dt1Cols)
{
result.Columns.Add(name + "_off", dt1.Columns[name].DataType);
result.Columns.Add(name + "_on", dt1.Columns[name].DataType);
result.Columns.Add(name + "_same", typeof(bool), name + "_off = " + name + "_on");
}
foreach (var key in keys)
{
// get a row from each data table with the current key
var rowOff = dt1.Select(keyName + " = " + key).FirstOrDefault();
var rowOn = dt2.Select(keyName + " = " + key).FirstOrDefault();
// create a new row
var newRow = result.NewRow();
// fill the new row with off data
if (rowOff != null)
{
newRow[keyName] = rowOff[keyName];
foreach (var name in dt1Cols)
{
newRow[name + "_off"] = rowOff[name];
}
}
// fill the new row with on data
if (rowOn != null)
{
foreach (var name in dt1Cols)
{
newRow[name + "_on"] = rowOn[name];
}
newRow[keyName] = rowOn[keyName];
}
// add the row to the result data table
result.Rows.Add(newRow);
}
return result;
}

这不是防弹的。最好检查数据表是否具有相同的结构。
发布于 2016-03-08 08:22:49
您可以先获取所有的键,然后创建一个新的Summary对象,将数据放在那里并让它进行比较。最后,你可以用它做你想做的事:
void Main()
{
var dt1 = new DataTable();
dt1.Columns.Add("emp_num", typeof(int));
dt1.Columns.Add("salary", typeof(int));
dt1.Columns.Add("ov", typeof(double));
dt1.Rows.Add(455, 3000, 67.891);
dt1.Rows.Add(677, 5000, 89.112);
dt1.Rows.Add(778, 6000, 12.672);
var dt2 = new DataTable();
dt2.Columns.Add("emp_num", typeof(int));
dt2.Columns.Add("salary", typeof(int));
dt2.Columns.Add("ov", typeof(double));
dt2.Rows.Add(455, 3000, 67.891);
dt2.Rows.Add(677, 5000, 50.113);
dt2.Rows.Add(778, 5500, 12.672);
dt2.Rows.Add(779, 5500, 12.672);
var keys = new HashSet<int>(dt1.AsEnumerable().Select (x => (int)x["emp_num"]));
keys.UnionWith(dt2.AsEnumerable().Select (x => (int)x["emp_num"]));
keys.Dump("emp_num (keys)");
var results = keys.Select (emp_num =>
{
var rowOff = dt1.Select("emp_num = " + emp_num).FirstOrDefault();
var rowOn = dt2.Select("emp_num = " + emp_num).FirstOrDefault();
return new Summary(emp_num, rowOff, rowOn);
});
results.Dump("Summary");
}Summary助手类:
class Summary
{
public Summary(int emp_num, DataRow rowOff, DataRow rowOn)
{
this.emp_num = emp_num;
if (rowOff != null)
{
salary_off = (int)rowOff["salary"];
ov_off = (double)rowOff["ov"];
}
if (rowOn != null)
{
salary_on = (int)rowOn["salary"];
ov_on = (double)rowOn["ov"];
}
}
public int emp_num;
public int salary_off ;
public int salary_on;
public bool salarySame { get { return salary_off == salary_on; } }
public double ov_off ;
public double ov_on;
public bool ovSame { get { return ov_off == ov_on; } }
}

发布于 2016-03-10 02:18:37
以下是完成任务的一种方法:
var dt1 = new DataTable();
dt1.Columns.Add("emp_num", typeof(int));
dt1.Columns.Add("salary", typeof(int));
dt1.Columns.Add("ov", typeof(double));
dt1.Columns[0].Unique = true;
dt1.Rows.Add(455, 3000, 67.891);
dt1.Rows.Add(677, 6000, 50.113);
dt1.Rows.Add(778, 5500, 12.650);
dt1.Rows.Add(779, 5500, 12.672);
var dt2 = new DataTable();
dt2.Columns.Add("emp_num", typeof(int));
dt2.Columns.Add("salary", typeof(int));
dt2.Columns.Add("ov", typeof(double));
dt2.Columns[0].Unique = true;
dt2.Rows.Add(455, 3000, 67.891);
dt2.Rows.Add(677, 5000, 50.113);
dt2.Rows.Add(778, 5500, 12.672);
dt2.Rows.Add(779, 5500, 12.672);
var dtListValues1 = new List<List<string>>();
for (int j = 0; j < dt2.Rows.Count; j++)
{
var list = new List<string>();
for (var i = 0; i < dt2.Columns.Count; i++)
{
list.Add(dt2.Rows[j][i].ToString());
list.Add("===");
list.Add(dt1.Rows[j][i].ToString());
list.Add("||");
if(dt2.Rows[j][i].ToString() == dt1.Rows[j][i].ToString())
{
list.Add("true");
}
else
{
list.Add("false");
}
}
dtListValues1.Add(list);
}
var rowsWithDifferentCells = dtListValues1.Where(x => x.Contains("false"));
foreach (var item in dtListValues1)
{
Console.WriteLine("Row-->> "+ string.Join(",",item));
}
Console.WriteLine("----------------------------------------");
foreach (var item in rowsWithDifferentCells)
{
Console.WriteLine("Row with different cell-->> "+string.Join(",", item));
}我将所有的测试数据放在一个List<List<string>>()中,在这个过程中我还检查了这些值是否相同。然后,我在false上设置了一个过滤器,只显示包含不同值的单元格的行。
您只需将这些代码放入ConsoleApp中即可。并测试它。
当然,这不是最好的方法,但它是检查数据的快速方法。
https://stackoverflow.com/questions/35817143
复制相似问题