jamesearnshaw.com

From Scratch: How to create a simple database frontend with ASP.NET Core (Part 1)

by James Earnshaw on June 11, 2024

This is a series where I create a simple web frontend for a SQL Server database.

This post is for you if you need to create a simple web frontend, but you're a BI Developer, not a web developer. Your users (and I'm talking about a corporate scenario where users are people in other departments), will be happy with a few CRUD pages, nothing fancy.

I will create a web UI for a SQL Server database using ASP.NET Core, Microsoft's framework for building web apps.

I won't explain ASP.NET Core in detail because that would require a book, and this series is more like a recipe. To learn more I'd start with the documentation or Pro ASP.NET Core by Adam Freeman. It's a great book. Buy it.

Overview of ASP.NET Core

It's helpful to know the basics about how the web works. Client's (e.g. browsers) send requests (e.g. GET some page URL) to a server, and the server parses the request and sends back a response that contains HTML, which the browser uses to render the requested web page.

ASP.NET Core is a server-side framework. When it receives a request it creates an object to represent it and creates a corresponding object for the response. It feeds these objects through its request pipeline where all kinds of configurable services and middleware components inspect the request and modify the response. Finally the response is sent back to the client.

Prerequisites

To follow along you need these:

ASP.NET Workload

  • A .NET SDK (software development kit). I use version 8 in this tutorial. Download it from here. Do a check that it's on your machine by running this command dotnet --list-sdks. It will list the SDKs installed on your machine, e.g.:

SDKs

The recipe

A web application will be created using the MVC Framework, which is one of the application frameworks available within ASP.NET Core. MVC (model view controller), is a software design pattern that emphasises the separation of concerns between the business logic and UI logic.

The web application uses Entity Framework Core, which is a type of software called an object relational mapping (ORM) tool. ORMs facilitate the easy communication between databases and .NET objects. It does a lot of the work for us, like creating model classes for the SQL tables and handling CRUD operations. This is called the "database first" approach, where a database schema exists first and the UI is built on top of it. This is the scenario described in the intro (needing to put a frontend on a database).

Setting up the project

I use PowerShell for setup tasks (creating projects, adding packages). Doing so means you can repeat these steps and (hopefully) get the same result.

Create a working directory

Make a root directory somewhere for the project.

mkdir C:\work\WebUI
cd C:\work\WebUI

The second command, cd C:\work\WebUI, sets the current directory of the PowerShell session to the project root, which is necessary when creating projects and solutions, and adding packages, which we do in the next few steps.

Create the project and solution

These commands create a blank web project and solution.

dotnet new globaljson --sdk-version 8.0.100
dotnet new web --no-https --framework net8.0
dotnet new sln
dotnet sln add .\WebUI.csproj

Open the solution file in Visual Studio 2022. Solution Explorer should look like this:

solution-explorer

Modify launch settings

In the launchSettings.json file, located in the Properties folder, change the value of the applicationUrl key to http://localhost:5000, like this:

"applicationUrl": "http://localhost:5000"

There are two lines that you need to do this on.

Run the project

Run the application with dotnet run in the PowerShell session with the current directory at the root (i.e. C:\work\WebUI).

Open a browser and go to http://localhost:5000. You should just see the text "Hello World!". Now shut it down with the cancel the command in PowerShell (Ctrl + C).

At this point we have a bare bones project. We could have instead used one of the project templates that comes preconfigured with features out of the box, like sample pages, controllers and views. I prefer the bottom up approach of building it from scratch. One reason is that the template projects come with a lot of boiler plate code that just feels like unnecessary noise, especially at first when you don't know what it's for.

The downside is you have to add all the boilerplate and setup parts yourself. But you can see it as a good thing because you'll learn why projects are structured the way they are.

Create more folders

We're using the MVC framework, so we'll need a folder for each letter:

New-Item .\Models\foo.cs, `
.\Views\Home\Index.cshtml, `
.\Controllers\foo.cs -Force

The foo.cs files are just placeholders that make the folders visible in Visual Studio because empty folders are hidden. They will be deleted soon.

Dependency injection and configuration

ASP.NET Core is flexible in that you can use whichever features you want in your project. This is done in the Program.cs file by adding things called services and middleware through the WebApplicationBuilder and WebApplication, i.e., the builder and app objects created in these statement in the Program.cs file:

var builder = WebApplication.CreateBuilder(args);

var app = builder.Build();

Services and middleware are added with statements like:

// add services
builder.Services.AddSomeService();

// add middleware
app.MapSomething();

This is a technique called dependency injection and it means that these configured services are available throughout the application.

There are three application frameworks in ASP.NET Core: MVC (the one used here), Razor Pages, and Blazor, and the flexibility means all three may be used in a project simultaneously. But whatever you decide to use has to be configured in the Program.cs file. So any time changes are made in the Program.cs file just know that the reason is to do some form of configuration or enabling of features.

So the MVC framework needs to be enabled by adding services and middleware through the builder.Services property and the app object in the Program.cs file. It should look like this before any changes are made:

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.MapGet("/", () => "Hello World!");

app.Run();

Now add two the statements and delete the "hello world" line so that it now looks like this:

var builder = WebApplication.CreateBuilder(args);

// MVC service
builder.Services.AddControllersWithViews();

var app = builder.Build();

// MVC middleware
app.MapDefaultControllerRoute();

app.Run();

So now the MVC framework is configured in the application.

Create a controller and view

Add a class to the Controllers folder called HomeController.cs. I do this by clicking the folder in Solution Explorer and pressing Ctrl + Shift + A to bring up the add new item dialog. Set the file's contents to this:

using Microsoft.AspNetCore.Mvc;

namespace WebUI.Controllers
{
    public class HomeController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }
    }
}

The foo.cs file can be deleted now.

Next change the contents of the Views\Home\Index.cshtml file to:

<h1>Hello from the View</h1>

Quick note on cshtml files

As the name implies, cshtml files contain both C# and HTML code. The official name is Razor syntax. They are server side pages used by ASP.NET Core to generate the HTML in the response.

Final run

Run the application and navigate to the URL http://localhost:5000/home. You should see the text "Hello from the View" that was just set in the Index.cshtml file.

Another command that you might use to run an application is dotnet watch. It essentially watches the source code for any changes, and then re-runs the application with the changes so they can be seen straight away. It's known as a hot reload and it's handy for iterative development.

End of Part 1

In Part 2 I'll add the data model and use Entity Framework Core to display the contents of a SQL Server table in a web page.