public void SaveToDatabase(MySqlConnection con)
{
ValidateDataBeforeSave();
if (ID == 0)
{
// Insert
string strCommandText = InsertCommand;
using (MySqlCommand cmd = new MySqlCommand(strCommandText, con))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new Exception(@"Insert: 'SELECT LAST_INSERT_ID()' seems to have failed");
}
ID = reader.GetUInt32(0);
}
}
AdditionalInsertCode(con);
}
else
{
// Update
MySqlUtils.ExecuteNonQuery(con, UpdateCommand);
AdditionalUpdateCode(con);
}
}
private static void SendDataDrivenEmail(MySqlConnection con)
{
Dictionary <string, string> dictFlareSystemConfigurationEntries = SystemConfigurationEntry.GetDictionary(con);
if (!dictFlareSystemConfigurationEntries.ContainsKey(SystemConfigurationEntryKeys.SMTPServerAddress))
{
throw new Exception(@"Flare system configuration error: No SMTP server address is specified");
}
if (!dictFlareSystemConfigurationEntries.ContainsKey(SystemConfigurationEntryKeys.SMTPServerUserName))
{
throw new Exception(@"Flare system configuration error: No SMTP server user name is specified");
}
if (!dictFlareSystemConfigurationEntries.ContainsKey(SystemConfigurationEntryKeys.SMTPServerPassword))
{
throw new Exception(@"Flare system configuration error: No SMTP server password is specified");
}
string strSMTPServerAddress = dictFlareSystemConfigurationEntries[SystemConfigurationEntryKeys.SMTPServerAddress];
string strSMTPServerUserName = dictFlareSystemConfigurationEntries[SystemConfigurationEntryKeys.SMTPServerUserName];
string strSMTPServerPassword = dictFlareSystemConfigurationEntries[SystemConfigurationEntryKeys.SMTPServerPassword];
const string strFrom = @"*****@*****.**";
const string strTo = @"*****@*****.**";
const string strSubject = @"Data-driven e-mail test";
string strBody = string.Format(@"Hello Tom; It is now {0} Universal Time.",
MySqlUtils.DateTimeToString(DateTime.UtcNow));
Console.WriteLine(@"About to send data-driven e-mail to {0}...", strTo);
MailUtils.SendMail(strSMTPServerAddress, strSMTPServerUserName, strSMTPServerPassword,
strFrom, strTo, null, null, strSubject, strBody);
Console.WriteLine(@"E-mail sent.");
}
private void DeleteFromDatabase(MySqlConnection con)
{
// Assert(FieldID > 0);
string strCommandText = string.Format(@"DELETE FROM TargetFormFields WHERE FieldID={0};", FieldID);
MySqlUtils.ExecuteNonQuery(con, strCommandText);
}
private void UpdateInDatabase(MySqlConnection con)
{
// Assert(FieldID > 0);
string strCommandText = string.Format(@"UPDATE TargetFormFields SET TargetID={0}, FieldName={1}, FieldValue={2} WHERE FieldID={3};",
TargetID,
MySqlUtils.RawStringToDatabaseString(FieldName),
MySqlUtils.RawStringToDatabaseString(FieldValue),
FieldID);
MySqlUtils.ExecuteNonQuery(con, strCommandText);
}
public void DeleteFromDatabase(MySqlConnection con)
{
if (ID == 0)
{
// This object's data has not yet been inserted into the database table; there is nothing to delete.
return;
}
AdditionalDeleteCode(con);
MySqlUtils.ExecuteNonQuery(con, DeleteCommand);
}
public static string GetSelectCommand(uint unTargetID, uint unAccountID, bool bFailedOnly, DateTime?ndtStartTimeStamp, DateTime?ndtEndTimeStamp)
{
StringBuilder sb = new StringBuilder();
//string strSelectCommand = @"SELECT LogID, TargetID, TimeStamp, Status, Message, ErrorCode, LocationID, ResponseTime FROM TargetLog";
string strSeparatorWord = @" WHERE ";
string strAnd = @" AND ";
sb.Append(@"SELECT tl.LogID, tl.TargetID, tl.TimeStamp, tl.Status, tl.Message, tl.ErrorCode, tl.LocationID, tl.ResponseTime FROM TargetLog tl");
if (unTargetID > 0)
{
sb.Append(strSeparatorWord);
sb.Append(string.Format(@"tl.TargetID={0}", unTargetID));
strSeparatorWord = strAnd;
}
else if (unAccountID > 0)
{
sb.Append(@", Targets t");
sb.Append(strSeparatorWord);
sb.Append(string.Format(@"tl.TargetID=t.TargetID AND t.AccountID={0}", unAccountID));
strSeparatorWord = strAnd;
}
if (bFailedOnly)
{
sb.Append(strSeparatorWord);
sb.Append(@"tl.Status LIKE 'FAIL'");
strSeparatorWord = strAnd;
}
if (ndtStartTimeStamp.HasValue)
{
sb.Append(strSeparatorWord);
sb.Append(string.Format(@"tl.TimeStamp >= {0}", MySqlUtils.DateTimeToDatabaseString(ndtStartTimeStamp.Value)));
strSeparatorWord = strAnd;
}
if (ndtEndTimeStamp.HasValue)
{
sb.Append(strSeparatorWord);
sb.Append(string.Format(@"tl.TimeStamp <= {0}", MySqlUtils.DateTimeToDatabaseString(ndtEndTimeStamp.Value)));
strSeparatorWord = strAnd;
}
sb.Append(@" ORDER BY tl.TimeStamp;");
return(sb.ToString());
}
public override void ProcessData()
{
var ids = MySqlUtils.Fill(GetSqlSources()).AsEnumerable()
.Select(r => Convert.ToUInt32(r["SupplierId"]))
.ToList();
foreach (var id in ids)
{
SessionHelper.StartSession(s => {
try {
session = s;
ProcessSource(id);
}
finally {
session = null;
}
});
}
}
private void InsertIntoDatabase(MySqlConnection con)
{
// Assert(FieldID == 0);
string strCommandText = string.Format(@"INSERT INTO TargetFormFields VALUES ({0}, {1}, {2}, NULL); SELECT LAST_INSERT_ID() FROM TargetFormFields;",
TargetID,
MySqlUtils.RawStringToDatabaseString(FieldName),
MySqlUtils.RawStringToDatabaseString(FieldValue));
using (MySqlCommand cmd = new MySqlCommand(strCommandText, con))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new Exception(@"Insert: 'SELECT LAST_INSERT_ID()' seems to have failed");
}
FieldID = reader.GetUInt32(0);
}
}
}
protected void lbSearchResultSummaries_SelectedIndexChanged(object sender, EventArgs e)
{
string strSelectedSearchResultSummary = lbSearchResultSummaries.SelectedValue;
if (strSelectedSearchResultSummary == null || !SearchResults.ContainsKey(strSelectedSearchResultSummary))
{
ClearSelectedSearchResultFields();
return;
}
TargetLogRecord tlrSearchResult = SearchResults[strSelectedSearchResultSummary];
tbLogRecordID.Text = tlrSearchResult.LogID.ToString();
tbTargetID.Text = tlrSearchResult.TargetID.ToString();
tbTimeStamp.Text = MySqlUtils.DateTimeToString(tlrSearchResult.TimeStamp);
tbStatus.Text = tlrSearchResult.Status.ToString();
tbMessage.Text = tlrSearchResult.Message;
tbErrorCode.Text = tlrSearchResult.ErrorCode.ToString();
tbLocationID.Text = tlrSearchResult.LocationID.ToString();
tbResponseTime.Text = tlrSearchResult.ResponseTime.ToString();
}
public void DeleteFromDatabase(MySqlConnection con, UInt32 unTargetID)
{
string strCommandText = string.Format(@"DELETE FROM TargetFormFields WHERE TargetID={0};", unTargetID);
MySqlUtils.ExecuteNonQuery(con, strCommandText);
}
public void UpdateLastTargetLogID(uint unLogID, MySqlConnection con)
{
LastTargetLogID = unLogID;
MySqlUtils.ExecuteNonQuery(con, UpdateLastTargetLogIDCommand);
}
// **** Methods ****
public void UpdateLastMonitoredAt(DateTime dt, MySqlConnection con)
{
LastMonitoredAt = dt;
MySqlUtils.ExecuteNonQuery(con, UpdateLastMonitoredAtCommand);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
uint unTargetID = 0;
if (rbTarget.Checked)
{
string strSelectedTargetName = lbTargetNames.SelectedValue;
if (strSelectedTargetName == null)
{
throw new Exception(@"Cannot search by target: no target name is selected");
}
unTargetID = m_dictTargets[strSelectedTargetName];
}
uint unAccountID = 0;
if (rbAccount.Checked)
{
string strSelectedAccountName = lbAccountNames.SelectedValue;
if (strSelectedAccountName == null)
{
throw new Exception(@"Cannot search by account: no account name is selected");
}
unAccountID = m_dictAccounts[strSelectedAccountName];
}
bool bFailedOnly = cbFailuresOnly.Checked;
DateTime?ndtStartTimeStamp = null;
if (cbStartTimeStamp.Checked)
{
ndtStartTimeStamp = DateTime.Parse(tbStartTimeStamp.Text);
}
DateTime?ndtEndTimeStamp = null;
if (cbEndTimeStamp.Checked)
{
ndtEndTimeStamp = DateTime.Parse(tbEndTimeStamp.Text);
}
string strCommandText = TargetLogRecord.GetSelectCommand(unTargetID, unAccountID, bFailedOnly, ndtStartTimeStamp, ndtEndTimeStamp);
MySqlConnection con = GetMySqlConnection();
List <TargetLogRecord> lstSearchResults = TargetLogRecord.GetRecords(con, strCommandText);
Dictionary <string, TargetLogRecord> dictSearchResults = new Dictionary <string, TargetLogRecord>();
foreach (TargetLogRecord tlr in lstSearchResults)
{
uint unTargetID2 = tlr.TargetID;
string strRecordSummary = string.Format(@"{0} {1} {2}",
//tlr.TimeStamp.ToString(@"yyyy-MM-dd HH:mm:ss"),
MySqlUtils.DateTimeToString(tlr.TimeStamp),
tlr.Status,
m_dictTargetsIDToName.ContainsKey(unTargetID2) ? m_dictTargetsIDToName[unTargetID2] : unTargetID2.ToString());
dictSearchResults.Add(strRecordSummary, tlr);
}
// Store (lstSearchResults and) dictSearchResults in the Session.
SearchResults = dictSearchResults;
WebUtils.PopulateListItemCollectionFromStringEnumerable(dictSearchResults.Keys, lbSearchResultSummaries.Items);
ClearSelectedSearchResultFields();
}
// Send an e-mail to each of the target's account's contacts.
private void SendFailureNotificationEmails(Target target, TargetLogRecord tlr)
{
if (!m_dictSystemConfigurationEntries.ContainsKey(SystemConfigurationEntryKeys.SMTPServerAddress))
{
throw new Exception(@"Flare system configuration error: No SMTP server address is specified");
}
if (!m_dictSystemConfigurationEntries.ContainsKey(SystemConfigurationEntryKeys.SMTPServerUserName))
{
throw new Exception(@"Flare system configuration error: No SMTP server user name is specified");
}
if (!m_dictSystemConfigurationEntries.ContainsKey(SystemConfigurationEntryKeys.SMTPServerPassword))
{
throw new Exception(@"Flare system configuration error: No SMTP server password is specified");
}
string strSMTPServerAddress = m_dictSystemConfigurationEntries[SystemConfigurationEntryKeys.SMTPServerAddress];
string strSMTPServerUserName = m_dictSystemConfigurationEntries[SystemConfigurationEntryKeys.SMTPServerUserName];
string strSMTPServerPassword = m_dictSystemConfigurationEntries[SystemConfigurationEntryKeys.SMTPServerPassword];
const string strFrom = @"Flare Server Monitoring";
const string strSubject = @"Server monitoring failure notification";
foreach (Contact contact in m_lstAllContacts)
{
if (contact.AccountID != target.AccountID || !contact.Enabled) // We could filter out disabled contacts at the SQL level
{
continue;
}
string strBody = string.Format(@"Hello {0} {1}; Your server '{2}' was unreachable at {3} Universal Time.",
contact.FirstName, contact.LastName, target.Name, MySqlUtils.DateTimeToString(tlr.TimeStamp));
#if DO_NOT_SEND_EMAILS
LogToConsole(@"**** Simulating the sending of e-mail ****");
LogToConsole(string.Format(@"SMTP Server Address : {0}", strSMTPServerAddress));
LogToConsole(string.Format(@"SMTP Server User Name : {0}", strSMTPServerUserName));
LogToConsole(string.Format(@"SMTP Server Password : {0}", strSMTPServerPassword));
LogToConsole(string.Format(@"From : {0}", strFrom));
LogToConsole(string.Format(@"To : {0}", contact.EmailAddress));
LogToConsole(string.Format(@"Subject : {0}", strSubject));
LogToConsole(string.Format(@"Body : {0}", strBody));
LogToConsole(@"**** End of e-mail ****");
#else
MailUtils.SendMail(strSMTPServerAddress, strSMTPServerUserName, strSMTPServerPassword,
strFrom, contact.EmailAddress, null, null, strSubject, strBody);
#endif
}
}
public static void Main(string[] args)
{
Console.WriteLine(@"GeneralTestApp: Begin");
try
{
/*
* // Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
* const string strConnectionString = @"Server=localhost;Database=flare;Uid=flare;Pwd=tomtom7";
*
* Console.WriteLine(@"Attempting to connect to MySQL database server...");
*
* using (MySqlConnection con = new MySqlConnection(strConnectionString))
* {
* con.Open();
* Console.WriteLine(string.Format(@"Connected to MySQL database server version {0}", con.ServerVersion));
* con.Close();
* }
*/
#if USE_MYSQL_DATABASE
ConnectToMySqlDatabase();
#endif
#if TEST_STRING_FORMAT_LEADING_ZEROES
Console.WriteLine(string.Format(@"Leading zero test: '{0:0000}'", 13));
#endif
#if TEST_WRITE_FORMATTED_DATETIME
DateTime dt = DateTime.Now;
Console.WriteLine(string.Format(@"It is now '{0:0000}-{1:00}-{2:00} {3:00}:{4:00}:{5:00}'",
dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second));
#endif
#if TEST_PARSE_STRING_AS_DATETIME
DateTime dt = DateTime.Now;
Console.WriteLine(dt.ToString());
string strDateTimeAsDatabaseString = MySqlUtils.DateTimeToDatabaseString(dt);
Console.WriteLine(strDateTimeAsDatabaseString);
string strDateTimeAsString = strDateTimeAsDatabaseString.Substring(1, strDateTimeAsDatabaseString.Length - 2);
Console.WriteLine(strDateTimeAsString);
DateTime dt2 = DateTime.Parse(strDateTimeAsString);
Console.WriteLine(dt2.ToString());
#endif
#if TEST_PING
Ping(args);
#endif
}
catch (Exception ex)
{
Console.WriteLine(string.Format(@"{0} caught: {1}", ex.GetType().FullName, ex.Message));
}
Console.WriteLine(@"GeneralTestApp: End");
}
protected override void PageLoadWorker(object sender, EventArgs e)
{
bool bPopulateTargetNamesListBox = false;
// Ensure that m_dictTargets is set.
m_dictTargets = TargetNameToIDDictionary;
m_dictTargetsIDToName = TargetIDToNameDictionary;
if (m_dictTargets == null)
{
MySqlConnection con = GetMySqlConnection();
m_dictTargets = NamedAndNumberedDatabaseObject.GetNameToIDDictionary(con, Target.SelectIDAndNameCommand);
TargetNameToIDDictionary = m_dictTargets;
m_dictTargetsIDToName = null; // Force a rebuild of the Targets ID To Name dictionary.
bPopulateTargetNamesListBox = true;
}
else if (!IsPostBack)
{
bPopulateTargetNamesListBox = true;
}
if (bPopulateTargetNamesListBox)
{
WebUtils.PopulateListItemCollectionFromStringEnumerable(m_dictTargets.Keys, lbTargetNames.Items);
}
if (m_dictTargetsIDToName == null)
{
/*
* m_dictTargetsIDToName = new Dictionary<uint, string>();
*
* foreach (KeyValuePair<string, uint> kvp in m_dictTargets)
* {
* m_dictTargetsIDToName.Add(kvp.Value, kvp.Key);
* }
*/
m_dictTargetsIDToName = NamedAndNumberedDatabaseObject.GetIDToNameDictionary(m_dictTargets);
TargetIDToNameDictionary = m_dictTargetsIDToName;
}
bool bPopulateAccountNamesListBox = false;
// Ensure that m_dictAccounts is set.
m_dictAccounts = AccountNameToIDDictionary;
if (m_dictAccounts == null)
{
MySqlConnection con = GetMySqlConnection();
m_dictAccounts = NamedAndNumberedDatabaseObject.GetNameToIDDictionary(con, Account.SelectIDAndNameCommand);
AccountNameToIDDictionary = m_dictAccounts;
bPopulateAccountNamesListBox = true;
}
else if (!IsPostBack)
{
bPopulateAccountNamesListBox = true;
}
if (bPopulateAccountNamesListBox)
{
WebUtils.PopulateListItemCollectionFromStringEnumerable(m_dictAccounts.Keys, lbAccountNames.Items);
}
if (!IsPostBack)
{
string strNow = MySqlUtils.DateTimeToString(DateTime.UtcNow); //dtNow.ToString(@"yyyy-MM-dd HH:mm:ss");
rbAny.Checked = true;
lbTargetNames.Enabled = false;
lbAccountNames.Enabled = false;
tbStartTimeStamp.Enabled = false;
tbStartTimeStamp.Text = strNow;
tbEndTimeStamp.Enabled = false;
tbEndTimeStamp.Text = strNow;
}
if (SearchResults == null)
{
ClearSelectedSearchResultFields();
}
}
public void setUp()
{
connection = new MySqlConnection(@"server=localhost;userid=root;password=admin;database=employees");
connection.Open();
dbUtils = new MySqlUtils(connection);
}
