Managing and updating database connections

Understanding the default database connection

When you generate standard C# projects from a low-code project, a default SQLite database connection is automatically created in the C# project. The appsettings.json file in the [project].WebApi project specifies the SQLite data.db as the data source of the connection string.

Setting a different database connection

Because the projects generated from the low-code project are standard C# projects, you can modify the database connection in the same way as in any other C# projects.

To change to a different database connection, remember to at least make the following changes:

Step 1: Install the database provider for EF Core for the database that you plan to use, and then update the PackageReference in [project].EFCore.csproj.

Step 2: Change the DataContext setting in the appsettings.json file in the [project].WebApi project.

Step 3: Update the AddDataContext method in the ServiceCollectionExtensions.cs file of the [project].EFCore project.

Updating the database after entity changes

After you compile and run the [project].WebApi project, the database tables will be created for the application based on the entity design. For example, if you use the default SQLite database, the data.db file is created in the project folder.

After the database tables and schemas have been successfully created, if you change any field setting in the entity designer, after you execute the “Generate Projects” operation, you must update the database schemas accordingly. Otherwise, the changes in the entity may conflict with the existing table schemas and will cause data operations fail to work.

This section gives you the possible options for updating the database at entity changes.

Option #1: Drop the existing database

While you are at the development stage, if you think it is valueless to keep the existing database tables, you may just completely drop it. Then, when you run the project again, the new database tables will be created.

  • With SQLite, it is easy. You can directly remove the data.db file in the [project].WebApi project.

  • With the other databases, you can execute the DROP TABLE statement to drop the existing tables:

  • Alternatively, you can add script in the [project].EFCore to remove existing tables and recreate new ones when running the Web API. With this approach, you must remember to comment out the script after the entity/model design is stable and not subject to further changes.

    In the [project].EFCore > ServerCollectionExtensions.cs, find the following script in the TryMigrateDatabase method:

    Change it to:

    try
         {
           context.Database.EnsureDeleted();
          }
    catch
          {
              //
           }
    context.Database.EnsureCreated();

Option #2: Applying migrations to the existing database

If you do not want to drop the database, because the C# projects generated from the low-code project are EF Core-based, you can apply the EF Core migrations to the existing database.

The complete methodology is provided in the EF Core documentation. The following highlights the main steps required.

Step 1: Install the EF Core command-line interface tools.

Step 2: Install the Microsoft.EntityFrameWorkCore.Design package in the [project].EFCore project. Note that the version should be identical to the corresponding database package for EF Core.

Step 3: Change the migrations assembly to [project].WebApi.

Step 4: Open command prompt and navigate to the root folder of [project].WebApi, and execute the appropriate comments for the migrations.

For example, you may execute this command to add a new migration.

dotnet ef migrations add initsample001

Execute this command to update the database to a specified migration.

dotnet ef database update

For the complete list of commands, please refer to https://entityframeworktutorial.net/efcore/cli-commands-for-ef-core-migration.aspx.