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