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