private void updateDatabaseMaterial(Dictionary <string, Dictionary <string, string> > update_dict) { //只添加新创建的材料,已有的材料就不添加了 ArrayList result = adb.queryALLMaterialFromTable("select * from Material"); bool isHaveMaterial = false; string materialName = ((Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["materialName"])["content"]; for (int j = 0; j < result.Count; j++) { if (materialName == ((ArrayList)result[j])[1].ToString()) { isHaveMaterial = true; break; } } if (isHaveMaterial == true) { string matName = ((Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["matName"])["content"]; string eosName = ((Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["soeName"])["content"]; Dictionary <string, string> matData = (Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["mat"]; Dictionary <string, string> eosData = (Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["soe"]; string refer = ((Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["refer"])["content"]; string density = ((Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["density"])["content"]; string color = ((Dictionary <string, string>)((Dictionary <string, Dictionary <string, string> >)update_dict)["color"])["content"]; string mid = matData["MID"]; string eosid = eosData["EOSID"]; //更新一条材料 需要更新三个表 bool success; string matTableName = "Mat_" + matName; string eosTableName = "Eos_" + eosName; //组装sql语句 //插入mat表 string matSql1 = "update " + matTableName; string matSql2 = " set "; for (int k = 0; k < matData.Count; k++) { matSql2 += matData.Keys.ElementAt(k) + '=' + matData[matData.Keys.ElementAt(k)]; if (k != matData.Count - 1) { matSql2 += ","; } } matSql2 += " where MID='" + mid + "'"; string matsql = matSql1 + matSql2; success = adb.updateTableData(matsql); if (success) { Console.WriteLine("更新mat表成功"); } else { Console.WriteLine("更新mat表失败"); } //插入eos表 string eosSql1 = "update " + eosTableName; string eosSql2 = " set "; for (int l = 0; l < eosData.Count; l++) { eosSql2 += eosData.Keys.ElementAt(l) + '=' + eosData[eosData.Keys.ElementAt(l)]; if (l != eosData.Count - 1) { eosSql2 += ","; } } eosSql2 += " where EOSID='" + eosid + "'"; string eosSql = eosSql1 + eosSql2; success = adb.updateTableData(eosSql); if (success) { Console.WriteLine("更新eos表成功"); } else { Console.WriteLine("更新eos表失败"); } //插入material表 //得到material表字段名 List <string> matFieldName = adb.GetTableFieldNameList("Material"); string materialSql1 = "update Material"; string materialSql2 = " set "; ArrayList materialType = adb.queryALLMaterialFromTable("select * from Material_Type where mat_name ='" + matName + "'"); string material_type_name = ((ArrayList)materialType[0])[1].ToString(); materialSql2 += matFieldName[1] + "='" + materialName + "'," + matFieldName[2] + "='" + material_type_name + "'," + matFieldName[3] + "='" + mid.ToString() + "'," + matFieldName[4] + "='" + eosid.ToString() + "'," + matFieldName[5] + "='" + refer + "'," + matFieldName[6] + "='" + density + "'," + matFieldName[7] + "='" + color + "'"; materialSql2 += " where material_name='" + materialName + "'"; string materialSql = materialSql1 + materialSql2; success = adb.updateTableData(materialSql); if (success) { Console.WriteLine("更新materila表成功"); } else { Console.WriteLine("更新materila表失败"); } } else { System.Windows.MessageBox.Show("该材料不在数据库中", "警告"); return; } }
private void findAllMaterialFromDb() { DataBaseMaterials = new List <Dictionary <string, Dictionary <string, string> > >(); ArrayList result = adb.queryALLMaterialFromTable("select * from Material"); for (int i = 0; i < result.Count; i++) { Dictionary <string, Dictionary <string, string> > record = new Dictionary <string, Dictionary <string, string> >(); ArrayList materialType = adb.queryALLMaterialFromTable("select * from Material_Type where material_type_name ='" + ((ArrayList)result[i])[2].ToString() + "'"); string matName = ((ArrayList)materialType[0])[2].ToString(); string eosName = ((ArrayList)materialType[0])[3].ToString(); ArrayList matDataResult = adb.queryALLMaterialFromTable("select * from Mat_" + matName + " where MID ='" + ((ArrayList)result[i])[3] + "'"); ArrayList eosDataResult = adb.queryALLMaterialFromTable("select * from Eos_" + eosName + " where EOSID ='" + ((ArrayList)result[i])[4] + "'"); //组装数据 //mat Dictionary <string, Dictionary <string, string> > mat = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> matData = new Dictionary <string, string>(); record.Add("mat", matData); int count = ((ArrayList)matDataResult[0]).Count; int num = 1; //得到字段名 List <string> matFieldName = adb.GetTableFieldNameList("Mat_" + matName); while (num < count) { matData.Add(matFieldName[num], ((ArrayList)matDataResult[0])[num].ToString()); num++; } //eos Dictionary <string, Dictionary <string, string> > eos = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> eosData = new Dictionary <string, string>(); record.Add("soe", eosData); count = ((ArrayList)eosDataResult[0]).Count; List <string> eosFieldName = adb.GetTableFieldNameList("Eos_" + eosName); num = 1; while (num < count) { eosData.Add(eosFieldName[num], ((ArrayList)eosDataResult[0])[num].ToString()); num++; } //materialName Dictionary <string, Dictionary <string, string> > materialName = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> mnData = new Dictionary <string, string>(); mnData.Add("content", ((ArrayList)result[i])[1].ToString()); record.Add("materialName", mnData); //refer Dictionary <string, Dictionary <string, string> > refer = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> referData = new Dictionary <string, string>(); referData.Add("content", ((ArrayList)result[i])[5].ToString()); record.Add("refer", referData); //matName Dictionary <string, Dictionary <string, string> > mat_name = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> m_nData = new Dictionary <string, string>(); m_nData.Add("content", matName.ToString()); record.Add("matName", m_nData); //eosName Dictionary <string, Dictionary <string, string> > eos_name = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> e_nData = new Dictionary <string, string>(); e_nData.Add("content", eosName.ToString()); record.Add("soeName", e_nData); //density Dictionary <string, Dictionary <string, string> > density = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> denData = new Dictionary <string, string>(); denData.Add("content", ((ArrayList)result[i])[6].ToString()); record.Add("density", denData); //color Dictionary <string, Dictionary <string, string> > color = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, string> colorData = new Dictionary <string, string>(); colorData.Add("content", ((ArrayList)result[i])[7].ToString()); record.Add("color", colorData); DataBaseMaterials.Add(record); } }