public void ComTable(DB.IDB from_db, DB.IDB to_db, out List <string> insert_lis, out List <string> update_lis) { insert_lis = new List <string>(); update_lis = new List <string>(); string sql = ""; if (sync_type == 0) { sql = $"select {sel_field2} keys,update_time from {table_name} order by {key_field}"; DataTable from_tb = from_db.ExecuteToTable(sql); Dictionary <string, string> to_dic = to_db.ExecuteToDic <string, string>(sql, "keys", "update_time"); foreach (DataRow dr in from_tb.Rows) { string key = dr["keys"].ToString(); DateTime update_time = dr["update_time"].ToDateTime(); if (to_dic.TryGetValue(key, out string dt)) { //比较时间 if (dt.ToDateTime() != update_time) { update_lis.Add(key); } } else { insert_lis.Add(key); } } } else if (sync_type == 1) { sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; DataTable from_tb = from_db.ExecuteToTable(sql); foreach (DataRow dr in from_tb.Rows) { string key = dr["keys"].ToString(); insert_lis.Add(key); } to_db.ExecuteScalar($"delete from {table_name}"); } else if (sync_type == 2) { sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; DataTable from_tb = from_db.ExecuteToTable(sql); List <string> to_lis = to_db.ExecuteToList <string>(sql, "keys"); HashSet <string> to_hs = new HashSet <string>(to_lis); foreach (DataRow dr in from_tb.Rows) { string key = dr["keys"].ToString(); if (!to_hs.Contains(key)) { insert_lis.Add(key); } } } }
private void update_Load(object sender, EventArgs e) { string sql = "select * from student where id=" + s.id + ""; List <student> ls = i.ExecuteToList <student>(sql); foreach (student s in ls) { this.textBox1.Text = s.id.ToString(); this.textBox3.Text = s.name; this.textBox2.Text = s.sex = s.sex == "nan" ? "男" : "女"; } }
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); } }