none
How to update a row only changed from source table to target table using c#

    Question

  • I am making a windows application.

    As you can see title, I have a source table (called as A) and target table(called as B).

    At first, when I insert A table to B table, I added a column to B named [Status] and set value = I.

    And then, when I updated A table rows then I want to only update changed rows to table B

    and also updated [Status] value = U

    I tried to search through internet, but I don't know how to compare that rows.

    Could you help me how can I solve it?

    code is below. Thanks

    DataTable DeptDt = null; //Source table
    DeptDt = DataSetMaker.Instance.FromMsSql(SourceDBPath, SourceDBName, SourceDBLoginID, SourceDBLoginPass, SourceDBDeptTableName);
    if (DeptDt != null)
    {
        Tool.Log(LogKinds.DEPT, LogLevels.INFORMATION, SourceKind, "Starting Dept Sync");
        if (DeptDt.Rows.Count == 0)
        {
            Tool.Log(LogKinds.DEPT, LogLevels.WARNNING, SourceKind, "There`s no Dept Datas.");
        }
        else
        {
            //pk of Dept Table
            string DeptPk = AppConfigHelper.GetAppString(Words.SourceDeptPKColumn);
            // add @ like @deptcode
            string DeptPkVar = Util.ColNameToVarName(DeptPk);
            //wrapped like [deptcode]
            DeptPk = Util.GetActualColName(DeptPk);
            List<string> ErrorPks = Tool.GetSyncErrorPks(LogKinds.DEPT, LogSections.SOURCE);
            foreach (DataRow row in DeptDt.Rows)
            {
                string pk = row[DeptPk] + "";
                if (IsOnlyErrorSync && !ErrorPks.Contains(pk))
                {
                    continue;
                }
                try
                {
                    List<SqlParameter> pms = new List<SqlParameter>();
                    foreach (DataColumn item in DeptDt.Columns)
                    {
                        var sp = new SqlParameter("@" + item.ColumnName, row[item.ColumnName]);
                        pms.Add(sp);
                    }
                    //Update and insert rows to Target Table. method is below 
                    //ShadowDeptTable is Target Table
                    QueryHelper.InsertOrUpdateShadowTables(Words.ShadowDeptTable,
                        new SqlParameter[]{new SqlParameter(DeptPkVar,pk)}, pms.ToArray());
                }
                catch (Exception ex)
                {
                    Tool.SetSyncError(pk, LogKinds.DEPT, LogSections.SOURCE, ex.ToString());
                }
            }
            if (IsOnlyErrorSync)
            {
                Tool.ClearSyncError(LogKinds.DEPT, LogSections.SOURCE);
            }
            Tool.Log(LogKinds.DEPT, LogLevels.INFORMATION, SourceKind,"Dept Sync completed.");
        }
    }
    else
    {
        Tool.ErrorLog(LogKinds.DEPT, LogSections.SOURCE, SourceKind, " Dept sync was failed");
    }
    



    • Edited by RydenChoi Wednesday, April 12, 2017 1:16 AM
    Wednesday, April 12, 2017 12:39 AM

All replies

  • And InsertOrUpdatesShadowTable method here

    public static int InsertOrUpdateShadowTables(string table, SqlParameter[] keys, params SqlParameter[] pms)
    {
        int st = 0;
        StringBuilder sb = new StringBuilder(string.Format("select count(*) from {0} ", table));
        string where = string.Empty;
        if (keys.Length > 0)
        {
            where += " where ";
            var temp = keys.Select(a => "[" + a.ParameterName.Substring(1, a.ParameterName.Length - 1) + "]"
             + " = '" + a.Value + "'").ToArray();
            where += string.Join(" and ", temp);
        }
        sb = sb.Append(where);
        //get connection string of Target Table
        string constr = Util.GetPropVal(Words.PropConnectionString);
        //count Target table's rows 
        var obj = SqlHelper.ExecuteScalar(constr, CommandType.Text, sb.ToString(), keys);
        int cnt = Convert.ToInt32(obj);
        sb = sb.Clear();
    
        //insert 
        sb = sb.Append("insert into " + table + "(");
        string cols = null;
        string vals = null;
        List<SqlParameter> pmlist = new List<SqlParameter>(keys);
        pmlist.AddRange(pms);
        var merged = keys.Union(pmlist).GroupBy(p => p.ParameterName).Select(e => e.First());
        cols = string.Join(",",
               merged.Select(a =>
              "[" + a.ParameterName.Substring(1, a.ParameterName.Length - 1) + "]"));
        vals = string.Join(",",
                    merged.Select(a => "'" + a.Value + "'"));
        sb = sb.Append(cols);
        sb = sb.Append(") values(");
        sb = sb.Append(vals);
        sb = sb.Append(")");
    
        //Update rows
        if (cnt > 0)
        {
            sb = sb.Clear();
            sb = sb.Append("update " + table + " set ");
            sb = sb.Append(string.Join(",", pms.Select(a =>
                  "[" + a.ParameterName.Substring(1, a.ParameterName.Length - 1) + "]" + " = "
                     + "'" + a.Value + "'")));
            sb = sb.Append(where);
            obj = SqlHelper.ExecuteScalar(constr, CommandType.Text, sb.ToString());
            //Set UpdateYN = U
            sb = sb.Clear();
            sb = sb.Append("update " + table + " set [UpdateYN] = 'U'");
            sb = sb.Append(where);
            obj = SqlHelper.ExecuteScalar(constr, CommandType.Text, sb.ToString());
        }
        //Insert rows
        else
        {
            obj = SqlHelper.ExecuteScalar(constr, CommandType.Text, sb.ToString());
            //set UpdateYN = I
            sb = sb.Clear();
            sb = sb.Append("update " + table + " set [UpdateYN] = 'I'");
            sb = sb.Append(where);
            obj = SqlHelper.ExecuteScalar(constr, CommandType.Text, sb.ToString());
        }
        return Convert.ToInt32(obj);
    }
    


    • Edited by RydenChoi Wednesday, April 12, 2017 12:42 AM
    Wednesday, April 12, 2017 12:40 AM