DbTools/Migrations.cs

using DbTools.EventArguments;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace DbTools {
    public class Migrations {
        public string MigrationPath { get; set; }
        public string CompletedMigrationFile { get; set; } = "migrations.txt";
        public string DatabaseName { get; set; }

        public int Pending { get; private set; }
        public int Successful { get; private set; }
        public int Failed { get; private set; }
        public int Total { get; private set; }

        public event MigrationProgressEventHandler MigrationProgressReported;
        public event MigrationCompletedEventHandler MigrationCompleted;
        public event MigrationStartedEventHandler MigrationStarted;

        public Migrations(string migrationPath, string databaseName) {
            MigrationPath = migrationPath;
            DatabaseName = databaseName;
            if (!Directory.Exists(MigrationPath)) {
                Directory.CreateDirectory(MigrationPath);
            }
        }

        public string[] GetAvailableMigrations(bool includeFailed = false) {
            string completedFile = Path.Combine(MigrationPath, CompletedMigrationFile);
            List<string> completedMigrations = new List<string>();
            if (!File.Exists(completedFile)) {
                File.WriteAllText(completedFile, "");
            }

            // migrations.txt format: filename.sql;date_time_performed;success=1-or-fail=0
            //                        data_6.0.12.123_01.sql;25252511;1
            string[] migrations = File.ReadAllLines(completedFile);
            foreach (string migration in migrations) {
                string[] parts = migration.Split(';');
                if (int.Parse(parts[2] ?? "0") != 0) {
                    completedMigrations.Add(migration);
                }
            }

            var migrationFiles = Directory.GetFiles(MigrationPath, DatabaseName + "_*.sql")
                .Where(f => !completedMigrations.Contains(Path.GetFileName(f)))
                .OrderBy(f => f);

            return migrationFiles.ToArray();
        }

        public void MarkMigrationComplete(string migrationName, bool wasSuccessful = true) {
            File.AppendAllText(Path.Combine(MigrationPath, CompletedMigrationFile),
                migrationName + ";" + DateTime.Now.ToString("yyyyMMddHHmmss") + ";" + Convert.ToInt16(wasSuccessful).ToString() + Environment.NewLine);
        }

        public async Task ApplyMigrationsAsync(IDbConnection dbConnection, string databaseName, bool attemptFailed = false) {
            await Task.Run(() => {
                ApplyMigrations(dbConnection, attemptFailed);
            });
        }

        public void ApplyMigrations(IDbConnection dbConnection, bool attemptFailed = false) {
            string[] migrationFiles = GetAvailableMigrations(attemptFailed);
            Total = migrationFiles.Count();
            Pending = Total;
            Failed = 0;
            Successful = 0;

            // Iterate through the migrations reporting progress along the way
            foreach (var migration in migrationFiles) {
                string migrationName = Path.GetFileNameWithoutExtension(migration);
                MigrationStarted?.Invoke(this, new MigrationStartedEventArgs(migrationName, --Pending));
                MigrationProgressReported?.Invoke(this, new MigrationProgressEventArgs(Total, Pending, Successful, Failed, $"Started Migration '{migrationName}'..."));

                // Do the work
                Exception error = null;
                bool wasSuccessful = false;
                try {
                    using (var transaction = dbConnection.BeginTransaction()) {
                        try {
                            using (var cmd = dbConnection.CreateCommand()) {
                                cmd.CommandText = File.ReadAllText(migration);
                                cmd.Transaction = transaction;
                                cmd.ExecuteNonQuery();
                            }
                            transaction.Commit();
                            wasSuccessful = true;
                        } catch (Exception ex) {
                            transaction.Rollback();
                            error = ex;
                            wasSuccessful = false;
                        }
                    }
                } catch (Exception ex) {
                    error = ex;
                    wasSuccessful = false;
                }

                // Report progress
                if (wasSuccessful) {
                    Successful++;
                } else {
                    Failed++;
                }
                MigrationCompleted?.Invoke(this, new MigrationCompletedEventArgs(migrationName, Pending, error, wasSuccessful));
                MigrationProgressReported?.Invoke(this, new MigrationProgressEventArgs(Total, Pending, Successful, Failed,
                    (wasSuccessful ? "Successfully" : "Unsuccessfully") + $" Completed Migration '{migrationName}'."));
                MarkMigrationComplete(migrationName + ".sql", wasSuccessful);
            }
        }
    }
}