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
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. 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_TEST;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
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. (Having the project in source control before attempting this stage will greatly help you out here, enabling you to recover from accidental overwrites.)
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
If it’s been a while since you scaffolded a database table, then you may get hit with the following warning:
Run “dotnet tool restore” to make the “dotnet-ef” command available.
Then, running the following :
dotnet tool install –local dotnet-ef
results in this:
Cannot add package. Manifest file already contains version 8.0.7 of the package dotnet-ef. Uninstall/install or edit manifest file D:\AzureSource\StockReferenceDataMaintenance.config\dotnet-tools.json to specify the new version 8.0.8.
Instead, use the following which satisifies both warnings:
dotnet tool update –local dotnet-ef;
Adding to Version Control
- git init
- git remote add origin https://LoganInc@dev.azure.com/LoganInc/LegacyDbMaintenance/_git/LegacyDbMaintenance
- git remote -v
- git add .
- git commit -m"Initial checkin of new project"
- git push –set-upstream origin main
I have detailed the initial local IIS deployment details in this post, along with the migration of the app into Azure.