Medhat Elmasry: seed

Showing posts with label seed. Show all posts

Wednesday, February 5, 2025

Develop simple REST API with PHP and MySQL

Overview

In this article we will develop is simple REST API with PHP. The database backend is MySQL running in a Docker container and the sample data represents students.

Source code: https://github.com/medhatelmasry/school-api

Getting Started

Start MySQL in a Docker container with:

docker run -d -p 3333:3306 --name maria -e MYSQL_ROOT_PASSWORD=secret mariadb:10.7.3

In a working directory named school-api, create the following sub-folders:

mkdir src
cd src
mkdir Controller
mkdir System
mkdir TableGateways
cd ..
mkdir public

Add a composer.json file in the top directory with just one dependency: the DotEnv library which will allow us to keep our authentication details in a .env file outside our code repository:

Contents of the composer.json file:

{
"require": {
"vlucas/phpdotenv": "^2.4"
},
"autoload": {
"psr-4": {
"Src\\": "src/"
}
}
}

We also configured a PSR-4 autoloader which will automatically look for PHP classes in the /src directory.

We can install our dependencies with:

composer install

We now have a /vendor directory, and the DotEnv dependency is installed (we can also use our autoloader to load our classes from /src with no include() calls).

Let’s create a .gitignore file for our project with two lines in it, so the /vendor directory and our local .env file are ignored if our code is pushed to source control. Add this to .gitignore:

vendor/
.env

Next, add a .env file where we’ll put our database connection details:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3333
DB_DATABASE=apidb
DB_USERNAME=root
DB_PASSWORD=secret

Add a bootstrap.php file to load environment variables.

<?php
error_reporting(E_ALL & ~E_DEPRECATED);

require 'vendor/autoload.php';

use Dotenv\Dotenv;

use Src\System\DatabaseConnector;

$dotenv = new DotEnv(__DIR__);
$dotenv->load();

$dbConnection = (new DatabaseConnector())->getConnection();

Configure DB for PHP REST API

We can now create a class to hold our database connection and add the initialization of the connection to our bootstrap.php file. In the src/System/ folder, add a file named DatabaseConnector.php with this code:

<?php
namespace Src\System;
class DatabaseConnector {
private $dbConnection = null;
public function __construct() {
$host = getenv('DB_HOST');
$port = getenv('DB_PORT');
$db = getenv('DB_DATABASE');
$user = getenv('DB_USERNAME');
$pass = getenv('DB_PASSWORD');
try {
$pdo = new \PDO("mysql:host=$host;port=$port;charset=utf8mb4", $user, $pass);
$pdo->exec("CREATE DATABASE IF NOT EXISTS `$db`");
} catch (\PDOException $e) {
die("DB ERROR: " . $e->getMessage());
}
try {
$this->dbConnection = new \PDO(
"mysql:host=$host;port=$port;charset=utf8mb4;dbname=$db",
$user,
$pass
);
} catch (\PDOException $e) {
exit($e->getMessage());
}
}
public function getConnection() {
return $this->dbConnection;
}
}

Let’s create a dbseed.php file which creates a students table and inserts some records for testing. Code for dbseed.php is shown below:

<?php
require 'bootstrap.php';
$statement = "
CREATE TABLE IF NOT EXISTS students (
id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
school VARCHAR(40) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO students
(firstname, lastname, school)
VALUES
('Mark', 'Fisher','Computing'),
('Lisa', 'Fisher','Computing'),
('Judy', 'Fisher','Computing'),
('Jane', 'Smith','Nursing'),
('Mary', 'Smith','Nursing'),
('Andy', 'Smith','Nursing'),
('Bill', 'Smith','Business'),
('Fred', 'Plumber','Business'),
('Anna', 'Plumber','Business');
";
try {
$createTable = $dbConnection->exec($statement);
echo "Success!\n";
} catch (\PDOException $e) {
exit($e->getMessage());
}

Run the following command to seed the database with sample data:

php dbseed.php

Add a Gateway Class for students table

In the src/TableGateways/StudentsGateway.php class file, we will implement methods to return all students, return a specific student and add/update/delete a student:

<?php
namespace Src\TableGateways;
class StudentsGateway {
private $db = null;
public function __construct($db) {
$this->db = $db;
}
public function findAll() {
$statement = "
SELECT
id, firstname, lastname, school
FROM
students;
";
try {
$statement = $this->db->query($statement);
$result = $statement->fetchAll(\PDO::FETCH_ASSOC);
return $result;
} catch (\PDOException $e) {
exit($e->getMessage());
}
}
public function find($id) {
$statement = "
SELECT
id, firstname, lastname, school
FROM
students
WHERE id = ?;
";
try {
$statement = $this->db->prepare($statement);
$statement->execute(array($id));
$result = $statement->fetchAll(\PDO::FETCH_ASSOC);
return $result;
} catch (\PDOException $e) {
exit($e->getMessage());
}
}
public function insert(Array $input) {
$statement = "
INSERT INTO students
(firstname, lastname, school)
VALUES
(:firstname, :lastname, :school);
";
try {
$statement = $this->db->prepare($statement);
$statement->execute(array(
'firstname' => $input['firstname'],
'lastname' => $input['lastname'],
'school' => $input['school'] ?? null,
));
return $statement->rowCount();
} catch (\PDOException $e) {
exit($e->getMessage());
}
}
public function update($id, Array $input) {
$statement = "
UPDATE students
SET
firstname = :firstname,
lastname = :lastname,
school = :school
WHERE id = :id;
";
try {
$statement = $this->db->prepare($statement);
$statement->execute(array(
'id' => (int) $id,
'firstname' => $input['firstname'],
'lastname' => $input['lastname'],
'school' => $input['school'] ?? null,
));
return $statement->rowCount();
} catch (\PDOException $e) {
exit($e->getMessage());
}
}
public function delete($id) {
$statement = "
DELETE FROM students
WHERE id = :id;
";
try {
$statement = $this->db->prepare($statement);
$statement->execute(array('id' => $id));
return $statement->rowCount();
} catch (\PDOException $e) {
exit($e->getMessage());
}
}
}

Implement the PHP REST API

Our REST API will have the following endpoints:

return all records	GET /students
return a specific record	GET /students/{id}
create a new record	POST /students
update an existing record	PUT /students/{id}
delete an existing record	DELETE /students/{id}

Create a src/Controller/StudentsController.php to handle the API endpoints with this code:

<?php
namespace Src\Controller;
use Src\TableGateways\StudentsGateway;
class StudentsController {
private $db;
private $requestMethod;
private $id;
private $studentsGateway;
public function __construct($db, $requestMethod, $id) {
$this->db = $db;
$this->requestMethod = $requestMethod;
$this->id = $id;
$this->studentsGateway = new StudentsGateway($db);
}
public function processRequest() {
switch ($this->requestMethod) {
case 'GET':
if ($this->id) {
$response = $this->getById($this->id);
} else {
$response = $this->getAll();
};
break;
case 'POST':
$response = $this->createRequest();
break;
case 'PUT':
$response = $this->updateFromRequest($this->id);
break;
case 'DELETE':
$response = $this->deleteById($this->id);
break;
default:
$response = $this->notFoundResponse();
break;
}
header($response['status_code_header']);
if ($response['body']) {
echo $response['body'];
}
}
private function getAll() {
$result = $this->studentsGateway->findAll();
$response['status_code_header'] = 'HTTP/1.1 200 OK';
$response['body'] = json_encode($result);
return $response;
}
private function getById($id) {
$result = $this->studentsGateway->find($id);
if (! $result) {
return $this->notFoundResponse();
}
$response['status_code_header'] = 'HTTP/1.1 200 OK';
$response['body'] = json_encode($result);
return $response;
}
private function createRequest() {
$input = (array) json_decode(file_get_contents('php://input'), TRUE);
if (! $this->validate($input)) {
return $this->unprocessableEntityResponse();
}
$this->studentsGateway->insert($input);
$response['status_code_header'] = 'HTTP/1.1 201 Created';
$response['body'] = null;
return $response;
}
private function updateFromRequest($id) {
$result = $this->studentsGateway->find($id);
if (! $result) {
return $this->notFoundResponse();
}
$input = (array) json_decode(file_get_contents('php://input'), TRUE);
if (! $this->validate($input)) {
return $this->unprocessableEntityResponse();
}
$this->studentsGateway->update($id, $input);
$response['status_code_header'] = 'HTTP/1.1 200 OK';
$response['body'] = null;
return $response;
}
private function deleteById($id) {
$result = $this->studentsGateway->find($id);
if (! $result) {
return $this->notFoundResponse();
}
$this->studentsGateway->delete($id);
$response['status_code_header'] = 'HTTP/1.1 200 OK';
$response['body'] = null;
return $response;
}
private function validate($input) {
if (! isset($input['firstname'])) {
return false;
}
if (! isset($input['lastname'])) {
return false;
}
return true;
}
private function unprocessableEntityResponse() {
$response['status_code_header'] = 'HTTP/1.1 422 Unprocessable Entity';
$response['body'] = json_encode([
'error' => 'Invalid input'
]);
return $response;
}
private function notFoundResponse() {
$response['status_code_header'] = 'HTTP/1.1 404 Not Found';
$response['body'] = null;
return $response;
}
}

Finally, create a /public/index.php file to serve as the front controller to process requests:

<?php
require "../bootstrap.php";

use Src\Controller\StudentsController;

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: OPTIONS,GET,POST,PUT,DELETE");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

$uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);

$uri = explode( '/', $uri );

// all of our endpoints start with /students
// everything else results in a 404 Not Found
if ($uri[1] !== 'students') {
header("HTTP/1.1 404 Not Found");
exit();
}

// the id is, of course, optional and must be a number:
$id = null;
if (isset($uri[2])) {
$id = (int) $uri[2];
}

$requestMethod = $_SERVER["REQUEST_METHOD"];

// pass the request method and user ID to the StudentsController and process the HTTP request:
$controller = new StudentsController($dbConnection, $requestMethod, $id);
$controller->processRequest();

Start the web server with the following command, where switch -t starts the server in the public folder:

php -S 127.0.0.1:8888 -t public

Test your API with postman with these endpoints:

return all records	GET http://localhost:8888/students
return a specific record	GET http://localhost:8888/students/{id}
create a new record	POST http://localhost:8888/students
update an existing record	PUT / http://localhost:8888/students/{id}
delete an existing record	DELETE http://localhost:8888/students/{id}

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

Source Code: https://github.com/medhatelmasry/Code1stUsersRoles

Companion Video: https://www.youtube.com/watch?v=wiRRyQOYMhU

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

Source Code: https://github.com/medhatelmasry/SeedIdentity

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

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:

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

</li>

<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.

Medhat Elmasry

Wednesday, February 5, 2025

Develop simple REST API with PHP and MySQL

Overview

Getting Started

Configure DB for PHP REST API

Add a Gateway Class for students table

Implement the PHP REST API

Sunday, October 1, 2023

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

Getting Started

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

Getting Started

Monday, September 25, 2023

Code First development with ASP.NET MVC

Assumptions

Visual Studio Code Extension

The Data Model

Getting started

Creating the model classes

The Context Class

Seeding the database with sample data

The database

Migrations

Scaffolding Teams & Players controller and views

The final product

Conclusion

About Me

Blog Archive