public void EnsureSchema(DatabaseContext context)
{
var sql = string.Format(@"
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = '{0}'
", _tableName);
var properties = context.ExecuteQuery(sql);
if (properties.Count == 0)
{
var newSchemaSql = string.Format(@"
CREATE TABLE [dbo].[{0}]
(
[MigrationVersion] int NOT NULL,
CONSTRAINT PK_MigrationVersion PRIMARY KEY CLUSTERED ([MigrationVersion])
);
INSERT INTO [dbo].[{0}] VALUES('0');
",
_tableName);
context.ExecuteNonQuery(newSchemaSql);
}
}
public int GetDatabaseVersionNumber(DatabaseContext context)
{
var versionProperty = context.ExecuteQuery(string.Format("select * from sys.extended_properties where [class] = 0 and [name] = '{0}'", ExtProp.DatabaseVersion))
.Single();
var currentVersion = int.Parse(versionProperty["value"].ToString());
return currentVersion;
}
public void EnsureSchema(DatabaseContext context)
{
var sql = string.Format("select * from sys.extended_properties where [class] = 0 and [name] = '{0}'", ExtProp.DatabaseVersion);
var properties = context.ExecuteQuery(sql);
if (properties.Count == 0)
{
context.ExecuteNonQuery(string.Format("exec sys.sp_addextendedproperty @name=N'{0}', @value=N'{1}'", ExtProp.DatabaseVersion, "0"));
}
}
public void UpdateVersion(DatabaseContext context, int newVersion)
{
context.ExecuteNonQuery(string.Format("UPDATE dbo.{0} SET MigrationVersion = '{1}'", _tableName, newVersion));
}
public int GetDatabaseVersionNumber(DatabaseContext context)
{
var currentVersion = context.GetSingleValue<int>(_tableName, "MigrationVersion");
return currentVersion;
}
public void UpdateVersion(DatabaseContext context, int newVersion)
{
context.ExecuteNonQuery(string.Format("exec sys.sp_updateextendedproperty @name=N'{0}', @value=N'{1}'", ExtProp.DatabaseVersion, newVersion));
}
private void EnsureDatabaseHasVersionMetaData()
{
using (var context = new DatabaseContext(_dbConnection))
{
context.NewTransaction();
_communicator.EnsureSchema(context);
context.Commit();
}
}
private int GetDatabaseVersionNumber(DatabaseContext context)
{
return _communicator.GetDatabaseVersionNumber(context);
}
private int GetDatabaseVersionNumber()
{
using (var context = new DatabaseContext(_dbConnection))
{
return GetDatabaseVersionNumber(context);
}
}
void ExecuteMigration(IMigration migration)
{
BeforeExecute(migration);
try
{
using (var context = new DatabaseContext(_dbConnection))
{
context.NewTransaction();
foreach (var sqlStatement in migration.SqlStatements)
{
try
{
context.ExecuteNonQuery(sqlStatement);
}
catch (Exception e)
{
throw new DatabaseMigrationException(e,
@"The following SQL could not be executed:
{0}
Exception:
{1}",
sqlStatement, e);
}
}
var currentVersion = GetDatabaseVersionNumber(context);
var newVersion = currentVersion + 1;
_communicator.UpdateVersion(context, newVersion);
context.Commit();
}
AfterExecuteSuccess(migration);
}
catch (Exception e)
{
AfterExecuteError(migration, e);
throw new DatabaseMigrationException(e, "The migration {0} (db version -> {1}) could not be executed", migration.Description, migration.TargetDatabaseVersion);
}
}