private static bool LoadTable(string ATableName, TSimpleYmlParser AYmlParser, TDBTransaction ATransaction)
{
if (!AYmlParser.StartParseList(StringHelper.UpperCamelCase(ATableName, false, false) + "Table"))
{
// TLogging.Log("empty table " + ATableName + " " + StringHelper.UpperCamelCase(ATableName, false, false) + "Table");
return false;
}
DataTable table = DBAccess.GDBAccessObj.SelectDT("Select * from " + ATableName, ATableName, ATransaction);
List <OdbcParameter>Parameters = new List <OdbcParameter>();
string OrigInsertStatement = "INSERT INTO pub_" + ATableName + "() VALUES ";
StringBuilder InsertStatement = new StringBuilder(OrigInsertStatement);
ConvertColumnNames(table.Columns);
SortedList <string, string>RowDetails;
int count = 0;
while ((RowDetails = AYmlParser.GetNextLineAttributes()) != null)
{
count++;
if (count != 1)
{
if ((CommonTypes.ParseDBType(DBAccess.GDBAccessObj.DBType) == TDBType.SQLite) || ((count % 500) == 0))
{
// SQLite does not support INSERT of several rows at the same time
try
{
DBAccess.GDBAccessObj.ExecuteNonQuery(InsertStatement.ToString(), ATransaction, Parameters.ToArray());
}
catch (Exception e)
{
TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
throw;
}
InsertStatement = new StringBuilder(OrigInsertStatement);
Parameters = new List <OdbcParameter>();
}
else
{
InsertStatement.Append(",");
}
}
InsertStatement.Append("(");
// needed for workaround for version 0.2.24, AAPDocument.BaseCurrency and AAPPayment.BaseCurrency;
if (ATableName == "a_ledger")
{
FCurrencyPerLedger.Add(Convert.ToInt32(RowDetails["LedgerNumber"]), RowDetails["BaseCurrency"]);
}
bool firstColumn = true;
foreach (DataColumn col in table.Columns)
{
if (!firstColumn)
{
InsertStatement.Append(",");
}
firstColumn = false;
if (RowDetails.ContainsKey(col.ColumnName))
{
string strValue = RowDetails[col.ColumnName];
if (col.DataType == typeof(DateTime))
{
OdbcParameter p;
if (strValue.Length == "yyyy-MM-dd".Length)
{
p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Date);
p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd", CultureInfo.InvariantCulture);
}
else
{
p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.DateTime);
p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
}
Parameters.Add(p);
}
else if (col.DataType == typeof(String))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
p.Value = strValue.Replace(""", "\"");
Parameters.Add(p);
}
else if (col.DataType == typeof(Int32))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Int);
p.Value = Convert.ToInt32(strValue);
Parameters.Add(p);
}
else if (col.DataType == typeof(Int64))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
p.Value = Convert.ToInt64(strValue);
Parameters.Add(p);
}
else if (col.DataType == typeof(double))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
p.Value = Convert.ToDouble(strValue, CultureInfo.InvariantCulture);
Parameters.Add(p);
}
else if (col.DataType == typeof(bool))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Bit);
p.Value = Convert.ToBoolean(strValue);
Parameters.Add(p);
}
else if (col.DataType == typeof(Decimal))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
p.Value = Convert.ToDecimal(strValue, CultureInfo.InvariantCulture);
Parameters.Add(p);
}
else
{
// should not get here?
throw new Exception("error in ResetDatabase, LoadTable: " + col.DataType.ToString() + " has not yet been implemented");
}
InsertStatement.Append("?");
}
else
{
// the following statements are for the demo databases generated before version 0.2.24.
// CurrencyCode was added to a_ap_document and a_ap_payment.
// it is impossible during the load, to get the correct currencycode, via the supplier, because a_ap_supplier is loaded after a_ap_document.
// as a temporary workaround, and because we are still in Alpha, we are using the Base currency of the ledger
if ((ATableName == "a_ap_document") && (col.ColumnName == "CurrencyCode"))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
p.Value = FCurrencyPerLedger[Convert.ToInt32(RowDetails["LedgerNumber"])];
Parameters.Add(p);
InsertStatement.Append("?");
}
else if ((ATableName == "a_ap_payment") && (col.ColumnName == "CurrencyCode"))
{
OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
p.Value = FCurrencyPerLedger[Convert.ToInt32(RowDetails["LedgerNumber"])];
Parameters.Add(p);
InsertStatement.Append("?");
}
else
{
InsertStatement.Append("NULL"); // DEFAULT
}
}
}
InsertStatement.Append(")");
}
try
{
DBAccess.GDBAccessObj.ExecuteNonQuery(InsertStatement.ToString(), ATransaction, Parameters.ToArray());
}
catch (Exception e)
{
TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
throw;
}
return true;
}
public static bool ResetDatabase(string AZippedNewDatabaseData)
{
List <string>tables = TTableList.GetDBNames();
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Importing database"),
tables.Count + 3);
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
tables.Reverse();
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("deleting current data"),
0);
foreach (string table in tables)
{
DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction);
}
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));
ymlParser.ParseCaptions();
tables.Reverse();
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("loading initial tables"),
1);
// one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
bool success = true;
success = success && LoadTable("s_user", ymlParser, Transaction);
success = success && LoadTable("s_module", ymlParser, Transaction);
success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction);
success = success && LoadTable("s_system_defaults", ymlParser, Transaction);
success = success && LoadTable("s_system_status", ymlParser, Transaction);
// make sure we have the correct database version
TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion);
DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format(
"UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'",
serverExeInfo.ToString()), Transaction);
if (!success)
{
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
DBAccess.GDBAccessObj.CommitTransaction();
tables.Remove("s_user");
tables.Remove("s_module");
tables.Remove("s_user_module_access_permission");
tables.Remove("s_system_defaults");
tables.Remove("s_system_status");
FCurrencyPerLedger = new SortedList <int, string>();
Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
int tableCounter = 2;
foreach (string table in tables)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("loading table {0}"), table),
tableCounter);
tableCounter++;
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
LoadTable(table, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("loading sequences"),
tables.Count + 5 + 3);
// set sequences appropriately, not lagging behind the imported data
foreach (string seq in TTableList.GetDBSequenceNames())
{
LoadSequence(seq, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("finish import"),
tables.Count + 5 + 4);
DBAccess.GDBAccessObj.CommitTransaction();
// reset all cached tables
TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
}
catch (Exception e)
{
TLogging.Log("Problem in ResetDatabase: " + e.Message);
TLogging.Log(e.StackTrace);
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
return true;
}
private static bool LoadSequence(string ASequenceName, TSimpleYmlParser AYmlParser, TDBTransaction ATransaction)
{
if (!AYmlParser.StartParseList("Sequences"))
{
return false;
}
SortedList <string, string>RowDetails;
while ((RowDetails = AYmlParser.GetNextLineAttributes()) != null)
{
if (RowDetails["RowName"] == StringHelper.UpperCamelCase(ASequenceName, false, false))
{
DBAccess.GDBAccessObj.RestartSequence(
ASequenceName,
ATransaction,
Convert.ToInt64(RowDetails["value"]));
return true;
}
}
return false;
}
public static bool ResetDatabase(string AZippedNewDatabaseData)
{
List <string>tables = TTableList.GetDBNames();
bool SubmissionResult = false;
TDBTransaction Transaction = null;
string ClientID = "ClientID";
try
{
ClientID = DomainManager.GClientID.ToString();
}
catch (Exception)
{
}
TProgressTracker.InitProgressTracker(ClientID,
Catalog.GetString("Restoring Database..."),
tables.Count + 3);
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction,
ref SubmissionResult,
delegate
{
try
{
tables.Reverse();
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Deleting current data..."),
0);
foreach (string table in tables)
{
DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction);
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));
ymlParser.ParseCaptions();
tables.Reverse();
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading initial tables..."),
1);
// one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
bool success = true;
success = success && LoadTable("s_user", ymlParser, Transaction);
success = success && LoadTable("s_module", ymlParser, Transaction);
success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction);
success = success && LoadTable("s_system_defaults", ymlParser, Transaction);
success = success && LoadTable("s_system_status", ymlParser, Transaction);
// make sure we have the correct database version
TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion);
DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format(
"UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'",
serverExeInfo.ToString()), Transaction);
if (!success)
{
// As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
tables.Remove("s_user");
tables.Remove("s_module");
tables.Remove("s_user_module_access_permission");
tables.Remove("s_system_defaults");
tables.Remove("s_system_status");
FCurrencyPerLedger = new SortedList <int, string>();
int tableCounter = 2;
foreach (string table in tables)
{
TProgressTracker.SetCurrentState(ClientID,
String.Format(Catalog.GetString("Loading Table {0}..."), table),
tableCounter);
tableCounter++;
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
LoadTable(table, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading Sequences..."),
tables.Count + 5 + 3);
// set sequences appropriately, not lagging behind the imported data
foreach (string seq in TTableList.GetDBSequenceNames())
{
LoadSequence(seq, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Finishing Restore..."),
tables.Count + 5 + 4);
SubmissionResult = true;
// reset all cached tables
TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();
TProgressTracker.FinishJob(ClientID);
}
catch (Exception e)
{
TLogging.Log("Problem in ResetDatabase: " + e.ToString());
TLogging.LogStackTrace(TLoggingType.ToLogfile);
throw;
}
});
return SubmissionResult;
}