public class DataSetCompare
{
SqlConnection _Comm = null;
DataSet dsEmployee = null;
DataSet dsEmployee_old = null;
public DataSetCompare()
{
_Comm = new SqlConnection("Data Source=10.10.10.56;Initial Catalog=PSAData;Persist Security Info=True;User ID=sa;Password=PWD27sjs");
SqlDataAdapter da1 = new SqlDataAdapter("select top 50 emp_id,emp_EmployeeNo,emp_EmployeeName,emp_EnglishName,emp_mobile,emp_Gender,emp_Birthday,emp_DomainName,emp_ComeDate from dbo.mng_Employee", _Comm);
dsEmployee = new DataSet();
da1.Fill(dsEmployee);
SqlDataAdapter da2 = new SqlDataAdapter("select top 50 emp_id,emp_EmployeeNo,emp_EmployeeName,emp_EnglishName,emp_mobile,emp_Gender,emp_Birthday,emp_DomainName,emp_ComeDate from dbo.mng_Employee_old", _Comm);
dsEmployee_old = new DataSet();
da2.Fill(dsEmployee_old);
}
public DataTable GetDataSetCompare(DataSet ds1, DataSet ds2)
{
DataTable dtCompareResult = new DataTable();
dtCompareResult.Columns.Add("emp_EmployeeNo");
dtCompareResult.Columns.Add("emp_EmployeeName");
dtCompareResult.Columns.Add("emp_EnglishName");
dtCompareResult.Columns.Add("emp_mobile");
dtCompareResult.Columns.Add("emp_Gender");
dtCompareResult.Columns.Add("emp_Birthday");
dtCompareResult.Columns.Add("emp_DomainName");
dtCompareResult.Columns.Add("emp_ComeDate");
dtCompareResult.Columns.Add("emp_EmployeeNo2");
dtCompareResult.Columns.Add("emp_EmployeeName2");
dtCompareResult.Columns.Add("emp_EnglishName2");
dtCompareResult.Columns.Add("emp_mobile2");
dtCompareResult.Columns.Add("emp_Gender2");
dtCompareResult.Columns.Add("emp_Birthday2");
dtCompareResult.Columns.Add("emp_DomainName2");
dtCompareResult.Columns.Add("emp_ComeDate2");
dtCompareResult.Columns.Add("Flag");
int i = 0;
foreach (DataRow row in ds1.Tables[0].Rows)
{
DataRowView drv = null;
string Flag = string.Empty;
if (!IsCompare(ds2.Tables[0], row, "emp_EmployeeName", ref drv, ref Flag))
{
//加到存在且不相同的记录
DataRow dr = null;
dr = dtCompareResult.NewRow();
dr["emp_EmployeeNo"] = row["emp_EmployeeNo"];
dr["emp_EmployeeName"] = row["emp_EmployeeName"];
dr["emp_EnglishName"] = row["emp_EnglishName"];
dr["emp_mobile"] = row["emp_mobile"];
dr["emp_Gender"] = row["emp_Gender"];
dr["emp_Birthday"] = row["emp_Birthday"];
dr["emp_DomainName"] = row["emp_DomainName"];
dr["emp_ComeDate"] = row["emp_ComeDate"];
if (drv != null)
{
dr["emp_EmployeeNo2"] = drv.Row["emp_EmployeeNo"];
dr["emp_EmployeeName2"] = drv.Row["emp_EmployeeName"];
dr["emp_EnglishName2"] = drv.Row["emp_EnglishName"];
dr["emp_mobile2"] = drv.Row["emp_mobile"];
dr["emp_Gender2"] = drv.Row["emp_Gender"];
dr["emp_Birthday2"] = drv.Row["emp_Birthday"];
dr["emp_DomainName2"] = drv.Row["emp_DomainName"];
dr["emp_ComeDate2"] = drv.Row["emp_ComeDate"];
}
dr["Flag"] = Flag;
dtCompareResult.Rows.Add(dr);
}
if (!isExist(ds2.Tables[0], row, "emp_EmployeeName"))
{
//加到表1存在表2不存在的记录
DataRow dr = null;
dr = dtCompareResult.NewRow();
dr["emp_EmployeeNo"] = row["emp_EmployeeNo"];
dr["emp_EmployeeName"] = row["emp_EmployeeName"];
dr["emp_EnglishName"] = row["emp_EnglishName"];
dr["emp_mobile"] = row["emp_mobile"];
dr["emp_Gender"] = row["emp_Gender"];
dr["emp_Birthday"] = row["emp_Birthday"];
dr["emp_DomainName"] = row["emp_DomainName"];
dr["emp_ComeDate"] = row["emp_ComeDate"];
dtCompareResult.Rows.Add(dr);
}
i++;
if (i > 50) break;
}
int j = 0;
foreach (DataRow row in ds2.Tables[0].Rows)
{
if (!isExist(ds1.Tables[0], row, "emp_EmployeeName"))
{
//加到表2存在表1不存在的记录
DataRow dr = null;
dr = dtCompareResult.NewRow();
dr["emp_EmployeeNo2"] = row["emp_EmployeeNo"];
dr["emp_EmployeeName2"] = row["emp_EmployeeName"];
dr["emp_EnglishName2"] = row["emp_EnglishName"];
dr["emp_mobile2"] = row["emp_mobile"];
dr["emp_Gender2"] = row["emp_Gender"];
dr["emp_Birthday2"] = row["emp_Birthday"];
dr["emp_DomainName2"] = row["emp_DomainName"];
dr["emp_ComeDate2"] = row["emp_ComeDate"];
dtCompareResult.Rows.Add(dr);
}
j++;
if (j > 50) break;
}
return dtCompareResult;
}
public DataTable GetDataTable()
{
DataTable dt = GetDataSetCompare(dsEmployee, dsEmployee_old);
return dt;
}
/// <summary>
/// 查询该行是否于目标数据库对于行匹配true 匹配 FALSE 不匹配
/// </summary>
/// <param name="dt">目标数据表</param>
/// <param name="dr">当前数据行</param>
/// <param name="PrmKeyName">主键名称</param>
/// <returns>true 匹配 FALSE 不匹配</returns>
public static bool IsCompare(DataTable dt, DataRow dr, string PrmKeyName, ref DataRowView drv,ref string Flag)
{
bool rtnFlag = true;
DataView dv = dt.DefaultView;
dv.RowFilter = PrmKeyName + " = '" + dr[PrmKeyName].ToString().Trim() + "'";
if (dv.Count > 0)
{
drv = dv[0];
if (dv[0].Row[1].ToString() != dr[1].ToString())
{
rtnFlag = false;
Flag = "1";
}
if (dv[0].Row[2].ToString() != dr[2].ToString())
{
rtnFlag = false;
Flag += "-2";
}
if (dv[0].Row[3].ToString() != dr[3].ToString())
{
rtnFlag = false;
Flag += "-3";
}
if (dv[0].Row[4].ToString() != dr[4].ToString())
{
rtnFlag = false;
Flag += "-4";
}
if (dv[0].Row[5].ToString() != dr[5].ToString())
{
rtnFlag = false;
Flag += "-5";
}
if (dv[0].Row[6].ToString() != dr[6].ToString())
{
rtnFlag = false;
Flag += "-6";
}
if (dv[0].Row[7].ToString() != dr[7].ToString())
{
rtnFlag = false;
Flag += "-7";
}
if (dv[0].Row[8].ToString() != dr[8].ToString())
{
rtnFlag = false;
Flag += "-8";
}
}
return rtnFlag;
}
/// <summary>
/// 判断是否存在
/// </summary>
/// <param name="dt"></param>
/// <param name="dr"></param>
/// <param name="PrmKeyName"></param>
/// <returns></returns>
public static bool isExist(DataTable dt, DataRow dr, string PrmKeyName)
{
bool rtnFlag = false;
DataView dv = dt.DefaultView;
dv.RowFilter = PrmKeyName + " = '" + dr[PrmKeyName].ToString().Trim() + "'";
if (dv.Count > 0)
{
rtnFlag=true;
}
return rtnFlag;
}
}