将具有许多列的2个数据表联接 [英] Join 2 DataTables with many columns

问题描述

我有一个问题，想将2个表与同一列连接在一起.表1具有名称"，姓氏"列和许多其他列，表2具有名称"，注释"和许多其他列.我想将它们与名称"列一起加入，并且结果"应该是名称"，姓氏"，注释"和其他列.我尝试使用外部左Linq，但不知道如何编写select new，因为不知道我还有多少其他列.

I have a question, want to join 2 tables with same column. Table 1 has Name, LastName Columns and many other columns, Table 2 has Name, Comment and many other Columns. I want to join them with Name column and as Result should be Name, LastName, Comment and other Columns. I tried with outer left Linq but don't know how to write select new because don't know how many other columns i have.

我的表1:

Name1   LastName ...
Niki   Row      ...
Hube   Slang    ...
Koke   Mi       ... 
...    ...      ...
...    ...      ...

表2:

Name  Comment   ...
Koke   "Hello"  ...
Niki   "Hi"     ...

结果应为:

Name   LastName   Comment ...
Niki    Row        "Hi"   ...
Hube    Sland             ...
Koke    Mi         "Hello"...
...     ...               ...

所以我试图将行彼此连接.但是它说table1的数组比table 2的数组长.是否有另一种连接方法?

So i tried to concat the rows with each others. But it said that the array from table1 is longer than array from table 2. Is there another way to join it?

        foreach (DataRow tbE in Table1.Rows)
        {
            foreach (DataRow opT in Table2.Rows)
            {
                if (tbE["Name"].ToString() == opT["Name"].ToString())
                {
                    var row = Result.NewRow();
                    row.ItemArray = tbE.ItemArray
                                       .Concat(opT.ItemArray).ToArray();

                    Result.Rows.Add(row);
                }
                else
                    Result.ImportRow(tbE);

            }
        } 
        Result.Columns.Remove(Name); 

推荐答案

您可以在这里使用此方法，我最近从头开始为 DataTable.Merge 方法:

You could use this method here which i've written from scratch recently for SO for another question(so it's not really tested). It allows to merge multiple tables by a common key. If no key is specified it will just use the default DataTable.Merge method:

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(); // Turns off notifications, index maintenance, and constraints while loading data
    foreach (DataTable t in tables)
    {
        table.Merge(t); // same as table.Merge(t, false, MissingSchemaAction.Add);
    }
    table.EndLoadData();

    if (primaryKeyColumn != null)
    {
        // since we might have no real primary keys defined, the rows now might have repeating fields
        // so now we're going to "join" these rows ...
        var pkGroups = table.AsEnumerable()
            .GroupBy(r => r[primaryKeyColumn]);
        var dupGroups = pkGroups.Where(g => g.Count() > 1);
        foreach (var grpDup in dupGroups)
        { 
            // use first row and modify it
            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];
                }
            }
            // remove all but first row
            var rowsToRemove = grpDup.Skip(1);
            foreach(DataRow rowToRemove in rowsToRemove)
                table.Rows.Remove(rowToRemove);
        }
    }

    return table;
}

您可以通过以下方式调用它:

You can call it in this way:

var tables = new[] { Table1, Table2 };
tables.MergeAll("Name");

编辑:这是调试器的屏幕截图，其中包含您的示例数据:

Edit: here's the screenshot from the debugger with your sample-data:

因此有效:)

在此处采样数据并进行测试:

Sample data and test here:

var Table1 = new DataTable();
var Table2 = new DataTable();
Table1.Columns.Add("Name");
Table1.Columns.Add("LastName");

Table2.Columns.Add("Name");
Table2.Columns.Add("Comment");

Table1.Rows.Add("Niki", "Row");
Table1.Rows.Add("Hube", "Slang");
Table1.Rows.Add("Koke", "Mi");

Table2.Rows.Add("Koke", "Hello");
Table2.Rows.Add("Niki", "Hi");

var tables = new DataTable[] { Table1, Table2 };
DataTable merged = tables.MergeAll("Name");

这篇关于将具有许多列的2个数据表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！