Fluent Migrator<\/h2>\n
A library called Fluent Migrator had most of the required features. You can find out more about here<\/a>.<\/p>\n Fluent Migrator is a NuGet package that helps you create migration classes or scripts that can be tracked and automated in code.<\/p>\n Migrations can be written programmatically, or you can execute SQL scripts directly from code. You can target multiple databases and have control down to the transaction level which can also be helpful.<\/p>\n <\/p>\n Some of the main points include:<\/p>\n <\/p>\n A unique version is assigned to each class along with a description using the Migration attribute.\u00a0 The upgrade and downgrade methods are implemented:<\/p>\n You don\u2019t need to run SQL scripts and just like Entity Framework, you can write code-first migrations.<\/p>\n Having to manually create unique version numbers adds extra friction to the development loop.<\/p>\n Luckily, you can create your own custom attribute to enforce a naming convention for each migration you create.<\/p>\n Here you can see a custom attribute.\u00a0 It accepts several parameters which are used to generate a unique migration version and description:<\/p>\n <\/p>\n By default, each migration will run in a database transaction. I\u2019ve extended the above example (taken from Fluent Migrator docs) to include additional parameter FluentMigrator.TransactionBehavior transactionBehavior .<\/p>\n This lets you specify if you want to wrap the C# migration in a transaction (Default<\/strong>). Setting it to None<\/strong> will prevent the transaction from being added.<\/p>\n Learn more about custom attributes here<\/a>:<\/p>\n As mentioned, Fluent Migrator will wrap each call within a database transaction.\u00a0 This means your migration will fail if you have any SQL scripts with logic such as :<\/p>\n <\/p>\n Set FluentMigrator.TransactionBehavior transactionBehavior = FluentMigrator.TransactionBehavior.None<\/strong> to prevent this from happening.<\/p>\n You can see an example migration class here.\u00a0 This class runs an upgrade that adds a column Age to the User table.<\/p>\n <\/p>\n The SQL script is nothing fancy:<\/p>\n <\/p>\n After a migration is complete, the Version table is updated.\u00a0 This signals this migration has run and can be ignored on subsequent runs:<\/p>\n All of this is wrapped in a method that fetches the connection string from a JSON config file and any projects with migrations are loaded in using reflection:<\/p>\n You can watch the video of it in action here<\/a> on YouTube or check it out here:<\/p>\nHow it Works<\/h2>\n
\n
Creating a Unique Version Id<\/h2>\n
[Migration(12345,\"a description\")]\r\npublic class M001_DB_DescriptionOfTask : Migration\r\n{\r\n public override void Up()\r\n {\r\n \u00a0 Execute.Script(Directory.GetCurrentDirectory() + @\"YourUpgradeScript.SQL\");\r\n }\r\n public override void Down()\r\n\u00a0 {\r\n\u00a0\u00a0\u00a0 throw new NotImplementedException();\r\n\u00a0 }\r\n}<\/pre>\nCustom Attribute<\/h2>\n
public class CustomMigrationAttribute : FluentMigrator.MigrationAttribute\r\n{\r\n public CustomMigrationAttribute(int branchId, int year, int month, int day, int hour, int minute, string author, FluentMigrator.TransactionBehavior transactionBehavior = \r\n FluentMigrator.TransactionBehavior.Default) : base(CalculateValue(branchId, year, month, day, hour, minute), transactionBehavior)\r\n {\r\n this.Author = author;\r\n }\r\n \r\n public string Author { get; private set; }\r\n \r\n private static long CalculateHash(int branchId, int year, int month, int day, int hour, int minute)\r\n {\r\n return branchNumber * 1000000000000L + year * 100000000L + month * 1000000L + day * 10000L + hour * 100L + minute;\r\n }\r\n}\r\n<\/pre>\nSQL Code with Transactions<\/h2>\n
IF (@@TRANCOUNT>0)\r\nBEGIN\r\n RAISERROR('Transaction Count mismatch',16,1)\r\nEND\r\nEND TRY\r\nBEGIN CATCH\r\n IF (@@TRANCOUNT>0)\r\n\u00a0 BEGIN\r\n \u00a0 ROLLBACK\r\n \u00a0\u00a0 SELECT 'Error inserting data schema, Operation rolled Back' AS [MSG]\r\nEND<\/pre>\nExample<\/h2>\n
[CustomMigration(0007, 2023, 02, 18, 08, 00, \"Jamie Maguire\")]\r\npublic class M0007_Sandbox_AlterUserAddAge : Migration\r\n{\r\n public override void Up()\r\n {\r\n Execute.Script(Directory.GetCurrentDirectory() + @\"\\..\\..\\..\\..\\SandboxMigrations\\Scripts\\2023\\02\\0007\\AlterUserAddAge.SQL\");\r\n }\r\n\r\n public override void Down()\r\n {\r\n throw new NotImplementedException();\r\n }\r\n}\r\n<\/pre>\nALTER TABLE [User]\r\n\r\nADD Age int<\/pre>\n
![](\"https:\/\/jamiemaguire.net\/wp-content\/uploads\/2023\/02\/Pasted.png\")
<\/p>\nMain Code<\/h2>\n
private static void Main(string[] args)\r\n{\r\n var builder = new ConfigurationBuilder()\r\n .SetBasePath(Directory.GetCurrentDirectory())\r\n .AddJsonFile(\"appsettings.json\", optional: true, reloadOnChange: true);\r\n\r\n\r\n string connectionString = string.Empty;\r\n\r\n connectionString = builder.Build().GetConnectionString(\"Sandbox\");\r\n\r\n var sc = new ServiceCollection()\r\n \/\/ Add common FluentMigrator services\r\n .AddFluentMigratorCore()\r\n .ConfigureRunner(rb => rb\r\n \/\/ Add SQL support to FluentMigrator\r\n .AddSqlServer()\r\n \/\/ Set the connection string\r\n .WithGlobalConnectionString(connectionString)\r\n \/\/ Define the assembly containing the migrations\r\n .ScanIn(typeof(SandboxMigrations.Sandbox).Assembly)\r\n .For.Migrations())\r\n \/\/ Enable logging to console in the FluentMigrator way\r\n .AddLogging(lb => lb.AddFluentMigratorConsole())\r\n \/\/ Build the service provider\r\n .BuildServiceProvider(false);\r\n\r\n using (var sandpitScope = sc.CreateScope())\r\n {\r\n UpdateDatabase(sandpitScope.ServiceProvider);\r\n }\r\n }\r\nstatic void UpdateDatabase(IServiceProvider serviceProvider)\r\n{\r\n \/\/ Instantiate the runner\r\n var runner = serviceProvider.GetRequiredService<IMigrationRunner>();\r\n \/\/ Execute the migrations\r\n if (runner.HasMigrationsToApplyUp())\r\n {\r\n runner.ListMigrations();\r\n runner.MigrateUp();\r\n }\r\n}\r\n<\/pre>\nDemo<\/h2>\n