下面的方法是查询两张表,并通过DEPT_CODE字段关联,将符合条件的数据查出来,并返回
private DataTable JoinDataTables(DataTable dt_A, DataTable dt_B, params Func<DataRow, DataRow, bool>[] joinOn)
{
DataTable dtResult = new DataTable();
foreach (DataColumn col in dt_A.Columns)
{
if (dtResult.Columns[col.ColumnName] == null)
dtResult.Columns.Add(col.ColumnName, col.DataType);
}
foreach (DataColumn col in dt_B.Columns)
{
if (dtResult.Columns[col.ColumnName] == null)
dtResult.Columns.Add(col.ColumnName, col.DataType);
}
foreach (DataRow rowA in dt_A.Rows)
{
var joinRows = dt_B.AsEnumerable().Where(rowB =>
{
foreach (var parameter in joinOn)
{
if (!parameter(rowA, rowB)) return false;
}
return true;
});
bool flag = false;
foreach (DataRow fromRow in joinRows)
{
flag = true;
DataRow insertRow = dtResult.NewRow();
foreach (DataColumn colA in dt_A.Columns)
{
insertRow[colA.ColumnName] = rowA[colA.ColumnName];
}
foreach (DataColumn colB in dt_B.Columns)
{
insertRow[colB.ColumnName] = fromRow[colB.ColumnName];
}
dtResult.Rows.Add(insertRow);
}
//在dt_B中不存在
if (!flag)
{
DataRow insertRow = dtResult.NewRow();
foreach (DataColumn colA in dt_A.Columns)
{
insertRow[colA.ColumnName] = rowA[colA.ColumnName];
}
dtResult.Rows.Add(insertRow);
}
}
return dtResult;
}
这里是调用查询结果
private DataTable resultTable;
public DataTable gettable(DataTable tab1, DataTable tab2)
{
bool need = false;
var vResult = JoinDataTables(tab1, tab2, (rowA, rowB) => rowA.Field<string>("DEPT_CODE") == rowB.Field<string>("DEPT_CODE"));
resultTable = (DataTable)vResult;
foreach (DataRow row in resultTable.Rows)
{
if (!string.IsNullOrEmpty(row[resultTable.Columns.Count - 2].ToString()))
{
string col = row[resultTable.Columns.Count - 2].ToString();
need = true;
break;
}
}
if (need)
{
resultTable = JoinDataTables(resultTable, tab2, (rowA, rowB) => rowA.Field<string>("DEPT_CODE") == rowB.Field<string>("DEPT_CODE"));
}
return resultTable;
}