Using DbUp - The first go...

So a release was coming up with multiple SQL scripts to be executed in a sequence. The best bet was to use a tool such as DbUp to automate the process.

DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

I used the default example on http://dbup.github.io/ to start off. But my scripts timed out as I had the embedded SQL scripts doing some serious data uploads as well.

A solution to the timeout problem can be found here https://phejndorf.wordpress.com/2014/07/02/setting-execution-timeout-in-dbup/

So the final code looks like the following with a deviation to use a configuration file with the connection string:

static int Main(string\[\] args)
        {
            var connectionString \=
                args.FirstOrDefault()
                ?? ConfigurationManager
                .ConnectionStrings\["DefaultConnection"\]
                .ConnectionString;

            var upgradeEngineBuilder \=
                DeployChanges.To
                    .SqlDatabase(connectionString)
                    .WithScriptsEmbeddedInAssembly(
                    Assembly.GetExecutingAssembly());

            upgradeEngineBuilder.Configure(c \=>
            {
                // 30 minutes in seconds
                c.ScriptExecutor.ExecutionTimeoutSeconds \= 30 \* 60; 
                Console.WriteLine("Configure ExecutionTimeoutSeconds to "
                    + c.ScriptExecutor.ExecutionTimeoutSeconds);
            });

            var upgrader \= upgradeEngineBuilder.LogToConsole().Build();

            var result \= upgrader.PerformUpgrade();

            if (!result.Successful)
            {
                Console.ForegroundColor \= ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                Console.ReadKey();
                return \-1;
            }

            Console.ForegroundColor \= ConsoleColor.Green;
            Console.WriteLine("Success!");
            Console.ResetColor();
            Console.ReadKey();
            return 0;
        }

This article was originally written on Google's Blogger platform and ported to Hashnode on 17 Sep 2022.

Using DbUp - The first go...

Comments

More from this blog

Tiny Elf - Transferring ownership of automated triggers

I dug a trench and unearthed some Software Engineering principles

Happy Birthday, Luminary - H5YR!

Content delivery API in Umbraco 12 RC 1 - Did I say Headless?

Tiny Elf - a Slack bot with Google sheets and Typescript

Command Palette

Comments

More from this blog