public async Task InitializeAsync(CancellationToken cancellationToken)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
// create all tables. all these table files should have existance checks in them
var initializationSql = SqlServerUtilities
.GetEmbeddedFileNames(SqlInitializationEmbeddedLocation)
.OrderBy(fileName => int.Parse(fileName.Replace(SqlInitializationEmbeddedLocation + ".", "")
.Replace(".sql", "")
.Split('-')
.First()))
.ToList()
.Select(SqlServerUtilities.GetEmbeddedFile).SelectMany(
filecontents => Regex.Split(filecontents, @"^GO.*$", RegexOptions.Multiline)
.Where(s => !string
.IsNullOrWhiteSpace(
s)) // we split the file on the GO statements and run each section as its own query
).ToArray();
foreach (var sql in initializationSql)
{
// execute each update
await dbConnection.ExecuteAsync(sql);
}
}
await MigrateAsync(cancellationToken);
}
public async Task MigrateAsync(CancellationToken cancellationToken)
{
using (var dbConnection = Connection())
{
dbConnection.Open();
var migrationSqlFilenames = SqlServerUtilities
.GetEmbeddedFileNames(SqlMigrationsEmbeddedLocation)
.OrderBy(filename => int.Parse(filename.Replace(SqlMigrationsEmbeddedLocation + ".", "")
.Replace(".sql", "").Split('-').First())).ToDictionary(
key => int.Parse(key.Replace(SqlMigrationsEmbeddedLocation, "").Replace(".sql", "").Split('-')
.First()), value => value);
var lastMigration = 0;
// these should be run in numerical order
foreach (var sqlFiles in migrationSqlFilenames)
{
if (await CheckAsync(cancellationToken) <= sqlFiles.Key)
{
continue;
}
var fileContents = SqlServerUtilities.GetEmbeddedFile(sqlFiles.Value);
// we split the file on the GO statements and run each section as its own query
var sqlQueries = Regex.Split(fileContents, @"^GO.*$", RegexOptions.Multiline)
.Where(s => !string.IsNullOrWhiteSpace(s));
foreach (var sql in sqlQueries)
{
// execute each update
await dbConnection.ExecuteAsync(sql);
}
lastMigration = sqlFiles.Key;
}
// update to current migration
await dbConnection.ExecuteAsync(
"UPDATE [Configuration] SET [Value] = @lastMigration WHERE [Id] = 'MigrationVersion'",
new { lastMigration });
}
}