private DataRow GetSingleDataRow(Nullable <int> pk, string tableName, string pkColumn, DatabaseType type)
{
DatabaseClient currentClient = DatabaseClientFactory.GetClient(type);
DataTable table = currentClient.ExecuteQuery("select * from " + tableName + " where " + pkColumn + " = " + pk, null);
return(table.Rows[0]);
}
private DataRow GetSingleDataRowForMultiPk(string json, string tableName, string pkColumn1, string pkColumn2, DatabaseType type)
{
string[] parts = json.Split(';');
Nullable <int> pk1 = new Nullable <int>(Int32.Parse(parts[0]));
Nullable <int> pk2 = new Nullable <int>(Int32.Parse(parts[1]));
DatabaseClient currentClient = DatabaseClientFactory.GetClient(type);
DataTable table = currentClient.ExecuteQuery("select * from " + tableName + " where " + pkColumn1 + " = " + pk1 + " and " + pkColumn2 + " = " + pk2, null);
return(table.Rows[0]);
}
public async Task PlatformBaseServiceAsync(IHttpClientFactory clientFactory)
{
DatabaseClientFactory dbFactory = new DatabaseClientFactory(clientFactory);
string dbConnectionString = await dbFactory.GetConnectionStringForClient();
this.dbClient = await dbFactory.GetClient(dbConnectionString);
StorageClientFactory storageFactory = new StorageClientFactory(clientFactory);
string storageConnectionString = await storageFactory.GetConnectionStringForClient();
this.storageClient = await storageFactory.GetClient(storageConnectionString);
this.Setup().Wait();
}
public List <HauspaketAttributZuord> GetAllHauspaketAttributZuordInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable hauspaketAttributZuordTable = ClientDB2.ExecuteQuery(new HauspaketAttributZuord().GetSelectAllString(), null);
var resultSet = from hauspaketAttributZuord in hauspaketAttributZuordTable.AsEnumerable()
select new HauspaketAttributZuord()
{
SyncOperation = "INSERT",
HauspaketId = HandleInt(hauspaketAttributZuord[0].ToString()),
WertId = HandleInt(hauspaketAttributZuord[1].ToString())
};
return(resultSet.ToList <HauspaketAttributZuord>());
}
public List <Hersteller> GetAllHerstellerInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable herstellerTable = ClientDB2.ExecuteQuery(new Hersteller().GetSelectAllString(), null);
var resultSet = from hersteller in herstellerTable.AsEnumerable()
select new Hersteller()
{
SyncOperation = "INSERT",
HerstellerId = HandleInt(hersteller[0].ToString()),
Name = hersteller[0].ToString()
};
return(resultSet.ToList <Hersteller>());
}
private static void TestDB2()
{
Logger.Info("Start TestDB2 ");
Logger.Info("Obtaining DB2 Client");
DatabaseClient mysqlClient = DatabaseClientFactory.GetClient(DatabaseType.DB2);
Logger.Info("Executing query DB2: " + DB2_TEST_QUERY);
DataTable dataTable = mysqlClient.ExecuteQuery(DB2_TEST_QUERY, null);
Logger.Info("Printing ResultSet");
foreach (DataRow row in dataTable.Rows)
{
Console.WriteLine(row);
}
}
private static void TestMySQL()
{
Logger.Info("Start TestMySQL ");
Logger.Info("Obtaining MYSQL Client");
DatabaseClient mysqlClient = DatabaseClientFactory.GetClient(DatabaseType.MYSQL);
Logger.Info("Executing query MYSQL: " + MYSQL_TEST_QUERY);
DataTable dataTable = mysqlClient.ExecuteQuery(MYSQL_TEST_QUERY, null);
Logger.Info("Printing ResultSet");
foreach (DataRow row in dataTable.Rows)
{
Console.WriteLine(row);
}
}
public List <HauspaketAttribut> GetAllHauspaketAttributInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable hauspaketAttributTable = ClientDB2.ExecuteQuery(new HauspaketAttribut().GetSelectAllString(), null);
var resultSet = from hauspaketAttribut in hauspaketAttributTable.AsEnumerable()
select new HauspaketAttribut()
{
SyncOperation = "INSERT",
AttributId = HandleInt(hauspaketAttribut[0].ToString()),
AttributTyp = hauspaketAttribut[1].ToString(),
AttributTypAnzeige = "" + hauspaketAttribut[2]
};
return(resultSet.ToList <HauspaketAttribut>());
}
public List <Berater> GetAllBeraterInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable beraterTable = ClientDB2.ExecuteQuery(new Berater().GetSelectAllString(), null);
var resultSet = from berater in beraterTable.AsEnumerable()
select new Berater()
{
SyncOperation = "INSERT",
BeraterId = HandleInt(berater[0].ToString()),
HerstellerId = HandleInt(berater[1].ToString()),
BenutzerId = HandleInt(berater[2].ToString()),
Bild = berater[3].ToString()
};
return(resultSet.ToList <Berater>());
}
public List <HauspaketAttributRegel> GetAllHauspaketAttributRegelInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable hauspaketAttributRegelTable = ClientDB2.ExecuteQuery(new HauspaketAttributRegel().GetSelectAllString(), null);
var resultSet = from hauspaketAttributRegel in hauspaketAttributRegelTable.AsEnumerable()
select new HauspaketAttributRegel()
{
SyncOperation = "INSERT",
RegelId = HandleInt(hauspaketAttributRegel[0].ToString()),
RegelAttributLeftId = HandleInt(hauspaketAttributRegel[1].ToString()),
RegelAttributRightId = HandleInt(hauspaketAttributRegel[2].ToString()),
RegelPreisModifikator = HandleDouble(hauspaketAttributRegel[3].ToString()),
RegelErlaubt = hauspaketAttributRegel[4].ToString(),
};
return(resultSet.ToList <HauspaketAttributRegel>());
}
public List <HauspaketAttributWert> GetAllHauspaketAttributWertInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable hauspaketAttributWertTable = ClientDB2.ExecuteQuery(new HauspaketAttributWert().GetSelectAllString(), null);
var resultSet = from hauspaketAttributWert in hauspaketAttributWertTable.AsEnumerable()
select new HauspaketAttributWert()
{
SyncOperation = "INSERT",
WertId = HandleInt(hauspaketAttributWert[0].ToString()),
AttributId = HandleInt(hauspaketAttributWert[1].ToString()),
WertText = hauspaketAttributWert[2].ToString(),
WertOrdnung = HandleInt(hauspaketAttributWert[3].ToString()),
Archived = hauspaketAttributWert[4].ToString()
};
return(resultSet.ToList <HauspaketAttributWert>());
}
public void FullSyncMySQLToDB2()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
ClientMySQL = DatabaseClientFactory.GetClient(DatabaseType.MYSQL);
ClientDB2.ExecuteQuery("delete from hauskauf", null);
ClientDB2.ExecuteQuery("delete from termin", null);
ClientDB2.ExecuteQuery("delete from berater", null);
ClientDB2.ExecuteQuery("delete from ebook_statistic", null);
ClientDB2.ExecuteQuery("delete from ebook", null);
ClientDB2.ExecuteQuery("delete from attachements", null);
ClientDB2.ExecuteQuery("delete from hauspaket_attribut_regel", null);
ClientDB2.ExecuteQuery("delete from hauspaket_attribut_zuord", null);
ClientDB2.ExecuteQuery("delete from hauspaket", null);
ClientDB2.ExecuteQuery("delete from hauspaket_attribut_wert", null);
ClientDB2.ExecuteQuery("delete from hauspaket_attribut", null);
ClientDB2.ExecuteQuery("delete from hersteller", null);
ClientDB2.ExecuteQuery("delete from benutzer", null);
ClientDB2.ExecuteQuery("delete from mdh_users", null);
SyncMdhUsers();
SyncBenutzer();
SyncHersteller();
SyncBerater();
SyncEbook();
SyncEbookStatistic();
SyncHauspaket();
SyncAttachements();
SyncHauspaketAttribut();
SyncHauspaketAttributWert();
SyncHauspaketAttributZuord();
SyncHauspaketAttributRegel();
}
public List <Attachements> GetAllAttachementsInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable attachementsTable = ClientDB2.ExecuteQuery(new Attachements().GetSelectAllString(), null);
var resultSet = from attachements in attachementsTable.AsEnumerable()
select new Attachements()
{
SyncOperation = "INSERT",
AttachementId = HandleInt(attachements[0].ToString()),
Filename = attachements[1].ToString(),
Bezeichnung = attachements[2].ToString(),
Size = HandleInt(attachements[3].ToString()),
Mimetype = attachements[4].ToString(),
HauspaketId = HandleInt(attachements[5].ToString())
};
return(resultSet.ToList <Attachements>());
}
private List <Sync_jn> GetAllSyncJnFromDB2()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable syncJnTable = ClientDB2.ExecuteQuery("select * from sync_jn where jn_synced = 0 order by jn_timestamp desc, jn_id asc", null);
var resultSet = from syncJn in syncJnTable.AsEnumerable()
select new Sync_jn()
{
jn_id = HandleInt(syncJn[0].ToString()).GetValueOrDefault(),
jn_sync_id = Guid.NewGuid(),
jn_table = syncJn[1].ToString(),
jn_timestamp = DateTime.ParseExact(syncJn[2].ToString(), "dd.MM.yyyy HH:mm:ss", CultureInfo.InvariantCulture),
jn_pk = HandleInt(syncJn[3].ToString()).GetValueOrDefault(),
jn_operation = syncJn[4].ToString(),
jn_synced = Convert.ToByte(HandleInt(syncJn[5].ToString()).GetValueOrDefault()),
jn_changeset_json = syncJn[6] == null ? "EMPTY" : syncJn[6].ToString(),
jn_application = "DB2"
};
return(resultSet.ToList <Sync_jn>());
}
public List <Hauspaket> GetAllHauspaketInsert()
{
ClientDB2 = DatabaseClientFactory.GetClient(DatabaseType.DB2);
DataTable hauspaketTable = ClientDB2.ExecuteQuery(new Hauspaket().GetSelectAllString(), null);
var resultSet = from hauspaket in hauspaketTable.AsEnumerable()
select new Hauspaket()
{
SyncOperation = "INSERT",
HauspaketId = HandleInt(hauspaket[0].ToString()),
HerstellerId = HandleInt(hauspaket[1].ToString()),
BeraterId = HandleInt(hauspaket[2].ToString()),
Bezeichnung = hauspaket[3].ToString(),
Preis = HandleDouble(hauspaket[4].ToString()),
Grundflaeche = HandleDouble(hauspaket[5].ToString()),
Wohnflaeche = HandleDouble(hauspaket[6].ToString()),
Stockwerke = HandleInt(hauspaket[7].ToString()),
BenutzerId = HandleInt(hauspaket[8].ToString()),
Archived = hauspaket[9].ToString()
};
return(resultSet.ToList <Hauspaket>());
}
private void SetSynced(SyncJn sync, DatabaseType type)
{
if (type.Equals(DatabaseType.LOCAL))
{
SynchronisationDatabaseEntities5 db = new SynchronisationDatabaseEntities5();
try
{
Sync_jn localSyncEntry = (from syncJn in db.Sync_jn where syncJn.jn_id.Equals(sync.JnId.Value) select syncJn).Single <Sync_jn>();
db.Sync_jn.Remove(localSyncEntry);
db.SaveChanges();
}
catch (Exception e)
{
string message = "Error updating local database: " + e.Message;
Logger.Error(message);
AddErrorLog(message);
}
return;
}
Logger.Info("Setting Entry with ID " + sync.JnId + " synced in " + type);
DatabaseClientFactory.GetClient(type).ExecuteQuery("update sync_jn set jn_synced = " + 1 + " where jn_id = " + sync.JnId, null);
}
private void ActivateTrigger(DatabaseType inDatabase)
{
DatabaseClient client = DatabaseClientFactory.GetClient(inDatabase);
client.ExecuteQuery("update trigger_enabled set trigger_enabled = 1 where trigger_enabled = 0", null);
}
private void AddErrorLog(string message)
{
DatabaseClientFactory.GetClient(DatabaseType.MYSQL).ExecuteQuery("insert into error_log (log_message) values ('" + message + "')", null);
}
private void SyncEntry(SyncJn sync, Table obj, List <ColumnProperty> updateColumnProperties, DatabaseType from, DatabaseType to)
{
switch (sync.JnOperation)
{
case "INSERT":
try
{
DataRow row;
if (obj.GetType().Equals(typeof(HauspaketAttributZuord)))
{
row = GetSingleDataRowForMultiPk(sync.JnChangesetJson, sync.JnTable, "hauspaket_id", "wert_id", from);
}
else
{
row = GetSingleDataRow(sync.JnPk, sync.JnTable, obj.GetPrimaryKeyColumn(), from);
}
DatabaseClientFactory.GetClient(to).ExecuteQuery(obj.GetInsertString(row, to), null);
}
catch (Exception e)
{
string message = "Error inserting " + obj.GetTableName() + " from " + from + ": " + e.Message + " on JN ID: " + sync.JnId;
Logger.Error(message);
AddErrorLog(message);
}
break;
case "UPDATE":
try
{
DataRow row;
if (obj.GetType().Equals(typeof(HauspaketAttributZuord)))
{
row = GetSingleDataRowForMultiPk(sync.JnChangesetJson, sync.JnTable, "hauspaket_id", "wert_id", from);
}
else
{
row = GetSingleDataRow(sync.JnPk, sync.JnTable, obj.GetPrimaryKeyColumn(), from);
}
string query = obj.GetUpdateString();
ColumnProperty last = updateColumnProperties.Last();
foreach (ColumnProperty columnProperty in updateColumnProperties)
{
query = Table.AddUpdateParam(query, columnProperty.ColumnName, row[columnProperty.index], to, columnProperty.dataType, columnProperty.Equals(last));
}
if (obj.GetType().Equals(typeof(HauspaketAttributZuord)))
{
string[] parts = sync.JnChangesetJson.Split(';');
Nullable <int> pk1 = new Nullable <int>(Int32.Parse(parts[2]));
Nullable <int> pk2 = new Nullable <int>(Int32.Parse(parts[3]));
query = query + " where hauspaket_id = " + pk1.Value + " and wert_id = " + pk2;
}
else
{
query = query + " where " + obj.GetWherePk(sync.JnPk.Value);
}
DatabaseClientFactory.GetClient(to).ExecuteQuery(query, null);
}
catch (Exception e)
{
string message = "Error updating " + obj.GetTableName() + " from " + from + ": " + e.Message + " on JN ID: " + sync.JnId;
Logger.Error(message);
AddErrorLog(message);
}
break;
case "DELETE":
try
{
if (obj.GetType().Equals(typeof(HauspaketAttributZuord)))
{
string[] parts = sync.JnChangesetJson.Split(';');
Nullable <int> pk1 = new Nullable <int>(Int32.Parse(parts[0]));
Nullable <int> pk2 = new Nullable <int>(Int32.Parse(parts[1]));
DatabaseClientFactory.GetClient(to).ExecuteQuery("delete from hauspaket_attribut_zuord where hauspaket_id = " + pk1 + " and wert_id = " + pk2, null);
}
else
{
DatabaseClientFactory.GetClient(to).ExecuteQuery(obj.getDeleteString(sync.JnPk.Value), null);
}
}
catch (Exception e)
{
string message = "Error deleting " + obj.GetTableName() + " from " + from + ": " + e.Message + " on JN ID: " + sync.JnId;
Logger.Error(message);
AddErrorLog(message);
}
break;
}
SetSynced(sync, from);
}