public void ImportSystems(String Filename)
{
DBConnector lDBCon = null;
String sqlString;
List<EDSystem> Systems;
EDSystem importSystem;
dsEliteDB.tbsystemsRow[] FoundRows;
dsEliteDB.tbsystems_orgRow[] FoundRows_org;
DateTime Timestamp_new, Timestamp_old;
Int32 ImportCounter = 0;
Dictionary<Int32, Int32> changedSystemIDs = new Dictionary<Int32, Int32>();
dsEliteDB localDataSet;
Int32 counter = 0;
Boolean dataChanged;
localDataSet = new dsEliteDB();
Int32 updated = 0;
Int32 added = 0;
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter_sys = null;
MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter_sysorg = null;
Int32 systemsTotal=0;
try
{
lDBCon = new DBConnector(Program.DBCon.ConfigData, true);
// gettin' some freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");
StreamReader rawDataStream;
JsonTextReader jsonReader;
JsonSerializer serializer = new JsonSerializer();
rawDataStream = new StreamReader(Filename);
jsonReader = new JsonTextReader(rawDataStream);
sendProgressEvent(new ProgressEventArgs() { Info="import systems...", NewLine = true } );
while (jsonReader.Read())
if((jsonReader.TokenType == JsonToken.StartObject) && (jsonReader.Depth == 1))
systemsTotal++;
jsonReader.Close();
rawDataStream.Close();
rawDataStream.Dispose();
rawDataStream = new StreamReader(Filename);
jsonReader = new JsonTextReader(rawDataStream);
while(jsonReader.Read())
{
if((jsonReader.TokenType == JsonToken.StartObject) && (jsonReader.Depth == 1))
{
dataChanged = false;
importSystem = serializer.Deserialize<EDSystem>(jsonReader);
localDataSet.Clear();
if(dataAdapter_sys != null)
{
dataAdapter_sys.Dispose();
dataAdapter_sys = null;
}
if(dataAdapter_sysorg != null)
{
dataAdapter_sysorg.Dispose();
dataAdapter_sysorg = null;
}
lDBCon.TableRead(String.Format("select * from tbSystems where id = {0} lock in share mode;", importSystem.Id), localDataSet.tbsystems, ref dataAdapter_sys);
//sqlString = "select * from tbSystems_org lock in share mode";
//lDBCon.TableRead(sqlString, Data.tbsystems_org);
if (localDataSet.tbsystems.Rows.Count > 0)
{
// system is existing
if ((bool)(localDataSet.tbsystems.Rows[0]["is_changed"]))
{
// data is changed by user - hold it ...
// ...and check table "tbSystems_org" for the original data
lDBCon.TableRead(String.Format("select * from tbSystems_org where id = {0} lock in share mode;", importSystem.Id), localDataSet.tbsystems_org, ref dataAdapter_sysorg);
if (localDataSet.tbsystems_org.Rows.Count > 0)
{
// system is in "tbSystems_org" existing - keep the newer version
Timestamp_old = (DateTime)(localDataSet.tbsystems_org.Rows[0]["updated_at"]);
Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(importSystem.UpdatedAt).DateTime;
if (Timestamp_new > Timestamp_old)
{
// data from file is newer
CopyEDSystemToDataRow(importSystem, (DataRow)localDataSet.tbsystems_org.Rows[0], false, null, true);
ImportCounter += 1;
dataChanged = true;
}
}
}
else
{
// system is existing - keep the newer version
Timestamp_old = (DateTime)(localDataSet.tbsystems.Rows[0]["updated_at"]);
Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(importSystem.UpdatedAt).DateTime;
if (Timestamp_new > Timestamp_old)
{
// data from file is newer
CopyEDSystemToDataRow(importSystem, localDataSet.tbsystems.Rows[0], false, null, true);
ImportCounter += 1;
dataChanged = true;
updated += 1;
}
}
}
else
{
if(dataAdapter_sys != null)
{
dataAdapter_sys.Dispose();
dataAdapter_sys = null;
}
// check if there's a user generated system
// self-created systems don't have the correct id so it must be identified by name
lDBCon.TableRead(String.Format("select * from tbSystems where systemname = {0} and id < 0 lock in share mode;", DBConnector.SQLAEscape(importSystem.Name.ToString()) ), localDataSet.tbsystems, ref dataAdapter_sys);
if (localDataSet.tbsystems.Rows.Count > 0)
{
// self created systems is existing -> correct id and get new data from EDDB
// (changed system_id in tbStations are automatically internal updated by the database itself)
CopyEDSystemToDataRow(importSystem, (DataRow)localDataSet.tbsystems.Rows[0], false, null, true);
dataChanged = true;
}
else
{
// add a new system
dsEliteDB.tbsystemsRow newRow = (dsEliteDB.tbsystemsRow)localDataSet.tbsystems.NewRow();
CopyEDSystemToDataRow(importSystem, (DataRow)newRow, false, null, true);
localDataSet.tbsystems.Rows.Add(newRow);
dataChanged = true;
}
added += 1;
ImportCounter += 1;
}
if(dataChanged)
{
if(localDataSet.tbsystems.Rows.Count > 0)
lDBCon.TableUpdate(localDataSet.tbsystems, dataAdapter_sys);
if(localDataSet.tbsystems_org.Rows.Count > 0)
lDBCon.TableUpdate(localDataSet.tbsystems_org, dataAdapter_sysorg);
dataChanged = false;
}
counter++;
if(sendProgressEvent(new ProgressEventArgs() { Info = String.Format("import systems : analysed={0}, updated={1}, added={2}", counter, ImportCounter-added, added), CurrentValue=counter, TotalValue=systemsTotal}))
break;
}
}
// reset freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
}
catch (Exception ex)
{
if(lDBCon != null)
{
if (lDBCon.TransActive())
lDBCon.TransRollback();
try
{
// reset freaky performance
lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
lDBCon.Dispose();
}
catch (Exception) { }
}
throw new Exception("Error while importing system data", ex);
}
}