So you want to build a web application? Cool, I’ll try to illustrate one way to do this using .NET Core step by step to build an application with a database, and even setup some CRUD management in an automated way. Let’s get some assumptions out of the way first.
Assumptions
- .NET Core 2.1 SDK installed
- use the archive link above to install 2.1 LTS, since it’s in “long-term support”
- Docker installed
- Access to
dotnetanddockercommands. Ideally, you have VS Code installed, but technically you could do all this from a powershell/bash prompt, depending on your OS
We’re going to heavily use the command line interface (CLI) of both .NET Core and Docker. These installations aren’t covered, but some links are provided above.
Conventions
$ Commands will look like this. I'm on a Mac, using bash, so my prompt is a "$". Your mileage may vary
C# samples will look like this. I'm using VS Code, but I don't care how you create these files.
Pay attention to boldface words. They’re usually going to be variable names or something of note.
We start on the backend: the database.
Our application will use a SQL Server data store. You can use something like SQL Server Express if you’re on Windows. Alternatively, you can use Docker regardless of your OS. Dockerhub has an image we can quickly (after downloading) spin up to use SQL Server for local development purposes. Note: this download might take a few minutes.$ docker pull mcr.microsoft.com/mssql/server:2017-latest
$ docker pull mcr.microsoft.com/mssql/server:2017-latest
Confirm the new image is downloaded and Docker sees it on your machine:
$ docker images
You should a tabular display similar to the following:
Run a new container using the new image, and add some parameters similar to the instructions on Dockerhub.
$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \ -p 1433:1433 --name sql1 \ -d mcr.microsoft.com/mssql/server:2017-latest
This command outputs a container ID–just a long string of alphanumeric. It’s not important for today. At this point, Docker has initialized and started a container running SQL Server from the image we downloaded. Let’s check that it’s running:
$ docker ps -a
You should see something like the below image, only the “CREATED” column should indicate that you just created the container.
Note the “Up X seconds” status that indicates the container is running, so we have an active SQL Server instance ready for us.
It’s worth noting the environment variable we used: SA_PASSWORD just sitting in an environment variable. While this isn’t a concern in this tutorial, you would never want a production container in the wild with the actual SQL SA password. So, I’ll show you how to quickly change it. Let’s make the new password: <YourNewStrong!Passw0rd>
docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \ -S localhost -U SA -P '<YourStrong!Passw0rd>' \ -Q 'ALTER LOGIN SA WITH PASSWORD="<YourNewStrong!Passw0rd>"'
That’s it. You should probably consider something stronger and more different than the environment variable/password we’ve used, but that’s another conversation.
Real quick, let’s do two things: run bash on the container so we can run commands as if we’re logged into this virtual machine (container,) and further log into our SQL Server and run a couple simple SQL commands to prove everything is working.
$ docker exec -t sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'
Now we’re logged into SQL Server using SQL authentication. Let’s create a quick database, and run a select command on sys.databases to make sure we’re working.
At the SQL prompt, try these commands:
1> create database testdb 2> select name from sys.databases 3> go
Don’t worry about the other databases e.g. master, tempdb, etc, but note our “testdb” that was created. Cool, SQL Server is up.
We’re done with Docker. Type exit and hit enter twice to get out of 1) SQL Server, and then 2) bash.
Let’s build a web application
We’ll be using the dotnet command extensively. Generate a new app in a folder called FancyDatabaseApp. This will scaffold a whole application that you can run right away, although it’s pretty boring.
$ dotnet new mvc -o FancyDatabaseApp
Change your directory to the new app:
$ cd FancyDatabaseApp
If you feel so inclined to run the application, use dotnet run and it should run the application. It should indicate which port it’s available on, and it will probably be something like https://localhost:5001. We will run the application a little later once we’ve actually added some database things.
Let’s pick a data model i.e. the things our app will manage. At a recent meetup I hosted, the audience picked a “beer store” as the data model, or at least some kind of beer inventory management system. As a contrived example, we stuck with a basic one-to-many relationship. For example, a Restaurant can have many Beers. So, Restaurant and Beer will be our data models. Let’s describe them in C# below.
As a note, this is where you could open VS Code in this folder for some a sweet IDE, but you can always use your favorite text editor. I don’t really care what you use, but you should create two new files in the Models folder within your app: Restaurant.cs and Beer.cs.
namespace FancyDatabaseApp
{
public class Restaurant
{
public int RestaurantId { get; set; }
public string Name { get; set; }
public string Location { get; set; }
}
}
namespace FancyDatabaseApp
{
public class Beer
{
public int BeerId { get; set; }
public string Name { get; set; }
public int HopCount { get; set; }
public int RestaurantId { get; set; }
public virtual Restaurant Restaurant { get; set; }
}
}
Other than the Plain Old Class Object’s (in case I use the POCO abbreviation any time below,) the only item worth noting is the RestaurantId/Restaurant members. We’re going to use Entity Framework Core (EF) to talk to our database using only C#. We don’t care to manage raw SQL commands, so let’s keep everything high level. By convention, one-to-many relationships in your data model can be created by adding the “…Id” property to the child class in addition to a navigation object to the parent class. If we had a Beer variable in our code, it would allow us to get information about its parent restaurant with the dot operator e.g. myBeerVariable.Restaurant. When Entity Framework creates the Beer table, it will create the RestaurantId field as a foreign key to the parent table, Restaurant.
Before we get too deep into EF, let’s install it. From your command prompt/bash/VS Code terminal/I don’t care, run a dotnet command to add a new package and note the version number. I only call out the version number since my application happens to target .NET Core 2.1, and you may have installed a later version of .NET Core. Tweak your version number accordingly.
$ dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 2.1.2
This will modify your FancyDatabaseApp.csproj file where .NET Core manages packages required. It won’t change any of your code.
Before we actually scaffold up the database, we need to tell our application where the hell our database lives. Our connection string will be:
Server=localhost,1433;Database=FancyDatabase;User ID=SA;Password=<YourNewStrong!Passw0rd>;Connection Timeout=30;
We’ll use this in a minute after we build a small class that will be used for our primary database communication.
Create a file called FancyDbContext.cs in your Models folder:
using Microsoft.EntityFrameworkCore;
namespace FancyDatabaseApp
{
public class FancyDbContext : DbContext
{
public DbSet<Restaurant> Restaurants { get; set; }
public DbSet<Beer> Beers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
@"Server=localhost,1433;Database=FancyDatabase;User ID=SA;Password=<YourNewStrong!Passw0rd>;Connection Timeout=30;"
);
}
}
}
Let’s break this down a bit.
The DbSet<…> members translate to SQL tables. So, when you add new objects to your data model and you need them represented as tables in your database, be sure to add a new DbSet here. We’ll get into the process of applying these database changes in a minute.
The override of OnConfigure here is a quick-and-dirty way, in one code sample, to show you one (poor) way of specifying where your database lives. Ideally, this should be an appsetting or even a local secret, in addition to being different on your production servers further down the software development lifecycle.
Next, we have to tell our MVC app that it’s using a DbContext in order to play well with EF.
In the existing Startup.cs file, add this line to the ConfigureServices method:
services.AddDbContext<FancyDbContext>();
Now we’re ready to generate a Migration using EF. This migration will be some automagically generated code that tells the database what it needs e.g. new tables, dropped columns, whatever representation your data model makes at the time of this command:
$ dotnet ef migrations add first-migration
If that command ran successfully, you should see a new folder called Migrations in the root of your file hierarchy. If you’re using VS Code, you can confirm this in the Explorer.
I’m not going to go into the generated code too deeply here, but suffice it to say the .cs file is the most relevant, and EF will leverage this migration object (and any other migration objects it finds in the future as the data model changes) to update the database accordingly.
Not surprisingly, the command to update the database and apply the migrations is:
$ dotnet ef database update
This applies our first migration, so the state of the SQL database in our Docker container we created above is synchronized with the current state of our application’s data model.
So, how do we manage the data? We could write controllers and a bunch of CRUD views, etc in order to manage the objects, but we can get a light-speed jumpstart with another package called CodeGeneration. Let’s install it into our app:
$ dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design -v 2.1.7
Again, this modified our .csproj file if you want to take a look, but it modified nothing else. This gives us the dotnet aspnet-codegenerator command, which can do all kinds of crazy things. Mostly, it can help us scaffold controllers, their actions, their views, etc for creating, reading, updating, and deleting records from our database in an MVC-conventionalized (is conventionalized a word?) way. Let’s skip to the awesome part. Run these commands one after the other, and CodeGeneration will scaffold everything for us.
$ dotnet aspnet-codegenerator controller -name RestaurantsController -actions -m Restaurant -dc FancyDbContext -outDir Controllers $ dotnet aspnet-codegenerator controller -name BeersController -actions -m Beer -dc FancyDbContext -outDir Controllers
Now we can run our application and see the fruits of our “labor”.
$ dotnet run
Open a browser and navigate to https://localhost:5001/restaurants
The style is super ugly, but we just scaffolded an entire controller (see your file hierarchy for a RestaurantsController.cs class with plenty of code) and its corresponding views (see new /Views/Restaurants folder and all its .cshtml files) to manage these objects in the database.
Click the “Create New” link and you’ll arrive at /restaurants/create in your application. Enter a name and location (they’re just strings, doesn’t matter what you enter) and click “Create”. You’ll be directed back to the index view that simply lists all the records in the database table Restaurants.
Now, let’s navigate to /beers. Similarly, we can create a new Beer record, but we have a bonus! There is a dropdown to select the Restaurant your beer belongs to. EF and CodeGeneration handled this automagically for us simply because of the way we built our data model. Remember the RestaurantId/Restaurant members? That did it!
A dropdown with the name of the restaurant would have been better, but it’s easy enough to change. I’ll leave that as an exercise for you or possibly a later blog post!
Let’s recap. We:
- Spun up a Docker container with a SQL Server image
- Created a fresh new web application in .NET Core 2.1
- Dreamt up a Beer/Restaurant data model
- Used EF and CodeGeneration to scaffold our database without any raw SQL queries (or writing any code ourselves to do this)
- Learned how to apply migrations to a database
That’s it for now!