Sunday, October 8, 2023

Extending Users and Roles with ASP.NET Identity in VS Code

In this tutorial, I will demo how to add more data fields to the standard users & roles database. In order to proceed with this tutorial, you need to have the following prerequisites:

  • VS Code
  • You have installed .NET 8.0
  • You have installed the dotnet-ef tool
  • You have installed the dotnet-aspnet-codegenerator tool

Companion Video: https://youtu.be/xo4usBberVA

Getting Started

Download the source code for an application that seeds some sample users and roles into an SQLite database from this GitHub repo:

git clone https://github.com/medhatelmasry/Code1stUsersRoles
cd Code1stUsersRoles

When you run this app, you will be able to access the privacy page (/privacy) with the following credentials:

EmailPasswordRolePage
aa@aa.aa P@$$w0rd Admin /privacy
mm@mm.mm P@$$w0rd Member /

This is because the PrivacyModel class in Pages/Privacy.cshtml.cs is annotated with the following:

[Authorize (Roles = "Member, Admin")]

Click on the Register link on the top-right side of your keyboard to add a new user. 

When you click on the Register button, you will receive a page that looks like this:


Click on Logout in the top-right corner.

Open the application folder in VS Code.

Suppose we want to capture more data about the user, in addition to email and password. Let us assume we want to extend user data with FirstName & LastName.

An easy way to do this is to create a new class that extends IdentityUser and adds the above properties. Create a Models folder and add a new class named CustomUser to it with the following class code: 

public class CustomUser : IdentityUser {
  public CustomUser() : base() { }

  public string? FirstName { get; set; }
  public string? LastName { get; set; }
}

We may also wish to extend the standard roles table with these properties:

Description
CreatedDate

Just as we did with users, we will also create another class for roles that inherits from IdentityRole. In the Models folder, create another class named CustomRole and add to it the following code:

public class CustomRole : IdentityRole {

  public CustomRole() : base() { }

  public CustomRole(string roleName) : base(roleName) { }

  public CustomRole(string roleName, string description,
    DateTime createdDate)
    : base(roleName) {
    base.Name = roleName;

    this.Description = description;
    this.CreatedDate = createdDate;
  }

  public string? Description { get; set; }
  public DateTime CreatedDate { get; set; }
}

Add the following to Pages/ _ViewImports.cshtml:

@using Code1stUsersRoles.Models

Edit Data/ApplicationDbContext.cs file and make ApplicationDbContext  inherit from IdentityDbContext<CustomUser, CustomRole, string>. The ApplicationDbContext class code should look like this:

public class ApplicationDbContext : IdentityDbContext<CustomUser, CustomRole, string> {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) { }
}

Modify Data/ModelBuilderExtensions.cs so that it uses CustomUser instead of IdentityUser & CustomRole instead of IdentityRole
  • When creating a role, add data for Description and CreatedDate.
  • When creating a user, add data for FirstName & LastName.
In the Program.cs class, replace IdentityUser with ApplicationUser and IdentityRole with ApplicationRole. The builder.Services.AddIdentity… statement will look like this:

builder.Services.AddIdentity<CustomUser, CustomRole>(
options => {
    options.Stores.MaxLengthForKeys = 128;
})
.AddEntityFrameworkStores<ApplicationDbContext>()
.AddDefaultUI()
.AddDefaultTokenProviders()
.AddRoles<CustomRole>();

Edit Pages/Shared/_LoginPartial.cshtml and change:

@inject SignInManager<IdentityUser> SignInManager
@inject UserManager<IdentityUser> UserManager

TO

@inject SignInManager<CustomUser> SignInManager
@inject UserManager<CustomUser> UserManager

Let us start with a clean database and migration. Therefore, delete app.db and the Data/Migrations folder. 

Then, execute the following commands from within a terminal window in the root folder of the application:

dotnet ef migrations add M1 -o Data/Migrations
dotnet ef database update

At this stage, all the database tables are created and seeded. Let us run our application.


To prove that user and role data are successfully seeded, login with any of the below credentials that were previously seeded:

Email: aa@aa.aa    Password: P@$$w0rd
Email: mm@mm.mm    Password: P@$$w0rd

The next task we need to accomplish is to modify the registration page so that the application can capture extended data such as FirstName & LastName. ASP.NET provides ASP.NET Core Identity as a Razor Class Library. This means that the registration UI is baked into the assemblies and is surfaced with the .AddDefaultUI() option with the services.AddIdentity() command in Program.cs.

We need to add some additional packages so that we can scaffold the view for account registration. From within a terminal window at the root of your application, run the following commands: 

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer


If you do not already have the .NET code-generation (scaffolding) tool, execute the following command from within a terminal window:

dotnet tool install -g dotnet-aspnet-codegenerator

Here are some useful commands pertaining to the code-generation (scaffolding) tool:

Help with the tool dotnet aspnet-codegenerator identity -h
List all the views that can be scaffolded dotnet aspnet-codegenerator identity --listFiles
Scaffold three views dotnet aspnet-codegenerator identity --files "Account.Register;Account.Login;Account.RegisterConfirmation"
Expose all files dotnet aspnet-codegenerator identity

Since we need to modify the registration controller and view, we instruct the scaffolder to surface the code used for registration. To do this, we will scaffold three pages that pertain to account registration and login. Run the following command from within a terminal window:

dotnet aspnet-codegenerator identity --files "Account.Register;Account.Login;Account.RegisterConfirmation" -dc ApplicationDbContext

NOTE: If you encounter an error, temporarily comment out the statement "builder.Seed();" in ApplicationDbContext.cs and try the above command again.

The above command generates a handful of razor view pages under folder Areas/Identity/Pages/Account.


Edit the code-behind file Areas/Identity/Pages/Account/Register.cshtml.cs

Add the following properties to the InputModel class: 

[Required]
[DataType(DataType.Text)]
[StringLength(50, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 2)]
[Display(Name ="First Name")]
public string FirstName { get; set; }

[Required]
[DataType(DataType.Text)]
[StringLength(50, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 2)]
[Display(Name = "Last Name")]
public string LastName { get; set; }

In the same file, edit the code in the OnPostAsync() method so that line:

var user = CreateUser();

is changed to: 

var user = new CustomUser {
  UserName = Input.Email,
  Email = Input.Email,
  FirstName = Input.FirstName,
  LastName = Input.LastName
};

Next, let us update the UI. Edit razor page Areas/Identity/Pages/Account/Register.cshtml. Add the following markup to the form right before the email/username block: 

<div class="form-floating mb-3">
  <input asp-for="Input.FirstName" class="form-control" autocomplete="firstname" aria-required="true" placeholder="First Name"/>
  <label asp-for="Input.FirstName"></label>
  <span asp-validation-for="Input.FirstName" class="text-danger"></span>
</div>
<div class="form-floating mb-3">
  <input asp-for="Input.LastName" class="form-control" autocomplete="lastname" aria-required="true" placeholder="Last Name"/>
  <label asp-for="Input.LastName"></label>
  <span asp-validation-for="Input.LastName" class="text-danger"></span>
</div>

The code generator added some unnecessary code to Program.cs around line 13. Find the following code in Program.cs and comment it out or delete it:

builder.Services.AddDefaultIdentity<CustomUser>(options => options.SignIn.RequireConfirmedAccount = true).AddEntityFrameworkStores<ApplicationDbContext>();

Run the web application and click on the Register button on the top-right side.


When you click on Register, all user data will be saved in the database. 


We have succeeded in updating the registration page so that additional user data is stored. Thanks for coming this far in this tutorial.

Sunday, October 1, 2023

Seed Users and Roles using EF Code First approach in ASP.NET Razor Pages

In this tutorial, I shall describe the steps you need to follow if you want to use Code First migration to seed both users and roles data. The seeding will be done inside the OnModelCreating() method of the Entity Framework DbContext class. To keep things simple, we will use SQLite.

In order to proceed with this tutorial you need to have the following prerequisites:

  • VS Code
  • You have installed .NET 8.0
  • You have installed the dotnet-ef tool

Getting Started

In a terminal window, execute the following command to create an ASP.NET Razor Pages application that supports database authentication using the lightweight SQLite database:

dotnet new razor --auth individual -f net8.0 -o Code1stUsersRoles

Change directory to the newly created folder then run the application:

cd Code1stUsersRoles
dotnet watch

Click on the Register link on the top-right side of your keyboard to add a new user. 


When you click on the Register button, you will receive a page that looks like this:


Click on the link “Click here to confirm your account” to simulate email confirmation. Thereafter, login with the newly created account email and password.

Click on Logout in the top-right corner.

Open the application folder in VS Code.

Create a class named SeedUsersRoles in the Data folder of your application. This will contain seed data for roles, users, and information about users that belong to roles. Below is the code for the SeedUsersRoles class:

public class SeedUsersRoles {
    private readonly List<IdentityRole> _roles;
    private readonly List<IdentityUser> _users;
    private readonly List<IdentityUserRole<string>> _userRoles; 
 
    public SeedUsersRoles() {
      _roles = GetRoles();
      _users = GetUsers();
      _userRoles = GetUserRoles(_users, _roles);
    } 
    public List<IdentityRole> Roles { get { return _roles; } }
    public List<IdentityUser> Users { get { return _users; } }
    public List<IdentityUserRole<string>> UserRoles { get { return _userRoles; } }
    private List<IdentityRole> GetRoles() {
      // Seed Roles
      var adminRole = new IdentityRole("Admin");
      adminRole.NormalizedName = adminRole.Name!.ToUpper();
      var memberRole = new IdentityRole("Member");
      memberRole.NormalizedName = memberRole.Name!.ToUpper();
      List<IdentityRole> roles = new List<IdentityRole>() {
adminRole,
memberRole
      };
      return roles;
    }
    private List<IdentityUser> GetUsers() {
      string pwd = "P@$$w0rd";
      var passwordHasher = new PasswordHasher<IdentityUser>();
      // Seed Users
      var adminUser = new IdentityUser {
        UserName = "aa@aa.aa",
        Email = "aa@aa.aa",
        EmailConfirmed = true,
      };
      adminUser.NormalizedUserName = adminUser.UserName.ToUpper();
      adminUser.NormalizedEmail = adminUser.Email.ToUpper();
      adminUser.PasswordHash = passwordHasher.HashPassword(adminUser, pwd);
      var memberUser = new IdentityUser {
        UserName = "mm@mm.mm",
        Email = "mm@mm.mm",
        EmailConfirmed = true,
      };
      memberUser.NormalizedUserName = memberUser.UserName.ToUpper();
      memberUser.NormalizedEmail = memberUser.Email.ToUpper();
      memberUser.PasswordHash = passwordHasher.HashPassword(memberUser, pwd);
      List<IdentityUser> users = new List<IdentityUser>() {
adminUser,
memberUser,
      };
      return users;
    }
    private List<IdentityUserRole<string>> GetUserRoles(List<IdentityUser> users, List<IdentityRole> roles) {
      // Seed UserRoles
      List<IdentityUserRole<string>> userRoles = new List<IdentityUserRole<string>>();
      userRoles.Add(new IdentityUserRole<string> {
        UserId = users[0].Id,
        RoleId = roles.First(q => q.Name == "Admin").Id
      });
      userRoles.Add(new IdentityUserRole<string> {
        UserId = users[1].Id,
        RoleId = roles.First(q => q.Name == "Member").Id
      });
      return userRoles;
    }
}

Open Data/ApplicationDbContext.cs in your editor. Add the following OnModelCreating() method to the class:

protected override void OnModelCreating(ModelBuilder builder) {
  base.OnModelCreating(builder);
  // Use seed method here
  SeedUsersRoles seedUsersRoles = new();
  builder.Entity<IdentityRole>().HasData(seedUsersRoles.Roles);
  builder.Entity<IdentityUser>().HasData(seedUsersRoles.Users);
  builder.Entity<IdentityUserRole<string>>().HasData(seedUsersRoles.UserRoles);
} 
 
In the Program.cs class, replace the call to builder.Services.AddDefaultIdentity statement so that it registers IdentityRole. Replace the entire builder.Services.AddDefaultIdentity statement with the following code:

builder.Services.AddIdentity<IdentityUser, IdentityRole>(
options => {
    options.Stores.MaxLengthForKeys = 128;
})
.AddEntityFrameworkStores<ApplicationDbContext>()
.AddRoles<IdentityRole>()
.AddDefaultUI()
.AddDefaultTokenProviders();

Delete the Data/Migrations folder and the app.db file because we will add new migrations. Thereafter, type the following from a terminal window inside of the root folder of your application:

dotnet ef migrations add M1 -o Data/Migrations

We will apply migrations and update the database with the following command:

dotnet ef database update

Now start the application. To prove that user and role data are successfully seeded, login with one of the below credentials that were previously seeded:

Email Password Role
aa@aa.aa P@$$w0rd Admin
mm@mm.mm P@$$w0rd Member

Add the following above the IndexModel class in Index.cshtml.cs to only allow users that belong to the Member role:

[Authorize (Roles="Member")]

Also, add the following above the PrivacyModel class in Privacy.cshtml.cs to only allow users that belong to the Admin role:

[Authorize (Roles="Admin")]

Only mm@mm.mm is allowed into the / home page and aa@aa.aa is allowed in the /privacy page.

We have succeeded in seeding user and role. Happy Coding.

Seeding Users and Roles in ASP.NET Razor Pages with RoleManager & UserManager

In this tutorial, I shall describe the steps you need to follow if you want seed both users and roles data. We will also look at how you can go about securing an ASP.NET Razor Pages application by user and by roles. To keep things simple, we will use the SQLite database.

To proceed with this tutorial, you need to have the following prerequisites:

  • VS Code
  • You have installed .NET 8.0

Getting Started

In a terminal window, execute the following command to create an ASP.NET Razor Pages application that supports database authentication using the lightweight SQLite database:

dotnet new razor --auth individual -f net8.0 -o SeedIdentity

Change directory to the newly created folder then run the application:

cd SeedIdentity

dotnet watch

Click on the Register link on the top-right side of your keyboard to add a new user. 




When you click on the Register button, you will receive a page that looks like this:



Click on the link “Click here to confirm your account” to simulate email confirmation. Thereafter, login with the newly created account email and password.


Click on Logout in the top-right corner.

Open the application folder in VS Code.

Let us create some sample data for roles and users. Create class named IdentitySeedData in the Data folder, and then add to it the following method: 

public class IdentitySeedData {
    public static async Task Initialize(ApplicationDbContext context,
        UserManager<IdentityUser> userManager,
        RoleManager<IdentityRole> roleManager) {
        context.Database.EnsureCreated();

        string asdminRole = "Admin";
        string memberRole = "Member";
        string password4all = "P@$$w0rd";

        if (await roleManager.FindByNameAsync(asdminRole) == null) {
            await roleManager.CreateAsync(new IdentityRole(asdminRole));
        }

        if (await roleManager.FindByNameAsync(memberRole) == null) {
            await roleManager.CreateAsync(new IdentityRole(memberRole));
        }

        if (await userManager.FindByNameAsync("aa@aa.aa") == null){
            var user = new IdentityUser {
                UserName = "aa@aa.aa",
                Email = "aa@aa.aa",
                PhoneNumber = "6902341234"
            };

            var result = await userManager.CreateAsync(user);
            if (result.Succeeded) {
                await userManager.AddPasswordAsync(user, password4all);
                await userManager.AddToRoleAsync(user, asdminRole);
            }
        }

        if (await userManager.FindByNameAsync("bb@bb.bb") == null) {
            var user = new IdentityUser {
                UserName = "bb@bb.bb",
                Email = "bb@bb.bb",
                PhoneNumber = "7788951456"
            };

            var result = await userManager.CreateAsync(user);
            if (result.Succeeded) {
                await userManager.AddPasswordAsync(user, password4all);
                await userManager.AddToRoleAsync(user, asdminRole);
            }
        }

        if (await userManager.FindByNameAsync("mm@mm.mm") == null) {
            var user = new IdentityUser {
                UserName = "mm@mm.mm",
                Email = "mm@mm.mm",
                PhoneNumber = "6572136821"
            };

            var result = await userManager.CreateAsync(user);
            if (result.Succeeded) {
                await userManager.AddPasswordAsync(user, password4all);
                await userManager.AddToRoleAsync(user, memberRole);
            }
        }

        if (await userManager.FindByNameAsync("dd@dd.dd") == null) {
            var user = new IdentityUser {
                UserName = "dd@dd.dd",
                Email = "dd@dd.dd",
                PhoneNumber = "6041234567"
            };

            var result = await userManager.CreateAsync(user);
            if (result.Succeeded) {
                await userManager.AddPasswordAsync(user, password4all);
                await userManager.AddToRoleAsync(user, memberRole);
            }
        }
    }
}

In the Program.cs class, replace the call to builder.Services.AddDefaultIdentity statement so that it uses the new IdentityUser & IdentityRole. Replace the entire statement with the following code:

builder.Services.AddIdentity<IdentityUser, IdentityRole>(
options => {
    options.Stores.MaxLengthForKeys = 128;
})
.AddEntityFrameworkStores<ApplicationDbContext>()
.AddRoles<IdentityRole>()
.AddDefaultUI()
.AddDefaultTokenProviders();

We need to call the Initialize() method in the IdentitySeedData class from Program.cs so that when the application starts, the users are seeded. In Program.cs, just before “app.Run();” at the bottom, add this code:

using (var scope = app.Services.CreateScope()) {
    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<ApplicationDbContext>();    
    context.Database.Migrate();

    var userMgr = services.GetRequiredService<UserManager<IdentityUser>>();  
    var roleMgr = services.GetRequiredService<RoleManager<IdentityRole>>();  

    IdentitySeedData.Initialize(context, userMgr, roleMgr).Wait();
}

In the above code the following takes place:
  • Instances of ApplicationDbContext, UserManager<IdentityUser> & RoleManager<IdentityRole> are obtained 
  • If there are any outstanding migrations, they are automatically executed
  • The IdentitySeedData.Initialize() method is called
At this stage, all the database tables are created. However, data is not yet seeded. Let us run our application so that the sample roles and users are seeded in the database. Make sure the application is running. Logout, if you are already logged in.


To prove that user and role data are successfully seeded, login with one of the below credentials that were previously seeded:

Email Password Role
aa@aa.aa P@$$w0rd Admin
mm@mm.mm P@$$w0rd Member

We have succeeded in seeding user and role. Happy Coding.

Monday, September 25, 2023

Code First development with ASP.NET MVC

 In this tutorial, you will develop a data driven web application using ASP.NET MVC, SQL Server, and Entity Framework. We shall use Visual Studio Code for our editor. The data model will be based on a Team/Player relationship in sports. We will use SQL Server running in a Docker Container.

Source code: https://github.com/medhatelmasry/TeamPlayersMvc

Assumptions

It is assumed that you have the following installed on your computer:

  • Visual Studio Code
  • .NET 7.0 SDK
  • Docker Desktop

Visual Studio Code Extension

Add this Visual Studio Code Extension if you do not have it already:

The Data Model

We will use the following class to represent a Team:

public class Team {
    [Key]
    public string? TeamName { get; set; }
    public string? City { get; set; }
    public string? Province { get; set; }
    public string? Country { get; set; }

    public List<Player>? Players { get; set; }
}

The primary key is the TeamName and a team has many players.

The following class represents a Player:

public class Player {
    public int PlayerId { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string?  Position { get; set; }

    public string? TeamName { get; set; }

    [ForeignKey("TeamName")]
    public Team? Team { get; set; }
}

The primary key is PlayerId and each player must belong to a team.

Getting started

In a working directory, run the following command in a terminal window to create a Razor Pages web application in a folder names TeamPlayers:

dotnet new mvc -f net7.0 -o TeamPlayersMvc

Change to the newly created folder with terminal command:

cd TeamPlayersMvc

For the application to work with SQL Server, we will need to add some packages by running the following commands:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design
 
We will use code generation to scaffold MVC controllers & views. For that purpose, you will also need to add this package:

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design

If you have not done so already, you will need to globally install the following tools for Entity Framework and Code Generation respectively:

dotnet tool install -g dotnet-aspnet-codegenerator
dotnet tool install -g dotnet-ef

NOTE: If these tools are already installed, run the above commands while replacing ‘install’ with ‘update’ to get the latest version of the tool.

Creating the model classes

Open your app in Visual Studio Code with this command:

code .

Add to the Models Teamclasses Team & Player mentioned under title “The Data Model” above.

The Context Class

We will need to create a database context class to work with relational databases using Entity Framework. To this end, create a Data folder. Inside the Data folder, create a class named ApplicationDbContext with the following code:
public class ApplicationDbContext : DbContext {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) {}


    public DbSet<Team>? Teams { get; set; }
    public DbSet<Player>? Players { get; set; }
}

Seeding the database with sample data

It is always useful to have some sample data to visualize what the app does. Therefore, we will create a class dedicated to seeding data. In the Data folder, create a static class named SeedData and add to it the following code that contains sample data for teams and players:

public static class SeedData {
    // this is an extension method to the ModelBuilder class
    public static void Seed(this ModelBuilder modelBuilder) {
        modelBuilder.Entity<Team>().HasData(
            GetTeams()
        );
        modelBuilder.Entity<Player>().HasData(
            GetPlayers()
        );
    }
    public static List<Team> GetTeams() {
        List<Team> teams = new List<Team>() {
            new Team() {    // 1
                TeamName="Canucks",
                City="Vancouver",
                Province="BC",
                Country="Canada",
            },
            new Team() {    //2
                TeamName="Sharks",
                City="San Jose",
                Province="CA",
                Country="USA",
            },
            new Team() {    // 3
                TeamName="Oilers",
                City="Edmonton",
                Province="AB",
                Country="Canada",
            },
            new Team() {    // 4
                TeamName="Flames",
                City="Calgary",
                Province="AB",
                Country="Canada",
            },
            new Team() {    // 5
                TeamName="Leafs",
                City="Toronto",
                Province="ON",
                Country="Canada",
            },
            new Team() {    // 6
                TeamName="Ducks",
                City="Anaheim",
                Province="CA",
                Country="USA",
            },
            new Team() {    // 7
                TeamName="Lightening",
                City="Tampa Bay",
                Province="FL",
                Country="USA",
            },
            new Team() {    // 8
                TeamName="Blackhawks",
                City="Chicago",
                Province="IL",
                Country="USA",
            },
        };

        return teams;
    }

    public static List<Player> GetPlayers() {
        List<Player> players = new List<Player>() {
            new Player {
                PlayerId = 1,
                FirstName = "Sven",
                LastName = "Baertschi",
                TeamName = "Canucks",
                Position = "Forward"
            },
            new Player {
                PlayerId = 2,
                FirstName = "Hendrik",
                LastName = "Sedin",
                TeamName = "Canucks",
                Position = "Left Wing"
            },
            new Player {
                PlayerId = 3,
                FirstName = "John",
                LastName = "Rooster",
                TeamName = "Flames",
                Position = "Right Wing"
            },
            new Player {
                PlayerId = 4,
                FirstName = "Bob",
                LastName = "Plumber",
                TeamName = "Oilers",
                Position = "Defense"
            },
        };

        return players;
    }
}

Note that the SeedData class is static because it contains an extension method named Seed() to ModelBuilder.

The Seed() method needs to be called from somewhere. The most appropriate place is the ApplicationDbContext class. Add the following OnModelCreating() method to the ApplicationDbContext class:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    builder.Entity<Player>().Property(m => m.TeamName).IsRequired();

    builder.Entity<Team>().Property(p => p.TeamName).HasMaxLength(30);

    builder.Entity<Team>().ToTable("Team");
    builder.Entity<Player>().ToTable("Player");

    builder.Seed();

In addition to seeding data, the above code ensures the following:
  • TeamName is required
  • The maximum length of TeamName is 30 characters
  • The names of the tables that get created in the database are Team & Player. Otherwise, the names get created as Teams & Players.

The database

You can use any SQL Server database you wish. In my case, so that this app works on Linux, Windows, Mac Intel, and Mac M1, I will run SQL Server in a Docker container. To run SQL Server in a Docker container, run the following command:

docker run --cap-add SYS_PTRACE -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD=SqlPassword! -p 1444:1433 --name azsql -d mcr.microsoft.com/azure-sql-edge

The connection string to the database is setup in the appsettings.json (or appsetting.Development.json) file. Edit this file and make the following highlighted updates to it:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:127.0.0.1,1444;Database=TeamPlayersDB;UID=sa;PWD=SqlPassword!;TrustServerCertificate=True;"
  }
}
 

To make our app work with SQL Server, you will need to add the following code to Program.cs just before “var app = builder.Build();”: 

string connStr = builder.Configuration.GetConnectionString("DefaultConnection")!;
builder.Services.AddDbContext<ApplicationDbContext>(
    options => options.UseSqlServer(connStr)
);

Migrations

We can now create a migration named m1 with:

dotnet ef migrations add M1 -o Data/Migrations

This creates a migrations file in Data/Migrations folder. To execute the migrations and create the database and seed data, run the following command:

dotnet ef database update

If all goes well and no errors are generated, we can assume that a database named TeamPlayersDB was created, and data is seeded into tables Team & Player.

NOTE: If you wish to drop the database for whatever reason, you can run command: dotnet ef database drop
 
This is what the tables in the database look like:

Scaffolding Teams & Players controller and views

To incorporate pages into our app that allow us to manage Team & Player data, we will scaffold the necessary controller & views using the aspnet-codegenerator utility. Run the following command from a terminal window in the root of the project to generate files pertaining to teams and players respectively:

dotnet aspnet-codegenerator controller -name TeamsController -outDir Controllers -m Team -dc ApplicationDbContext -udl -scripts

dotnet aspnet-codegenerator controller -name PlayersController -outDir Controllers -m Player -dc ApplicationDbContext -udl -scripts

This produces controllers in the Controllers folder and fiews files in folders Views/Teams & Views/Players respectively. To add menu items on the home page that point to Team & Player pages, edit Views/Shared/_Layout.cshtml and add the following HTML to the <ul> block around line 28:

<li class="nav-item">
    <a class="nav-link text-dark" asp-area="" asp-controller="Teams" asp-action="Index">Teams</a>
</li>

<li class="nav-item">
    <a class="nav-link text-dark" asp-area="" asp-controller="Players" asp-action="Index">Players</a>
</li>

The final product

Run the web app and notice the main menu:




Click on Teams:


Click on Players:


Conclusion

You just learned how to use the code-first database approach with ASP.NET MVC. The same priciples work with ASP.NET Razor Pages.