/// <summary> /// checks if the result for this query is already cached. /// If not, the result is retrieved from the database. /// The result is added to the cache, and the result is returned /// as a DataSet from the cache. /// </summary> /// <param name="sql"></param> /// <param name="ATable">can already have some prepared columns; optional parameter, can be null /// </param> /// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null /// gets passed then the Method executes DB commands with the 'globally available' /// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this /// Argument!</param> /// <returns>void</returns> public DataSet GetDataSet(String sql, DataTable ATable, TDataBase ADataBase = null) { TDataBase DBAccessObj = DBAccess.GetDBAccessObj(ADataBase); TDBTransaction ReadTransaction; Boolean NewTransaction = false; DataSet newDataSet; int counter = 0; foreach (string sqlstr in storedSQLQuery) { if (sqlstr == sql) { // create a clone of the result, so that the returned datasets // can be treated separately return(((DataSet)storedDataSet[counter]).Copy()); } counter++; } try { ReadTransaction = DBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); if (ATable == null) { newDataSet = DBAccessObj.Select(sql, "Cache", ReadTransaction); } else { newDataSet = new DataSet(); newDataSet.Tables.Add(ATable); ATable.TableName = "Cache"; DBAccessObj.Select(newDataSet, sql, "Cache", ReadTransaction); } } finally { if (NewTransaction) { DBAccessObj.CommitTransaction(); } } storedDataSet.Add(newDataSet); storedSQLQuery.Add(sql); // return a copy, so that changes to the dataset don't affect the stored copy. return(newDataSet.Copy()); }
/// <summary> /// checks if the result for this query is already cached. /// If not, the result is retrieved from the database. /// The result is added to the cache, and the result is returned /// as a DataSet from the cache. /// </summary> /// <param name="sql"></param> /// <param name="AParameters">odbc parameters</param> /// <param name="ATable">can already have some prepared columns; optional parameter, can be null /// </param> /// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null /// gets passed then the Method executes DB commands with the 'globally available' /// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this /// Argument!</param> /// <returns>void</returns> public DataSet GetDataSet(String sql, OdbcParameter[] AParameters, DataTable ATable, TDataBase ADataBase = null) { TDataBase DBAccessObj = DBAccess.GetDBAccessObj(ADataBase); TDBTransaction ReadTransaction; Boolean NewTransaction = false; DataSet newDataSet; int counter = 0; foreach (string sqlstr in storedSQLQuery) { if (sqlstr == sql) { bool SameParameters = false; if (AParameters == null && storedParameters[counter] == null) { SameParameters = true; } else if (AParameters == null || storedParameters[counter] == null) { SameParameters = false; } else if (AParameters.Length != ((OdbcParameter[])storedParameters[counter]).Length) { SameParameters = false; } else { int countParam = 0; SameParameters = true; foreach (OdbcParameter p in AParameters) { if (p.OdbcType != ((OdbcParameter[])storedParameters[counter])[countParam].OdbcType) { SameParameters = false; } else { SameParameters = (p.Value.ToString() == ((OdbcParameter[])storedParameters[counter])[countParam].ToString()); } countParam++; } } if (SameParameters) { // create a clone of the result, so that the returned datasets // can be treated separately return(((DataSet)storedDataSet[counter]).Copy()); } } counter++; } try { ReadTransaction = DBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); if (ATable == null) { newDataSet = DBAccessObj.Select(sql, "Cache", ReadTransaction, AParameters); } else { newDataSet = new DataSet(); newDataSet.Tables.Add(ATable); ATable.TableName = "Cache"; DBAccessObj.Select(newDataSet, sql, "Cache", ReadTransaction, AParameters); } } finally { if (NewTransaction) { DBAccessObj.CommitTransaction(); } } storedDataSet.Add(newDataSet); storedSQLQuery.Add(sql); storedParameters.Add(AParameters); // return a copy, so that changes to the dataset don't affect the stored copy. return(newDataSet.Copy()); }
/// <summary> /// Gets called in regular intervals from a Timer in Class TTimedProcessing. /// </summary> /// <param name="ADataBaseObj">Already instatiated DB Access object with opened DB connection.</param> /// <param name="ARunManually">this is true if the process was called manually from the server admin console</param> public static void Process(TDataBase ADataBaseObj, bool ARunManually) { TDBTransaction ReadWriteTransaction; bool NewTransaction; bool LastReminderDateAcquired; DateTime LastReminderDate; DataSet ReminderResultsDS; SSystemDefaultsRow SystemDefaultsDR; PPartnerReminderTable PartnerReminderDT; int ReminderFreqency; TSmtpSender Sender = null; if (TLogging.DebugLevel >= 6) { TLogging.Log("Entering TProcessPartnerReminders.Process..."); } ReadWriteTransaction = ADataBaseObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); /* * This whole process must either succeed or fail, therefore the whole thing is in a try-catch. */ try { /* * Obtain date when PartnerReminders last ran. This is stored in a SystemDefault. If it doesn't exist already, * a new SystemDefault with an ancient date is created for us. */ LastReminderDateAcquired = GetLastReminderDate(out LastReminderDate, out SystemDefaultsDR, ReadWriteTransaction); if (!LastReminderDateAcquired) { TLogging.Log( TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Could not send Partner Reminders because Petra couldn't create the required SystemDefault setting for the Last Reminder Date!"); ADataBaseObj.RollbackTransaction(); return; } try { Sender = new TSmtpSender(); } catch (ESmtpSenderInitializeException e) { TLogging.Log( TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": " + e.Message); if (e.InnerException != null) { TLogging.Log(e.InnerException.ToString(), TLoggingType.ToLogfile); } return; } // Retrieve all PartnerReminders we need to process. ReminderResultsDS = GetRemindersToProcess(LastReminderDate, out PartnerReminderDT, ADataBaseObj, ReadWriteTransaction); /* * We now have a Typed DataTable with the PartnerReminders that we need to process. * Iterate through the PartnerReminders, update data, and send an email for each PartnerReminder. */ if (TLogging.DebugLevel >= 6) { TLogging.Log("_---------------------------------_"); TLogging.Log("PartnerReminders data before we start processing all PartnerReminders...."); TLogging.Log(ReminderResultsDS.GetXml().ToString()); } foreach (PPartnerReminderRow PartnerReminderDR in PartnerReminderDT.Rows) { if (TLogging.DebugLevel >= 4) { TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Processing Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey)); } ReminderFreqency = (PartnerReminderDR.IsReminderFrequencyNull()) ? 0 : PartnerReminderDR.ReminderFrequency; PartnerReminderDR.BeginEdit(); PartnerReminderDR.LastReminderSent = DateTime.Now.Date; PartnerReminderDR.NextReminderDate = DateTime.Now.Date.AddDays(ReminderFreqency); if (!PartnerReminderDR.IsEventDateNull()) // Reminder has an Event Date { if (PartnerReminderDR.NextReminderDate > PartnerReminderDR.EventDate) { if (TLogging.DebugLevel >= 5) { TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Deactivating Reminder ID {0} for Partner {1} as its Event Date is in the past.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey)); } PartnerReminderDR.ReminderActive = false; } } if (TLogging.DebugLevel >= 4) { TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Sending email for Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey)); } Boolean emailSentOk = false; try { emailSentOk = SendReminderEmail(PartnerReminderDR, ReadWriteTransaction, Sender); } catch (ESmtpSenderInitializeException e) // if an exception was thrown, assume the email didn't go. { TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey, e.Message)); if (e.InnerException != null) { TLogging.Log(e.InnerException.Message); } } catch (ESmtpSenderSendException e) { TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey, e.Message)); if (e.InnerException != null) { TLogging.Log(e.InnerException.Message); } } catch (Exception e) { TLogging.Log(e.Message); } if (emailSentOk) { // Accept the edit if (TLogging.DebugLevel >= 4) { TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Reminder ID {0} for Partner {1} accepted by SMTP server.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey)); } PartnerReminderDR.EndEdit(); } else { // Cancel the edit TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + ": Reminder ID {0} for Partner {1} REJECTED by SMTP server.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey)); PartnerReminderDR.CancelEdit(); } } if (TLogging.DebugLevel >= 6) { TLogging.Log("_---------------------------------_"); TLogging.Log("PartnerReminders data after processing all PartnerReminders, before writing it to DB...."); TLogging.Log(PartnerReminderDT.DataSet.GetXml().ToString()); } // Update all the changed PartnerReminder Rows PPartnerReminderAccess.SubmitChanges(PartnerReminderDT, ReadWriteTransaction); if (TLogging.DebugLevel >= 6) { TLogging.Log("_---------------------------------_"); } /* * Update the SystemDefault that keeps track of when Partner Reminders last ran. * (SystemDefaultsDR will point to the row we loaded earlier on, OR the row we added earlier on * if there wasn't already a SystemDefault row.) */ UpdateLastReminderDate(SystemDefaultsDR, ReadWriteTransaction); if (NewTransaction) { ADataBaseObj.CommitTransaction(); } TLogging.LogAtLevel(1, TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " ran succesfully."); } catch (Exception Exc) { TLogging.Log( TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " encountered an Exception:" + Environment.NewLine + Exc.ToString()); if (NewTransaction) { ADataBaseObj.RollbackTransaction(); } throw; } finally { if (Sender != null) { Sender.Dispose(); } } }
/// <summary> /// Records the logging-out (=disconnection) of a Client to the s_login DB Table. That DB Table contains a log /// of all the log-ins/log-in attempts to the system, and of log-outs from the system. /// </summary> /// <param name="AUserID">UserID of the User for which a logout should be recorded.</param> /// <param name="AProcessID">ProcessID of the User for which a logout should be recorded. /// This will need to be the number that got returned from an earlier call to /// <see cref="AddLoginLogEntry(string, string, string, out int, TDBTransaction)"/>!</param> /// <param name="ATransaction">Either an instantiated DB Transaction, or null. In the latter case /// a separate DB Connection gets opened, a DB Transaction on that separate DB Connection gets started, /// then committed/rolled back and the separate DB Connection gets closed. This is needed when this Method /// gets called from Method 'Ict.Common.Remoting.Server.TDisconnectClientThread.StartClientDisconnection()'!</param> public void RecordUserLogout(String AUserID, int AProcessID, TDBTransaction ATransaction) { TDataBase DBConnectionObj = null; TDBTransaction WriteTransaction = null; SLoginTable LoginTable = new SLoginTable(); SLoginRow NewLoginRow = LoginTable.NewRowTyped(false); bool SubmissionOK = false; DateTime LogoutDateTime = DateTime.Now; // Set DataRow values NewLoginRow.LoginProcessId = -1; NewLoginRow.UserId = AUserID.ToUpper(); NewLoginRow.LoginType = LOGIN_STATUS_TYPE_LOGOUT; NewLoginRow.LoginDetails = Catalog.GetString("User logout."); NewLoginRow.LoginProcessIdRef = AProcessID; NewLoginRow.Date = LogoutDateTime.Date; NewLoginRow.Time = Conversions.DateTimeToInt32Time(LogoutDateTime); //TLogging.Log(String.Format("RecordUserLogout: NewLoginRow.Date: {0}; NewLoginRow.Time: {1}", // NewLoginRow.Date, NewLoginRow.Time)); LoginTable.Rows.Add(NewLoginRow); if (ATransaction == null) { // Open a separate DB Connection (necessary because this Method gets executed in the Server's (Main) AppDomain // which hasn't got an instance of DBAccess.GDBAccess!) ... DBConnectionObj = DBAccess.SimpleEstablishDBConnection("RecordUserLogout"); // ...and start a DB Transaction on that separate DB Connection WriteTransaction = DBConnectionObj.BeginTransaction(IsolationLevel.RepeatableRead, 0, "RecordUserLogout"); } else { DBConnectionObj = ATransaction.DataBaseObj; WriteTransaction = ATransaction; } try { try { SLoginAccess.SubmitChanges(LoginTable, WriteTransaction); SubmissionOK = true; } catch (Exception Exc) { TLogging.Log("RecordUserLogout: An Exception occured during the saving of a Login Log entry:" + Environment.NewLine + Exc.ToString()); throw; } } finally { if (SubmissionOK) { DBConnectionObj.CommitTransaction(); } else { DBConnectionObj.RollbackTransaction(); } if (DBConnectionObj != null) { DBConnectionObj.CloseDBConnection(); } } }