public async Task UpdateAsync(SqlServerSpatialDbContext context)
{
await EnsureMetadataTablesCreated(context);
const int geometryColumnType = 32;
var coordinateSystemSubquery = $"(select csguid from GCoordSystem where name = '{Settings.CoordinateSystemName}')";
var spatialReferenceId = Settings.SpatialReferenceId;
var sql = new StringBuilder();
var entities = SqlServerSpatialColumn.GetSpatialColumns(context.Model);
foreach (var(entity, column, geometryType) in entities)
{
var tableName = entity.SqlServer().TableName;
var columnName = column.Name;
var geometryTypeValue = GetGeoMediaDataSubType(geometryType);
// Remove any existing metadata
sql.AppendLine($"delete from GFeatures where FeatureName = '{tableName}'");
sql.AppendLine($"delete from GFieldMapping where TABLE_NAME = '{tableName}'");
// Insert metadata
var geoMediaSpatialColumnName = GetGeoMediaSpatialColumnName(columnName);
sql.AppendLine($"insert into GFeatures values ('{tableName}', {geometryTypeValue}, '{geoMediaSpatialColumnName}', '')");
sql.AppendLine($"insert into GFieldMapping values('{tableName}', '{geoMediaSpatialColumnName}', {geometryColumnType}, {geometryTypeValue}, {coordinateSystemSubquery}, null, '{columnName}', {spatialReferenceId})");
sql.AppendLine();
}
var sqlText = sql.ToString();
await context.Database.ExecuteSqlCommandAsync(sqlText);
}
private async Task EnsureMetadataTablesCreated(SqlServerSpatialDbContext context)
{
// Check if metadata objects already exist
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "select count(*) from sys.tables where name = 'GFeatures' and type = 'U'";
await context.Database.OpenConnectionAsync();
try
{
var metadataExists = 1 == (int)(await cmd.ExecuteScalarAsync());
if (metadataExists)
{
return;
}
}
finally
{
context.Database.CloseConnection();
}
}
// Create metadata objects starting with the GeoMedia metadata script (created by SSMS)
var type = GetType();
var ddl = await Resources.ReadResourceAsync($"{type.Namespace}.Resources.MetadataDDL.sql", type.Assembly);
var cmds = ddl
.Split(new[] { "GO\r\n", "GO\n" }, StringSplitOptions.None)
.Where(cmd => !cmd.StartsWith("USE ") && !string.IsNullOrWhiteSpace(cmd))
.ToList();
AppendCoordinateSystemData(cmds);
// Executre each command in sequence
foreach (var cmd in cmds)
{
try
{
await context.Database.ExecuteSqlCommandAsync(cmd);
}
catch (Exception ex)
{
throw new InvalidOperationException($"Error executing GeoMedia metadata DDL cmd: {cmd}", ex);
}
}
}
