Performing C# (procedural) migrations with DbUp

Question

Using DbUp, is it possible to write a procedural migration in C# (rather than SQL)?

I realise the general philosophy of DbUp is to do everything in SQL but there might be cases where C# is the better tool for the job.

For example let's say you're storing serialized binary blobs in your database (ignoring whether that's a good idea or not) and you want to alter the structure of those blobs. Your application code has the know-how to deserialize/serialize those blobs. Doing so might be possible via SQL, but doing it via C# would be simpler.

Is there a way to do this (other than IScript)? Basically I'm looking for a mechanism to

• Allow issuing arbitary commands against the database in the context of a transaction
• If the transation completes succesfully, record that "migration" as having completed

IScript seems like the closest thing there is - wondering if there is something better.

Answer 1

Yes - see https://dbup.readthedocs.io/en/latest/usage/#code-based-scripts

Code-based scripts Sometimes migrations may require more logic than is easy or possible to perform in SQL alone. Code-based scripts provide the facility to generate SQL in code, with an open database connection and a System.Data.IDbCommand factory provided.

The code-based migration is a class that implements the IScript interface. The ProvideScript() method is called when it is the migration's turn to be executed, so the scripts before it have already been executed.

Of course, the command factory can be used for more than just queries. The entire migration itself can be performed in code:

public class Script0006UpdateInCode : IScript
{
    public string ProvideScript(Func<IDbCommand> commandFactory)
    {
        var command = commandFactory();

        command.CommandText = "CREATE TABLE [dbo].[Foo]( [Name] NVARCHAR(MAX) NOT NULL )";
        command.ExecuteNonQuery();

        return "";
    }
}