public void VerifyColumnType(CorrelationProperty correlationProperty)
{
string columnType;
if (correlationProperty.Type == CorrelationPropertyType.String)
{
columnType = "nvarchar";
}
else
{
columnType = MsSqlServerCorrelationPropertyTypeConverter.GetColumnType(correlationProperty.Type);
}
var name = correlationProperty.Name;
writer.Write($@"
declare @dataType_{name} nvarchar(max);
set @dataType_{name} = (
select data_type
from INFORMATION_SCHEMA.COLUMNS
where
table_name = @tableNameWithoutSchema and
table_schema = @schema and
column_name = 'Correlation_{name}'
);
if (@dataType_{name} <> '{columnType}')
begin
declare @error_{name} nvarchar(max) = N'Incorrect data type for Correlation_{name}. Expected {columnType} got ' + @dataType_{name} + '.';
throw 50000, @error_{name}, 0
end
");
}
public void Write()
{
var testDirectory = TestContext.CurrentContext.TestDirectory;
var path = Path.Combine(testDirectory, "../../../../../nservicebus/sql-persistence/correlationpropertytypes.include.md");
path = Path.GetFullPath(path);
File.Delete(path);
using (var writer = File.CreateText(path))
{
writer.WriteLine(@"
#### Microsoft SQL Server
| CorrelationPropertyType | Sql Type |
|--|--|");
foreach (var type in GetValues())
{
var columnType = MsSqlServerCorrelationPropertyTypeConverter.GetColumnType(type);
writer.WriteLine($"| `{type}` | `{columnType}` |");
}
writer.WriteLine(@"
#### MySQL
| CorrelationPropertyType | Sql Type |
|--|--|");
foreach (var type in GetValues().Where(x => x != CorrelationPropertyType.DateTimeOffset))
{
var columnType = MySqlCorrelationPropertyTypeConverter.GetColumnType(type);
writer.WriteLine($"| `{type}` | `{columnType}` |");
}
}
}
public void AddProperty(CorrelationProperty correlationProperty)
{
var columnType = MsSqlServerCorrelationPropertyTypeConverter.GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
if not exists
(
select * from sys.columns
where
name = N'Correlation_{name}' and
object_id = object_id(@tableName)
)
begin
declare @createColumn_{name} nvarchar(max);
set @createColumn_{name} = '
alter table ' + @tableName + N'
add Correlation_{name} {columnType};';
exec(@createColumn_{name});
end
");
}
public void VerifyColumnType(CorrelationProperty correlationProperty)
{
var columnType = MsSqlServerCorrelationPropertyTypeConverter.GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
declare @dataType_{name} nvarchar(max);
set @dataType_{name} = (
select data_type
from information_schema.columns
where
table_name = ' + @tableName + N' and
column_name = 'Correlation_{name}'
);
if (@dataType_{name} <> '{columnType}')
begin
declare @error_{name} nvarchar(max) = N'Incorrect data type for Correlation_{name}. Expected {columnType} got ' + @dataType_{name} + '.';
throw 50000, @error_{name}, 0
end
");
}
