Contents
data/authors/Paul Logan.json

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 maintain the data.

The Solution

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. Then copy and paste it into the VS Code terminal (the end of line ticks are intentional and act as line-continuation character).

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;

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

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

Update the Program.cs file with the DbContext connection string configuration. This will be used by the scaffolding commands that follow.

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();

Scaffolding the DbContext

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;

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
VS Code Extension
I installed the new C# Dev Kit extension for VS Code as part of this project. However, the first attempt failed as it was not compatible with the version of VS Code I was using. There were no notifications showing for updates being available. I expected to find a manual menu option under Help, but alas no. It turns out the the “Check For Updates” option under the Help menu is not shown for User installations of VS Code that are set to run as admin. I needed to uninstall VS Code and download the System Installer, as opposed to the User Installer. When complete, the “Check For Updates” option was now show - but I didn’t need to use it as the latest version was installed.

Deployment to local web server

Done manually at the moment.

  • VS Code terminal: dotnet publish –configuration Release

  • Go to D:\Source\LegacyDatabaseMaintenanceApp\bin\Release\net7.0\publish

    • Sort files by date modified.
    • Copy the files that have just been updated.
  • Browse to the application folder on the web server and paste the files, overwriting those already there.

  • Restart the AppPool for the app in IIS Manager.