public async Task Exec(IScenarioContext context)
{
bool isPostgres = context.Dialect == SqlDialect.PgSql;
var table = new TableItAllColumnTypes(isPostgres);
await context.Database.Statement(table.Script.DropAndCreate());
var testData = GetTestData(isPostgres);
await InsertDataInto(table, testData)
.MapData(Mapping)
.Exec(context.Database);
var mapper = new Mapper(new MapperConfiguration(cfg =>
{
cfg.AddDataReaderMapping();
var map = cfg.CreateMap <IDataRecord, AllColumnTypesDto>();
map
.ForMember(nameof(table.ColByteArraySmall), c => c.Ignore())
.ForMember(nameof(table.ColByteArrayBig), c => c.Ignore())
.ForMember(nameof(table.ColNullableByteArraySmall), c => c.Ignore())
.ForMember(nameof(table.ColNullableByteArrayBig), c => c.Ignore())
.ForMember(nameof(table.ColNullableFixedSizeByteArray), c => c.Ignore())
.ForMember(nameof(table.ColFixedSizeByteArray), c => c.Ignore());
if (isPostgres)
{
map
.ForMember(nameof(table.ColByte), c => c.Ignore())
.ForMember(nameof(table.ColNullableByte), c => c.Ignore());
}
if (context.Dialect == SqlDialect.MySql)
{
map
.ForMember(nameof(table.ColBoolean), c => c.MapFrom((r, dto) => r.GetBoolean(r.GetOrdinal(nameof(table.ColBoolean)))))
.ForMember(nameof(table.ColNullableBoolean), c => c.MapFrom((r, dto) => r.IsDBNull(r.GetOrdinal(nameof(table.ColNullableBoolean))) ? (bool?)null : r.GetBoolean(r.GetOrdinal(nameof(table.ColNullableBoolean)))))
.ForMember(nameof(table.ColGuid), c => c.MapFrom((r, dto) => r.GetGuid(r.GetOrdinal(nameof(table.ColGuid)))))
.ForMember(nameof(table.ColNullableGuid), c => c.MapFrom((r, dto) => r.IsDBNull(r.GetOrdinal(nameof(table.ColNullableGuid)))? (Guid?)null : r.GetGuid(r.GetOrdinal(nameof(table.ColNullableGuid)))));
}
}));
var expr = Select(table.Columns)
.From(table).Done();
context.WriteLine(PgSqlExporter.Default.ToSql(expr));
var result = await expr
.QueryList(context.Database, r =>
{
var allColumnTypesDto = mapper.Map <IDataRecord, AllColumnTypesDto>(r);
allColumnTypesDto.ColByteArrayBig = StreamToByteArray(table.ColByteArrayBig.GetStream(r));
allColumnTypesDto.ColByteArraySmall = table.ColByteArraySmall.Read(r);
allColumnTypesDto.ColNullableByteArrayBig = table.ColNullableByteArrayBig.Read(r);
allColumnTypesDto.ColNullableByteArraySmall = table.ColNullableByteArraySmall.Read(r);
allColumnTypesDto.ColFixedSizeByteArray = table.ColFixedSizeByteArray.Read(r);
allColumnTypesDto.ColNullableFixedSizeByteArray = table.ColNullableFixedSizeByteArray.Read(r);
return(allColumnTypesDto);
});
private async Task Insert(IScenarioContext context, string json, TableItAllColumnTypes table)
{
var doc = JsonDocument.Parse(json);
var tableName = table.FullName.AsExprTableFullName().TableName.Name;
foreach (var obj in doc.RootElement.EnumerateObject())
{
if (obj.Name != tableName)
{
throw new Exception($"Unexpected property: {obj.Name}");
}
var rowsEnumerable = obj.Value.EnumerateArray()
.Select(rowArray =>
{
int colIndex = 0;
var row = new ExprValue[table.Columns.Count];
foreach (var cell in rowArray.EnumerateArray())
{
row[colIndex] = table.Columns[colIndex]
.FromString(cell.ValueKind == JsonValueKind.Null ? null : cell.GetString());
colIndex++;
}
return(row);
});
await SqQueryBuilder
.IdentityInsertInto(table, table.Columns.Select(c => c.ColumnName).ToList())
.Values(rowsEnumerable)
.Exec(context.Database);
}
}
private static async Task <string> ReadAsJsonString(IScenarioContext context, TableItAllColumnTypes table)
{
using var ms = new MemoryStream();
using Utf8JsonWriter writer = new Utf8JsonWriter(ms);
writer.WriteStartObject();
writer.WriteStartArray(table.FullName.AsExprTableFullName().TableName.Name);
await SqQueryBuilder
.Select(table.Columns)
.From(table)
.Query(context.Database,
r =>
{
writer.WriteStartArray();
foreach (var column in table.Columns)
{
var readAsString = column.ReadAsString(r);
writer.WriteStringValue(readAsString);
}
writer.WriteEndArray();
});
writer.WriteEndArray();
writer.WriteEndObject();
writer.Flush();
var jsonString = Encoding.UTF8.GetString(ms.ToArray());
return(jsonString);
}
