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” :
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.