Skip to content

How to Insert Binary Data With DbUp

DbUp is a great tool to manage changes to our database schemas. While its strength is with changing tables, we can use DbUp to insert seed data as well. It even works with binary data, if we use the right settings and find the correct method of SqlCommand.

Accept code for migrations

While it theoretically is possible to write a SQL statement that loads the binary data from your computer, it is much simpler to write some C# code. But before we can use code for our migrations, we need to activate it.

In the Program.cs file we need to replace WithScriptsEmbeddedInAssembly with WithScriptsAndCodeEmbeddedInAssembly:

1
2
3
4
5
6
7
var upgrader = DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsAndCodeEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .WithTransactionPerScript()
        .WithVariablesDisabled()
        .LogToConsole()
        .Build();

Create the table

We can create the table that will contain our binary data as usual with a SQL script inside the Scripts folder:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE dbo.Files(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [File] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
ON [PRIMARY] FILESTREAM_ON [DemoFS],
UNIQUE NONCLUSTERED 
(
    [FileId] ASC
) WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [DemoFS]
GO

Add the binary data

For this post I want to add a logo that I put in the folder Binary and set the option "Copy to Output Directory" to "Copy always" :

The logo.png file is inside the folder Binary

You can choose whatever name you like for the folder, just make sure that you use the same name for the upcoming code to read the file.

Create the migration code

Next to our script to create the table we can create a C# file with the name Script_02_InsertBinary.cs:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
using DbUp.Engine;
using Microsoft.Data.SqlClient;
using System.Data;

namespace WithBinary.Scripts
{
    public class Script_02_InsertBinary : IScript
    {
        public string ProvideScript(Func<IDbCommand> dbCommandFactory)
        {
            var payload = File.ReadAllBytes(@"Binary\logo.png");

            var querry = $@"
                INSERT INTO [dbo].[Files]
                            ([FileId],
                            [File])
                VALUES
                            (newid(),
                            @logo);";

            var cmd = dbCommandFactory();
            SqlCommand insert = new SqlCommand(querry, (SqlConnection)cmd.Connection!);
            insert.Parameters.AddWithValue("@logo", payload);
            insert.ExecuteNonQuery();

            return string.Empty;
        }
    }
}

Our class implements the IScript interface with the method ProvideScript(). The parameter to that method is our entry into the SQL connection that we then can use to create our own INSERT statement.

We can read our binary file and use it as the value to the @logo parameter that we placed where we want to insert the data.

Finally, we return an empty string back to DbUp. For any other script to change our database, we would let DbUp run the SQL commands. In this case we make sure that everything is done before we hand back the control.

Run the database migration

If we now run DbUp, it will first create the table and then insert our logo. We can check the SchemaVersions table to see if our code script did run:

1
2
3
4
5
SELECT * FROM dbo.SchemaVersions

Id  ScriptName                                    Applied
1   WithBinary.Scripts.Script_01_CreateTable.sql  2024-03-04 21:10:47.034
2   WithBinary.Scripts.Script_02_InsertBinary.cs  2024-03-04 21:10:47.937

Conclusion

DbUp works like a charm with binary data. While this may not be a daily use case for most users, it is good to know that DbUp can handle this task without any problems.