private void btnSetMainKey_Click(object sender, RoutedEventArgs e)
{
viewmodel.tableRelation.mainTable.keyFieldName = (lstMainFields.SelectedItem as FieldDesc).fieldName;
TableDesc td = viewmodel.tableRelation.tables.FirstOrDefault(p => p.tableName == viewmodel.tableRelation.mainTable.tableName);
td.keyFieldName = viewmodel.tableRelation.mainTable.keyFieldName;
}
///<summary>克隆对象实例，但其中的fields仍为引用</summary>
public TableDesc Clone()
{
TableDesc newinstance = new TableDesc()
{
tableName = this.tableName,
isMainTable = this.isMainTable,
filter = this.filter,
keyFieldName = this.keyFieldName,
tableCName = this.tableCName,
fields = this.fields
};
return(newinstance);
}
void test()
{
DataTable dt;
string tmpid;
string tmpfielter = "";
string s = "";
//================== 生成查询参数
keyPara = "999999999";
#region ===== 测试台账语句 =====
infos.Add(new CInfo()
{
info = "★开始测试台账SQL语句"
});
infos.Add(new CInfo()
{
info = " ☆开始测试台账Insert语句"
});
if (sql.acntInsert.Count == 0)
{
infos.Add(new CInfo()
{
info = " ☆无台账Insert语句，放弃后续台账Update、Select、Delete语句测试。", brush = Brushes.Red
});
}
else
{
foreach (string ss in sql.acntInsert)
{
s = ss;
foreach (var item in sql.keypdesces)
{
TableDesc td = sql.acntTableRelation.tables.FirstOrDefault(p => p.tableName == item.tablename);
if (item.fieldname == td.keyFieldName) //主键字段
{
s = s.Replace(string.Format("※{0}※", item.propertyname), keyPara);
}
else
{
s = s.Replace(string.Format("※{0}※", item.propertyname), simValue(item.fieldtypename));
}
}
foreach (var item in sql.anctdesces)
{
s = s.Replace(string.Format("※{0}※", item.propertyname), simValue(item.fieldtypename));
}
if (DataLayer.DataProvider.ExecuteSQL(s, false))
{
infos.Add(new CInfo()
{
info = " 台账Insert语句测试成功。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 台账Insert语句测试失败。", sql = s, brush = Brushes.Red
});
}
}
//-----
infos.Add(new CInfo()
{
info = " ☆开始测试台账Update语句"
});
foreach (string ss in sql.acntUpdate)
{
s = ss;
foreach (var item in sql.keypdesces)
{
if (item.propertyname == "ID" || item.propertyname == "ID2")
{
s = s.Replace(string.Format("※{0}※", item.propertyname), keyPara);
}
else
{
s = s.Replace(string.Format("※{0}※", item.propertyname), simValue(item.fieldtypename));
}
}
foreach (var item in sql.anctdesces)
{
s = s.Replace(string.Format("※{0}※", item.propertyname), simValue(item.fieldtypename));
}
s = string.Format(s, keyPara);
if (DataLayer.DataProvider.ExecuteSQL(s, false))
{
infos.Add(new CInfo()
{
info = " 台账Update语句测试成功。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 台账Update语句测试失败。", sql = s, brush = Brushes.Red
});
}
}
//-----
infos.Add(new CInfo()
{
info = " ☆开始测试台账Select语句"
});
s = string.Format(sql.acntSelect, keyPara);
dt = DataLayer.DataProvider.getDataTableFromSQL(s, false);
if (dt != null)
{
infos.Add(new CInfo()
{
info = " 台账Select语句测试成功，查询记录" + dt.Rows.Count + "条。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 台账Select语句测试失败。", sql = s, brush = Brushes.Red
});
}
//-----
infos.Add(new CInfo()
{
info = " ☆开始测试台账Delete语句"
});
foreach (string ss in sql.acntDelete)
{
s = string.Format(ss, keyPara);
if (DataLayer.DataProvider.ExecuteSQL(s, false))
{
infos.Add(new CInfo()
{
info = " 台账Delete语句测试成功。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 台账Delete语句测试失败。", sql = s, brush = Brushes.Red
});
}
}
//再次清理insert产生的记录
foreach (var tab in sql.acntTableRelation.tables)
{
s = string.Format("delete {0} where {1}='{2}'", tab.tableName, tab.keyFieldName, keyPara);
if (DataLayer.DataProvider.ExecuteSQL(s, false))
{
infos.Add(new CInfo()
{
info = " 已清理台账Insert语句测试产生的记录。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 清理台账测试Insert产生的记录失败，请手动清理数据库。", sql = s, brush = Brushes.Red
});
}
}
}
#endregion
#region ===== 测试实时运行和规划运行语句 =====
if (!string.IsNullOrWhiteSpace(sql.rundataSelect))
{
infos.Add(new CInfo()
{
info = "★开始测试实时运行数据Select语句"
});
dt = DataLayer.DataProvider.getDataTableFromSQL(sql.rundataTestSQL, false);
if (dt != null && dt.Rows.Count > 0)
{
tmpid = dt.Rows[0][0].ToString();
s = string.Format(sql.rundataSelect, tmpid);
dt = DataLayer.DataProvider.getDataTableFromSQL(s, false);
if (dt != null)
{
infos.Add(new CInfo()
{
info = " 实时运行Select语句测试成功，查询记录" + dt.Rows.Count + "条。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 实时运行Select语句测试失败。", sql = s, brush = Brushes.Red
});
}
}
else
{
infos.Add(new CInfo()
{
info = " 可能因主表无数据，无法测试实时运行Select语句。", brush = new SolidColorBrush(Color.FromRgb(0xFF, 0x99, 0x00))
});
}
}
else
{
infos.Add(new CInfo()
{
info = "☆无实时运行数据描述，跳过实时运行数据测试。"
});
}
//===============================
if (!string.IsNullOrWhiteSpace(sql.planningSelect))
{
infos.Add(new CInfo()
{
info = "★开始测试规划模拟运行数据Select语句"
});
dt = DataLayer.DataProvider.getDataTableFromSQL(sql.planningTestSQL, false);
if (dt != null && dt.Rows.Count > 0)
{
tmpid = dt.Rows[0][0].ToString();
s = string.Format(sql.planningSelect, tmpid);
dt = DataLayer.DataProvider.getDataTableFromSQL(s, false);
if (dt != null)
{
infos.Add(new CInfo()
{
info = " 规划模拟运行Select语句测试成功，查询记录" + dt.Rows.Count + "条。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 规划模拟运行Select语句测试失败。", sql = s, brush = Brushes.Red
});
}
}
else
{
infos.Add(new CInfo()
{
info = " 可能因主表无数据，无法测试规划模拟运行Select语句。", brush = new SolidColorBrush(Color.FromRgb(0xFF, 0x99, 0x00))
});
}
}
else
{
infos.Add(new CInfo()
{
info = "☆无规划模拟运行数据描述，跳过规划模拟运行数据测试。"
});
}
#endregion
//#region ===== 测试拓扑基础关联语句 =====
//infos.Add(new CInfo() { info = "★开始测试拓扑数据基础关联SQL语句" });
//infos.Add(new CInfo() { info = " ☆开始拓扑基础关联Insert语句" });
//if (sql.topoReInsert.Count == 0)
//{
// infos.Add(new CInfo() { info = " ☆无拓扑基础关联Insert语句，放弃后续Update、Select、Delete语句测试。", brush = Brushes.Red });
//}
//else
//{
// foreach (string ss in sql.topoReInsert)
// {
// s = ss;
// foreach (var item in sql.toporelationdesces)
// s = s.Replace(string.Format("※{0}※", item.tablename + "." + item.fieldname), simValue(item.fieldtypename));
// s = string.Format(s, keyPara);
// if (DataLayer.DataProvider.ExecuteSQL(s, false))
// infos.Add(new CInfo() { info = " 拓扑基础关联Insert语句测试成功。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础关联Insert语句测试失败。", sql = s, brush = Brushes.Red });
// }
// //-----
// infos.Add(new CInfo() { info = " ☆开始测试拓扑基础关联Update语句" });
// foreach (string ss in sql.topoReUpdate)
// {
// s = ss;
// foreach (var item in sql.toporelationdesces)
// s = s.Replace(string.Format("※{0}※", item.tablename + "." + item.fieldname), simValue(item.fieldtypename));
// s = string.Format(s, keyPara);
// if (DataLayer.DataProvider.ExecuteSQL(s, false))
// infos.Add(new CInfo() { info = " 拓扑基础关联Update语句测试成功。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础关联Update语句测试失败。", sql = s, brush = Brushes.Red });
// }
// //-----
// infos.Add(new CInfo() { info = " ☆开始测试拓扑基础关联Select语句" });
// s = string.Format(sql.topoReSelect, keyPara);
// dt = DataLayer.DataProvider.getDataTableFromSQL(s, false);
// if (dt != null)
// infos.Add(new CInfo() { info = " 拓扑基础关联Select语句测试成功，查询记录" + dt.Rows.Count + "条。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础关联Select语句测试失败。", sql = s, brush = Brushes.Red });
// //-----
// infos.Add(new CInfo() { info = " ☆开始测试拓扑基础关联Delete语句" });
// foreach (string ss in sql.topoReDelete)
// {
// s = string.Format(ss, keyPara);
// if (DataLayer.DataProvider.ExecuteSQL(s, false))
// infos.Add(new CInfo() { info = " 拓扑基础关联Delete语句测试成功。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础关联Delete语句测试失败，请手动清理数据库。", sql = s, brush = Brushes.Red });
// }
//}
//#endregion
//#region ===== 测试拓扑基础从属语句 =====
//infos.Add(new CInfo() { info = "★开始测试拓扑数据基础从属SQL语句" });
//infos.Add(new CInfo() { info = " ☆开始拓扑基础从属Insert语句" });
//if (sql.topoSuInsert.Count == 0)
//{
// infos.Add(new CInfo() { info = " ☆无拓扑基础从属Insert语句，放弃后续Update、Select、Delete语句测试。", brush = Brushes.Red });
//}
//else
//{
// foreach (string ss in sql.topoSuInsert)
// {
// s = ss;
// foreach (var item in sql.toposubordinatedesces)
// s = s.Replace(string.Format("※{0}※", item.tablename + "." + item.fieldname), simValue(item.fieldtypename));
// s = string.Format(s, keyPara);
// if (DataLayer.DataProvider.ExecuteSQL(s, false))
// infos.Add(new CInfo() { info = " 拓扑基础从属Insert语句测试成功。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础从属Insert语句测试失败。", sql = s, brush = Brushes.Red });
// }
// //-----
// infos.Add(new CInfo() { info = " ☆开始测试拓扑基础从属Update语句" });
// foreach (string ss in sql.topoSuUpdate)
// {
// s = ss;
// foreach (var item in sql.toposubordinatedesces)
// s = s.Replace(string.Format("※{0}※", item.tablename + "." + item.fieldname), simValue(item.fieldtypename));
// s = string.Format(s, keyPara);
// if (DataLayer.DataProvider.ExecuteSQL(s, false))
// infos.Add(new CInfo() { info = " 拓扑基础从属Update语句测试成功。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础从属Update语句测试失败。", sql = s, brush = Brushes.Red });
// }
// //-----
// infos.Add(new CInfo() { info = " ☆开始测试拓扑基础从属Select语句" });
// s = string.Format(sql.topoSuSelect, keyPara);
// dt = DataLayer.DataProvider.getDataTableFromSQL(s, false);
// if (dt != null)
// infos.Add(new CInfo() { info = " 拓扑基础从属Select语句测试成功，查询记录" + dt.Rows.Count + "条。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础从属Select语句测试失败。", sql = s, brush = Brushes.Red });
// //-----
// infos.Add(new CInfo() { info = " ☆开始测试拓扑基础从属Delete语句" });
// foreach (string ss in sql.topoSuDelete)
// {
// s = string.Format(ss, keyPara);
// if (DataLayer.DataProvider.ExecuteSQL(s, false))
// infos.Add(new CInfo() { info = " 拓扑基础从属Delete语句测试成功。", sql = s });
// else
// infos.Add(new CInfo() { info = " 拓扑基础从属Delete语句测试失败，请手动清理数据库。", sql = s, brush = Brushes.Red });
// }
//}
//#endregion
#region ===== 测试拓扑扩展关联语句 =====
infos.Add(new CInfo()
{
info = "★开始测试拓扑数据扩展关联SQL语句"
});
infos.Add(new CInfo()
{
info = " ☆开始拓扑扩展关联Insert语句"
});
string keyPara2 = "888888888";
if (string.IsNullOrWhiteSpace(sql.dbdesc.exTopo.topoExInsert))
{
infos.Add(new CInfo()
{
info = " ☆无拓扑扩展关联Insert语句，放弃后续Select、Delete语句测试。", brush = Brushes.Red
});
}
else
{
s = string.Format(sql.dbdesc.exTopo.topoExInsert, keyPara, keyPara2);
if (DataLayer.DataProvider.ExecuteSQL(s, false))
{
infos.Add(new CInfo()
{
info = " 拓扑扩展关联Insert语句测试成功。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 拓扑扩展关联Insert语句测试失败。", sql = s, brush = Brushes.Red
});
}
//-----
infos.Add(new CInfo()
{
info = " ☆开始测试拓扑扩展关联Select语句"
});
s = string.Format(sql.dbdesc.exTopo.topoExSelect, keyPara);
dt = DataLayer.DataProvider.getDataTableFromSQL(s, false);
if (dt != null)
{
infos.Add(new CInfo()
{
info = " 拓扑扩展关联Select语句测试成功，查询记录" + dt.Rows.Count + "条。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 拓扑扩展关联Select语句测试失败。", sql = s, brush = Brushes.Red
});
}
//-----
infos.Add(new CInfo()
{
info = " ☆开始测试拓扑扩展关联Delete语句"
});
s = string.Format(sql.dbdesc.exTopo.topoExDelete, keyPara, keyPara2);
if (DataLayer.DataProvider.ExecuteSQL(s, false))
{
infos.Add(new CInfo()
{
info = " 拓扑扩展关联Delete语句测试成功。", sql = s
});
}
else
{
infos.Add(new CInfo()
{
info = " 拓扑扩展关联Delete语句测试失败，请手动清理数据库。", sql = s, brush = Brushes.Red
});
}
}
#endregion
infos.Add(new CInfo()
{
info = "===== 测试完毕 ====="
});
}
public WinDBDescToolViewModel(string xmlFileName)
{
dbdesc = DBDesc.ReadFromXml(xmlFileName);
if (dbdesc != null) //读取到数据库描述，设置数据源
{
if (string.IsNullOrWhiteSpace(dbdesc.datasourceName))
{
dbdesc.datasourceName = DataLayer.DataProvider.curDataSourceName;
}
else
{
DataLayer.DataProvider.curDataSourceName = dbdesc.datasourceName;
}
}
tables = new List <TableDesc>();
//获取数据库信息
string sql = "";
if (DataLayer.DataProvider.databaseType == DataLayer.EDataBaseType.MsSql)
{
sql = @"
select t1.name,t2.value cname from
(select * from sysobjects t1 where xtype='U') t1 left join
(select * from sys.extended_properties where minor_id=0 and name='MS_Description') t2 on t1.id=t2.major_id order by name
";
}
else if (DataLayer.DataProvider.databaseType == DataLayer.EDataBaseType.MySql)
{
sql = "SELECT TABLE_NAME as name,Table_COMMENT as cname FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = (select database() limit 1) and table_type='base table'";
}
DataTable dt = DataLayer.DataProvider.getDataTableFromSQL(sql);
foreach (DataRow dr in dt.Rows)
{
string tname = dr.Field <string>("name");
string cname = dr.Field <string>("cname");
TableDesc tabledesc = new TableDesc()
{
tableName = tname, tableCName = cname
};
tables.Add(tabledesc);
//SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID ('mem_server' )
if (DataLayer.DataProvider.databaseType == DataLayer.EDataBaseType.MsSql)
{
sql = @"
SELECT syscolumns.name,systypes.name ftype,t3.value cname
FROM syscolumns join systypes on syscolumns.xusertype = systypes.xusertype
left join sys.extended_properties t3 on syscolumns.id=t3.major_id and syscolumns.colorder=t3.minor_id
WHERE syscolumns.id = object_id('{0}') order by name
";
}
else if (DataLayer.DataProvider.databaseType == DataLayer.EDataBaseType.MySql)
{
sql = @"select column_name as name,data_type as ftype,column_comment as cname from information_schema.columns where table_schema=(select database() limit 1) and table_name='{0}'";
}
DataTable dt2 = DataLayer.DataProvider.getDataTableFromSQL(String.Format(sql, tname));
foreach (DataRow dr2 in dt2.Rows)
{
FieldDesc fd = new FieldDesc()
{
fieldName = dr2.Field <string>("name"), fieldCName = dr2.Field <string>("cname"), fieldTypeName = dr2.Field <string>("ftype")
};
if (fd.fieldCName == null)
{
fd.fieldCName = fd.fieldName;
}
tabledesc.fields.Add(fd);
}
}
//
//获取配网类型
objtypedesces = new List <TypeDesc>();
objtypedesces.Add(new TypeDesc("变电站", typeof(DNSubStation), typeof(AcntSubstation), typeof(RunDataSubstation)));
objtypedesces.Add(new TypeDesc("变电站出线", typeof(DNSubstationOutline), typeof(AcntSubstationOutline), typeof(RunDataSubstationOutline)));
objtypedesces.Add(new TypeDesc("开关站", typeof(DNSwitchStation), typeof(AcntSwitchStation), typeof(RunDataSwitchStation)));
objtypedesces.Add(new TypeDesc("配电室", typeof(DNSwitchHouse), typeof(AcntSwitchHouse), typeof(RunDataSwitchHouse)));
objtypedesces.Add(new TypeDesc("主变压器", typeof(DNMainTransformer), typeof(AcntMainTransformer), typeof(RunDataMainTransformer)));
objtypedesces.Add(new TypeDesc("两卷主变", typeof(DNMainTransformer2W), typeof(AcntMainTransformer2W), typeof(RunDataMainTransformer2W)));
objtypedesces.Add(new TypeDesc("三卷主变", typeof(DNMainTransformer3W), typeof(AcntMainTransformer3W), typeof(RunDataMainTransformer3W)));
objtypedesces.Add(new TypeDesc("配变", typeof(DNDistTransformer), typeof(AcntDistTransformer), typeof(RunDataDistTransformer)));
objtypedesces.Add(new TypeDesc("柱上变", typeof(DNColumnTransformer), typeof(AcntColumnTransformer), typeof(RunDataColumnTransformer)));
objtypedesces.Add(new TypeDesc("用户变", typeof(DNCustomerTransformer), typeof(AcntCustomerTransformer), typeof(RunDataCustomerTransformer)));
objtypedesces.Add(new TypeDesc("节点", typeof(DNNode), typeof(AcntNode), typeof(RunDataNode)));
objtypedesces.Add(new TypeDesc("隔离开关", typeof(DNSwitch), typeof(AcntSwitch), typeof(RunDataSwitch)));
objtypedesces.Add(new TypeDesc("负荷开关", typeof(DNLoadSwitch), typeof(AcntLoadSwitch), typeof(RunDataLoadSwitch)));
objtypedesces.Add(new TypeDesc("断路器", typeof(DNBreaker), typeof(AcntBreaker), typeof(RunDataBreaker)));
objtypedesces.Add(new TypeDesc("熔断器", typeof(DNFuse), typeof(AcntFuse), typeof(RunDataFuse)));
objtypedesces.Add(new TypeDesc("输电线路", typeof(DNACLine), typeof(AcntACLine), typeof(RunDataACLine)));
objtypedesces.Add(new TypeDesc("导线段", typeof(DNLineSeg), typeof(AcntLineSeg), typeof(RunDataLineSeg)));
objtypedesces.Add(new TypeDesc("电缆段", typeof(DNCableSeg), typeof(AcntCableSeg), typeof(RunDataCableSeg)));
objtypedesces.Add(new TypeDesc("母线", typeof(DNBusBar), typeof(AcntBusBar), typeof(RunDataBusBar)));
objtypedesces.Add(new TypeDesc("母联开关", typeof(DNBusBarSwitch), typeof(AcntBusBarSwitch), typeof(RunDataBusBarSwitch)));
objtypedesces.Add(new TypeDesc("连接线", typeof(DNConnectivityLine), typeof(AcntConnectivityLine), typeof(RunDataConnectivityLine)));
objtypedesces.Add(new TypeDesc("连接点", typeof(DNConnectivityNode), typeof(AcntConnectivityNode), typeof(RunDataConnectivityNode)));
objtypedesces.Add(new TypeDesc("光伏发电", typeof(DNPVPlant), typeof(AcntPVPlant), typeof(RunDataPVPlant)));
objtypedesces.Add(new TypeDesc("风力发电", typeof(DNWindPlant), typeof(AcntWindPlant), typeof(RunDataWindPlant)));
objtypedesces.Add(new TypeDesc("网格区域", typeof(DNGridArea), typeof(AcntGridArea), typeof(RunDataGridArea)));
objtypedesces.Add(new TypeDesc("分界室", typeof(DNDividingRoom), typeof(AcntDividing), typeof(RunDataDividing)));
objtypedesces.Add(new TypeDesc("分界箱", typeof(DNDividingBox), typeof(AcntDividing), typeof(RunDataDividing)));
objtypedesces.Add(new TypeDesc("直线杆塔", typeof(DNIntermediateSupport), typeof(AcntIntermediateSupport), typeof(RunDataIntermediateSupport)));
objtypedesces.Add(new TypeDesc("耐张杆塔", typeof(DNStrainSupport), typeof(AcntStrainSupport), typeof(RunDataStrainSupport)));
//==========载入数据操作定义
if (dbdesc == null)
{
dbdesc = new DBDesc();
dbdesc.xmlFileName = xmlFileName;
}
else
{
//更新数据库定义
TableDesc oldtd, newtd;
foreach (var itemsql in dbdesc.SQLS)
{
//更新主表
if (itemsql.acntTableRelation.mainTable != null)
{
oldtd = itemsql.acntTableRelation.mainTable;
newtd = tables.FirstOrDefault(p => p.tableName == itemsql.acntTableRelation.mainTable.tableName).Clone();
if (newtd != null)
{
newtd.filter = oldtd.filter;
newtd.isMainTable = oldtd.isMainTable;
newtd.keyFieldName = oldtd.keyFieldName;
}
itemsql.acntTableRelation.mainTable = newtd;
}
//更新表集
BindingList <TableDesc> newlist = new BindingList <TableDesc>();
foreach (var itemtable in itemsql.acntTableRelation.tables)
{
if (itemtable.isMainTable && itemtable.tableName == itemsql.acntTableRelation.mainTable.tableName)
{
newlist.Add(itemsql.acntTableRelation.mainTable);
}
else
{
oldtd = itemtable;
newtd = tables.FirstOrDefault(p => p.tableName == itemtable.tableName).Clone();
if (newtd != null)
{
newtd.filter = oldtd.filter;
newtd.isMainTable = false;// oldtd.isMainTable;
newtd.keyFieldName = oldtd.keyFieldName;
newlist.Add(newtd);
}
}
}
itemsql.acntTableRelation.tables = newlist;
}
}
}