比較資料表 Compare DataTable

比較兩個資料表,並產生出新增、刪除、修改三張資料表

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;
                    }
                }
            }
        }
    }
}