public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDateCurrentPeriod, out DateTime AEndDateCurrentPeriod) { DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetCurrentPeriodDates"); db.ReadTransaction( ref Transaction, delegate { ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, ledgerTable[0].CurrentPeriod, Transaction); startDate = accountingPeriodTable[0].PeriodStartDate; endDate = accountingPeriodTable[0].PeriodEndDate; }); db.CloseDBConnection(); AStartDateCurrentPeriod = startDate; AEndDateCurrentPeriod = endDate; return(true); }
/// <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 bool TypeAheadMotivationDetail(Int32 ALedgerNumber, string ASearch, Int32 ALimit, out DataTable AResult) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TypeAheadMotivationDetail"); DataTable result = new DataTable(); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadMotivationDetail.sql"); OdbcParameter[] parameters = new OdbcParameter[3]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("MotivationDetailCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("DescDetail", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; SqlStmt += " LIMIT " + ALimit.ToString(); result = db.SelectDT(SqlStmt, "Search", Transaction, parameters); }); db.CloseDBConnection(); AResult = result; return(result.Rows.Count > 0); }
private ALedgerInitFlagRow FindRecord(String AFlag) { TDBTransaction ReadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("FindRecord", FDataBase); ALedgerInitFlagTable LedgerInitFlagTable = null; ALedgerInitFlagRow Ret = null; db.ReadTransaction( ref ReadTransaction, delegate { LedgerInitFlagTable = ALedgerInitFlagAccess.LoadByPrimaryKey(FLedgerNumber, AFlag, ReadTransaction); if ((LedgerInitFlagTable != null) && (LedgerInitFlagTable.Rows.Count == 1)) { Ret = LedgerInitFlagTable[0]; } }); if (FDataBase == null) { db.CloseDBConnection(); } return(Ret); }
public static String GetUserDefault(String AKey, String ADefaultValue, TDataBase ADataBase = null) { String ReturnValue = ""; TDataBase db = DBAccess.Connect("GetUserDefault", ADataBase); TDBTransaction ReadTransaction = new TDBTransaction(); db.ReadTransaction(ref ReadTransaction, delegate { if (!SUserDefaultsAccess.Exists(UserInfo.GetUserInfo().UserID, AKey, ReadTransaction)) { ReturnValue = ADefaultValue; } else { SUserDefaultsTable DT = SUserDefaultsAccess.LoadByPrimaryKey(UserInfo.GetUserInfo().UserID, AKey, ReadTransaction); ReturnValue = DT[0].DefaultValue; } }); if (ADataBase == null) { db.CloseDBConnection(); } return(ReturnValue); }
/// <summary> /// THIS METHOD DOES NOT RETURN USEFUL VALUES because the Year is not specified. /// It is only called from tests, and those tests pass, /// because there's no previous financial year in the database, /// because the data returned by this method is from the earliest year in the ledger. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACostCentreCode"></param> /// <param name="ADataBase"></param> public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, string ACostCentreCode, TDataBase ADataBase = null) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TGet_GLM_Info", ADataBase); db.ReadTransaction(ref transaction, delegate { FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.CostCentreCode = ACostCentreCode; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); if (FGLMTbl.Rows.Count == 0) { // String msg = TLogging.StackTraceToText(new StackTrace(true)); String msg = ""; TLogging.Log(String.Format("ERROR: No TGet_GLM_Info row found for ({0}, {1}).", ACostCentreCode, AAccountCode, msg)); } }); if (ADataBase == null) { db.CloseDBConnection(); } }
/// <summary> /// Load a single row by sequence and period /// </summary> /// <returns>True if it seemed to work</returns> public Boolean LoadBySequence(Int32 ASequence, Int32 APeriod) { Boolean LoadedOk = false; if (ASequence != -1) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadBySequence", FDataBase); db.ReadTransaction(ref transaction, delegate { FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(ASequence, APeriod, transaction); LoadedOk = FGLMpTable.Rows.Count > 0; FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null; }); if (FDataBase == null) { db.CloseDBConnection(); } } return(LoadedOk); }
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_RequestedConnection() { TDataBase RequestedConnection = EstablishDBConnectionAndReturnIt("New DB Connection"); TDBTransaction ReadTransaction = new TDBTransaction(); DBAccess.SimpleAutoDBConnAndReadTransactionSelector(ATransaction: out ReadTransaction, AName: "NewTransaction", ADatabase: RequestedConnection, AEncapsulatedDBAccessCode: delegate { Result = Convert.ToInt32(ReadTransaction.DataBaseObj.ExecuteScalar("SELECT COUNT(*) FROM p_partner WHERE p_partner_key_n = 43005001", ReadTransaction)); // Is this the expected connection? Assert.AreEqual("New DB Connection", ReadTransaction.DataBaseObj.ConnectionName); }); // Did we get the expected transaction? Assert.AreEqual("NewTransaction", ReadTransaction.TransactionName); // Check we get a result Assert.AreEqual(1, Result); // Check the new transaction is rolled back Assert.False(ReadTransaction.Valid); RequestedConnection.CloseDBConnection(); }
private static string GetUserIDFromEmail(string AUserEmail) { TDataBase db = DBAccess.Connect("GetUserIDFromEmail"); TDBTransaction Transaction = new TDBTransaction(); string UserID = AUserEmail; db.ReadTransaction(ref Transaction, delegate { string sql = "SELECT s_user_id_c FROM PUB_s_user WHERE UPPER(s_email_address_c) = ?"; OdbcParameter[] parameters = new OdbcParameter[1]; parameters[0] = new OdbcParameter("EmailAddress", OdbcType.VarChar); parameters[0].Value = AUserEmail.ToUpper(); DataTable result = db.SelectDT(sql, "user", Transaction, parameters); if (result.Rows.Count == 1) { UserID = result.Rows[0][0].ToString(); } else { TLogging.Log("Login with E-Mail address failed for " + AUserEmail + ". " + "We found " + result.Rows.Count.ToString() + " matching rows for this address."); throw new Exception("multiple users are matching this email address"); } }); db.CloseDBConnection(); return(UserID); }
public static bool GetFirstDayOfAccountingPeriod(Int32 ALedgerNumber, DateTime ADateInAPeriod, out DateTime AFirstDayOfPeriod, TDataBase ADataBase = null) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetFirstDayOfAccountingPeriod", ADataBase); DateTime Result = DateTime.MinValue; db.ReadTransaction(ref Transaction, delegate { // Get the accounting periods for this ledger. The table will contain more than 12 rows. // The start dates will be the correct day and month but may have been inserted for an arbitrary year when the table was first created. // We are really only interested in the Day anyway AAccountingPeriodTable periods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction); DataView periodsView = new DataView(periods, "", String.Format("{0} ASC", AAccountingPeriodTable.GetPeriodStartDateDBName()), DataViewRowState.CurrentRows); AAccountingPeriodRow row = (AAccountingPeriodRow)periodsView[0].Row; Result = new DateTime(ADateInAPeriod.Year, ADateInAPeriod.Month, row.PeriodStartDate.Day); if (ADateInAPeriod.Day < row.PeriodStartDate.Day) { Result = Result.AddMonths(-1); } }); if (ADataBase == null) { db.CloseDBConnection(); } AFirstDayOfPeriod = Result; return(Result != DateTime.MinValue); }
/// <summary> /// this returns the default next available (highest) partner key of the given field /// </summary> /// <param name="AFieldPartnerKey">if this is -1, then the sitekey defined in System Parameters is used</param> /// <param name="ADataBase"></param> /// <returns>void</returns> public static System.Int64 GetNewPartnerKey(System.Int64 AFieldPartnerKey, TDataBase ADataBase = null) { PPartnerLedgerTable PartnerLedgerTable = null; Int64 ReturnValue = -1; if (AFieldPartnerKey == -1) { AFieldPartnerKey = DomainManager.GSiteKey; } TDBTransaction ReadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetNewPartnerKey", ADataBase); db.ReadTransaction(ref ReadTransaction, delegate { PartnerLedgerTable = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction); ReturnValue = PartnerLedgerTable[0].PartnerKey + PartnerLedgerTable[0].LastPartnerId + 1; // Now check that this does not exist, and increment until we // find one which does not while (PPartnerAccess.Exists(ReturnValue, ReadTransaction)) { ReturnValue = ReturnValue + 1; } }); if (ADataBase == null) { db.CloseDBConnection(); } return(ReturnValue); }
/// <summary> /// ... /// </summary> /// <param name="ALedgerNumber"></param> public TGetAccountHierarchyDetailInfo(Int32 ALedgerNumber) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } #endregion Validate Arguments TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TGetAccountHierarchyDetailInfo"); try { db.ReadTransaction( ref Transaction, delegate { FHierarchyDetailTable = AAccountHierarchyDetailAccess.LoadViaALedger(ALedgerNumber, Transaction); FAccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction); FAccountTable.DefaultView.Sort = "a_account_code_c"; }); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } db.CloseDBConnection(); }
private void LoadData() { FAccountRow = null; if (FTransaction != null) { FAccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, FTransaction); } else { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TAccountInfo.LoadData"); db.ReadTransaction( ref Transaction, delegate { FAccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, Transaction); }); db.CloseDBConnection(); } #region Validate Data if ((FAccountTable == null) || (FAccountTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account data for Ledger {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), FLedgerNumber)); } #endregion Validate Data }
/// <summary> /// The constructor needs a ledgerinfo (for the ledger number) /// </summary> /// <param name="ALedgerNumber"></param> public THandleAccountPropertyInfo(Int32 ALedgerNumber) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } #endregion Validate Arguments TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("THandleAccountPropertyInfo"); try { db.ReadTransaction( ref Transaction, delegate { FPropertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction); }); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } db.CloseDBConnection(); }
private void LoadDatabase() { intBaseCurrencyDigits = DIGIT_INIT_VALUE; intForeignCurrencyDigits = DIGIT_INIT_VALUE; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadDatabase"); db.ReadTransaction( ref transaction, delegate { currencyTable = ACurrencyAccess.LoadAll(transaction); }); db.CloseDBConnection(); if (currencyTable.Rows.Count == 0) { EVerificationException terminate = new EVerificationException( Catalog.GetString("The table a_currency is empty!")); terminate.Context = "Common Accounting"; terminate.ErrorCode = "TCurrencyInfo01"; throw terminate; } }
/// <summary> /// Gets the next available key for PPartnerGiftDestination /// </summary> /// <param name="ADataBase"></param> /// <returns>The next available key</returns> internal static int GetNewKeyForPartnerGiftDestination(TDataBase ADataBase = null) { int ReturnValue = 0; TDataBase db = DBAccess.Connect("GetNewKeyForPartnerGiftDestination", ADataBase); TDBTransaction Transaction = new TDBTransaction(); db.ReadTransaction( ref Transaction, delegate { PPartnerGiftDestinationTable Table = PPartnerGiftDestinationAccess.LoadAll(Transaction); foreach (PPartnerGiftDestinationRow Row in Table.Rows) { if (Row.Key >= ReturnValue) { ReturnValue = Row.Key + 1; } } }); if (ADataBase == null) { db.CloseDBConnection(); } return(ReturnValue); }
private void PrepareTestCaseData() { TDBTransaction Transaction = new TDBTransaction(); bool AccountTestCasesAvailable = false; bool CostCentreTestCasesAvailable = false; TDataBase db = DBAccess.Connect("PrepareTestCaseData"); db.ReadTransaction(ref Transaction, delegate { // Check if some special test data are available - otherwise load ... AccountTestCasesAvailable = AAccountAccess.Exists(LedgerNumber, "6001", Transaction); CostCentreTestCasesAvailable = ACostCentreAccess.Exists(LedgerNumber, "4301", Transaction); }); db.CloseDBConnection(); if (!AccountTestCasesAvailable) { CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\" + "test-sql\\gl-test-account-data.sql", LedgerNumber); } if (!CostCentreTestCasesAvailable) { CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\" + "test-sql\\gl-test-costcentre-data.sql", LedgerNumber); } }
/// 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(); }
/// <summary> /// Loads only GLM Data selected by Ledger Number, Year and Account Code ... /// </summary> public TGlmInfo(int ALedgerNumber, int ACurrentFinancialYear, string AAccountCode, TDataBase ADataBase = null) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TGlmInfo", ADataBase); db.ReadTransaction( ref transaction, delegate { FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.Year = ACurrentFinancialYear; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); TLogging.LogAtLevel(1, "TGlmInfo(" + ALedgerNumber + ", " + ACurrentFinancialYear + ", " + AAccountCode + ") has loaded " + FGLMTbl.Rows.Count + " Rows."); iPtr = -1; }); if (ADataBase == null) { db.CloseDBConnection(); } }
private void CloseTestDBConnection(TDataBase ADBAccessObject, string AConnectionName = null, bool ALogNumberOfConnections = false) { if (ALogNumberOfConnections) { if (FDBType == TDBType.PostgreSQL) { TLogging.Log(" CloseTestDBConnection: Number of open DB Connections on PostgreSQL BEFORE closing Database connection: " + TDataBase.GetNumberOfDBConnections(FDBType) + ADBAccessObject.GetDBConnectionIdentifier()); } } TLogging.Log(" CloseTestDBConnection: Closing connection to Database..." + ADBAccessObject.GetDBConnectionIdentifier()); ADBAccessObject.CloseDBConnection(); TLogging.Log(" CloseTestDBConnection: Database connection closed." + TDataBase.GetDBConnectionName(AConnectionName)); if (ALogNumberOfConnections) { if (FDBType == TDBType.PostgreSQL) { TLogging.Log(" CloseTestDBConnection: Number of open DB Connections on PostgreSQL AFTER closing Database connection: " + TDataBase.GetNumberOfDBConnections(FDBType) + TDataBase.GetDBConnectionName(AConnectionName)); } } }
/// <summary> /// Load all GLMP rows for this Cost Centre in this period /// </summary> /// <returns></returns> public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod) { Boolean LoadedOk = false; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadByCostCentreAccountPeriod", FDataBase); FGLMpTable = new AGeneralLedgerMasterPeriodTable(); db.ReadTransaction(ref transaction, delegate { db.SelectDT( FGLMpTable, "SELECT a_general_ledger_master_period.* FROM" + " a_general_ledger_master_period, a_general_ledger_master" + " WHERE" + " a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" + " AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber + " AND a_general_ledger_master.a_year_i = " + AYear + " AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" + " AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" + " AND a_general_ledger_master_period.a_period_number_i=" + APeriod, transaction); LoadedOk = (FGLMpTable.Rows.Count > 0); }); // AutoReadTransaction if (FDataBase == null) { db.CloseDBConnection(); } FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null; return(LoadedOk); }
/// <summary> /// Direct access to the unposted gifts /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ADateEndOfPeriod"></param> public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod) { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[3]; ParametersArray[0] = new OdbcParameter("", OdbcType.Int); ParametersArray[0].Value = ALedgerNumber; ParametersArray[1] = new OdbcParameter("", OdbcType.Date); ParametersArray[1].Value = ADateEndOfPeriod; ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetUnpostedGiftInfo"); db.ReadTransaction( ref transaction, delegate { string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() + " WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" + " AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" + " AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(); FDataTable = db.SelectDT( strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray); }); db.CloseDBConnection(); }
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 ACurrencyLanguageRow GetCurrencyLanguage(string ACurrencyCode) { ACurrencyLanguageRow ReturnValue = null; string Language = TUserDefaults.GetStringDefault(MSysManConstants.USERDEFAULT_UILANGUAGE); if (Language.Length > 2) { // need to get the two digit language code of p_language: de-DE => DE, en-EN => EN Language = Language.Substring(Language.Length - 2).ToUpper(); } TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetCurrencyLanguage"); db.ReadTransaction( ref Transaction, delegate { ACurrencyLanguageTable CurrencyLanguageTable = ACurrencyLanguageAccess.LoadByPrimaryKey(ACurrencyCode, Language, Transaction); if ((CurrencyLanguageTable != null) && (CurrencyLanguageTable.Rows.Count > 0)) { ReturnValue = CurrencyLanguageTable[0]; } }); db.CloseDBConnection(); return(ReturnValue); }
/// <summary> /// processes the delegate /// </summary> private static void GenericProcessor(string ADelegateName, bool ARunManually = false) { if (!FProcessDelegates.ContainsKey(ADelegateName)) { return; } TProcessDelegate TypedDelegate = FProcessDelegates[ADelegateName]; TDataBase db = null; try { db = DBAccess.Connect("Servers's DB Connection for TimedProcessing"); TypedDelegate(db, ARunManually); if (TLogging.DebugLevel >= 9) { TLogging.Log("Timed Processing: Delegate " + ADelegateName + " has run."); } } finally { if (db != null) { db.CloseDBConnection(); } } }
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_RequestedConnectionJoin() { if (FDBType == TDBType.SQLite) { // do not run this test with SQLite return; } TDataBase RequestedConnection = DBAccess.Connect("New DB Connection"); TDBTransaction FirstTransaction = RequestedConnection.BeginTransaction(IsolationLevel.ReadCommitted, -1, "FirstTransaction"); bool newTransaction; TDBTransaction ReadTransaction = RequestedConnection.GetNewOrExistingTransaction( IsolationLevel.ReadCommitted, out newTransaction, "NewTransaction"); Assert.AreEqual("New DB Connection", ReadTransaction.DataBaseObj.ConnectionName); // Did we get not a new transaction Assert.AreEqual(false, newTransaction); // Did we get the expected transaction? Assert.AreEqual("FirstTransaction", ReadTransaction.TransactionName); // Check the transaction we joined is left open Assert.True(ReadTransaction.Valid); ReadTransaction.Rollback(); RequestedConnection.CloseDBConnection(); }
/// <summary> /// Gets daily exchange rate for the given currencies and date. The APriorDaysAllwed parameter limits how 'old' the rate can be. /// The unique rate parameter can ensure that a rate is only returned if there is only one to choose from. /// TODO: might even collect the latest exchange rate from the web /// </summary> /// <param name="ACurrencyFrom"></param> /// <param name="ACurrencyTo"></param> /// <param name="ADateEffective"></param> /// <param name="APriorDaysAllowed">Sets a limit on how many days prior to ADateEffective to search. Use -1 for no limit, /// 0 to imply that the rate must match for the specified date, 1 for the date and the day before and so on.</param> /// <param name="AEnforceUniqueRate">If true the method will only return a value if there is one unique rate in the date range. /// Otherwise it returns the latest rate.</param> /// <returns>Zero if no exchange rate found</returns> public static decimal GetDailyExchangeRate(string ACurrencyFrom, string ACurrencyTo, DateTime ADateEffective, int APriorDaysAllowed, Boolean AEnforceUniqueRate) { // TODO: collect exchange rate from the web; save to db // see Mantis tracker case #87 // The rule is that we don't enforce finding a unique rate over the whole date range as that doesn't really make sense if (AEnforceUniqueRate && (APriorDaysAllowed == -1)) { throw new ArgumentException( "The GetDailyExchangeRate method does not allow 'AEnforceUniqueRate' to be true when 'APriorDaysAllowed' is -1. Unique rates should only be requested over a limited date range."); } if (ACurrencyFrom == ACurrencyTo) { return(1.0M); } // Define our earliest date, if set DateTime earliestDate = DateTime.MinValue; if (APriorDaysAllowed >= 0) { earliestDate = ADateEffective.AddDays(-APriorDaysAllowed); } // Query the database using the specific period ... TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetDailyExchangeRate"); ExchangeRateTDS allRates = null; db.ReadTransaction( ref transaction, delegate { allRates = TCrossLedger.LoadDailyExchangeRateData(false, earliestDate, ADateEffective); }); db.CloseDBConnection(); // Now work out the correct rate from the returned rows if (allRates != null) { decimal rateOfExchange; DateTime effectiveDate; if (CommonRoutines.GetBestExchangeRate(allRates.ADailyExchangeRate, ACurrencyFrom, ACurrencyTo, AEnforceUniqueRate, out rateOfExchange, out effectiveDate)) { return(rateOfExchange); } } //Returning 0 causes a validation error to force the user to select an exchange rate: return(0.0m); }
public static bool GetGiftsForFieldChangeAdjustment(ref GiftBatchTDS AGiftDS, Int32 ALedgerNumber, Int64 ARecipientKey, DateTime AStartDate, DateTime AEndDate, Int64 AOldField, out TVerificationResultCollection AMessages) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetGiftsForFieldChangeAdjustment"); GiftBatchTDS MainDS = new GiftBatchTDS(); AMessages = new TVerificationResultCollection(); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Gift.GetGiftsToAdjustField.sql"); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("StartDate", OdbcType.Date); param.Value = AStartDate; parameters.Add(param); param = new OdbcParameter("EndDate", OdbcType.Date); param.Value = AEndDate; parameters.Add(param); param = new OdbcParameter("RecipientKey", OdbcType.BigInt); param.Value = ARecipientKey; parameters.Add(param); param = new OdbcParameter("OldField", OdbcType.BigInt); param.Value = AOldField; parameters.Add(param); db.Select(MainDS, SqlStmt, MainDS.AGiftDetail.TableName, Transaction, parameters.ToArray()); // get additional data foreach (GiftBatchTDSAGiftDetailRow Row in MainDS.AGiftDetail.Rows) { AGiftBatchAccess.LoadByPrimaryKey(MainDS, Row.LedgerNumber, Row.BatchNumber, Transaction); AGiftRow GiftRow = AGiftAccess.LoadByPrimaryKey(MainDS, Row.LedgerNumber, Row.BatchNumber, Row.GiftTransactionNumber, Transaction); Row.DateEntered = GiftRow.DateEntered; Row.DonorKey = GiftRow.DonorKey; Row.IchNumber = 0; Row.DonorName = PPartnerAccess.LoadByPrimaryKey(Row.DonorKey, Transaction)[0].PartnerShortName; } }); AGiftDS = MainDS; db.CloseDBConnection(); return(CheckGiftsNotPreviouslyReversed(AGiftDS, out AMessages)); }
private void CloseTestConnections(TDataBase ATestDBInstance1, TDataBase ATestDBInstance2, int AInitialNumberOfDBConnections) { // Close the two independent DB Connections one after the other. // Note: If you look at the Log output you will see that closing these two DB Connections HAS NOT GOT AN // IMPACT on the number of open DB Connections that PostgreSQL reports - this is because of the Connection // Pooling that is going on and these DB Connections not having been 'released' by the Connection Pool...! ATestDBInstance1.CloseDBConnection(); ATestDBInstance2.CloseDBConnection(); }
/// <summary>Use a separate DB Connection to get the Ledger name</summary> /// <param name="ALedgerNumber"></param> /// <returns></returns> public static string GetLedgerNameUsingSeparateDb(Int32 ALedgerNumber) { TDataBase dataBase = DBAccess.SimpleEstablishDBConnection("GetLedgerName"); String ledgerName = GetLedgerName(ALedgerNumber, dataBase); dataBase.CloseDBConnection(); return(ledgerName); }
/// <summary> /// Closes the Database connection to the Database /// for TTimedProcessing. /// </summary> /// <returns>void</returns> private static void CloseDBConnection(TDataBase DBAccessObj) { DBAccessObj.CloseDBConnection(); }
public static void Main(string[] args) { try { // establish connection to database TCmdOpts settings = new TCmdOpts(); if (!settings.IsFlagSet("Server.ODBC_DSN")) { Console.WriteLine( "sample call: " + "ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml"); Environment.Exit(-1); } new TLogging("debug.log"); TDataBase db = new TDataBase(); TDBType dbtype = TDBType.ProgressODBC; if (settings.IsFlagSet("Server.RDBMSType")) { dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType")); } if (dbtype != TDBType.ProgressODBC) { throw new Exception("at the moment only Progress ODBC db is supported"); } db.EstablishDBConnection(dbtype, settings.GetOptValue("Server.ODBC_DSN"), "", "", settings.GetOptValue("username"), settings.GetOptValue("password"), ""); DBAccess.GDBAccessObj = db; TLogging.DebugLevel = 10; string sqlText = ""; if (!settings.IsFlagSet("sql")) { Console.WriteLine("Please enter sql and finish with semicolon: "); while (!sqlText.Trim().EndsWith(";")) { sqlText += " " + Console.ReadLine(); } sqlText = sqlText.Substring(0, sqlText.Length - 1); } else { sqlText = settings.GetOptValue("sql"); Console.WriteLine(sqlText); } TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted); DataTable table = db.SelectDT(sqlText, "temp", transaction); XmlDocument doc = TDataBase.DataTableToXml(table); if (settings.IsFlagSet("output")) { if (settings.GetOptValue("output").EndsWith("yml")) { TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output")); } else if (settings.GetOptValue("output").EndsWith("csv")) { TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output")); } else if (settings.GetOptValue("output").EndsWith("xml")) { StreamWriter sw = new StreamWriter(settings.GetOptValue("output")); sw.Write(TXMLParser.XmlToString2(doc)); sw.Close(); } } else { TYml2Xml.Xml2Yml(doc, "temp.yml"); StreamReader sr = new StreamReader("temp.yml"); Console.WriteLine(sr.ReadToEnd()); sr.Close(); } db.RollbackTransaction(); db.CloseDBConnection(); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine(e.StackTrace); } }