C#中DataTables的内部联接

设T1和T2是DataTable以下字段

T1(CustID, ColX, ColY)

T2(CustID, ColZ)

我需要联合表

TJ (CustID, ColX, ColY, ColZ)

如何在C#代码中以简单的方式完成此操作？谢谢.

1> CZFox..：

---

如果允许使用LINQ,请查看以下示例.它创建两个带有整数列的DataTables,用一些记录填充它们,使用LINQ查询连接它们并将它们输出到Console.

    DataTable dt1 = new DataTable();
    dt1.Columns.Add("CustID", typeof(int));
    dt1.Columns.Add("ColX", typeof(int));
    dt1.Columns.Add("ColY", typeof(int));

    DataTable dt2 = new DataTable();
    dt2.Columns.Add("CustID", typeof(int));
    dt2.Columns.Add("ColZ", typeof(int));

    for (int i = 1; i <= 5; i++)
    {
        DataRow row = dt1.NewRow();
        row["CustID"] = i;
        row["ColX"] = 10 + i;
        row["ColY"] = 20 + i;
        dt1.Rows.Add(row);

        row = dt2.NewRow();
        row["CustID"] = i;
        row["ColZ"] = 30 + i;
        dt2.Rows.Add(row);
    }

    var results = from table1 in dt1.AsEnumerable()
                 join table2 in dt2.AsEnumerable() on (int)table1["CustID"] equals (int)table2["CustID"]
                 select new
                 {
                     CustID = (int)table1["CustID"],
                     ColX = (int)table1["ColX"],
                     ColY = (int)table1["ColY"],
                     ColZ = (int)table2["ColZ"]
                 };
    foreach (var item in results)
    {
        Console.WriteLine(String.Format("ID = {0}, ColX = {1}, ColY = {2}, ColZ = {3}", item.CustID, item.ColX, item.ColY, item.ColZ));
    }
    Console.ReadLine();

// Output:
// ID = 1, ColX = 11, ColY = 21, ColZ = 31
// ID = 2, ColX = 12, ColY = 22, ColZ = 32
// ID = 3, ColX = 13, ColY = 23, ColZ = 33
// ID = 4, ColX = 14, ColY = 24, ColZ = 34
// ID = 5, ColX = 15, ColY = 25, ColZ = 35

---

这最终对我有用,但我花了一个多小时,除了"Specified cast not valid",质疑我的理智,直到我将它从(int)table1 ["field"]更改为Convert.ToInt32(table1 [ "领域"]).

2> Bognar..：

---

我想要一个连接表的函数,而不需要使用匿名类型选择器定义列,但很难找到任何.我最终不得不自己做.希望这将有助于将来搜索此内容的任何人:

private DataTable JoinDataTables(DataTable t1, DataTable t2, params Func[] joinOn)
{
    DataTable result = new DataTable();
    foreach (DataColumn col in t1.Columns)
    {
        if (result.Columns[col.ColumnName] == null)
            result.Columns.Add(col.ColumnName, col.DataType);
    }
    foreach (DataColumn col in t2.Columns)
    {
        if (result.Columns[col.ColumnName] == null)
            result.Columns.Add(col.ColumnName, col.DataType);
    }
    foreach (DataRow row1 in t1.Rows)
    {
        var joinRows = t2.AsEnumerable().Where(row2 =>
            {
                foreach (var parameter in joinOn)
                {
                    if (!parameter(row1, row2)) return false;
                }
                return true;
            });
        foreach (DataRow fromRow in joinRows)
        {
            DataRow insertRow = result.NewRow();
            foreach (DataColumn col1 in t1.Columns)
            {
                insertRow[col1.ColumnName] = row1[col1.ColumnName];
            }
            foreach (DataColumn col2 in t2.Columns)
            {
                insertRow[col2.ColumnName] = fromRow[col2.ColumnName];
            }
            result.Rows.Add(insertRow);
        }
    }
    return result;
}

您可以如何使用此示例:

var test = JoinDataTables(transactionInfo, transactionItems,
               (row1, row2) =>
               row1.Field("TransactionID") == row2.Field("TransactionID"));

一个警告:这当然没有优化,所以在获得超过20k的行数时要小心.如果您知道一个表将比另一个表大,请尝试将较小的表放在第一个,将较大的表放在第二个表中.

3> 小智..：

---

这是我的代码.不完美,但工作得很好.我希望它对某人有所帮助:

    static System.Data.DataTable DtTbl (System.Data.DataTable[] dtToJoin)
    {
        System.Data.DataTable dtJoined = new System.Data.DataTable();

        foreach (System.Data.DataColumn dc in dtToJoin[0].Columns)
            dtJoined.Columns.Add(dc.ColumnName);

        foreach (System.Data.DataTable dt in dtToJoin)
            foreach (System.Data.DataRow dr1 in dt.Rows)
            {
                System.Data.DataRow dr = dtJoined.NewRow();
                foreach (System.Data.DataColumn dc in dtToJoin[0].Columns)
                    dr[dc.ColumnName] = dr1[dc.ColumnName];

                dtJoined.Rows.Add(dr);
            }

        return dtJoined;
    }