/// <summary> /// 生成单个表的完整模型 /// </summary> /// <param name="tbname"></param> /// <returns></returns> public TableCompareModel CreateTableModel(string tbname) { try { TableCompareModel tbmodel = new TableCompareModel(); tbmodel.Tablename = tbname;//表 tbmodel.Isequal = "10"; DataTable dt = GetColumnList(string.Format(" AND TABLE_NAME = '{0}'", tbname)); if (dt != null)//列 { ColumnModel colmodel = new ColumnModel(); foreach (DataRow dr in dt.Rows)//列中属性 { colmodel = new ColumnModel(); colmodel.Columnname = dr["COLUMN_NAME"].ToString(); colmodel.Isequal = "10"; colmodel.Ispropertyequal = "000"; colmodel.Source.Type = SplicingType(dr); colmodel.Source.Isnull = dr["NULLABLE"].ToString() == "N" ? "not null" : "null"; colmodel.Source.Default = dr["DATA_DEFAULT"].ToString().TrimEnd('\n'); colmodel.Target.Clear(); tbmodel.Column.Add(colmodel); } } return(tbmodel); } catch (Exception ex) { throw ex; } }
private void BindDate(string tbname) { #region 表格显示比对结果 List <ShowColumnCompare> sourcelist = bll.GetSingleTable(tbname, CommonConfig.EqualValue.Source, this.xmlname); List <ShowColumnCompare> targetlist = bll.GetSingleTable(tbname, CommonConfig.EqualValue.Target, this.xmlname); //设置行高 dgvSource.DataSource = sourcelist; dgvSource.Size = new Size(dgvSource.Size.Width, 25 * (sourcelist.Count + 1)); dgvTarget.DataSource = targetlist; dgvTarget.Size = new Size(dgvTarget.Size.Width, 25 * (targetlist.Count + 1)); #endregion #region 生成对应的缺失sql语句 TableCompareModel tbmodel = bll.GetSingleTable(tbname, this.xmlname); List <string> sourcesql = new List <string>(); List <string> targetsql = new List <string>(); bll.GetSqlbyColumn(tbmodel, ref sourcesql, ref targetsql); foreach (string item in sourcesql) { tbSourceSql.Text += item + ";\r\n"; tbSourceSql.BackColor = Color.Silver; } tbSourceSql.Size = new Size(tbSourceSql.Size.Width, 15 * sourcesql.Count + 15); foreach (string item in targetsql) { tbTargetSql.Text += item + ";\r\n"; tbTargetSql.BackColor = Color.Silver; } tbTargetSql.Size = new Size(tbTargetSql.Size.Width, 15 * targetsql.Count + 15); #endregion }
/// <summary> /// 生成单个表的完整模型 /// </summary> /// <param name="tbname"></param> /// <returns></returns> public TableCompareModel CreateTableModel(string tbname) { var XmlDoc = LoadXML(); var xelem = XElement.Parse(XmlDoc.SelectSingleNode("//TableList//Table[@Tablename='" + tbname + "']").OuterXml); List <ColumnModel> collist = (from o in xelem.Descendants("Column") select new ColumnModel { Columnname = o.Attribute("Columnname").Value, Isequal = o.Attribute("Isequal").Value, Ispropertyequal = o.Attribute("Ispropertyequal").Value, Source = (from d in o.Descendants("Source") //源 select new ColumnCompareModel { Type = d.Attribute("Type").Value, Isnull = d.Attribute("Isnull").Value, Default = d.Attribute("Default").Value }).ToList()[0], Target = (from d in o.Descendants("Target") //目标 select new ColumnCompareModel { Type = d.Attribute("Type").Value, Isnull = d.Attribute("Isnull").Value, Default = d.Attribute("Default").Value }).ToList()[0] }).ToList(); TableCompareModel tbmodel = new TableCompareModel(); tbmodel.Tablename = tbname; tbmodel.Isequal = "10"; tbmodel.Column = collist; return(tbmodel); }
/// <summary> /// 缓存数据库 /// </summary> /// <param name="pgs"></param> /// <param name="FileName"></param> /// <param name="msg"></param> /// <returns></returns> public Boolean CacheTable(PgsBar pgs, string FileName, out string msg) { try { List <TableCompareModel> tblist = new List <TableCompareModel>(); TableCompareModel tbmodel = new TableCompareModel(); DataTable dt = new DataTable(); string retmsg = "缓存失败"; dt = idefdal.GetDBList(new List <WhereModel>()); pgs.PgsScoroll(10, "源数据读取完成"); if (dt != null) { pgs.SetPgsMax(dt.Rows.Count + 20); foreach (DataRow dr in dt.Rows)//表 { string tbname = dr["SelName"].ToString(); tbmodel = new TableCompareModel(); tbmodel = idefdal.CreateTableModel(tbname); tblist.Add(tbmodel); //Thread.Sleep(500); pgs.PgsScoroll(1, tbname + "缓存完成"); } } pgs.PgsScoroll(0, "生成缓存文件。。。"); if (CreateXmlFile(tblist, CommonConfig.basecachepath + FileName, out retmsg)) { pgs.PgsScoroll(10, "数据缓存完成"); } else { pgs.PgsScoroll(10, "生成缓存文件失败"); } msg = "数据缓存完成"; return(true); } catch (Exception ex) { msg = ex.Message; pgs.PgsScoroll(0, ex.Message); return(false); } }
/// <summary> /// 查询表并转化为表的模型 /// </summary> /// <param name="list"></param> /// <returns></returns> public List <TableCompareModel> GetTableListForModel(List <WhereModel> list) { try { List <TableCompareModel> tblist = new List <TableCompareModel>(); TableCompareModel tbmodel = new TableCompareModel(); DataTable dt = GetDBList(list); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tbmodel = new TableCompareModel(); tbmodel.Tablename = dr["SelName"].ToString(); tblist.Add(tbmodel); } } return(tblist); } catch (Exception ex) { throw ex; } }
/// <summary> /// 表中字段比对 /// </summary> /// <param name="model"></param> /// <returns></returns> public TableCompareModel CompareColumn(TableCompareModel model) { model.Isequal = "11"; TableCompareModel sourcemodel = isourcedal.CreateTableModel(model.Tablename); TableCompareModel targetmodel = itargetdal.CreateTableModel(model.Tablename); TableCompareModel outmodel; TableCompareModel inmodel; ColumnModel column = new ColumnModel(); bool flag = false; for (int i = 0; i < 2; i++) { #region 列互换比较 if (i == 0) { outmodel = sourcemodel; inmodel = targetmodel; } else { outmodel = targetmodel; inmodel = sourcemodel; } #endregion foreach (ColumnModel outcol in outmodel.Column) { string colname = outcol.Columnname; flag = false; foreach (ColumnModel incol in inmodel.Column) { #region 列存在 if (colname == incol.Columnname) { if (i == 0) { string propertyequal = CompareSingleColumn(incol.Source, outcol.Source); if (propertyequal != "111")//字段类型、是否为空、默认值不匹配 { column = new ColumnModel(); column.Columnname = colname; column.Isequal = "11"; column.Ispropertyequal = propertyequal; column.Source.CloneIt(outcol.Source); column.Target.CloneIt(incol.Source); model.Column.Add(column); } } flag = true; break; } #endregion } #region 列不存在 //列不存在,并且允许比对时 if (!flag && scc.ColumnAdd) { column = new ColumnModel(); column.Columnname = colname; column.Isequal = i == 0 ? "10" : "01"; column.Ispropertyequal = "000"; if (i == 0) { column.Source.CloneIt(outcol.Source); column.Target.Clear(); } else { column.Source.Clear(); column.Target.CloneIt(outcol.Source); } model.Column.Add(column); } #endregion } } return(model); }
/// <summary> /// 根据比对结果生成sql语句 /// </summary> /// <param name="tbmodel"></param> /// <param name="sourcesql"></param> /// <param name="targetsql"></param> public void GetSqlbyColumn(TableCompareModel tbmodel, ref List <string> sourcesql, ref List <string> targetsql) { sourcesql = new List <string>(); targetsql = new List <string>(); string Def = ""; StringBuilder sb = new StringBuilder(); foreach (ColumnModel item in tbmodel.Column) { sb = new StringBuilder(); if (item.Isequal == "11")//字段不相同 { Def = string.IsNullOrEmpty(item.Target.Default) ? " " : " DEFAULT " + item.Target.Default; if (item.Target.Isnull == "not null") { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" MODIFY (").Append(item.Columnname).Append(" "); sb.Append(item.Target.Type).Append(Def).Append(" ").Append(item.Target.Isnull).Append(")"); } else { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" MODIFY (").Append(item.Columnname).Append(" "); sb.Append(item.Target.Type).Append(Def); if (item.Source.Isnull == "not null") { sb.Append(" null"); } sb.Append(")"); } sourcesql.Add(sb.ToString()); sb = new StringBuilder(); Def = string.IsNullOrEmpty(item.Source.Default) ? " " : " DEFAULT " + item.Source.Default; if (item.Source.Isnull == "not null") { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" MODIFY (").Append(item.Columnname).Append(" "); sb.Append(item.Source.Type).Append(Def).Append(" ").Append(item.Source.Isnull).Append(")"); } else { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" MODIFY (").Append(item.Columnname).Append(" "); sb.Append(item.Source.Type).Append(Def); if (item.Target.Isnull == "not null") { sb.Append(" null"); } sb.Append(")"); } targetsql.Add(sb.ToString()); } else if (item.Isequal == "10")//目标数据库字段不存在 { Def = string.IsNullOrEmpty(item.Source.Default) ? " " : " DEFAULT " + item.Source.Default; if (item.Source.Isnull == "not null") { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" ADD (").Append(item.Columnname).Append(" "); sb.Append(item.Source.Type).Append(Def).Append(" ").Append(item.Source.Isnull).Append(")"); } else { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" ADD (").Append(item.Columnname).Append(" "); sb.Append(item.Source.Type).Append(Def).Append(")"); } targetsql.Add(sb.ToString()); } else if (item.Isequal == "01")//源数据库字段不存在 { Def = string.IsNullOrEmpty(item.Target.Default) ? " " : " DEFAULT " + item.Target.Default; if (item.Target.Isnull == "not null") { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" ADD (").Append(item.Columnname).Append(" "); sb.Append(item.Target.Type).Append(Def).Append(" ").Append(item.Target.Isnull).Append(")"); } else { sb.Append("ALTER TABLE ").Append(tbmodel.Tablename).Append(" ADD (").Append(item.Columnname).Append(" "); sb.Append(item.Target.Type).Append(Def).Append(")"); } sourcesql.Add(sb.ToString()); } } }