protected virtual TableSchema GetSchema(string table)
{
var columns = new List <TableSchema.ColumnDefinition>();
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SqlServerStatement.Create("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", table)))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var def = new TableSchema.ColumnDefinition
{
Name = reader["COLUMN_NAME"].ToString(),
Type = reader["DATA_TYPE"].ToString(),
IsNullable =
(string.Compare(reader["IS_NULLABLE"].ToString(), "yes", true) == 0)
};
columns.Add(def);
}
}
}
return(new TableSchema
{
Columns = columns.AsReadOnly()
});
}
public override void Initialise()
{
try
{
Logger.Debug("\tCreating AgentData table...");
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AgentData]') AND type in (N'U')) BEGIN")
.Append("CREATE TABLE [dbo].[AgentData](")
.Append("[TypeId] [uniqueidentifier] NOT NULL,")
.Append("[EventType] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,")
.Append("[SiteId] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,")
.Append("[AgentId] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,")
.Append("[CheckId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,")
.Append("[Result] [bit] NULL,")
.Append("[GeneratedOnUtc] [datetime] NOT NULL,")
.Append("[ReceivedOnUtc] [datetime] NOT NULL,")
.Append("[Data] [xml] NOT NULL,")
.Append("[Version] [uniqueidentifier] NOT NULL) END")))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tDone");
}
Logger.Debug("\tApplying schema updates (ResultCount) to AgentData table...");
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AgentData' AND COLUMN_NAME = 'ResultCount') BEGIN")
.Append("ALTER TABLE [dbo].[AgentData] ADD ResultCount DECIMAL(20,4) NULL")
.Append("END ELSE BEGIN")
.Append("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AgentData' AND COLUMN_NAME = 'ResultCount' AND DATA_TYPE = 'bigint') BEGIN")
.Append("ALTER TABLE dbo.AgentData ALTER COLUMN ResultCount DECIMAL(20,4)")
.Append("END")
.Append("END")))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tDone");
}
Logger.Debug("\tApplying schema updates to AgentData table...");
AddColumnIfMissing("Tags", "VARCHAR(200)", true);
AddColumnIfMissing("MinuteBucket", "INT", true);
AddColumnIfMissing("HourBucket", "INT", true);
AddColumnIfMissing("DayBucket", "INT", true);
Logger.Debug("\tSuccess, AgentData table established");
}
catch (Exception)
{
Logger.Debug("\tFailed to create AgentData table");
throw;
}
}
protected virtual bool AddColumnIfMissing(string column, string datatype, bool nullable)
{
if (GetSchema("AgentData").HasColumn(column))
{
return(false);
}
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SqlServerStatement.Create("ALTER TABLE [dbo].[AgentData] ADD {0} {1} {2} NULL",
column,
datatype,
nullable ? string.Empty : "NOT")))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tAdded column {0} [{1}]", column, datatype);
}
return(true);
}
public void Publish(HealthCheckResult message)
{
var data = SerialisationHelper <HealthCheckResult> .DataContractSerialize(message);
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SqlServerStatement.Create("INSERT INTO dbo.AgentData (")
.Append("TypeId,EventType,SiteId,AgentId,CheckId,")
.AppendIf(() => message.Check.Result.HasValue, "Result,")
.AppendIf(() => message.Check.ResultCount.HasValue, "ResultCount,")
.AppendIf(() => !string.IsNullOrEmpty(message.Check.Tags), "Tags,")
.Append("GeneratedOnUtc,ReceivedOnUtc,Data,")
.AppendIf(() => message.MinuteBucket.HasValue, "MinuteBucket,")
.AppendIf(() => message.HourBucket.HasValue, "HourBucket,")
.AppendIf(() => message.DayBucket.HasValue, "DayBucket,")
.Append("Version")
.Append(") VALUES (")
.InsertParameter("@pTypeId", message.Check.Identity.TypeId).Append(",")
.InsertParameter("@pEventType", message.EventType).Append(",")
.InsertParameter("@pSiteId", message.Agent.SiteId).Append(",")
.InsertParameter("@pAgentId", message.Agent.AgentId).Append(",")
.InsertParameter("@pCheckId", message.Check.Identity.Name).Append(",")
.InsertParameterIf(() => message.Check.Result.HasValue, "@pResult", message.Check.Result)
.AppendIf(() => message.Check.Result.HasValue, ",")
.InsertParameterIf(() => message.Check.ResultCount.HasValue, "@pResultCount", message.Check.ResultCount)
.AppendIf(() => message.Check.ResultCount.HasValue, ",")
.InsertParameterIf(() => !string.IsNullOrEmpty(message.Check.Tags), "@pTags", message.Check.Tags)
.AppendIf(() => !string.IsNullOrEmpty(message.Check.Tags), ",")
.InsertParameter("@pGeneratedOnUtc", message.Check.GeneratedOnUtc).Append(",")
.InsertParameter("@pReceivedOnUtc", DateTime.UtcNow).Append(",")
.InsertParameter("@pData", data).Append(",")
.InsertParameterIf(() => message.MinuteBucket.HasValue, "@pMinuteBucket", message.MinuteBucket)
.AppendIf(() => message.MinuteBucket.HasValue, ",")
.InsertParameterIf(() => message.HourBucket.HasValue, "@pHourBucket", message.HourBucket)
.AppendIf(() => message.HourBucket.HasValue, ",")
.InsertParameterIf(() => message.DayBucket.HasValue, "@pDayBucket", message.DayBucket)
.AppendIf(() => message.DayBucket.HasValue, ",")
.InsertParameter("@pVersion", message.Id)
.Append(")")))
{
cmd.ExecuteNonQuery();
}
}
public void Publish(HealthCheckAgentStart message)
{
var data = SerialisationHelper <HealthCheckAgentStart> .DataContractSerialize(message);
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SqlServerStatement.Create("INSERT INTO dbo.AgentData (")
.Append("TypeId,EventType,SiteId,AgentId,GeneratedOnUtc,ReceivedOnUtc,Data,Version")
.Append(") VALUES (")
.InsertParameter("@pTypeId", message.Id).Append(",")
.InsertParameter("@pEventType", "SessionStart").Append(",")
.InsertParameter("@pSiteId", message.Agent.SiteId).Append(",")
.InsertParameter("@pAgentId", message.Agent.AgentId).Append(",")
.InsertParameter("@pGeneratedOnUtc", message.DiscoveryStarted).Append(",")
.InsertParameter("@pReceivedOnUtc", DateTime.UtcNow).Append(",")
.InsertParameter("@pData", data).Append(",")
.InsertParameter("@pVersion", message.Id)
.Append(")")))
{
cmd.ExecuteNonQuery();
}
}