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