/// <summary>
/// Creates the failure report single audit.
/// </summary>
/// <param name="audit">The audit.</param>
/// <param name="auditDataSet">The audit data set.</param>
/// <returns>System.String.</returns>
public string CreateFailureReportSingleAudit(Audit audit, DataSet auditDataSet)
{
return(PrepareResultsSingleAudit(audit, auditDataSet));
}
private static string PrepareResultsSingleAudit(Audit testedAudit, DataSet testData)
{
var body = new StringBuilder();
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
string sourceEmailDescription = config.AppSettings.Settings["sourceEmailDescription"].Value;
if (testedAudit.Test.SendReport)
{
testedAudit.ShowThresholdMessage = false;
testedAudit.ShowQueryMessage = false;
if (testedAudit.EmailSubject != null)
{
body.AppendLine("<h2>" + testedAudit.EmailSubject + "</h2>");
}
body.Append("This report ran at " +
DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture) +
AuditUtils.HtmlBreak + AuditUtils.HtmlBreak);
}
if (testedAudit.ShowThresholdMessage)
{
body.AppendLine("<h2>ERROR MESSAGE</h2>");
body.Append(testedAudit.Test.FailedMessage + AuditUtils.HtmlBreak + AuditUtils.HtmlBreak);
}
if (testedAudit.ShowCommentMessage)
{
body.AppendLine("COMMENTS AND INSTRUCTIONS" + AuditUtils.HtmlBreak);
body.AppendLine("============================" + AuditUtils.HtmlBreak);
if (testedAudit.Test.Instructions != null)
{
if (testedAudit.Test.Instructions.Length > 0)
{
body.Append(testedAudit.Test.Instructions.ToHtml() + AuditUtils.HtmlBreak);
body.AppendLine(AuditUtils.HtmlBreak);
}
}
}
if (testedAudit.IncludeDataInEmail)
{
if (testData.Tables.Count > 0)
{
EmailTableTemplate currTemplate = testedAudit.Test.TemplateColorScheme;
string htmlData = AuditUtils.CreateHtmlData(testedAudit, testData, currTemplate);
body.Append(htmlData);
}
}
body.AppendLine(AuditUtils.HtmlBreak);
if (testedAudit.Test.SendReport)
{
body.Append("This report ran at " + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture) + AuditUtils.HtmlBreak);
body.Append("<b>This report was run on: " + testedAudit.TestServer + "</b>" + AuditUtils.HtmlBreak + AuditUtils.HtmlBreak);
}
else
{
body.Append("This audit ran at " + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture) + AuditUtils.HtmlBreak);
}
if (testedAudit.ShowQueryMessage)
{
body.Append(AuditUtils.HtmlBreak);
body.Append("The '" + testedAudit.Name + "' audit has failed. The following SQL statement was used to test this audit :" + AuditUtils.HtmlBreak);
body.Append(testedAudit.Test.SqlStatementToCheck.ToHtml() + AuditUtils.HtmlBreak);
body.Append("<b>This query was run on: " + testedAudit.TestServer + "</b>" + AuditUtils.HtmlBreak + AuditUtils.HtmlBreak);
}
string cleanBody = body.ToString().Replace("\r\n", string.Empty);
return(cleanBody);
}
private static void SendEmail(Audit testedAudit, string body, string sourceEmailDescription)
{
var message = new MailMessage {
IsBodyHtml = true
};
foreach (string recipient in _colAudits.EmailSubscribers)
{
message.To.Add(new MailAddress(recipient));
}
if (_colAudits.EmailCarbonCopySubscribers != null)
{
// Carbon Copies - CC
foreach (string ccemail in _colAudits.EmailCarbonCopySubscribers)
{
message.CC.Add(new MailAddress(ccemail));
}
}
if (_colAudits.EmailBlindCarbonCopySubscribers != null)
{
// Blind Carbon Copies - BCC
foreach (string bccemail in _colAudits.EmailBlindCarbonCopySubscribers)
{
message.Bcc.Add(new MailAddress(bccemail));
}
}
message.Body = body;
switch (testedAudit.EmailPriority)
{
case EmailPriorityEnum.Low:
message.Priority = MailPriority.Low;
break;
case EmailPriorityEnum.Normal:
message.Priority = MailPriority.Normal;
break;
case EmailPriorityEnum.High:
message.Priority = MailPriority.High;
break;
default:
message.Priority = MailPriority.Normal;
break;
}
if (!string.IsNullOrEmpty(testedAudit.EmailSubject))
{
message.Subject = testedAudit.EmailSubject;
}
else
{
message.Subject = "Audit Failure - " + testedAudit.Name;
}
message.From = new MailAddress(_colAudits.SmtpSourceEmail, sourceEmailDescription);
var server = new SmtpClient();
if (_colAudits.SmtpHasCredentials)
{
server.UseDefaultCredentials = false;
server.DeliveryMethod = SmtpDeliveryMethod.Network;
server.Host = _colAudits.SmtpServerAddress;
server.Port = _colAudits.SmtpPort;
server.Credentials = new NetworkCredential(_colAudits.SmtpUserName, _colAudits.SmtpPassword);
server.EnableSsl = _colAudits.SmtpUseSsl;
}
else
{
server.Host = _colAudits.SmtpServerAddress;
}
try
{
server.Send(message);
}
catch (SmtpException smtpEx)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(smtpEx.Message);
if (smtpEx.InnerException != null)
{
sb.AppendLine(smtpEx.InnerException.Message);
}
throw;
}
}
/// <summary>
/// Creates the HTML content for the email.
/// </summary>
/// <param name="testedAudit">The tested audit.</param>
/// <param name="testData">The test data.</param>
/// <param name="emailTableTemplate">The email table template.</param>
/// <returns>System.String.</returns>
public static string CreateHtmlData(Audit testedAudit, DataSet testData, EmailTableTemplate emailTableTemplate)
{
var sb = new StringBuilder();
if (emailTableTemplate.Equals(null))
{
emailTableTemplate = GetDefaultTemplate();
}
int tableNamesCount = 0;
foreach (DataTable currTable in testData.Tables)
{
if (testedAudit.Test.MultipleResults)
{
sb.Append("<B>");
sb.Append(testedAudit.Test.TableNames[tableNamesCount]);
sb.Append("</B>");
sb.AppendLine("<br>");
}
sb.AppendFormat(@"<caption> Total Rows = ");
sb.AppendFormat(currTable.Rows.Count.ToString(CultureInfo.InvariantCulture));
sb.AppendFormat(@"</caption>");
if (!string.IsNullOrEmpty(emailTableTemplate.CssTableStyle))
{
sb.AppendLine("<style>");
sb.AppendLine(emailTableTemplate.CssTableStyle);
sb.AppendLine("</style>");
sb.Append("<TABLE id=emailtable>");
sb.Append("<TR>");
}
else
{
sb.Append("<TABLE BORDER=1>");
sb.Append("<TR ALIGN='LEFT' style='white-space: nowrap;'>");
}
// first append the column names.
foreach (DataColumn column in currTable.Columns)
{
if (!string.IsNullOrEmpty(emailTableTemplate.CssTableStyle))
{
sb.Append("<TH>" + column.ColumnName + "</TH>");
}
else
{
sb.Append("<TD style='white-space: nowrap;' bgcolor=\"" +
emailTableTemplate.HtmlHeaderBackgroundColor +
"\"><B>");
sb.Append("<font color=\"" + emailTableTemplate.HtmlHeaderFontColor + "\">" + column.ColumnName +
"</font>");
sb.Append("</B></TD>");
}
}
sb.Append("</TR>");
int rowCounter = 1;
// next, the column values.
foreach (DataRow row in currTable.Rows)
{
if (!string.IsNullOrEmpty(emailTableTemplate.CssTableStyle))
{
if (emailTableTemplate.UseAlternateRowColors)
{
if (rowCounter % 2 == 0)
{
// Even numbered row, so tag it with a different background color.
sb.Append("<TR style='white-space: nowrap;' ALIGN='LEFT' bgcolor=\"" +
emailTableTemplate.AlternateRowColor + "\">");
}
else
{
sb.Append("<TR style='white-space: nowrap;' ALIGN='LEFT'>");
}
}
else
{
sb.Append("<TR>");
}
}
else
{
if (emailTableTemplate.UseAlternateRowColors)
{
if (rowCounter % 2 == 0)
{
// Even numbered row, so tag it with a different background color.
sb.Append("<TR style='white-space: nowrap;' ALIGN='LEFT' bgcolor=\"" +
emailTableTemplate.AlternateRowColor + "\">");
}
else
{
sb.Append("<TR style='white-space: nowrap;' ALIGN='LEFT'>");
}
}
else
{
sb.Append("<TR style='white-space: nowrap;' ALIGN='LEFT'>");
}
}
foreach (DataColumn column in currTable.Columns)
{
if (!string.IsNullOrEmpty(emailTableTemplate.CssTableStyle))
{
sb.Append("<TD>");
if (row[column].ToString().Trim().Length > 0)
{
sb.Append(row[column]);
}
else
{
sb.Append(" ");
}
}
else
{
sb.Append("<TD style='white-space: nowrap;'>");
if (row[column].ToString().Trim().Length > 0)
{
sb.Append(row[column]);
}
else
{
sb.Append(" ");
}
}
sb.Append("</TD>");
}
sb.Append("</TR>");
rowCounter++;
}
sb.Append("</TABLE>");
sb.Append("<br>");
tableNamesCount++;
}
return(sb.ToString());
}
private void RunTests(ref Audit currentAudit)
{
int testCount;
int tempFor1 = 1;
for (testCount = 0; testCount < tempFor1; testCount++)
{
DataSet dsTest = GetTestDataSet(ref currentAudit, testCount);
if (dsTest.Tables.Count == 0)
{
AuditTest currTest = currentAudit.Test;
if (!currTest.FailIfConditionIsTrue)
{
currentAudit.Result = true;
}
else
{
// TODO: This is a hack that needs to be fixed.
// I want the test to succeed, but not send
// any emails. When this app was first built,
// it always assumed that the audit would fail or
// succeed with no further processing. This is to handle
// the weird case where there are actually two thresholds;
// the first one is the usual one, and the second one
// is for the data itself.
// TODO: Think of a better way of doing this!
if (currTest.SendReport)
{
currentAudit.Result = true;
}
else
{
currentAudit.Result = false;
PrepareResultsEmailData(currentAudit.Test.SqlStatementToCheck, currentAudit,
dsTest);
}
}
}
else
{
var currTest = currentAudit.Test;
int rowCount = dsTest.Tables[0].Rows.Count;
if (currTest.TestReturnedRows)
{
if (currTest.Criteria.ToUpper() == "COUNTROWS")
{
string threshold;
switch (currTest.Operator)
{
case ">":
if (rowCount > currTest.RowCount)
{
if (!currTest.FailIfConditionIsTrue)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was greater than " + threshold + " rows. This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
else
{
if (rowCount <= currTest.RowCount)
{
// Threshold was not broken, so the test passes.
currentAudit.Result = true;
}
else
{
threshold =
currentAudit.Test.RowCount.ToString(
CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage =
"The failure threshold was greater than " + threshold +
" rows. This audit returned " +
rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
break;
case ">=":
case "=>":
if (rowCount >= currTest.RowCount)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was greater than or equal to " + threshold + " rows. This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
break;
case "<":
if (rowCount < currTest.RowCount)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was less than " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "<=":
case "=<":
if (rowCount <= currTest.RowCount)
{
currentAudit.Result = true;
}
else
{
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was less than or equal to " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "=":
if (rowCount == currTest.RowCount)
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
}
else
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
threshold = currentAudit.Test.RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Test.FailedMessage = "The failure threshold was equal to " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "<>":
case "!=":
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
break;
}
}
else
{
if (rowCount > 0)
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
}
else
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
currentAudit.Result = false;
currentAudit.Test.FailedMessage = "This audit was set to have more than zero rows returned. " + "This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
else
{
currentAudit.Result = true;
}
}
}
}
else
{
if (rowCount == 0)
{
currentAudit.Result = true;
}
else
{
currentAudit.Test.FailedMessage = "This audit was set to not return any rows. " + "This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
if (currentAudit.Result == false)
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
PrepareResultsEmailData(currentAudit.Test.SqlStatementToCheck, currentAudit, dsTest);
}
}
else
{
if (currentAudit.Test.FailIfConditionIsTrue)
{
if (currentAudit.Test.SendReport)
{
// It's not really a failure. Just want to send a report-like email.
PrepareResultsEmailData(currentAudit.Test.SqlStatementToCheck, currentAudit, dsTest);
}
}
}
dsTest.Dispose();
}
}
currentAudit.HasRun = true;
}
private DataSet GetTestDataSet(ref Audit auditToRun, int testIndex)
{
IAuditDbProvider currDbProvider = _providers.Providers[_colAudits.DatabaseProvider];
currDbProvider.ConnectionString = _colAudits.ConnectionString.ToString();
if (_colAudits.ConnectionString.ConnectionTimeout != null)
{
currDbProvider.ConnectionTimeout = _colAudits.ConnectionString.ConnectionTimeout;
}
else
{
currDbProvider.CommandTimeout = 15.ToString();
_colAudits.ConnectionString.ConnectionTimeout = 15.ToString();
}
if (_colAudits.ConnectionString.CommandTimeout != null)
{
currDbProvider.CommandTimeout = _colAudits.ConnectionString.CommandTimeout;
}
else
{
currDbProvider.CommandTimeout = 30.ToString();
_colAudits.ConnectionString.CommandTimeout = 30.ToString();
}
currDbProvider.CreateDatabaseSession();
var dsAudit = new DataSet();
string sql = BuildSqlStatement(auditToRun, testIndex);
CommandType commandType = (CommandType)0;
if (auditToRun.Test.SqlType == Audit.SqlStatementTypeEnum.SqlText)
{
commandType = CommandType.Text;
}
else if (auditToRun.Test.SqlType == Audit.SqlStatementTypeEnum.StoredProcedure)
{
commandType = CommandType.StoredProcedure;
}
IDbCommand cmdAudit = currDbProvider.CreateDbCommand(sql, commandType, int.Parse(_colAudits.ConnectionString.CommandTimeout));
IDbDataAdapter daAudit = currDbProvider.CreateDbDataAdapter(cmdAudit);
string intConnectionTimeout = _colAudits.ConnectionString.ConnectionTimeout;
string intCommandTimeout = _colAudits.ConnectionString.CommandTimeout;
try
{
daAudit.Fill(dsAudit);
}
catch (Exception ex)
{
int intFound = 0;
string strMsg = null;
strMsg = ex.Message;
intFound = (strMsg.IndexOf("Timeout expired.", 0, StringComparison.Ordinal) + 1);
if (intFound == 1)
{
auditToRun.Test.FailedMessage = "Timeout expired while running this audit. The connection timeout was " + intConnectionTimeout.ToString(CultureInfo.InvariantCulture) + " seconds. The command timeout was " + intCommandTimeout + " seconds.";
auditToRun.ErrorMessages.Add(auditToRun.Test.FailedMessage);
}
else
{
auditToRun.Test.FailedMessage = strMsg;
auditToRun.ErrorMessages.Add(strMsg);
}
auditToRun.WasSuccessful = false;
}
finally
{
cmdAudit.Dispose();
}
return(dsAudit);
}
/// <summary>
/// Run a single audit.
/// </summary>
/// <param name="currentAudit">The Audit object to use</param>
public void RunDataAudit(ref Audit currentAudit)
{
OnSingleAuditRunning(currentAudit);
RunTests(ref currentAudit);
OnSingleAuditDone(currentAudit);
}
/// <summary>
/// Determines whether [contains] [the specified item].
/// </summary>
/// <param name="item">The item.</param>
/// <returns><c>true</c> if [contains] [the specified item]; otherwise, <c>false</c>.</returns>
public bool Contains(Audit item)
{
return(this.List.Contains(item));
}
/// <summary>
/// Removes the specified item.
/// </summary>
/// <param name="item">The item.</param>
public void Remove(Audit item)
{
this.List.Remove(item);
}
/// <summary>
/// Inserts the specified index.
/// </summary>
/// <param name="index">The index.</param>
/// <param name="item">The item.</param>
public void Insert(int index, Audit item)
{
this.List.Insert(index, item);
}
/// <summary>
/// Adds the specified item.
/// </summary>
/// <param name="item">The item.</param>
/// <returns>System.Int32.</returns>
public int Add(Audit item)
{
return(this.List.Add(item));
}
