private string BuildSqlStatement(Audit auditToParse, int testIndex)
{
string result;
StringBuilder sql = new StringBuilder();
string sqlStatement = auditToParse.SqlStatement;
string orderBy = auditToParse.OrderByClause;
bool useCriteria = auditToParse.Tests[testIndex].UseCriteria;
if (useCriteria)
{
sql.Append(sqlStatement);
string whereClause = BuildWhereClause(auditToParse, testIndex);
sql.Append(" WHERE " + whereClause);
if (orderBy != null)
{
if (orderBy.Length > 0)
{
sql.Append(" ORDER BY " + orderBy);
}
}
result = sql.ToString();
}
else
{
result = auditToParse.SqlStatement;
}
auditToParse.Tests[testIndex].SqlStatementToCheck = result;
return result;
}
private static void PrepareResultsEmailData(string sqlTested, int testIndex, Audit testedAudit, DataSet testData)
{
var body = new StringBuilder();
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
string sourceEmailDescription = config.AppSettings.Settings["sourceEmailDescription"].Value;
if (testedAudit.Tests[testIndex].SendReport)
{
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.Append(testedAudit.Tests[testIndex].FailedMessage + AuditUtils.HtmlBreak + AuditUtils.HtmlBreak);
}
if (testedAudit.ShowCommentMessage)
{
body.AppendLine("COMMENTS AND INSTRUCTIONS" + AuditUtils.HtmlBreak);
body.AppendLine("============================" + AuditUtils.HtmlBreak);
if (testedAudit.Tests[testIndex].Instructions != null)
{
if (testedAudit.Tests[testIndex].Instructions.Length > 0)
{
body.Append(testedAudit.Tests[testIndex].Instructions.ToHtml() + AuditUtils.HtmlBreak);
body.AppendLine(AuditUtils.HtmlBreak);
}
}
}
if (testedAudit.IncludeDataInEmail)
{
if (testData.Tables.Count > 0)
{
EmailTableTemplate currTemplate = testedAudit.Tests[testIndex].TemplateColorScheme;
string htmlData = AuditUtils.CreateHtmlData(testedAudit, testData, currTemplate);
body.Append(htmlData);
}
}
body.AppendLine(AuditUtils.HtmlBreak);
if (testedAudit.Tests[testIndex].SendReport)
{
body.Append("This report ran at " + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture) + 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(sqlTested.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);
SendEmail(testedAudit, cleanBody, sourceEmailDescription);
}
private static void SendEmail(Audit testedAudit, string body, string sourceEmailDescription)
{
var message = new MailMessage {IsBodyHtml = true};
foreach (string recipient in testedAudit.EmailSubscribers)
{
message.To.Add(new MailAddress(recipient));
}
if (testedAudit.EmailCarbonCopySubscribers != null)
{
// Carbon Copies - CC
foreach (string ccemail in testedAudit.EmailCarbonCopySubscribers)
{
message.CC.Add(new MailAddress(ccemail));
}
}
if (testedAudit.EmailBlindCarbonCopySubscribers != null)
{
// Blind Carbon Copies - BCC
foreach (string bccemail in testedAudit.EmailBlindCarbonCopySubscribers)
{
message.Bcc.Add(new MailAddress(bccemail));
}
}
message.Body = body;
switch (testedAudit.EmailPriority)
{
case Audit.EmailPriorityEnum.Low:
message.Priority = MailPriority.Low;
break;
case Audit.EmailPriorityEnum.Normal:
message.Priority = MailPriority.Normal;
break;
case Audit.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(testedAudit.SmtpSourceEmail, sourceEmailDescription);
var server = new SmtpClient();
if (testedAudit.SmtpHasCredentials)
{
server.Host = testedAudit.SmtpServerAddress;
server.Port = testedAudit.SmtpPort;
server.Credentials = new NetworkCredential(testedAudit.SmtpUserName, testedAudit.SmtpPassword);
server.EnableSsl = testedAudit.SmtpUseSsl;
}
else
{
server.Host = testedAudit.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>
/// 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 List.Contains(item);
}
private void _auditTesting_CurrentSingleAuditRunning(Audit CurrentAudit)
{
lsvAudits.Items[_selectedIndex].ImageIndex = 1;
lsvAudits.Items[_selectedIndex].SubItems[2].Text = "Processing";
this.Refresh();
}
private void RunTests(ref Audit currentAudit)
{
int testCount;
int tempFor1 = currentAudit.Tests.Count;
for (testCount = 0; testCount < tempFor1; testCount++)
{
DataSet dsTest = GetTestDataSet(ref currentAudit, testCount);
if (dsTest.Tables.Count == 0)
{
AuditTest currTest = currentAudit.Tests[testCount];
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.Tests[testCount].SqlStatementToCheck, testCount, currentAudit,
dsTest);
}
}
}
else
{
var currTest = currentAudit.Tests[testCount];
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.Tests[testCount].RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Tests[testCount].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.Tests[testCount].RowCount.ToString(
CultureInfo.InvariantCulture);
currentAudit.Tests[testCount].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.Tests[testCount].RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Tests[testCount].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.Tests[testCount].RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Tests[testCount].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.Tests[testCount].RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Tests[testCount].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.Tests[testCount].FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
}
else
{
if (currentAudit.Tests[testCount].FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
threshold = currentAudit.Tests[testCount].RowCount.ToString(CultureInfo.InvariantCulture);
currentAudit.Tests[testCount].FailedMessage = "The failure threshold was equal to " + threshold + " rows. This audit returned " + rowCount + " rows.";
}
break;
case "<>":
case "!=":
if (currentAudit.Tests[testCount].FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
break;
}
}
else
{
if (rowCount > 0)
{
if (currentAudit.Tests[testCount].FailIfConditionIsTrue)
{
currentAudit.Result = false;
}
else
{
currentAudit.Result = true;
}
}
else
{
if (currentAudit.Tests[testCount].FailIfConditionIsTrue)
{
currentAudit.Result = false;
currentAudit.Tests[testCount].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.Tests[testCount].FailedMessage = "This audit was set to not return any rows. " + "This audit returned " + rowCount.ToString(CultureInfo.InvariantCulture) + " rows.";
}
}
if (currentAudit.Result == false)
{
if (currentAudit.Tests[testCount].FailIfConditionIsTrue)
{
PrepareResultsEmailData(currentAudit.Tests[testCount].SqlStatementToCheck, testCount, currentAudit, dsTest);
}
}
else
{
if (currentAudit.Tests[testCount].FailIfConditionIsTrue)
{
if (currentAudit.Tests[testCount].SendReport)
{
// It's not really a failure. Just want to send a report-like email.
PrepareResultsEmailData(currentAudit.Tests[testCount].SqlStatementToCheck, testCount, currentAudit, dsTest);
}
}
}
dsTest.Dispose();
}
}
currentAudit.HasRun = true;
}
/// <summary>
/// Removes the specified item.
/// </summary>
/// <param name="item">The item.</param>
public void Remove(Audit item)
{
List.Remove(item);
}
private void ProcessAudits(XmlNodeList auditList)
{
int nodeCount;
XmlDocument auditDoc = new XmlDocument();
int counter = auditList.Count;
for (nodeCount = 0; nodeCount < counter; nodeCount++)
{
var newAudit = new Audit();
XmlNode auditBranch = auditList[nodeCount];
newAudit.SqlStatement = auditBranch["sqlcommand"].InnerText;
newAudit.Name = auditBranch.Attributes[0].InnerText;
auditDoc.LoadXml(auditBranch.OuterXml);
GetEmailSettings(auditDoc, ref newAudit, auditBranch);
GetSmtpDetails(auditBranch, newAudit);
GetReportUiElements(auditBranch, newAudit);
XmlNodeList testList = auditDoc.GetElementsByTagName("test");
ProcessTests(ref newAudit, testList);
XmlNodeList sqlType = auditDoc.GetElementsByTagName("sqltype");
newAudit.SqlType = (Audit.SqlStatementTypeEnum) Convert.ToInt32(sqlType[0].InnerText);
XmlNode dbProvider = auditBranch["databaseprovider"];
if (dbProvider != null)
{
newAudit.DatabaseProvider = dbProvider.InnerText.ToLower();
}
XmlNodeList connectionString = auditDoc.GetElementsByTagName("connectionstring");
newAudit.ConnectionString = new AuditConnectionString(connectionString[0].InnerText, newAudit.DatabaseProvider);
XmlNode commandTimeout = auditBranch["commandtimeout"];
if (commandTimeout != null)
{
newAudit.ConnectionString.CommandTimeout = commandTimeout.InnerText;
}
else
{
newAudit.ConnectionString.CommandTimeout = "180";
}
XmlNode connectionTimeout = auditBranch["connectiontimeout"];
if (connectionTimeout != null)
{
newAudit.ConnectionString.ConnectionTimeout = connectionTimeout.InnerText;
}
else
{
newAudit.ConnectionString.CommandTimeout = "15";
}
XmlNodeList orderbyNode = auditDoc.GetElementsByTagName("orderbyclause");
if (orderbyNode.Count > 0)
{
newAudit.OrderByClause = orderbyNode[0].InnerText;
}
XmlNodeList includeDataNode = auditDoc.GetElementsByTagName("includedatainemail");
if (includeDataNode.Count > 0)
{
newAudit.IncludeDataInEmail = bool.Parse(includeDataNode[0].InnerText);
}
XmlNodeList testRunOn = auditDoc.GetElementsByTagName("testrunon");
if (testRunOn.Count > 0)
{
newAudit.TestServer = testRunOn[0].InnerText;
}
else
{
newAudit.TestServer = newAudit.ConnectionString.DatabaseServer;
}
_colAuditGroup.Add(newAudit);
}
}
/// <summary>
/// Run a single audit.
/// </summary>
/// <param name="currentAudit">The Audit object to use</param>
public void RunAudit(ref Audit currentAudit)
{
OnSingleAuditRunning(currentAudit);
RunTests(ref currentAudit);
OnSingleAuditDone(currentAudit);
}
private static void ProcessEmails(ref Audit currentAudit, XmlNodeList auditEmails, Audit.EmailTypeEnum emailType)
{
int nodeCount;
var counter = auditEmails.Count;
for (nodeCount = 0; nodeCount < counter; nodeCount++)
{
string currEmail = null;
XmlNode emailNode = null;
emailNode = auditEmails[nodeCount];
currEmail = emailNode.InnerText;
switch (emailType)
{
case Audit.EmailTypeEnum.Recipient:
currentAudit.EmailSubscribers.Add(currEmail);
break;
case Audit.EmailTypeEnum.CarbonCopy:
currentAudit.EmailCarbonCopySubscribers.Add(currEmail);
break;
case Audit.EmailTypeEnum.BlindCarbonCopy:
currentAudit.EmailBlindCarbonCopySubscribers.Add(currEmail);
break;
default:
throw new ArgumentOutOfRangeException(nameof(emailType), emailType, null);
}
}
}
private static void ProcessTests(ref Audit currentAudit, XmlNodeList auditTests)
{
int nodeCount = 0;
int counter = 0;
counter = auditTests.Count;
for (nodeCount = 0; nodeCount < counter; nodeCount++)
{
var newTest = new AuditTest();
XmlNode columnNode = auditTests[nodeCount];
newTest.ColumnName = columnNode["column"].InnerText;
newTest.Operator = columnNode["operator"].InnerText;
newTest.Criteria = columnNode["criteria"].InnerText;
newTest.WhereClause = newTest.ColumnName + " " + newTest.Operator + " " + newTest.Criteria;
newTest.TestReturnedRows = Convert.ToBoolean(columnNode["testreturnedrows"].InnerText);
newTest.UseCriteria = Convert.ToBoolean(columnNode["usecriteria"].InnerText);
if (newTest.Criteria.ToUpper() == "COUNTROWS")
{
newTest.RowCount = Convert.ToInt32(columnNode["rowcount"].InnerText);
}
newTest.FailIfConditionIsTrue = Convert.ToBoolean(columnNode["failiftrue"].InnerText);
var xmlElement = columnNode["instructions"];
if (xmlElement != null)
{
newTest.Instructions = columnNode["instructions"].InnerText;
}
xmlElement = columnNode["sendReport"];
if (xmlElement != null)
{
newTest.SendReport = Convert.ToBoolean(columnNode["sendReport"].InnerText);
}
xmlElement = columnNode["multipleResults"];
if (xmlElement != null)
{
newTest.MultipleResults = Convert.ToBoolean(columnNode["multipleResults"].InnerText);
if (newTest.MultipleResults)
{
xmlElement = columnNode["tableNames"];
if (xmlElement != null)
{
int tableCount;
string[] stringSeparators = new[] {"::"};
var tableCounter = xmlElement.InnerText.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);
for (tableCount = 0; tableCount < tableCounter.Length; tableCount++)
{
newTest.TableNames.Add(tableCounter[tableCount]);
}
}
}
}
xmlElement = columnNode["reportTemplate"];
if (xmlElement != null)
{
string templateName = columnNode["reportTemplate"].InnerText;
EmailTableTemplate currTemplate = TableTemplates.FirstOrDefault(t => t.Name.ToLower() == templateName.ToLower());
newTest.TemplateColorScheme = currTemplate;
}
else
{
newTest.TemplateColorScheme = AuditUtils.GetDefaultTemplate();
}
currentAudit.Tests.Add(newTest);
}
}
private static void GetSmtpDetails(XmlNode auditBranch, Audit newAudit)
{
var xmlSmtpElement = auditBranch["smtp"];
if (xmlSmtpElement["sourceEmail"] != null)
{
newAudit.SmtpSourceEmail = xmlSmtpElement["sourceEmail"].InnerText;
}
if (xmlSmtpElement["address"] != null)
{
newAudit.SmtpServerAddress = xmlSmtpElement["address"].InnerText;
}
if (xmlSmtpElement["port"] != null)
{
newAudit.SmtpPort = Convert.ToInt32(xmlSmtpElement["port"].InnerText);
}
else
{
newAudit.SmtpPort = 25;
}
if (xmlSmtpElement["usessl"] != null)
{
newAudit.SmtpUseSsl = bool.Parse(xmlSmtpElement["usessl"].InnerText);
}
// Process SMTP credentials, if any
XmlNode xmlSmtpCredElement = xmlSmtpElement["smtpcredentials"];
if (xmlSmtpCredElement != null)
{
newAudit.SmtpHasCredentials = true;
if (xmlSmtpCredElement["username"] != null)
{
newAudit.SmtpUserName = xmlSmtpCredElement["username"].InnerText;
}
if (xmlSmtpCredElement["password"] != null)
{
newAudit.SmtpPassword = xmlSmtpCredElement["password"].InnerText;
}
}
}
private static void GetReportUiElements(XmlNode auditBranch, Audit newAudit)
{
// See if we should show the threshold message for this audit.
var xmlShowThresholdElement = auditBranch["showThresholdMessage"];
if (xmlShowThresholdElement != null)
{
newAudit.ShowThresholdMessage = bool.Parse(xmlShowThresholdElement.InnerText);
}
// See if we should show the query text for this audit.
var xmlShowQueryElement = auditBranch["showQueryMessage"];
if (xmlShowQueryElement != null)
{
newAudit.ShowQueryMessage = bool.Parse(xmlShowQueryElement.InnerText);
}
// See if we should show the comments and instructions for this audit.
var xmlShowCommentElement = auditBranch["showComments"];
if (xmlShowCommentElement != null)
{
newAudit.ShowCommentMessage = bool.Parse(xmlShowCommentElement.InnerText);
}
}
private static void GetEmailSettings(XmlDocument auditDoc, ref Audit newAudit, XmlNode auditBranch)
{
// Process email list
XmlNodeList emailList = auditDoc.GetElementsByTagName("email");
if (emailList.Count > 0)
{
ProcessEmails(ref newAudit, emailList, Audit.EmailTypeEnum.Recipient);
}
// Process cc email list
XmlNodeList ccEmailList = auditDoc.GetElementsByTagName("ccEmail");
if (ccEmailList.Count > 0)
{
ProcessEmails(ref newAudit, ccEmailList, Audit.EmailTypeEnum.CarbonCopy);
}
// Process email list
XmlNodeList bccEmailList = auditDoc.GetElementsByTagName("bccEmail");
if (bccEmailList.Count > 0)
{
ProcessEmails(ref newAudit, bccEmailList, Audit.EmailTypeEnum.BlindCarbonCopy);
}
// See if there is a custom email subject for this audit.
var xmlElement = auditBranch["emailSubject"];
if (xmlElement != null)
{
newAudit.EmailSubject = xmlElement.InnerText;
}
// See if there is a custom email subject for this audit.
xmlElement = auditBranch["emailpriority"];
if (xmlElement != null)
{
switch (xmlElement.InnerText.ToLower())
{
case "low":
newAudit.EmailPriority = Audit.EmailPriorityEnum.Low;
break;
case "normal":
newAudit.EmailPriority = Audit.EmailPriorityEnum.Normal;
break;
case "high":
newAudit.EmailPriority = Audit.EmailPriorityEnum.High;
break;
default:
newAudit.EmailPriority = Audit.EmailPriorityEnum.High;
break;
}
}
else
{
newAudit.EmailPriority = Audit.EmailPriorityEnum.High;
}
// See if there is a source email the FROM email address.
var xmlSourceElement = auditBranch["sourceEmail"];
if (xmlSourceElement != null)
{
newAudit.SmtpSourceEmail = xmlSourceElement.InnerText;
}
}
private string BuildWhereClause(Audit auditToParse, int testIndex)
{
string result;
AuditTest currTest = auditToParse.Tests[testIndex];
string criteria = currTest.Criteria;
string columnName = currTest.ColumnName;
string Operator = currTest.Operator;
// Add additional custom criteria inside this select statement
switch (criteria.ToUpper())
{
case "TODAY":
result = Today(columnName) + Operator + "0";
auditToParse.Tests[testIndex].WhereClause = result;
break;
default:
result = auditToParse.Tests[testIndex].WhereClause;
break;
}
return result;
}
/// <summary>
/// Used to fire the <see cref="CurrentAuditDone"/> event.
/// </summary>
/// <param name="auditIndex">The index of this audit in the collection</param>
/// <param name="currentAudit">The actual Audit object that just got done running</param>
protected virtual void OnCurrentAuditDone(int auditIndex, Audit currentAudit)
{
if (CurrentAuditDone != null)
CurrentAuditDone(auditIndex, currentAudit.Name);
}
private DataSet GetTestDataSet(ref Audit auditToRun, int testIndex)
{
IAuditDbProvider currDbProvider = _providers.Providers[auditToRun.DatabaseProvider];
currDbProvider.ConnectionString = auditToRun.ConnectionString.ToString();
if (auditToRun.ConnectionString.ConnectionTimeout != null)
{
currDbProvider.ConnectionTimeout = auditToRun.ConnectionString.ConnectionTimeout;
}
if (auditToRun.ConnectionString.CommandTimeout != null)
{
currDbProvider.CommandTimeout = auditToRun.ConnectionString.CommandTimeout;
}
currDbProvider.CreateDatabaseSession();
var dsAudit = new DataSet();
string sql = BuildSqlStatement(auditToRun, testIndex);
CommandType commandType = (CommandType) 0;
if (auditToRun.SqlType == Audit.SqlStatementTypeEnum.SqlText)
{
commandType = CommandType.Text;
}
else if (auditToRun.SqlType == Audit.SqlStatementTypeEnum.StoredProcedure)
{
commandType = CommandType.StoredProcedure;
}
IDbCommand cmdAudit = currDbProvider.CreateDbCommand(sql, commandType, int.Parse(auditToRun.ConnectionString.CommandTimeout));
IDbDataAdapter daAudit = currDbProvider.CreateDbDataAdapter(cmdAudit);
string intConnectionTimeout = auditToRun.ConnectionString.ConnectionTimeout;
string intCommandTimeout = auditToRun.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.Tests[testIndex].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.Tests[testIndex].FailedMessage);
}
else
{
auditToRun.Tests[testIndex].FailedMessage = strMsg;
auditToRun.ErrorMessages.Add(strMsg);
}
auditToRun.WasSuccessful = false;
}
finally
{
cmdAudit.Dispose();
}
return dsAudit;
}
/// <summary>
/// Used to fire the <see cref="CurrentAuditRunning"/> event.
/// </summary>
/// <param name="auditIndex">The index of this audit in the collection</param>
/// <param name="currentAudit">The actual Audit object that is currently running</param>
protected virtual void OnCurrentAuditRunning(int auditIndex, Audit currentAudit)
{
if (CurrentAuditRunning != null)
CurrentAuditRunning(auditIndex, currentAudit.Name);
}
/// <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.Tests[0].MultipleResults)
{
sb.Append("<B>");
sb.Append(testedAudit.Tests[0].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();
}
/// <summary>
/// Used to fire the <see cref="CurrentSingleAuditDone"/> event.
/// </summary>
/// <param name="currentAudit">The audit object that just got done running</param>
protected virtual void OnSingleAuditDone(Audit currentAudit)
{
if (CurrentSingleAuditDone != null)
CurrentSingleAuditDone(currentAudit);
}
/// <summary>
/// Adds the specified item.
/// </summary>
/// <param name="item">The item.</param>
/// <returns>System.Int32.</returns>
public int Add(Audit item)
{
return List.Add(item);
}
/// <summary>
/// Used to fire the <see cref="CurrentSingleAuditRunning"/> event.
/// </summary>
/// <param name="currentAudit">The audit object that is currently running</param>
protected virtual void OnSingleAuditRunning(Audit currentAudit)
{
if (CurrentSingleAuditRunning != null)
CurrentSingleAuditRunning(currentAudit);
}
/// <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)
{
List.Insert(index, item);
}
private void _auditTesting_CurrentSingleAuditDone(Audit CurrentAudit)
{
bool result = CurrentAudit.Result;
if (result)
{
lsvAudits.Items[_selectedIndex].ImageIndex = 3;
lsvAudits.Items[_selectedIndex].SubItems[2].Text = "Completed";
}
else
{
lsvAudits.Items[_selectedIndex].ImageIndex = 4;
lsvAudits.Items[_selectedIndex].SubItems[2].Text = "Failure";
}
this.Refresh();
}