/// <summary> /// this method will try to create a gift for a non existing gift batch /// </summary> private void EnforceForeignKeyConstraint() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; TDataBase db = DBAccess.Connect("Test", FDataBase); string sql = "INSERT INTO a_gift(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i) " + "VALUES(43, 99999999, 1)"; db.WriteTransaction(ref t, ref SubmissionOK, delegate { db.ExecuteNonQuery(sql, t); }); // UNDO the test t = new TDBTransaction(); SubmissionOK = true; sql = "DELETE FROM a_gift" + " WHERE a_ledger_number_i = 43 AND a_batch_number_i = 99999999 AND a_gift_transaction_number_i = 1"; db.WriteTransaction(ref t, ref SubmissionOK, delegate { db.ExecuteNonQuery(sql, t); }); }
/// <summary> /// this method will try to create a gift for a new gift batch before creating the gift batch. /// if the constraints would be checked only when committing the transaction, everything would be fine. /// but usually you get a violation of foreign key constraint a_gift_fk1 /// </summary> private void WrongOrderSqlStatements() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; TDataBase db = DBAccess.Connect("Test", FDataBase); // setup test scenario: a gift batch, with 2 gifts, each with 2 gift details db.WriteTransaction(ref t, ref SubmissionOK, delegate { string sql = "INSERT INTO a_gift(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i) " + "VALUES(43, 99999999, 1)"; db.ExecuteNonQuery(sql, t); sql = "INSERT INTO a_gift_batch(a_ledger_number_i, a_batch_number_i, a_batch_description_c, a_bank_account_code_c, a_batch_year_i, a_currency_code_c, a_bank_cost_centre_c) " + "VALUES(43, 99999999, 'Test', '6000', 1, 'EUR', '4300')"; db.ExecuteNonQuery(sql, t); }); // UNDO the test t = null; SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { string sql = "DELETE FROM a_gift" + " WHERE a_ledger_number_i = 43 AND a_batch_number_i = 99999999 AND a_gift_transaction_number_i = 1"; db.ExecuteNonQuery(sql, t); sql = "DELETE FROM a_gift_batch" + " WHERE a_ledger_number_i = 43 AND a_batch_number_i = 99999999"; db.ExecuteNonQuery(sql, t); }); }
public void TestInsertMultipleRows() { TDBTransaction t = new TDBTransaction(); bool SubmissionOK = true; string sql; TDataBase db = DBAccess.Connect("TestInsertMultipleRows", FDataBase); db.WriteTransaction( ref t, ref SubmissionOK, delegate { sql = "INSERT INTO a_gift_batch(a_ledger_number_i, a_batch_number_i, a_batch_description_c, a_bank_account_code_c, a_batch_year_i, a_currency_code_c, a_bank_cost_centre_c, a_gl_effective_date_d) " + "VALUES (43, 990, 'TEST', '6000', 1, 'EUR', '4300', NOW()),(43, 991, 'TEST', '6000', 1, 'EUR', '4300', NOW())"; db.ExecuteNonQuery(sql, t); }); // UNDO the test t = new TDBTransaction(); SubmissionOK = true; db.WriteTransaction(ref t, ref SubmissionOK, delegate { sql = "DELETE FROM a_gift_batch" + " WHERE a_ledger_number_i = 43 AND (a_batch_number_i = 990 or a_batch_number_i = 991)"; db.ExecuteNonQuery(sql, t); }); }
public void Test_TAccountPeriodToNewYear() { // create new ledger which is in year 2010 int intLedgerNumber2010 = CommonNUnitFunctions.CreateNewLedger(new DateTime(2010, 1, 1)); TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("Test_TAccountPeriodToNewYear"); bool SubmissionOK = false; TAccountPeriodToNewYear accountPeriodToNewYear = null; db.WriteTransaction( ref transaction, ref SubmissionOK, delegate { // We are in 2010 and this and 2011 is not a leap year TVerificationResultCollection verificationResult = new TVerificationResultCollection(); accountPeriodToNewYear = new TAccountPeriodToNewYear(intLedgerNumber2010, transaction); accountPeriodToNewYear.VerificationResultCollection = verificationResult; accountPeriodToNewYear.IsInInfoMode = false; // RunEndOfPeriodOperation ... accountPeriodToNewYear.RunOperation(); SubmissionOK = true; }); TAccountPeriodInfo accountPeriodInfo = new TAccountPeriodInfo(intLedgerNumber2010); accountPeriodInfo.AccountingPeriodNumber = 2; Assert.AreEqual(2011, accountPeriodInfo.PeriodStartDate.Year, "Test of the year"); Assert.AreEqual(28, accountPeriodInfo.PeriodEndDate.Day, "Test of the Feb. 28th"); SubmissionOK = false; db.WriteTransaction( ref transaction, ref SubmissionOK, delegate { // Switch to 2012 - this is a leap year ... accountPeriodToNewYear = new TAccountPeriodToNewYear(intLedgerNumber2010, transaction); accountPeriodToNewYear.IsInInfoMode = false; accountPeriodToNewYear.RunOperation(); SubmissionOK = true; }); accountPeriodInfo = new TAccountPeriodInfo(intLedgerNumber2010); accountPeriodInfo.AccountingPeriodNumber = 2; Assert.AreEqual(29, accountPeriodInfo.PeriodEndDate.Day, "Test of the Feb. 29th"); }
/// remove all variables that start with a name, eg. PROGRESSTRACKER public static void ClearVariables(string ANameStartsWith) { TDataBase db = ConnectDB("SessionClearVariables"); TDBTransaction t = new TDBTransaction(); bool SubmissionOK = false; db.WriteTransaction(ref t, ref SubmissionOK, delegate { bool finished = false; while (!finished) { finished = true; foreach (string name in FSessionValues.Keys) { if (name.StartsWith(ANameStartsWith)) { FSessionValues.Remove(name); finished = false; break; } } } SaveSession(t); SubmissionOK = true; }); db.CloseDBConnection(); }
/// Upgrade to version 2019-10 public static bool UpgradeDatabase201909_201910(TDataBase ADataBase) { // new module PARTNERSELFSERVICE and new user SELFSERVICE TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201909_201910.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// Upgrade to version 2020-06 public static bool UpgradeDatabase202004_202006(TDataBase ADataBase) { // add new tables for data consent, and some values // add permission for p_country TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade202004_202006.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
public void Unsuspense() { // The equivalent try/catch block that is used for Suspense() was removed 27 Jan 2015 in order to fix // the issue in Mantis #3730 OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[2]; ParametersArray[0] = new OdbcParameter("", OdbcType.Int); ParametersArray[0].Value = ledgerNumber; ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[1].Value = strAcount; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("Unsuspense"); bool SubmissionOK = true; db.WriteTransaction(ref transaction, ref SubmissionOK, delegate { string strSQL = "DELETE FROM PUB_" + ASuspenseAccountTable.GetTableDBName() + " "; strSQL += "WHERE " + ASuspenseAccountTable.GetLedgerNumberDBName() + " = ? "; strSQL += "AND " + ASuspenseAccountTable.GetSuspenseAccountCodeDBName() + " = ? "; db.ExecuteNonQuery(strSQL, transaction, ParametersArray); }); }
/// Upgrade to version 2021-01 public static bool UpgradeDatabase202012_202101(TDataBase ADataBase) { // there are no changes to the database structure // but there is data we might need to add TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; string sql = String.Empty; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { sql = "SELECT COUNT(*) FROM PUB_p_banking_type"; if (Convert.ToInt32(ADataBase.ExecuteScalar(sql, SubmitChangesTransaction)) == 0) { sql = "INSERT INTO PUB_p_banking_type( p_id_i, p_type_c) VALUES(0, 'BANK ACCOUNT')"; ADataBase.ExecuteNonQuery(sql, SubmitChangesTransaction); } sql = "SELECT COUNT(*) FROM PUB_p_banking_details_usage_type"; if (Convert.ToInt32(ADataBase.ExecuteScalar(sql, SubmitChangesTransaction)) == 0) { sql = "INSERT INTO PUB_p_banking_details_usage_type(p_type_c, p_type_description_c) VALUES ('MAIN','The default banking detail that should be used for this partner')"; ADataBase.ExecuteNonQuery(sql, SubmitChangesTransaction); } SubmitOK = true; }); return(true); }
/// Upgrade to version 2018-09 public static bool UpgradeDatabase201808_201809() { // there are some changes to the database structure TDataBase db = DBAccess.Connect("TDBUpgrade"); TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; db.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201808_201809.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { db.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
public void Suspense() { try { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[2]; ParametersArray[0] = new OdbcParameter("", OdbcType.Int); ParametersArray[0].Value = ledgerNumber; ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[1].Value = strAcount; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("Suspense"); bool SubmissionOK = true; db.WriteTransaction(ref transaction, ref SubmissionOK, delegate { string strSQL = "INSERT INTO PUB_" + ASuspenseAccountTable.GetTableDBName() + " "; strSQL += "(" + ASuspenseAccountTable.GetLedgerNumberDBName(); strSQL += "," + ASuspenseAccountTable.GetSuspenseAccountCodeDBName() + ") "; strSQL += "VALUES ( ? , ? )"; db.ExecuteNonQuery(strSQL, transaction, ParametersArray); }); } catch (Exception) { Assert.Fail("No database access to run the test"); } }
/// <summary> /// set a session variable. /// store to database immediately /// </summary> /// <param name="name"></param> /// <param name="value"></param> public static void SetVariable(string name, object value) { TDataBase db = ConnectDB("SessionSetVariable"); TDBTransaction t = new TDBTransaction(); bool SubmissionOK = false; db.WriteTransaction(ref t, ref SubmissionOK, delegate { if (FSessionValues.Keys.Contains(name)) { FSessionValues[name] = (new TVariant(value)).EncodeToString(); } else { FSessionValues.Add(name, (new TVariant(value)).EncodeToString()); } SaveSession(t); SubmissionOK = true; }); db.CloseDBConnection(); }
public static TSubmitChangesResult SaveData(string ATablename, ref TTypedDataTable ASubmitTable, out TVerificationResultCollection AVerificationResult) { // check access permissions for the current user TModuleAccessManager.CheckUserPermissionsForTable(ATablename, TTablePermissionEnum.eCanModify | TTablePermissionEnum.eCanModify | TTablePermissionEnum.eCanDelete); TSubmitChangesResult ReturnValue = TSubmitChangesResult.scrError; TTypedDataTable SubmitTable = null; TVerificationResultCollection VerificationResult = null; TDBTransaction WriteTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("SaveData"); SubmitTable = ASubmitTable; bool submitOK = true; // Automatic handling of a DB Transaction - and also the automatic establishment and closing of a DB // Connection where a DB Transaction can be exectued (only if that should be needed). db.WriteTransaction( ref WriteTransaction, ref submitOK, delegate { ReturnValue = SaveData(ATablename, ref SubmitTable, out VerificationResult, WriteTransaction); submitOK = ReturnValue == TSubmitChangesResult.scrOK; }); AVerificationResult = VerificationResult; if ((ATablename == SSystemDefaultsTable.GetTableDBName()) && (ReturnValue == TSubmitChangesResult.scrOK)) { // TODO Reload the site key into the session, in case that was changed } return(ReturnValue); }
private void DeleteFlag(String AFlag) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("DeleteFlag", FDataBase); Boolean SubmissionOK = true; db.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { ALedgerInitFlagTable LedgerInitFlagTable = ALedgerInitFlagAccess.LoadByPrimaryKey( FLedgerNumber, AFlag, Transaction); if (LedgerInitFlagTable.Rows.Count == 1) { LedgerInitFlagTable[0].Delete(); ALedgerInitFlagAccess.SubmitChanges(LedgerInitFlagTable, Transaction); } }); if (FDataBase == null) { db.CloseDBConnection(); } }
public static bool ReloadUserInfo() { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("ReloadUserInfo"); TPetraPrincipal UserDetails = null; bool SubmitOK = false; try { db.WriteTransaction(ref Transaction, ref SubmitOK, delegate { LoadUser(UserInfo.GetUserInfo().UserID, out UserDetails, Transaction); }); UserInfo.SetUserInfo(UserDetails); SubmitOK = true; } catch (Exception Exp) { TLogging.Log("Exception occured in ReloadCachedUserInfo: " + Exp.ToString()); throw; } return(true); }
/// <summary> /// load test data written in PostgreSQL syntax into MySQL /// </summary> private static void LoadTestDataMySQL(string fileName) { // parse the sql file, and for each table (are there multiple???) load the data string sqlfileContent = LoadCSVFileToString(fileName); string[] lines = sqlfileContent.Split(new char[] { '\n' }); string currenttable = string.Empty; string sqlStmt = string.Empty; TDataDefinitionParser parser = new TDataDefinitionParser(rootPath + Path.DirectorySeparatorChar + "db" + Path.DirectorySeparatorChar + "petra.xml"); TDataDefinitionStore store = new TDataDefinitionStore(); TTable table = null; if (!parser.ParseDocument(ref store)) { throw new Exception("failed to parse petra.xml"); } TDBTransaction LoadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadTestDataMySQL"); bool SubmissionOK = false; db.WriteTransaction(ref LoadTransaction, ref SubmissionOK, delegate { foreach (string line in lines) { if (line.StartsWith("--") || (line.Trim() == String.Empty)) { continue; } else if (line.StartsWith("COPY ")) { currenttable = line.Substring("COPY ".Length, line.IndexOf("(") - "COPY ".Length - 1); table = store.GetTable(currenttable); sqlStmt = table.PrepareSQLInsertStatement(); } else if (line == "\\.") { currenttable = String.Empty; } else if (currenttable != String.Empty) { List <OdbcParameter> Parameters = table.PrepareParametersInsertStatement(line); if (db.ExecuteNonQuery(sqlStmt, LoadTransaction, Parameters.ToArray()) == 0) { throw new Exception("failed to import line for table " + currenttable); } } } SubmissionOK = true; }); }
/// <summary> /// prepare the test case /// </summary> public static bool ImportAndPostGiftBatch(int ALedgerNumber, out TVerificationResultCollection VerificationResult) { TGiftImporting importer = new TGiftImporting(); string testFile = TAppSettingsManager.GetValue("GiftBatch.file", "../../csharp/ICT/Testing/lib/MFinance/SampleData/sampleGiftBatch.csv"); StreamReader sr = new StreamReader(testFile); string FileContent = sr.ReadToEnd(); FileContent = FileContent.Replace("{ledgernumber}", ALedgerNumber.ToString()); FileContent = FileContent.Replace("{thisyear}", DateTime.Today.Year.ToString()); sr.Close(); Hashtable parameters = new Hashtable(); parameters.Add("Delimiter", ","); parameters.Add("ALedgerNumber", ALedgerNumber); parameters.Add("DateFormatString", "yyyy-MM-dd"); parameters.Add("DatesMayBeIntegers", false); parameters.Add("NumberFormat", "American"); parameters.Add("NewLine", Environment.NewLine); GiftBatchTDSAGiftDetailTable NeedRecipientLedgerNumber; bool refreshRequired; if (!importer.ImportGiftBatches(parameters, FileContent, out NeedRecipientLedgerNumber, out refreshRequired, out VerificationResult)) { return(false); } int BatchNumber = importer.GetLastGiftBatchNumber(); Int32 generatedGlBatchNumber; if (!TGiftTransactionWebConnector.PostGiftBatch(ALedgerNumber, BatchNumber, out generatedGlBatchNumber, out VerificationResult)) { CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult); return(false); } TDataBase db = DBAccess.Connect("FixSendMailPartnerLocation"); TDBTransaction t = new TDBTransaction(); bool SubmissionOK = false; db.WriteTransaction(ref t, ref SubmissionOK, delegate { // need to set sendmail = true for the donor with partner key 43005001 string sql = "UPDATE p_partner_location SET p_send_mail_l = true WHERE p_partner_key_n = 43005001"; db.ExecuteNonQuery(sql, t); SubmissionOK = true; }); return(true); }
/// <summary> /// Any foreign account that has a non-zero opening balance should be marked /// for revaluation. /// </summary> private void NoteForexRevalRequired(Int32 ALedgerNumber, Int32 AYear, Int32 ABatchPeriod) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("NoteForexRevalRequired", FDataBase); Boolean submissionOK = true; if (ABatchPeriod == FledgerInfo.NumberOfAccountingPeriods) { if (FledgerInfo.NumberFwdPostingPeriods == 0) { return; } else { // If we're closing the final period, SetNextPeriod() doesn't update FledgerInfo.CurrentPeriod. But we still want to // check the next period for foreign forward-posted amounts, so we have to increment the period locally here. ABatchPeriod++; } } db.WriteTransaction( ref transaction, ref submissionOK, delegate { string strSQL = "SELECT Account.a_account_code_c," + "SUM (GLMP.a_actual_foreign_n) AS Balance" + " FROM PUB_a_account Account, PUB_a_general_ledger_master GLM, PUB_a_general_ledger_master_period GLMP" + " WHERE Account.a_ledger_number_i=" + ALedgerNumber + " AND Account.a_foreign_currency_flag_l=true" + " AND GLM.a_ledger_number_i=" + ALedgerNumber + " AND GLM.a_account_code_c=Account.a_account_code_c" + " AND GLM.a_year_i= " + AYear + " AND GLMP.a_glm_sequence_i=GLM.a_glm_sequence_i" + " AND GLMP.a_period_number_i=" + ABatchPeriod + " GROUP BY Account.a_account_code_c"; DataTable Balance = db.SelectDT(strSQL, "Balance", transaction); foreach (DataRow Row in Balance.Rows) { if (Convert.ToDecimal(Row["Balance"]) != 0) { TLedgerInitFlag flag = new TLedgerInitFlag(ALedgerNumber, "", transaction.DataBaseObj); flag.SetFlagComponent(MFinanceConstants.LEDGER_INIT_FLAG_REVAL, Row["a_account_code_c"].ToString()); } } }); if (FDataBase == null) { db.CloseDBConnection(); } }
/// <summary> /// reserve a number of partner keys, to be used by the calling function. /// useful to create many partner at once, eg. for the demodata /// </summary> /// <param name="AFieldPartnerKey"></param> /// <param name="ANumberOfKeys"></param> /// <param name="ADataBase"></param> /// <returns>the first valid partner key to use</returns> public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys, TDataBase ADataBase = null) { Int64 NextPartnerKey = -1; Int32 NumberOfKeys = ANumberOfKeys; if (AFieldPartnerKey == -1) { AFieldPartnerKey = DomainManager.GSiteKey; } TDBTransaction ReadWriteTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("ReservePartnerKeys", ADataBase); bool SubmissionOK = true; db.WriteTransaction(ref ReadWriteTransaction, ref SubmissionOK, delegate { PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction); NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1; Int64 NextUsedKey = Convert.ToInt64(db.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " + NextPartnerKey.ToString(), ReadWriteTransaction)); if (NextUsedKey < NextPartnerKey + NumberOfKeys) { NumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey); } PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + NumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey); PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction); SubmissionOK = true; }); if (ADataBase == null) { db.CloseDBConnection(); } if (!SubmissionOK) { throw new Exception("ReservePartnerKeys failed"); } ANumberOfKeys = NumberOfKeys; return(NextPartnerKey); }
public static void SetDefault(String AKey, object AValue, Boolean ASendUpdateInfoToClient = true, TDataBase ADataBase = null) { SUserDefaultsTable UserDefaultsDataTable; TDataBase db = DBAccess.Connect("LoadUserDefaultsTable", ADataBase); TDBTransaction WriteTransaction = new TDBTransaction(); bool SubmitOK = false; db.WriteTransaction(ref WriteTransaction, ref SubmitOK, delegate { LoadUserDefaultsTable(UserInfo.GetUserInfo().UserID, out UserDefaultsDataTable, db); DataView view = new DataView(UserDefaultsDataTable); view.Sort = SUserDefaultsTable.GetDefaultCodeDBName(); int FoundInRow = view.Find(AKey); if (FoundInRow != -1) { // User default found if (AValue.ToString() != view[FoundInRow][SUserDefaultsTable.GetDefaultValueDBName()].ToString()) { // Update only if the value is actually different view[FoundInRow][SUserDefaultsTable.GetDefaultValueDBName()] = AValue.ToString(); SubmitOK = true; } } else { // User default not found, add it to the user defaults table SUserDefaultsRow row = UserDefaultsDataTable.NewRowTyped(); row.UserId = UserInfo.GetUserInfo().UserID; row.DefaultCode = AKey; row.DefaultValue = AValue.ToString(); UserDefaultsDataTable.Rows.Add(row); FoundInRow = view.Find(AKey); SubmitOK = true; } if (SubmitOK) { SUserDefaultsAccess.SubmitChanges(UserDefaultsDataTable, WriteTransaction); if (ASendUpdateInfoToClient) { UpdateUserDefaultsOnClient(UserInfo.GetUserInfo().UserID, AKey, AValue.ToString(), view[FoundInRow][SUserDefaultsTable.GetModificationIdDBName()].ToString()); } } }); }
/// <summary> /// load test data written in PostgreSQL syntax into MySQL /// </summary> private static void LoadTestDataMySQL(string fileName) { // parse the sql file, and for each table (are there multiple???) load the data // with command: load data infile '/tmp/mysqltsv' into table tblname string sqlfileContent = LoadCSVFileToString(fileName); string[] lines = sqlfileContent.Split(new char[] { '\n' }); string currenttable = string.Empty; string tempfile = string.Empty; StreamWriter sw = null; foreach (string line in lines) { if (line.StartsWith("--") || (line.Trim() == String.Empty)) { continue; } else if (line.StartsWith("COPY ")) { currenttable = line.Substring("COPY ".Length, line.IndexOf("(") - "COPY ".Length - 1); tempfile = Path.GetTempFileName(); sw = new StreamWriter(tempfile); } else if (line == "\\.") { sw.Close(); TDBTransaction LoadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadTestDataMySQL"); bool SubmissionOK = false; db.WriteTransaction(ref LoadTransaction, ref SubmissionOK, delegate { db.ExecuteNonQuery("LOAD DATA LOCAL INFILE '" + tempfile + "' INTO TABLE " + currenttable, LoadTransaction); SubmissionOK = true; }); currenttable = String.Empty; File.Delete(tempfile); } else if (currenttable != String.Empty) { string convertedLine = line; convertedLine = convertedLine.Replace("\tt\t", "\t1\t").Replace("\tf\t", "\t0\t"); sw.WriteLine(convertedLine); } } }
public void Init() { TPetraServerConnector.Connect("../../etc/TestServer.config"); // These will be our test ledger numbers FTestLedgerList.AddRange(new int[] { 9997, 9998, 9999 }); // Load existing data FMainDS = new GLSetupTDS(); try { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("Init"); db.ReadTransaction( ref transaction, delegate { ALedgerAccess.LoadAll(FMainDS, transaction); }); // Check that our test rows are not in the database already if (!FindTestRows(FTestLedgerList)) { // Get the initial number of available ledgers FInitialLedgerCount = TGLSetupWebConnector.GetAvailableLedgers().DefaultView.Count; bool SubmitOK = true; db.WriteTransaction( ref transaction, ref SubmitOK, delegate { // Add our test rows AddTestRow(FTestLedgerList[0], "NUnitTestLedger1", false); AddTestRow(FTestLedgerList[1], "NUnitTestLedger2", true, "JPY"); AddTestRow(FTestLedgerList[2], "NUnitTestLedger2", true); // Save these new rows ALedgerAccess.SubmitChanges(FMainDS.ALedger, transaction); FMainDS.AcceptChanges(); }); FInitSucceeded = true; } } catch (Exception ex) { FInitExceptionMessage = ex.Message; } }
/// this is a direct way to create a serializable transaction on an anonymous database connection public static void WriteTransaction(ref TDBTransaction ATransaction, ref bool ASubmitOK, Action AEncapsulatedDBAccessCode) { if (!ATransaction.Valid) { TDataBase db = DBAccess.Connect("DBAccess.WriteTransaction"); db.WriteTransaction(ref ATransaction, ref ASubmitOK, AEncapsulatedDBAccessCode); db.CloseDBConnection(); } else { ATransaction.DataBaseObj.WriteTransaction(ref ATransaction, ref ASubmitOK, AEncapsulatedDBAccessCode); } }
/// <summary> /// set current database version /// </summary> private static bool SetCurrentDBVersion(TFileVersionInfo ANewVersion, TDataBase ADataBase) { TDBTransaction transaction = new TDBTransaction(); bool SubmitOK = true; ADataBase.WriteTransaction(ref transaction, ref SubmitOK, delegate { string newVersionSql = String.Format("UPDATE s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion';", ANewVersion.ToStringDotsHyphen()); ADataBase.ExecuteNonQuery(newVersionSql, transaction); }); return(true); }
/// Upgrade to version 2019-12 public static bool UpgradeDatabase201911_201912(TDataBase ADataBase) { TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { // fix for motivation details, new details have not been activated string stmt = "update a_motivation_detail set a_motivation_status_l = 1"; ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); SubmitOK = true; }); return(true); }
public static void UpdateUnpostedGiftsTaxDeductiblePct(Int64 ARecipientKey, decimal ANewPct, DateTime ADateFrom) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("UpdateUnpostedGiftsTaxDeductiblePct"); bool SubmissionOK = false; db.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { string Query = "SELECT a_gift_detail.*" + " FROM a_gift_detail, a_gift_batch, a_gift" + " WHERE a_gift_detail.p_recipient_key_n = " + ARecipientKey + " AND a_gift_detail.a_tax_deductible_pct_n <> " + ANewPct + " AND a_gift_detail.a_modified_detail_l <> true" + " AND a_gift_detail.a_tax_deductible_l = true" + " AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i" + " AND a_gift_batch.a_batch_status_c = 'Unposted'" + " AND a_gift.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift.a_batch_number_i = a_gift_detail.a_batch_number_i" + " AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" + " AND a_gift.a_date_entered_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'"; AGiftDetailTable Table = new AGiftDetailTable(); db.SelectDT(Table, Query, Transaction); // update fields for each row for (int i = 0; i < Table.Rows.Count; i++) { AGiftDetailRow Row = Table[i]; Row.TaxDeductiblePct = ANewPct; TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref Row); } AGiftDetailAccess.SubmitChanges(Table, Transaction); SubmissionOK = true; }); }
private void CommitLedgerChange() { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("CommitLedgerChange", FDataBase); Boolean SubmissionOK = false; try { db.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { ALedgerAccess.SubmitChanges(FLedgerTbl, Transaction); SubmissionOK = true; }); FLedgerTbl.AcceptChanges(); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw; } if (FDataBase == null) { db.CloseDBConnection(); } GetDataRow(); if (FDataBase == null) { db.CloseDBConnection(); } }
private static void RemoveSession() { TDataBase db = ConnectDB("RemoveSession"); TDBTransaction t = new TDBTransaction(); bool SubmissionOK = false; db.WriteTransaction(ref t, ref SubmissionOK, delegate { OdbcParameter[] parameters = new OdbcParameter[1]; parameters[0] = new OdbcParameter("s_session_id_c", OdbcType.VarChar); parameters[0].Value = FSessionID; string sql = "DELETE FROM s_session WHERE s_session_id_c = ?"; db.ExecuteNonQuery(sql, t, parameters); SubmissionOK = true; }); db.CloseDBConnection(); }
public static Int64 GetNextSequence(TSequenceNames ASequence, TDataBase ADataBase = null) { Int64 NewSequenceValue = 0; TDBTransaction Transaction = new TDBTransaction(); bool SubmissionOK = false; TDataBase db = DBAccess.Connect("GetNextSequence " + ASequence.ToString(), ADataBase); db.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { NewSequenceValue = db.GetNextSequenceValue(ASequence.ToString(), Transaction); SubmissionOK = true; }); return(NewSequenceValue); }
private void UnloadTestData_GetBatchInfo() { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[1]; ParametersArray[0] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[0].Value = strTestDataBatchDescription; TDBTransaction transaction = new TDBTransaction(); bool SubmissionOK = true; TDataBase db = DBAccess.Connect("UnloadTestData_GetBatchInfo"); db.WriteTransaction(ref transaction, ref SubmissionOK, delegate { string strSQL = "DELETE FROM PUB_" + ABatchTable.GetTableDBName() + " "; strSQL += "WHERE " + ABatchTable.GetBatchDescriptionDBName() + " = ? "; db.ExecuteNonQuery( strSQL, transaction, ParametersArray); }); db.CloseDBConnection(); }