/// <summary>
/// 比较两个DataTable数据
/// </summary>
/// <param name="dtSource">主库数据</param>
/// <param name="dtSrc">目标库数据</param>
/// <param name="dtDiff">对比结果</param>
/// <param name="defaultKey">对比表主键</param>
/// <param name="keyFields">对比表字段</param>
public static void CompareTable(DataTable dtSource, DataTable dtSrc, out DataTable dtDiff, string defaultKey, List <string> keyFields)
{
//源记录集与目标记录集有一个为null则退出
if (dtSource == null || dtSrc == null)
{
dtDiff = null;
return;
}
//keyFields.ForEach(t => dtSource.Columns.Remove(t.ToString()));
//keyFields.ForEach(t => dtSrc.Columns.Remove(t.ToString()));
foreach (string t in keyFields)
{
if (dtSource.Columns.Contains(t.ToString()))
{
dtSource.Columns.Remove(t.ToString());
}
if (dtSrc.Columns.Contains(t.ToString()))
{
dtSrc.Columns.Remove(t.ToString());
}
}
#region 排除表差异
List <String> listSourceColumns = new List <string>();
foreach (var sourceColumns in dtSource.Columns)
{
listSourceColumns.Add(sourceColumns.ToString());
}
List <String> listSrcColumns = new List <string>();
foreach (var srcColumns in dtSrc.Columns)
{
listSrcColumns.Add(srcColumns.ToString());
}
var listDiffSource = listSourceColumns.Except(listSrcColumns);
var listDiffSrc = listSrcColumns.Except(listSourceColumns);
listDiffSource.ToList().ForEach(t => dtSource.Columns.Remove(t.ToString()));
listDiffSrc.ToList().ForEach(t => dtSrc.Columns.Remove(t.ToString()));
#endregion
dtDiff = dtSource.Clone();
object oSource;
object oSrc;
for (int i = 0; i < dtDiff.Columns.Count; i++)
{
dtSrc.Columns[dtDiff.Columns[i].ColumnName].SetOrdinal(i);
}
List <String> removeProps = new List <string>();
//增加和修改的情况只需要检查主库
foreach (DataRow item in dtSource.Rows)
{
string filterQuery = string.Empty;
var keyArray = defaultKey.Split(',');
if (keyArray.Length == 1 && keyArray[0] != string.Empty)
{
filterQuery = string.Format("{0}='{1}'", defaultKey, item[0]);
}
else
{
for (int i = 0; i < keyArray.Length; i++)
{
if (keyArray[i] != string.Empty)
{
filterQuery += string.Format("{0}='{1}' And ", keyArray[i], item[i]);
}
}
filterQuery += "1=1";
}
var hasRow = dtSrc.Select(filterQuery);
CheckAction checkAction = CheckAction.Equal;
if (hasRow.Count() > 0)
{
//循环比对列值
foreach (var itemColumns in dtSource.Columns)
{
//获得列值
oSource = item[itemColumns.ToString()];
oSrc = hasRow[0][itemColumns.ToString()];
if (oSource != null && oSrc != null)
{
if (oSource.ToString() != oSrc.ToString())
{
checkAction = CheckAction.Update;
break;//某一列不同就跳出
}
}
else if (!oSource.Equals(oSrc))
{
checkAction = CheckAction.Update;
break;//某一列不同就跳出
}
}
}
else
{
checkAction = CheckAction.Add;
}
//不相等的才写入差异
if (!checkAction.Equals(CheckAction.Equal))
{
item["CheckAction"] = checkAction.ToString();
dtDiff.Rows.Add(item.ItemArray);
}
}
foreach (var removeProp in removeProps.Distinct())
{
dtDiff.Columns.Remove(removeProp);
}
//删除需要以子库为主
foreach (DataRow item in dtSrc.Rows)
{
string filterQuery = string.Empty;
var keyArray = defaultKey.Split(',');
if (keyArray.Length == 1 && keyArray[0] != string.Empty)
{
filterQuery = string.Format("{0}='{1}'", defaultKey, item[0]);
}
else
{
for (int i = 0; i < keyArray.Length; i++)
{
if (keyArray[i] != string.Empty)
{
filterQuery += string.Format("{0}='{1}' And ", keyArray[i], item[i]);
}
}
filterQuery += "1=1";
}
var hasRow = dtSource.Select(filterQuery);
if (hasRow.Count() < 1)
{
item["CheckAction"] = CheckAction.Delete.ToString();
dtDiff.Rows.Add(item.ItemArray);
}
}
}