Seeding your database can be a quite tricky task. Here we are going to introduce some tools which simplify this operation.

Problem

When we distribute a .NET (Core) project, it’s often essential to create and fill the underlying database with some data on the first launch of the app.

For example, let us imagine you have a demo app of some library and for its presentation, it is necessary that the database already have some data.
Another possible use case: each copy of our application works with its own database and we need to fill it with some initial content during the installation or at the first start.

Creating the database is not a big problem, especially if the project uses Entity Framework (Core) — it can be done literally in one line of code. However, filling our database with data is a completely different matter and there are no ready-to-use solutions for this task in .NET or Entity Framework.

Moreover, it is not a rare situation when the required seed data is already in the database on your local computer, but you need a way to include this data in your project and deploy them at application’s startup.

Solution

Of course, there are many different ways to solve the tasks described above.

DbTool is a .NET Core global tool and can be installed on any computer with .NET Core SDK using one simple console command. Using this utility, we will export our database data to some format suitable for distribution (JSON or XML).

Then we will include the resulting data file (or files) in our project and use Korzh.DbUtils library to initialize our database when the application is launched for the first time.

Step 1: Export DB data with DbTool

1.1 Installing DbTool

As we said before, DbTool is a global .NET Core utility that can be installed on your computer and then used as any other shell command on your system. You will need the .NET Core SDK version 2.1 (or higher) to be installed on your machine.

Installing DbTool is easy. To do this, simply open the terminal (Command Prompt) and run the following command:

dotnet tool install -g Korzh.DbTool

If you run the dbtool command right after that, you will see a very detailed help with the list of all available commands.

1.2 Registering DB connection

To get started, we need to add a connection to our database first:

dbtool connections add {YourConnectionId} {DbType} {YourConnectionString}

Here:

• {YourConnectionId} is just an unique name you would like to assign to this connection.
• DbType— the type of your DB server. Currently (version 1.2.0), DbTool supports SQL Server (sqlserver), MySQL (mysql) and PostgreSQL ( postgre) databases.
• The last parameter in this command is a connection string. The same you are already using in your project to connect the DB.

Example:

After that you can check all your connections by typing:

dbtool connections list

1.3 Exporting the data

Now when we added the connection we can export our database using the export command:

dbtool export {ConnectionId} [--format = xml | json] [--output = path] [--zip = filename]

Any option listed above can be omitted. For example, the following command:

dbtool export MyDb01 --zip = MyDbData.zip

will create a ZIP archive named MyDbData.zip in the current directory and fill it with a bunch of data files in JSON (the default) format. The content of each DB table is stored in a separated .json file.

Step 2: Add data seeding code to your app

We are going to show how to use the file created on the previous step to initialize the DB in some ASP.NET Core project. However, the procedure described below can be applied to any project made with .NET Core or .NET Framework version 4.6.1 or higher.
We assume here that our project uses Entity Framework (Core), so the database itself is created automatically with EF means (using eitherEnsureCreated or Migrate method). Let’s also assume that our DB is running under SQL Server (or its local variant). The instructions listed here are almost the same for MySQL DB as well.

2.1 Add the data file to your project

First of all, we need to add `MyDbData.zip` file to your project. The best place for it is the`App_Data` folder within your project’s folder.
Please note, that you will also need to include that file into your project manually for .NET Framework projects.

2.2. Installing Korzh.DbUtils packages

For our tasks we will need the following 2 NuGet packages:

• Korzh.DbUtils.Import
• Korzh.DbUtils.SqlServer (or Korzh.DbUtils.MySql in case of using MySQL database)

To install the packages you can use either NuGet Package Manager, NuGet console or add them manually (as <ProjectReference...> tag) right to the .csproj file.

2.3. Add the initialization code

Finally, we need to create an instance of DbInitializer class and call its Seed method on the first start. The best place for this procedure is theConfigure method of our Startup class:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    .     .     .     .    app.UseMvc();    //add the following piece of code at the end of your Configure method
    using (var scope = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>().CreateScope())
    using (var context = scope.ServiceProvider.GetService<AppDbContext>()) {
        //the next lines will run only if the database was not created previously
        if (context.Database.EnsureCreated()) { 
            Korzh.DbUtils.DbInitializer.Create(options => {
                //set the connection string for our database
                options.UseSqlServer(Configuration.GetConnectionString("MyDemoDb"));
                options.UseZipPacker(System.IO.Path.Combine(env.ContentRootPath, "App_Data", "MyDbData.zip"));
            })
            .Seed();
        }
    }
}

If you need to do some additional initialization on the first start (e.g. adding some default user accounts) it would be a good idea to hide all these initialization codes into a separate class and expose only an extension function (let’s call it EnsureDbInitialized) to IApplicationBuilder interface. The same way as we did in our sample project for EasyQuery library.

In this case, you will just need to add one call at the end of your Startup.Configure method:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    .  .  .  .
    app.UseMvc();    //Init demo database (if necessary)
    app.EnsureDbInitialized(Configuration, env);
}