比較兩個資料表,並產生出新增、刪除、修改三張資料表
private void outputDataTable(DataTable dt1,DataTable dt2,string keyword,DataTable dtDel,DataTable dtIns,DataTable dtUpd) {
//<DELETE>
//假設新資料為 1 2 4
//舊資料為 1 2 3 4
//把舊資料複製至C資料表
//C資料表刪除1 2 4 剩下3
//3 為真正要刪除的資料
//使用者在畫面上操作的資料表為 dt2
//資料庫查詢的原始資料表為 dt1 (原始資料)
//將可變動資料表的 keyword 串起來
//去原始資料表過濾，條件為NOT IN
string strKeywordDelete = "";
for (int i = 0; i < dt2.Rows.Count; i++)
{
if (i == dt2.Rows.Count - 1)
strKeywordDelete = strKeywordDelete + "'" + dt2.Rows[i][keyword] + "'";
else
strKeywordDelete = strKeywordDelete + "'" + dt2.Rows[i][keyword] + "',";
}
if (!strKeywordDelete.Equals(""))
{
string _RowFilter = keyword +" NOT IN (" + strKeywordDelete + ")";
dtDel.Clear();
dtDel = dt1.Copy();
dtDel.DefaultView.RowFilter = _RowFilter;
dtDel = dtDel.DefaultView.ToTable();
}
if (dt2.Rows.Count == 0)
{
dtDel.Clear();
dtDel = dt1.Copy();
}
//<INSERT>
//將原始資料表的keyword串起來
//去可變動資料表過濾，條件為NOT IN
string strKeywordInsert = "";
for (int i = 0; i < dt1.Rows.Count; i++)
{
if (i == dt1.Rows.Count - 1)
{
strKeywordInsert = strKeywordInsert + "'" + dt1.Rows[i][keyword] + "'";
}
else
{
strKeywordInsert = strKeywordInsert + "'" + dt1.Rows[i][keyword] + "',";
}
}
if (!strKeywordInsert.Equals(""))
{
string _RowFilter = keyword+" NOT IN (" + strKeywordInsert + ")";
dtIns.Clear();
dtIns = dt2.Copy();
dtIns.DefaultView.RowFilter = _RowFilter;
dtIns = dtIns.DefaultView.ToTable();
}
if (dt1.Rows.Count == 0)
{
dtIns.Clear();
dtIns = dt2.Copy();
}
//<UPDATE>
dtUpd = dt2.Clone();
for (int i = 0; i < dt1.Rows.Count; i++)
{
for (int j = 0; j < dt2.Rows.Count; j++)
{
//假設可變動的keyword與原始的keyword相同
if (Equals(dt1.Rows[i][keyword], dt2.Rows[j][keyword]))
{
for (int c = 0; c < dt1.Columns.Count; c++)
{
if (!Equals(dt1.Rows[i][c], dt2.Rows[j][c]))
{
dtUpd.ImportRow(dt2.Rows[j]);
break;
}
}
}
}
}
}
