private void StartReset(DbConnectionDO conn, string dbname, string tablename, int startNum) { msgForm.IsWorking = true; string conn_str = "server=" + conn.HOST + ";port=" + conn.PORT + ";user="******";password="******"; database=" + dbname + ";"; msgForm.ClearText(); msgForm.SetText("正在查询数据记录相关信息"); MySqlDbHelper dbhelper = new MySqlDbHelper(conn_str); string sql = "select max(id) from " + tablename; object v = dbhelper.GetFirstValue(sql); int maxId = 0; if (v != null) { maxId = Convert.ToInt32(v); } if (maxId <= 0 || maxId < startNum) { MessageBox.Show("表起始值无效!"); return; } CommittableTransaction trans = new CommittableTransaction(TimeSpan.FromMinutes(120)); sql = "select count(*) from " + tablename; v = dbhelper.GetFirstValue(sql); int count = Convert.ToInt32(v); msgForm.SetText(tablename + "表找到" + count + "条数据,最大ID=" + maxId + (count > 10000 ? ",开始分批执行" : "")); int currentId = 0; List <string> sb_updateid = new List <string>(); int pcount = 10000; int execCount = 0; if (count > pcount) { while (true) { int currentStartNum = 0; int currentExecCount = 0; string ss1 = ""; sql = "select id from " + tablename + " where id > " + currentId + " order by id asc limit " + pcount + ";"; currentId = ExecResetId(dbhelper, sql, tablename, startNum, trans, out currentStartNum, out ss1, out currentExecCount); if (currentId <= 0) { break; } startNum = currentStartNum; execCount += currentExecCount; sb_updateid.Add(ss1); msgForm.SetText("执行到ID=" + currentId + ",剩" + (count - execCount)); } } else { sql = "select id from " + tablename + " order by id asc"; int currStartNum; string ss1 = ""; ExecResetId(dbhelper, sql, tablename, startNum, trans, out currStartNum, out ss1, out execCount); sb_updateid.Add(ss1); } msgForm.SetText("执行最后的ID更新"); int index = 0; foreach (string s in sb_updateid) { msgForm.SetText("执行更新第" + (index + 1) + "批,剩" + (sb_updateid.Count - index - 1)); new MySqlDbHelper(dbhelper.ConnectionString).RunSql(s, null, null, trans); index++; } trans.Commit(); msgForm.SetText("执行完成"); msgForm.IsWorking = false; }
private void CompareDbSchemaField(DbConnectionDO source_conn, string source_db, DbConnectionDO target_conn, string target_db) { msgForm.IsWorking = true; //1、获取表列表 InitTableList(); List <string> targetFound = new List <string>(); //目标表中已经找到的表名称 if (sourceDbs.Count > 0) { CommittableTransaction trans = new CommittableTransaction(); for (int i = 0; i < sourceDbs.Count; i++) { string sd = sourceDbs[i]; mainForm.SetTitle("比较" + sd); msgForm.SetText("比较" + sd); int process = Convert.ToInt32((i + 1) * 100 / sourceDbs.Count * 0.8); mainForm.SetProcessBar(10 + process); string sql_s = "show columns from `" + sd + "`"; DataTable dt_s = new MySqlDbHelper(conn_source_str).RunDataTableSql(sql_s); List <string> fields_s = new List <string>(); if (dt_s != null && dt_s.Rows.Count > 0) { if (targetDbs.IndexOf(sd) > -1) { DataTable dt_t = new MySqlDbHelper(conn_target_str).RunDataTableSql(sql_s); CompareTable(dt_s, dt_t, sd); targetFound.Add(sd); } else { CreateTable(dt_s, sd); } } } } mainForm.SetTitle("删除多余表..."); msgForm.SetText("删除多余表..."); //在source没有,但是target有的要删除 if (targetDbs != null && targetDbs.Count > 0 && targetFound.Count > 0) { foreach (string table in targetDbs) { if (targetFound.IndexOf(table) < 0) { sb.AppendLine("DROP TABLE " + table + ";"); } } } if (sb.ToString() != "") { mainForm.SetProcessBar(95); mainForm.SetTitle("写入文件..."); msgForm.SetText("写入文件..."); string dir = AppDomain.CurrentDomain.BaseDirectory + "\\sql"; if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } string file = dir + "\\sql_" + DateTime.Now.ToString("yyyyMMdd") + ".sql"; File.WriteAllText(file, sb.ToString()); mainForm.SetProcessBar(100); MessageBox.Show("操作成功,点击确定打开文件!", "恭喜", MessageBoxButtons.OK); Process.Start(file); mainForm.SetTitle(""); mainForm.SetProcessBar(0); } else { mainForm.SetProcessBar(100); MessageBox.Show("很好, 两边数据库结构无差异!"); } msgForm.SetText("完成"); msgForm.IsWorking = false; }