Merge
(因此它并没有真正测试过)在之后出现了重复的主键,因为没有定义主键。因此,请指定PK或尝试我从头编写的此方法:
public static DataTable MergeAll(this IList<DataTable> tables, String primaryKeyColumn)
{
if (!tables.Any())
throw new ArgumentException("Tables must not be empty", "tables");
if(primaryKeyColumn != null)
foreach(DataTable t in tables)
if(!t.Columns.Contains(primaryKeyColumn))
throw new ArgumentException("All tables must have the specified primarykey column " + primaryKeyColumn, "primaryKeyColumn");
if(tables.Count == 1)
return tables[0];
DataTable table = new DataTable("TblUnion");
table.BeginLoadData();
foreach (DataTable t in tables)
{
table.Merge(t);
}
table.EndLoadData();
if (primaryKeyColumn != null)
{
var pkGroups = table.AsEnumerable()
.GroupBy(r => r[primaryKeyColumn]);
var dupGroups = pkGroups.Where(g => g.Count() > 1);
foreach (var grpDup in dupGroups)
{
DataRow firstRow = grpDup.First();
foreach (DataColumn c in table.Columns)
{
if (firstRow.IsNull(c))
{
DataRow firstNotNullRow = grpDup.Skip(1).FirstOrDefault(r => !r.IsNull(c));
if (firstNotNullRow != null)
firstRow[c] = firstNotNullRow[c];
}
}
var rowsToRemove = grpDup.Skip(1);
foreach(DataRow rowToRemove in rowsToRemove)
table.Rows.Remove(rowToRemove);
}
}
return table;
}
您可以这样调用它:
var tables = new[] { tblA, tblB, tblC };
DataTable TblUnion = tables.MergeAll("c1");
使用此示例数据:
var tblA = new DataTable();
tblA.Columns.Add("c1", typeof(int));
tblA.Columns.Add("c2", typeof(int));
tblA.Columns.Add("c3", typeof(string));
tblA.Columns.Add("c4", typeof(char));
var tblB = new DataTable();
tblB.Columns.Add("c1", typeof(int));
tblB.Columns.Add("c5", typeof(int));
tblB.Columns.Add("c6", typeof(string));
tblB.Columns.Add("c7", typeof(char));
var tblC = new DataTable();
tblC.Columns.Add("c1", typeof(int));
tblC.Columns.Add("c8", typeof(int));
tblC.Columns.Add("c9", typeof(string));
tblC.Columns.Add("c10", typeof(char));
tblA.Rows.Add(1, 8500, "abc", 'A');
tblA.Rows.Add(2, 950, "cde", 'B');
tblA.Rows.Add(3, 150, "efg", 'C');
tblA.Rows.Add(4, 850, "ghi", 'D');
tblA.Rows.Add(5, 50, "ijk", 'E');
tblB.Rows.Add(1, 7500, "klm", 'F');
tblB.Rows.Add(2, 900, "mno", 'G');
tblB.Rows.Add(3, 150, "opq", 'H');
tblB.Rows.Add(4, 850, "qrs", 'I');
tblB.Rows.Add(5, 50, "stu", 'J');
tblC.Rows.Add(1, 7500, "uvw", 'K');
tblC.Rows.Add(2, 900, "wxy", 'L');
tblC.Rows.Add(3, 150, "yza", 'M');
tblC.Rows.Add(4, 850, "ABC", 'N');
tblC.Rows.Add(5, 50, "CDE", 'O');
在
MergeAll
中使用
DataTable.Merge
后:
对MergeAll
进行了一些修改,以连接行:
更新
由于这个问题在评论中出现,如果两个表之间唯一的关系是DataRow
在表中的索引,并且您想按照索引合并两个表:
public static DataTable MergeTablesByIndex(DataTable t1, DataTable t2)
{
if (t1 == null || t2 == null) throw new ArgumentNullException("t1 or t2", "Both tables must not be null");
DataTable t3 = t1.Clone();
foreach (DataColumn col in t2.Columns)
{
string newColumnName = col.ColumnName;
int colNum = 1;
while (t3.Columns.Contains(newColumnName))
{
newColumnName = string.Format("{0}_{1}", col.ColumnName, ++colNum);
}
t3.Columns.Add(newColumnName, col.DataType);
}
var mergedRows = t1.AsEnumerable().Zip(t2.AsEnumerable(),
(r1, r2) => r1.ItemArray.Concat(r2.ItemArray).ToArray());
foreach (object[] rowFields in mergedRows)
t3.Rows.Add(rowFields);
return t3;
}
示例:
var dt1 = new DataTable();
dt1.Columns.Add("ID", typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt1.Rows.Add(1, "Jon");
var dt2 = new DataTable();
dt2.Columns.Add("Country", typeof(string));
dt2.Rows.Add("US");
var dtMerged = MergeTablesByIndex(dt1, dt2);
结果表包含三列
ID
,
Name
,
Country
和一行数据:
1 Jon US
。
DataTable.Union
。 - Tim SchmelterDispose
方法是为了继承而提供的,但 DataTable(或 DataSets)在构造函数中抑制终结,所以Dispose
方法不起作用。但无论如何,这并不会有太大影响。 - Tim Schmelter