public static int DbExecuteNonQuery(this IDbConnection db, SqlQueryWithParams qp, IDbTransaction transaction, int commandTimeout = 30)
{
IDbCommand cmd = BuildDbCommand(db, qp, commandTimeout);
if (transaction != null)
{
cmd.Transaction = transaction;
}
return(cmd.ExecuteNonQuery());
}
// Execute queries, logging the SQL and rows affected
private void Execute(IDbTransaction transaction, int batchNum, string sql)
{
sql = sql.Trim(); // trimming makes the log messages clearer
var qp = new SqlQueryWithParams(sql);
_migrationUtils.LogSqlBatch(batchNum, sql);
// Some updates take a very long time. Would like to be able to specify this in the SQL file itself (maybe in a comment?)
int commandTimeout = 90;
int rowsAffected = _db.DbExecuteNonQuery(qp, transaction, commandTimeout);
_migrationUtils.LogInformation("Batch {0} exectued successfully on {1}. {2} rows affected.", batchNum, _db.Database, rowsAffected);
}
private void UpdateDBVersion(IDbTransaction transaction, TVer ver)
{
SqlQueryWithParams query = _migrationUtils.SetDBVersionQuery(ver); // call the user-supplied delegate to get the query
// The set version query is not required. The migration script itself may set the version
if (query == null)
{
return;
}
string sqlmsg = query.Query + " PARAMETERS: (" + string.Join(", ", query.Parameters.Select(p => $"{p.Name}: {p.Value}")) + ")";
sqlmsg = sqlmsg.Trim(); // trimming makes the log messages clearer
_migrationUtils.LogInformation("Setting {0} Version to {1} with SQL '{2}'", _db.Database, ver, sqlmsg);
int rowsAffected = _db.DbExecuteNonQuery(query, transaction);
}
static IDbCommand BuildDbCommand(IDbConnection db, SqlQueryWithParams q, int commandTimeout = 30)
{
IDbCommand cmd = db.CreateCommand();
cmd.CommandText = q.Query;
cmd.CommandTimeout = commandTimeout;
if (q.Parameters != null)
{
foreach (var p in q.Parameters)
{
IDbDataParameter dbparm = cmd.CreateParameter();
dbparm.ParameterName = p.Name;
dbparm.Value = p.Value;
cmd.Parameters.Add(dbparm);
}
}
return(cmd);
}
public static T DbExecuteScalar <T>(this IDbConnection db, SqlQueryWithParams qp)
{
IDbCommand cmd = BuildDbCommand(db, qp);
return((T)cmd.ExecuteScalar());
}
private void BringToVersionWorker(TVer requiredVersion)
{
// Get the current database version
TVer currentDBVersion;
try
{
_migrationUtils.LogInformation("Getting current database version");
SqlQueryWithParams query = _migrationUtils.GetDBVersionQuery();
currentDBVersion = _db.DbExecuteScalar <TVer>(query);
}
catch (Exception e)
{
_migrationUtils.LogError(e, "Exception in GetDBVersionQuery: {0}", e.Message);
return;
}
// See if we have any migration scripts to run
TVer[] migrationVers;
try
{
_migrationUtils.LogInformation("Calling ListRequiredMigrationScripts({0},{1})", currentDBVersion, requiredVersion);
migrationVers = _migrationUtils.ListRequiredMigrationScripts(currentDBVersion, requiredVersion);
if (migrationVers == null || migrationVers.Length == 0)
{
_migrationUtils.LogInformation("No migrations required from {0} to {1}", currentDBVersion, requiredVersion);
return;
}
_migrationUtils.LogInformation("Migrations required: {0}", string.Join(", ", migrationVers));
}
catch (Exception e)
{
_migrationUtils.LogError(e, "Exception in ListRequiredMigrationScripts({0},{1})", currentDBVersion, requiredVersion);
return;
}
// Run each migration script in turn
foreach (TVer migrationVer in migrationVers)
{
_migrationUtils.LogInformation("Starting Migration {0}", migrationVer);
IDbTransaction transaction;
string sql;
try
{
// Read the Migration script
sql = _migrationUtils.ReadMigrationScript(migrationVer);
// Begin transaction
_migrationUtils.LogInformation("Beginning Transaction");
transaction = _db.BeginTransaction();
}
catch (Exception e)
{
_migrationUtils.LogError(e, "Exception encountered while running migration {0}: {1}", migrationVer, e.Message);
return;
}
try {
// Update the DB version indicatating that we're in progress
TVer inProgressVer = _migrationUtils.GetInProgressVer(migrationVer);
UpdateDBVersion(transaction, inProgressVer);
// Run the migration!
RunMigration(transaction, migrationVer, sql);
// Update the DB version to the final version
UpdateDBVersion(transaction, migrationVer);
_migrationUtils.LogInformation("Commiting transaction");
transaction.Commit();
}
catch (Exception e)
{
_migrationUtils.LogError(e, "Exception encountered while running migration {0} on {1}: {2}", migrationVer, _db.Database, e.Message);
RollbackTransaction(transaction);
return; // Abort after the first failure
}
_migrationUtils.LogInformation("Finished Migration {0}", migrationVer);
}
}
