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