C#中数据表的内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/665754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inner join of DataTables in C#
提问by Dhana
Let T1 and T2 are DataTables with following fields
让 T1 和 T2 是DataTable具有以下字段的 s
T1(CustID, ColX, ColY)
T2(CustID, ColZ)
I need the joint table
我需要联合表
TJ (CustID, ColX, ColY, ColZ)
How this can be done in C# code in a simple way? Thanks.
如何以简单的方式在 C# 代码中完成此操作？谢谢。
采纳答案by CZFox
If you are allowed to use LINQ, take a look at the following example. It creates two DataTables with integer columns, fills them with some records, join them using LINQ query and outputs them to Console.
如果您被允许使用 LINQ，请查看以下示例。它创建两个带有整数列的数据表，用一些记录填充它们，使用 LINQ 查询连接它们并将它们输出到控制台。
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
回答by Bognar
I wanted a function that would join tables without requiring you to define the columns using an anonymous type selector, but had a hard time finding any. I ended up having to make my own. Hopefully this will help anyone in the future who searches for this:
我想要一个可以连接表而不需要您使用匿名类型选择器定义列的函数，但是很难找到任何。我最终不得不自己做。希望这将有助于将来搜索此内容的任何人：
private DataTable JoinDataTables(DataTable t1, DataTable t2, params Func<DataRow, DataRow, bool>[] 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;
}
An example of how you might use this:
您可能如何使用它的示例：
var test = JoinDataTables(transactionInfo, transactionItems,
(row1, row2) =>
row1.Field<int>("TransactionID") == row2.Field<int>("TransactionID"));
One caveat: This is certainly not optimized, so be mindful when getting to row counts above 20k. If you know that one table will be larger than the other, try to put the smaller one first and the larger one second.
一个警告：这当然不是优化的，所以当行数超过 20k 时要注意。如果您知道一张桌子会比另一张大，请尝试将较小的放在第一位，然后将较大的放在第一位。
回答by Honza
This is my code. Not perfect, but working good. I hope it helps somebody:
这是我的代码。不完美，但工作良好。我希望它可以帮助某人：
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;
}
回答by Yakir Manor
this function will join 2 tables with a known join field, but this cannot allow 2 fields with the same name on both tables except the join field, a simple modification would be to save a dictionary with a counter and just add number to the same name filds.
此函数将连接具有已知连接字段的 2 个表，但是除了连接字段之外，这不允许两个表上具有相同名称的 2 个字段，一个简单的修改是保存一个带有计数器的字典，然后将数字添加到相同的名称字段。
public static DataTable JoinDataTable(DataTable dataTable1, DataTable dataTable2, string joinField)
{
var dt = new DataTable();
var joinTable = from t1 in dataTable1.AsEnumerable()
join t2 in dataTable2.AsEnumerable()
on t1[joinField] equals t2[joinField]
select new { t1, t2 };
foreach (DataColumn col in dataTable1.Columns)
dt.Columns.Add(col.ColumnName, typeof(string));
dt.Columns.Remove(joinField);
foreach (DataColumn col in dataTable2.Columns)
dt.Columns.Add(col.ColumnName, typeof(string));
foreach (var row in joinTable)
{
var newRow = dt.NewRow();
newRow.ItemArray = row.t1.ItemArray.Union(row.t2.ItemArray).ToArray();
dt.Rows.Add(newRow);
}
return dt;
}
回答by Antikiller
I tried to do this in next way
我试图以下一种方式做到这一点
public static DataTable JoinTwoTables(DataTable innerTable, DataTable outerTable)
{
DataTable resultTable = new DataTable();
var innerTableColumns = new List<string>();
foreach (DataColumn column in innerTable.Columns)
{
innerTableColumns.Add(column.ColumnName);
resultTable.Columns.Add(column.ColumnName);
}
var outerTableColumns = new List<string>();
foreach (DataColumn column in outerTable.Columns)
{
if (!innerTableColumns.Contains(column.ColumnName))
{
outerTableColumns.Add(column.ColumnName);
resultTable.Columns.Add(column.ColumnName);
}
}
for (int i = 0; i < innerTable.Rows.Count; i++)
{
var row = resultTable.NewRow();
innerTableColumns.ForEach(x =>
{
row[x] = innerTable.Rows[i][x];
});
outerTableColumns.ForEach(x =>
{
row[x] = outerTable.Rows[i][x];
});
resultTable.Rows.Add(row);
}
return resultTable;
}
