public Int32 CorrectMisspelledCommodities()
{
String sqlString;
String sqlBaseString;
DataTable data = new DataTable();
Int32 found = 0;
try
{
sqlBaseString = "select c1.id as ID, c2.ID as WrongID" +
" from" +
" (select id from tbcommodity where commodity = {0}) c1" +
" join" +
" (select id from tbcommodity where commodity = {1}) c2;";
foreach (dsEliteDB.tbdnmap_commodityRow mapping in BaseData.tbdnmap_commodity.Rows)
{
Program.DBCon.Execute(String.Format(sqlBaseString, DBConnector.SQLAEscape(mapping.GameName), DBConnector.SQLAEscape(mapping.CompanionName)), data);
foreach (DataRow wrongSpellings in data.Rows)
{
if(((Int32)wrongSpellings["ID"]) != ((Int32)wrongSpellings["WrongID"]) && (((Int32)wrongSpellings["WrongID"]) < 0))
{
Program.SplashScreen.InfoAdd(String.Format("...alter '{0}' to '{1}'...", mapping.CompanionName, mapping.GameName));
Program.DBCon.TransBegin();
// change the collected data to the new id
sqlString = String.Format("update tbCommodityData" +
" set commodity_id = {1}" +
" where commodity_id = {0}",
wrongSpellings["WrongID"],
wrongSpellings["ID"]);
Program.DBCon.Execute(sqlString);
sqlString = String.Format("update tbPriceHistory" +
" set commodity_id = {1}" +
" where commodity_id = {0}",
wrongSpellings["WrongID"],
wrongSpellings["ID"]);
Program.DBCon.Execute(sqlString);
// delete entry from tbCommodity, the ForeigenKeys will delete the
// entries from the other affected tables
// entries in table "tbCommodityClassification" can be deleted
sqlString = String.Format("delete from tbCommodity" +
" where id = {0}",
wrongSpellings["WrongID"]);
Program.DBCon.Execute(sqlString);
Program.Data.DeleteMultiplePrices(new List<Int32>() {(Int32)wrongSpellings["ID"]});
Program.DBCon.TransCommit();
Program.SplashScreen.InfoAppendLast("OK");
found++;
}
}
}
Program.Data.AddMissingLocalizationEntries();
Program.Data.updateTranslation();
return found;
}
catch (Exception ex)
{
if(Program.DBCon.TransActive())
Program.DBCon.TransRollback();
throw new Exception("Error while saving data", ex);
}
}