public void Handle(CreateProductLineSchema message)
{
GreenLogger.Log("Creating schema {0}", message.Schema);
runner.ExecuteCommand(string.Format(
CultureInfo.InvariantCulture,
"CREATE SCHEMA [{0}] AUTHORIZATION [dbo]",
message.Schema));
}
public void Handle(CreateRiskTableColumn message)
{
GreenLogger.Log("Creating column {0} in table {1}.{2}", message.ColumnName, message.Schema, message.TableName);
runner.ExecuteCommand(string.Format(
CultureInfo.InvariantCulture,
"ALTER TABLE [{0}].[{1}] ADD {2} NVARCHAR(250) NULL ",
message.Schema,
message.TableName,
message.ColumnName));
}
public void Handle(CreateRiskTable message)
{
GreenLogger.Log("Creating table {0}.{1}", message.Schema, message.Name);
const string CreateTableSql = @"
CREATE TABLE [{0}].[{1}](
[Id] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_{0}_{1}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]";
runner.ExecuteCommand(string.Format(CultureInfo.InvariantCulture, CreateTableSql, message.Schema, message.Name));
}
public void Handle(UpdateRiskTableColumnValue message)
{
var mapping = projectionStore
.GetProjection <RiskCaptureItemToDataWarehouseColumnMapping>(RiskCaptureItemToDataWarehouseColumnMappingId.Parse(message.Schema))
.Single(i => i.ItemId == message.Id);
GreenLogger.Log("Updating column {0} in table {1}.{2} with value {3}", mapping.TableName, message.Schema, mapping.ColumnName, message.ColumnValue);
const string UpsertSql = @"
IF NOT EXISTS (SELECT * FROM [{0}].[{1}] WHERE Id = '{2}')
INSERT INTO [{0}].[{1}](Id,{3}) VALUES('{2}','{4}')
ELSE
UPDATE [{0}].[{1}] SET {3} = '{4}' WHERE Id = '{2}'" ;
runner.ExecuteCommand(string.Format(
CultureInfo.InvariantCulture,
UpsertSql,
message.Schema,
mapping.ColumnName,
message.Id,
mapping.TableName,
message.ColumnValue));
}
