Building a web app to maintain database tables

The Brief

To create a dashboard displaying actual production against targets per week.

The data to build the dashboard resided in a mission-critical database created many years ago (aka legacy database), that had no front-end.

The developers were required to update database records manually with SQL as and when required by management. For some tables, management had been provided with SQL commands to INSERT and DELETE records! ⏱️💣💩

My first priority was to create maintenance screens for each of the underlying database tables.

This would allow management to safely update the data.

The Solution

Entity Framework can be instructed to look at an existing database and scaffold entity type classes and a DbContext class based on the schema of that database.

Using the following tools in the .Net Core CLI, I can create a basic CRUD web application:

• .Net Core Tools - Built-in templates for creating projects and files
• EF Core - a DbContext based on a database - Scaffolding/reverse engineering schema.
• ASP.NET Core Tools - code generator and scaffolding engine for generating CRUD pages

The Setup

• Review all the database tables to be maintained in the new app and create any missing Primary Keys, Foreign Keys, Unique Constraints etc. This will not only make the rest of the templating much easier, it will highlight any invlaid/duplicated data and prevent further corruption.
• Windows Exporer to your main source folder.
• Right-click the folder and select “Open with Code”
• When VS Code opens up, press Ctrl and ' to toggle open the Terminal panel.

App Creation

Copy and paste the below code into Notepad and change “LegacyDbMaintenance” to your own app name.

dotnet new razor -o LegacyDbMaintenance; `
cd LegacyDbMaintenance; `
dotnet new gitignore; `
dotnet new tool-manifest; `
dotnet tool install --local dotnet-ef; `
dotnet add package Microsoft.EntityFrameworkCore.Design; `
dotnet add package Microsoft.EntityFrameworkCore.SqlServer; `
dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design; `
dotnet add package Microsoft.EntityFrameworkCore.Tools; `
dotnet tool install --local dotnet-aspnet-codegenerator;

If you have already cloned the repo onto your computer, then replace the first two lines with the following so that you don’t create an unnecessary subfolder:

dotnet new razor; `

Then copy and paste it into the VS Code terminal (the end of line ticks are intentional and act as line-continuation character).

Open the app folder in VS Code and add the connection string to the appsettings.json file:

{
  "ConnectionStrings": {
    "LegacyDbConnectionString": "SERVER=LegacyServer;DATABASE=LegacyDb_TEST;User ID=userID;PASSWORD=userPassword;Encrypt=False;"
  },
  "AllowedHosts": "*"
}

The user name and password will be moved to a secret file later, before commiting this code.

HTTPS development certificate

Trust the HTTPS development certificate by running the following command:

dotnet dev-certs https --trust

Scaffolding the DbContext

The following code creates a DB context allowing the app to interact with the database.

It generates the entities for the 4 specified tables. If no tables are specified, entities for all tables in the database will be generated.

dotnet ef dbcontext scaffold "Name=ConnectionStrings:LegacyDbConnectionString" Microsoft.EntityFrameworkCore.SqlServer `
--context LegacyDbCtx `
--context-dir Data `
--output-dir Models `
--namespace Logan.Legacy `
--table DashboardFactories `
--table Lines `
--table LineStage `
--table LineStageTargets `
-f;

Update the Program.cs file with the DbContext connection string configuration.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

using Logan.Legacy;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<LegacyDbCtx>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("LegacyDbConnectionString")));

// Add services to the container.
builder.Services.AddRazorPages();

Generating the Pages

dotnet aspnet-codegenerator razorpage -m Line -dc LegacyDbCtx -outDir Pages\Lines -udl; `
dotnet aspnet-codegenerator razorpage -m LineStageTarget -dc LegacyDbCtx -outDir Temp1 -udl; `
dotnet aspnet-codegenerator razorpage -m DashboardFactory -dc LegacyDbCtx -outDir Pages\DashboardFactories -udl;

dotnet run

DbContext and the Properties to be Maintained

Remove the entity properties from the model that you will not by displaying to the user on the maintenance screen.

Otherwise, they will be scaffolded into the views and you will then need to remove them from the view and replace them with hidden inputs.

If you forget to include the hidden inputs, then EntityFramework will assume a NULL value and try to update the database accordingly.

Post-Scaffolding

After the initial scaffolding of entity type classes and a DbContext class based on the database schema, there will come a time when you need to include maintenance for a new database table.

If you were to run the scaffolding code above, and just append the new table name, then any changes you have previously made to the DbContext file will be overwritten.

The technique I use is to scaffold the DbContext for the new table/entity into a temporary file, and then extract the model builder and place it in the “real” file.

dotnet ef dbcontext scaffold "Name=ConnectionStrings:LegacyDbConnectionString" Microsoft.EntityFrameworkCore.SqlServer `
--context LegacyDbCtx `
--context-dir Data\TEMP `
--output-dir Models `
--namespace Logan.Legacy `
--table Suppliers

Cloning onto Another Computer

When you or another developer clones this repo onto another computer, you will need to install the tools that were added to the project before they can be used.

Otherwise, you will receive the following error in the terminal when trying to use it.

For this app, the dotnet-aspnet-codegenerator and dotnet-ef tools needs to be installed.

dotnet tool restore

Microsoft describes this command:

#Run this in the terminal to list all the tools installed for current directory/project
dotnet tool list

I have detailed the initial local IIS deployment details in this post, along with the migration of the app into Azure.