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); } }