c# - 基于列将两个数据表合并为一个
问题描述
有人问过这个问题，但我的方法不同链接 1
l 有一个包含以下数据的数据表
DataTable dtProduct = new DataTable();
dtProduct.Columns.Add("productId");
dtProduct.Columns.Add("name");
DataRow dataRow = dtProduct.NewRow();
dataRow["productId"] = 1;
dataRow["name"] = "Burger";
dtProduct.Rows.Add(dataRow);
DataRow dataRow2 = dtProduct.NewRow();
dataRow2["productId"] = 2;
dataRow2["name"] = "Chicken";
dtProduct.Rows.Add(dataRow2);
DataTable dtSales = new DataTable();
dtSales.Columns.Add("productId");
dtSales.Columns.Add("saleId");
dtSales.Columns.Add("month");
dtSales.Columns.Add("quantity");
DataRow dataSalesRow = dtSales.NewRow();
dataSalesRow["productId"] = 1;
dataSalesRow["saleId"] = "1";
dataSalesRow["month"] = "Jan";
dataSalesRow["quantity"] = 3433;
dtSales.Rows.Add(dataSalesRow);
DataRow drSales2 = dtSales.NewRow();
drSales2["productId"] = 1;
drSales2["saleId"] = "2";
drSales2["month"] = "Feb";
drSales2["quantity"] = 56;
dtSales.Rows.Add(drSales2);
DataRow drSales3 = dtSales.NewRow();
drSales3["productId"] = 1;
drSales3["saleId"] = "3";
drSales3["month"] = "Mar";
drSales3["quantity"] = 34522;
dtSales.Rows.Add(drSales3);
DataRow drSales4 = dtSales.NewRow();
drSales4["productId"] = 2;
drSales4["saleId"] = "4";
drSales4["month"] = "Feb";
drSales4["quantity"] = 345;
dtSales.Rows.Add(drSales4);
另一个样本 2
DataTable dtStudents = new DataTable();
dtStudents.Columns.Add("studentId");
dtStudents.Columns.Add("fullname");
DataRow drStudentrow = dtStudents.NewRow();
drStudentrow["studentId"] = 1;
drStudentrow["fullname"] = "Bil";
dtStudents.Rows.Add(drStudentrow);
DataRow drStudentrow2 = dtStudents.NewRow();
drStudentrow2["studentId"] = 2;
drStudentrow2["fullname"] = "Paul";
dtStudents.Rows.Add(drStudentrow2);
DataTable dtStudentExam = new DataTable();
dtStudentExam.Columns.Add("studentId");
dtStudentExam.Columns.Add("subjectId");
dtStudentExam.Columns.Add("mark");
DataRow dataStudentExamRow = dtStudentExam.NewRow();
dataStudentExamRow["studentId"] = 1;
dataStudentExamRow["subjectId"] = "E123";
dataStudentExamRow["mark"] = 34;
dtStudentExam.Rows.Add(dataStudentExamRow);
DataRow dataStudentExamRow2 = dtStudentExam.NewRow();
dataStudentExamRow2["studentId"] = 2;
dataStudentExamRow2["subjectId"] = "E123";
dataStudentExamRow2["mark"] = 90;
dtStudentExam.Rows.Add(dataStudentExamRow2);
DataRow dataStudentExamRow3 = dtStudentExam.NewRow();
dataStudentExamRow3["studentId"] = 1;
dataStudentExamRow3["subjectId"] = "E155";
dataStudentExamRow3["mark"] = 78;
dtStudentExam.Rows.Add(dataStudentExamRow3);
DataRow dataStudentExamRow4 = dtStudentExam.NewRow();
dataStudentExamRow4["studentId"] = 1;
dataStudentExamRow4["subjectId"] = "E101";
dataStudentExamRow4["mark"] = 12;
dtStudentExam.Rows.Add(dataStudentExamRow4);
DataRow dataStudentExamRow5 = dtStudentExam.NewRow();
dataStudentExamRow5["studentId"] = 1;
dataStudentExamRow5["subjectId"] = "E234";
dataStudentExamRow5["mark"] = 42;
dtStudentExam.Rows.Add(dataStudentExamRow5);
DataTable products( dtProduct ) 有一个 productId，它是键，另一个数据表 sales ( dtSales ) 有基于 month 的产品销售额。并有一列 productId 。
我想要实现的是将这两个表合并到这个
同样适用于学生
我有一个学生数据表 ( dtStudents ) 但是我有每个科目的学生考试成绩数据表 ( dtStudentExam )
并结合到这样的东西
所以我的问题是我可以动态地做到这一点。我尝试了以下
public static System.Data.DataTable MergeRowsToColumns(DataTable rowDataTable, string rowDataTableIdColumnName, string friendlyName, DataTable columData)
{
List<string> columnsToAdd = new List<string>();
if (rowDataTable == null)
{
return null;
}
DataTable finalDataTable = new DataTable();
finalDataTable.Columns.Add(friendlyName.ToLower());
finalDataTable.Columns.Add(rowDataTableIdColumnName.ToLower());
foreach (DataColumn column in columData.Columns)
{
if (column.ColumnName.ToString() == rowDataTableIdColumnName.ToString())
{
continue;
}
else
{
finalDataTable.Columns.Add(column.ColumnName.ToString());
columnsToAdd.Add(column.ColumnName.ToString());
}
}
foreach (DataRow row in rowDataTable.Rows)
{
DataRow newRow = finalDataTable.NewRow();
newRow[rowDataTableIdColumnName] = row[rowDataTableIdColumnName];
newRow[friendlyName] = row[friendlyName];
foreach (DataRow columnRows in columData.Rows)
{
foreach (string column in columnsToAdd)
{
var value = columnRows[column].ToString();
newRow[column] = value;
}
}
finalDataTable.Rows.Add(newRow);
}
return finalDataTable;
}
算法逻辑 表 1 保存关键信息 表 2 保存表的值数据
使用表 1中的列名作为 id，与表 2相同
我可以在一个函数中通用地做到这一点，比如
var dtMergedProducts = HelperDataTable.MergeRowsToColumns(dtProduct, "productId", "name", dtSales);
var dtStudents = HelperDataTable.MergeRowsToColumns(dtStudents, "studentId", "fullname", dtStudentExam);
请按原样检索数据，因此无法在表 2 中分组，并且表 2 没有将显示的友好名称。
加载的大部分数据不超过 100 000
编辑一
DataTable 2 将具有列值，一个用于列，另一个用于值例如 dtSales
1 个月：垂直显示
2-数量：将是单元格值
样品 2
dt学生考试
1- subjectId ：垂直显示
2- 标记：将是学生标记
解决方案
我今天早上开始工作了
这里如果方法
public static System.Data.DataTable MergeRowsToColumns(DataTable rowDataTable, string rowDataTableIdColumnName, string friendlyName, DataTable dataAllValue , string dataColumnValue , string dataColumnKey)
{
List<string> columnsToAdd = new List<string>();
if (rowDataTable == null)
{
return null;
}
DataTable finalDataTable = new DataTable();
finalDataTable.Columns.Add(friendlyName.ToLower());
finalDataTable.Columns.Add(rowDataTableIdColumnName.ToLower());
foreach (DataRow row in dataAllValue.Rows)
{
if (row[rowDataTableIdColumnName].ToString() == rowDataTableIdColumnName.ToString())
{
continue;
}
else
{
var isExistColumnValue = columnsToAdd.Where(c => c == row[dataColumnKey].ToString()).FirstOrDefault();
if(isExistColumnValue == null)
{
columnsToAdd.Add(row[dataColumnKey].ToString());
finalDataTable.Columns.Add(row[dataColumnKey].ToString());
}
}
}
foreach (DataRow row in rowDataTable.Rows)
{
DataRow newRow = finalDataTable.NewRow();
newRow[rowDataTableIdColumnName] = row[rowDataTableIdColumnName];
newRow[friendlyName] = row[friendlyName];
foreach (DataRow columnRows in dataAllValue.Rows)
{
if(row[rowDataTableIdColumnName].ToString() != columnRows[rowDataTableIdColumnName].ToString())
{
continue;
}
var columnName = columnRows[dataColumnKey].ToString();
var columnValue = columnRows[dataColumnValue].ToString();
newRow[columnName] = columnValue;
}
finalDataTable.Rows.Add(newRow);
}
return finalDataTable;
}
看起来我在合并之前未能添加列名
你可以打电话
var dt = HelperDataTable.MergeRowsToColumns(dtProduct, "productId", "name", dtSales , "quantity", "month");
var dt2 = HelperDataTable.MergeRowsToColumns(dtStudents, "studentId", "fullname", dtStudentExam , "mark", "subjectId");