int ISync.SyncData() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; SyncHelper syncHelper; string sql = ""; { syncHelper = new SyncHelper("sa_t_operator_i", "oper_id", 0); string table_name = syncHelper.table_name; string key_field = syncHelper.key_field; string sel_field2 = syncHelper.sel_field2; string where_field = syncHelper.where_field; syncHelper.ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); //删除 目标库中有的,源库没有的数据 sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; List <string> del_lis = form_d.ExecuteToList <string>(sql, "keys"); if (del_lis.Count > 0) { to_d.ExecuteScalar($"delete {table_name} where ({where_field}) not in ({string.Join(",", del_lis.ToArray())})"); } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.UpdateOfTmp(to_d, tb); tb = null; } } return(0); } }
string Sync_SaleOrderCur() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; SyncHelper syncHelper; string sql = ""; { sql = "select top 1 settle_time from sys_t_daily_settle_log where settle_status='0' order by settle_time desc"; string time = form_d.ExecuteScalar(sql).ToString(); to_d.ExecuteScalar($"delete from sm_t_sale_order_cur where create_time<='{time}'"); } syncHelper = new SyncHelper("sm_t_sale_order_cur", "flow_id,sheet_no", 0); string table_name = syncHelper.table_name; string key_field = syncHelper.key_field; string sel_field2 = syncHelper.sel_field2; string where_field = syncHelper.where_field; syncHelper.ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); { //删除 目标库中有的,源库没有的数据 sql = $"select {where_field} keys from {table_name} order by {key_field}"; DataTable del_tb = form_d.ExecuteToTable(sql); if (del_tb.Rows.Count > 0) { syncHelper.DeleteOfTmp(to_d, del_tb); } else { to_d.ExecuteScalar($"delete {table_name}"); } } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); syncHelper.InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); syncHelper.UpdateOfTmp(to_d, tb); tb = null; } } SyncResult result = new SyncResult { flag = true, Message = "* " + String.Format("{0,-31}", table_name) + " | " + String.Format("{0,-30}", insert_lis.Count + update_lis.Count) + " *" }; return(result.Message); }
string Sync_Oper() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; SyncHelper syncHelper; string sql = ""; syncHelper = new SyncHelper("sa_t_operator_i", "oper_id", 0); string table_name = syncHelper.table_name; string key_field = syncHelper.key_field; string sel_field2 = syncHelper.sel_field2; string where_field = syncHelper.where_field; syncHelper.ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); { //删除 目标库中有的,源库没有的数据 sql = $"select {where_field} keys from {table_name} order by {key_field}"; DataTable del_tb = form_d.ExecuteToTable(sql); if (del_tb.Rows.Count > 0) { syncHelper.DeleteOfTmp(to_d, del_tb); } else { to_d.ExecuteScalar($"delete {table_name}"); } } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.UpdateOfTmp(to_d, tb); tb = null; } } SyncResult result = new SyncResult { flag = true, Message = "* " + String.Format("{0,-31}", table_name) + " | " + String.Format("{0,-30}", insert_lis.Count + update_lis.Count) + " *" }; return(result.Message); }