SQL Select Statement for Time and attendance for a month

Anyone can help with this one please? Our attendance system generates the following data:

Anyone can help with this one please? Our attendance system generates the following data:

Empid    Department  Timestamp                  Read_ID
3221      IT          2017-01-29 11:12:00.000    1
5565      IT          2017-01-29 12:28:06.000    1
5565      IT          2017-01-29 12:28:07.000    1
3221      IT          2017-01-29 13:12:00.000    2
5565      IT          2017-01-29 13:28:06.000    2
3221      IT          2017-01-30 07:42:15.000    1
3221      IT          2017-01-30 16:16:15.000    2
3221      IT          2017-01-31 09:05:00.000    1
3221      IT          2017-01-31 11:05:00.000    2
3221      IT          2017-01-31 13:20:00.000    1
3221      IT          2017-01-31 16:10:00.000    2

Where Read_ID value,
1 = Entry
2 = Exit

I'm looking for SQL query to run on MS SQL 2014 that summarize attendance time for each employee on monthly basis, for instance

Empid   Department  Year    Month   TotalHours
3221 IT 2017 1 15:24
5565 IT 2017 1 02:00


Build a REST API with Express and Sequelize

Build a REST API with Express and Sequelize

In this tutorial all steps for building a REST API with Express and Sequelize.

In this tutorial all steps for building a REST API with Express and Sequelize.

When I started learning Node I made the same mistake as most bootcamp graduates these days. I went straight for NoSQL databases without even considering SQL as an option and I had no idea what I had missed out on until when I was recently exposed to Ruby on Rails and SQL. I learned the difference between the two type of databases and managed to love both of them, equally. Because of my newly found knowledge I decided to write a walkthrough on how to build a REST API using Express and Sequelize.

Let’s start by installing the npm packages that we will use:

npm init
npm install express sequelize sqlite3 body-parser --save
npm install sequelize-cli -g

sequelize init

The command “sequelize init” will create the backbone of our database architecture and will allow us to easily connect to our database to insert/retrieve/delete records. Once that command is completed you will see a couple of extra folders and files in your app and your folder structure should look something like this:

|- express-sequelize
|-- config
|--- config.json
|-- migrations
|-- models
|--- index.js
|-- node_modules
|-- seeders
|-- package.json
|-- package-lock.json

The planned database models and the relationships between them will look something like this:

For this tutorial I will use SQLite3 for the sake of simplicity; in order to use that with Sequelize we have to go into our config/config.json and replace the content of the file with the following:

{
    "development": {
    "dialect": "sqlite",
    "storage": "./database.sqlite3"
  },
    "test": {
    "dialect": "sqlite",
    "storage": ":memory"
  },
    "production": {
    "dialect": "sqlite",
    "storage": "./database.sqlite3"
  }
}

Now that our environment is all set up, it’s time to create the models for our database. Sequelize-CLI allows us to create models on the fly with no effort; we simply have to write the name of the model and the name of the columns in the database that will belong to the current model and the type of data that we will store. Just like this:

sequelize model:create --name Physician --attributes name:string
sequelize model:create --name Patient --attributes name:string
sequelize model:create --name Appointment --attributes physicianId:integer,patientId:integer

After we’re done with this, you’ll see that we have 3 new files inside our *models *folder. All we have to do now is to commit these changes to the database by migrating the changes with the following command:

sequelize db:migrate

This means that our models are created in the database (but, we still have to create the associations between them). We’ll have 2 main models: Physician and Patient. They will be connected by a join table: Appointment. A Physician can have many Patients and vice versa through an Appointment. Let’s start with the Physician. All we have to do is define the association inside the “associate’’ function. The way we do this is on line 11. We select the current model and use the belongsToMany() function that will receive the model we want to connect it to and the optional arguments. In this case we have to include our join table Appointment.

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Physician = sequelize.define(
    "Physician",
    {
      name: DataTypes.STRING
    },
    {}
  );
  Physician.associate = function(models) {
    Physician.belongsToMany(models.Patient, { through: "Appointment", foreignKey: "physicianId" });
  };
  return Physician;
};

We have to do the same with our Patient model:

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Patient = sequelize.define(
    "Patient",
    {
      name: DataTypes.STRING
    },
    {}
  );
  Patient.associate = function(models) {
    Patient.belongsToMany(models.Physician, { through: "Appointment", foreignKey: "patientId" });
  };
  return Patient;
};

And now it’s time to connect these two models with our join model by defining a relationship where Appointment belongs to a Patient and a Physician.

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Appointment = sequelize.define(
    "Appointment",
    {
      physicianId: DataTypes.INTEGER,
      patientId: DataTypes.INTEGER
    },
    {}
  );
  Appointment.associate = function(models) {
    Appointment.belongsTo(models.Physician);
    Appointment.belongsTo(models.Patient);
  };
  return Appointment;
};

This means that our database is ready. It’s time to get started with the Node server. Nothing complicated, just a simple Express server. First of all let’s create a file called server.js and let’s paste this code inside.

const express = require("express");
const bodyparser = require("body-parser");
const port = process.env.PORT || 5000;

const app = express();

app.use(bodyparser.urlencoded({ extended: true }));
app.use(bodyparser.json());

require("./routes/appointmentRoutes")(app);
require("./routes/physicianRoutes")(app);
require("./routes/patientRoutes")(app);

app.listen(port, () => console.log(`Server started on ${port}`));

All that is left is to define the routes where our server will receive requests. For this I will create 3 files inside a folder named “routes”, one file for each model. Each file will have the following routes included:

'GET'    /name
'GET'    /name/:id
'POST'   /name
'PUT'    /name/:id
'DELETE' /name/:id 

In these files we’ll import our models from the database that we’ll use and Sequelize will allow us to call methods on these models that execute specific queries in the database. The content of your files will be almost identical, but with different model names.

This is what “physicianRoutes.js” will look like:

const Physician = require("../models").Physician;
const Patient = require("../models").Patient;

module.exports = function(router) {
  router.get("/physicians", (req, res) => {
    Physician.findAll({
      include: [Patient]
    })
      .then(physicians => {
        res.json(physicians);
      })
      .catch(err => res.json(err));
  });

  router.get("/physicians/:id", (req, res) => {
    Physician.findAll({
      where: { id: req.params.id }
    })
      .then(physician => {
        res.json(physician[0]);
      })
      .catch(err => res.json(err));
  });

  router.post("/physicians", (req, res) => {
    Physician.create({
      name: req.body.name
    })
      .then(res => {
        res.json(res);
      })
      .catch(err => res.json(err));
  });

  router.put("/physicians/:id", (req, res) => {
    Physician.update({ name: req.body.name }, { where: { id: req.params.id } })
      .then(updatedPhysician => {
        res.json(updatedPhysician);
      })
      .catch(err => res.json(err));
  });

  router.delete("/physicians/:id", (req, res) => {
    Physician.destroy({
      where: { id: req.params.id }
    })
      .then(physician => {
        res.json(physician);
      })
      .catch(err => res.json(err));
  });
};

If you compare it to “patientRoutes.js”, the only main difference you will see is the model names.

const Physician = require("../models").Physician;
const Patient = require("../models").Patient;

module.exports = function(router) {
  router.get("/patients", (req, res) => {
    Patient.findAll({
      include: [Physician]
    })
      .then(patients => {
        res.json(patients);
      })
      .catch(err => res.json(err));
  });

  router.get("/patients/:id", (req, res) => {
    Patient.findAll({
      where: { id: req.params.id }
    })
      .then(patient => {
        res.json(patient[0]);
      })
      .catch(err => res.json(err));
  });

  router.post("/patients", (req, res) => {
    Patient.create({
      name: req.body.name
    })
      .then(res => {
        res.json(res);
      })
      .catch(err => res.json(err));
  });

  router.put("/patients/:id", (req, res) => {
    Patient.update({ name: req.body.name }, { where: { id: req.params.id } })
      .then(updatedPatient => {
        res.json(updatedPatient);
      })
      .catch(err => res.json(err));
  });

  router.delete("/patients/:id", (req, res) => {
    Patient.destroy({
      where: { id: req.params.id }
    })
      .then(patient => {
        res.json(patient);
      })
      .catch(err => res.json(err));
  });
};

And “appointmentRoutes.js”:

const Appointment = require("../models").Appointment;
const Physician = require("../models").Physician;
const Patient = require("../models").Patient;

module.exports = function(router) {
  router.get("/appointments", (req, res) => {
    Appointment.findAll({
      include: [Physician, Patient]
    }).then(appointments => {
      res.json(appointments);
    });
  });

  router.get("/appointments/:id", (req, res) => {
    Appointment.findAll({
      where: { id: req.params.id },
      include: [Physician, Patient]
    }).then(appointment => {
      res.json(appointment[0]);
    });
  });

  router.post("/appointments", (req, res) => {
    Appointment.create({
      physicianId: req.body.physicianId,
      patientId: req.body.patientId
    })
      .then(appointments => {
        res.json(appointments);
      })
      .catch(err => res.json(err));
  });

  router.put("/appointments/:id", (req, res) => {
    Appointment.update(
      { physicianId: req.body.physicianId, patientId: req.body.patientId },
      { where: { id: req.params.id } }
    )
      .then(updatedAppointment => {
        res.json(updatedAppointment);
      })
      .catch(err => console.log(err));
  });

  router.delete("/appointments/:id", (req, res) => {
    Appointment.destroy({
      where: { id: req.params.id }
    }).then(appointment => {
      res.json(appointment);
    });
  });
};

These three files will help us to carry out the basic CRUD functionality in our app. Of course we can create more endpoints where we can describe what to do in specific cases or what to return.

This is all for now, if you start up your server and populate your database you can Create, Read, Update and Delete your records.

Building Web App using ASP.NET Web API Angular 7 and SQL Server

Building Web App using ASP.NET Web API Angular 7 and SQL Server

The comprehensive step by step tutorial on building Web Application using ASP.NET Web API, Angular 7 and Microsoft SQL Server

The comprehensive step by step tutorial on building Web Application using ASP.NET Web API, Angular 7 and Microsoft SQL Server. In this tutorial, we will create a RESTful API web service using ASP.NET Core Web API then create a front-end application with Angular 7. For the backend, we use Microsoft SQL Server 2017 Express Edition with the additional database Northwind. This is our first ASP.NET tutorial, so there will be many shortcomings and a lot of discussions.

Table of Contents:

The following tools, frameworks, package, and modules are required for this tutorial:

We assume that you already install all above required tools. Now, we have to check that DotNet SDK already installed and added to the path. Open command prompt then type this command to make sure DotNet installed and runnable via command prompt.

dotnet --version

You will get this result.

2.1.403

We are using DotNet Framework SDK version 2.1.403. That’s mean you’re ready to move to the main steps.

1. Download and Install Northwind Sample Database

To download Northwind sample database for SQL Server 2017, open your browser then go to this link https://northwinddatabase.codeplex.com/. After download, extract to your root folder (for example C:). Next, open Microsoft SQL Server Management Studio. Login to your local Server then right-click Database in the Object Explorer Window then choose Restore Database.

Choose Device then Click ... button. The select device dialog will open then click Add button, browse to the .bak file that previously extracted then click OK button then click again OK button.

Click OK button to Restore the Northwind Database. Now, you should see the Northwind Database in the Database.

2. Create and Configure a new ASP.NET Web API Application

A slightly different of ASP.NET Core application creation, we will use the command prompt. Of course, you can use the more easiest way when using Microsoft Visual Studio. In the command prompt go to your projects folder then type this command.

dotnet  new webapi –o AspNetAngular –n AspNetAngular

You will the output like below in the Command Prompt.

The template "ASP.NET Core Web API" was created successfully.

Processing post-creation actions...
Running 'dotnet restore' on AspNetAngular\AspNetAngular.csproj...
  Restoring packages for C:\Users\DIDIN\Projects\AspNetAngular\AspNetAngular.csproj...
  Generating MSBuild file C:\Users\DIDIN\Projects\AspNetAngular\obj\AspNetAngular.csproj.nuget.g.props.
  Generating MSBuild file C:\Users\DIDIN\Projects\AspNetAngular\obj\AspNetAngular.csproj.nuget.g.targets.
  Restore completed in 5.82 sec for C:\Users\DIDIN\Projects\AspNetAngular\AspNetAngular.csproj.

Restore succeeded.

That’s mean, you can open directly as a folder from Visual Studio Code as well as CSharp Project/Solution from Microsoft Visual Studio. Or you can call this new ASP.NET Core Web API Project by type this command.

cd AspNetAngular
code .

The project will be opened by Visual Studio Code. If you get a notification like below just click Yes button.

Next, open terminal from the menu or press Ctrl+Shift+`. Run these commands to installs all required packages

dotnet add package Microsoft.EntityFrameworkCore.Tools -v 2.1.2
dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 2.1.2
dotnet add package Microsoft.EntityFrameworkCore.SqlServer.Design -v 1.1.6
dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design -v 2.1.5
dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Tools -v 2.0.4
dotnet add package AutoMapper.Extensions.Microsoft.DependencyInjection -v 5.0.1

If you see a notification like below, just click Restore button.

Now, you will have this packages with the right version installed shown in AspNetAngular.csproj file.

<ItemGroup>
&nbsp; <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="6.0.0" />
&nbsp; <PackageReference Include="Microsoft.AspNetCore.App" />
&nbsp; <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.1.2" PrivateAssets="All" />
&nbsp; <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.2" />
&nbsp; <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" Version="1.1.6" />
&nbsp; <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.1.2">
&nbsp; &nbsp; <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets>
&nbsp; &nbsp; <PrivateAssets>all</PrivateAssets>
&nbsp; </PackageReference>
&nbsp; <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.1.5" />
&nbsp; <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.4" />
</ItemGroup>

Next, build the DotNet application to make sure there’s no error in package dependencies.

dotnet build

You must see this output when everything on the right path.

Build succeeded.
&nbsp; &nbsp; 0 Warning(s)
&nbsp; &nbsp; 0 Error(s)

Next, we have to configure connections to Microsoft SQL Server Database. First, open and edit appsettings.json then add this lines before logging JSON object.

"ConnectionStrings": {
&nbsp; "SQLConnection": "Server=.;Database=NORTHWND;Trusted_Connection=True;User Id=sa;Password=q;Integrated Security=false;MultipleActiveResultSets=true"
},

Open and edit Startup.cs then add this line inside ConfigureServices bracket.

services.AddDbContext<NORTHWNDContext>(options => options.UseSqlServer(Configuration.GetConnectionString("SQLConnection")));

Next, add this line to enable CORS after above line.

services.AddCors();

Also, add this line inside Configure bracket.

app.UseCors(x => x.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod());

Don’t forget to import the library or module that added in the Configuration.

3. Generate Models from Microsoft SQL Server Database

We will use the Code Generation Tools package to generate all models that represent all tables in the Northwind Database. Run this command to generate the models and their context.

dotnet ef dbcontext scaffold "Server=.;Database=NORTHWND;Trusted_Connection=True;User Id=sa;Password=q;Integrated Security=false;" Microsoft.EntityFrameworkCore.SqlServer -o Models

You will see the generated Models and Context inside the Models folder.

If there’s a lot of warning or error that comes from IDE linter, just re-open the Visual Studio Code. As you see in the table of contents, we will use only Supplier model for this tutorial. The generated suppliers look like this.

using System;
using System.Collections.Generic;

namespace AspNetAngular.Models
{
&nbsp; &nbsp; public partial class Suppliers
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; public Suppliers()
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Products = new HashSet<Products>();
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; public int SupplierId { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string CompanyName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactTitle { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Address { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string City { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Region { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string PostalCode { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Country { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Phone { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Fax { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string HomePage { get; set; }

&nbsp; &nbsp; &nbsp; &nbsp; public ICollection<Products> Products { get; set; }
&nbsp; &nbsp; }
}

That model declared in the NORTHWNDContext.cs the file inside Models folder.

public virtual DbSet<Suppliers> Suppliers { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
&nbsp; modelBuilder.Entity<Suppliers>(entity =>
&nbsp; {
&nbsp; &nbsp; &nbsp; entity.HasKey(e => e.SupplierId);

&nbsp; &nbsp; &nbsp; entity.HasIndex(e => e.CompanyName)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .HasName("CompanyName");

&nbsp; &nbsp; &nbsp; entity.HasIndex(e => e.PostalCode)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .HasName("PostalCode");

&nbsp; &nbsp; &nbsp; entity.Property(e => e.SupplierId).HasColumnName("SupplierID");

&nbsp; &nbsp; &nbsp; entity.Property(e => e.Address).HasMaxLength(60);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.City).HasMaxLength(15);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.CompanyName)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .IsRequired()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .HasMaxLength(40);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.ContactName).HasMaxLength(30);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.ContactTitle).HasMaxLength(30);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.Country).HasMaxLength(15);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.Fax).HasMaxLength(24);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.HomePage).HasColumnType("ntext");

&nbsp; &nbsp; &nbsp; entity.Property(e => e.Phone).HasMaxLength(24);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.PostalCode).HasMaxLength(10);

&nbsp; &nbsp; &nbsp; entity.Property(e => e.Region).HasMaxLength(15);
&nbsp; });
}

4. Create DTO (Data Transfer Object) for Request Body and Response

To specify request body and response fields, we will use a Data Transfer Object (DTO). For that, create a new DTOs folder and AddSupplierDto.cs, EditSupplierDto.cs, SupplierResponse.cs files inside that folder. Next, open and edit AddSupplierDto.cs then Replace all codes with this.

using System;
using System.ComponentModel.DataAnnotations;

namespace AspNetAngular.Dtos
{
&nbsp; &nbsp; public class AddSupplierDto
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; [Required]
&nbsp; &nbsp; &nbsp; &nbsp; public string CompanyName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactTitle { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Address { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string City { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Region { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string PostalCode { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Country { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Phone { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Fax { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string HomePage { get; set; }
&nbsp; &nbsp; }
}

Next, open and edit EditSupplierDto.cs then Replace all codes with this.

using System;
using System.ComponentModel.DataAnnotations;

namespace AspNetAngular.Dtos
{
&nbsp; &nbsp; public class EditSupplierDto
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; [Required]
&nbsp; &nbsp; &nbsp; &nbsp; public int SupplierId { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; [Required]
&nbsp; &nbsp; &nbsp; &nbsp; public string CompanyName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactTitle { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Address { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string City { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Region { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string PostalCode { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Country { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Phone { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Fax { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string HomePage { get; set; }
&nbsp; &nbsp; }
}

Next, open and edit SupplierResponseDto.cs then Replace all codes with this.

namespace AspNetAngular.Dtos
{
&nbsp; &nbsp; public class SupplierResponseDto
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; public int SupplierId { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string CompanyName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactName { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string ContactTitle { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string Address { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string City { get; set; }
&nbsp; &nbsp; &nbsp; &nbsp; public string PostalCode { get; set; }

&nbsp; &nbsp; }
}

5. Create Helpers Class for Mapping DTO with Model Classes

To create a helper for mapping DTO to Model classes, create the folder Helpers in the root of project folder then create a file AutoMapperProfile.cs inside that new folder. Open and edit this new file then replace all codes with this.

using AspNetAngular.Dtos;
using AspNetAngular.Models;
using AutoMapper;

namespace AspNetAngular.Helpers
{
&nbsp; &nbsp; public class AutoMapperProfiles: Profile
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; public AutoMapperProfiles()
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CreateMap<AddSupplierDto, Suppliers>();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CreateMap<EditSupplierDto, Suppliers>();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CreateMap<Suppliers, SupplierResponseDto>();
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; }
}

Next, open and edit again Startup.cs then add this line inside ConfigureServices.

services.AddAutoMapper();

Don’t forget to auto import the library in ConfigureServices.

6. Create Repositories for CRUD (Create, Read, Update Delete) Operations

To create a repository class and interface, we have to create a folder Repositories in the root of the project folder. Next, create an interface file inside that folder with the name IDataRepository.cs then replace all codes with this.

using System.Threading.Tasks;

namespace AspNetAngular.Repositories
{
&nbsp; &nbsp; public interface IDataRepository<T> where T : class
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; void Add(T entity);
&nbsp; &nbsp; &nbsp; &nbsp; void Update(T entity);
&nbsp; &nbsp; &nbsp; &nbsp; void Delete(T entity);
&nbsp; &nbsp; &nbsp; &nbsp; Task<T> SaveAsync(T entity);
&nbsp; &nbsp; }
}

Next, create a file inside that folder with the name DataRepository.cs then replace all codes with this.

using System.Threading.Tasks;
using AspNetAngular.Models;

namespace AspNetAngular.Repositories
{
&nbsp; &nbsp; public class DataRepository<T> : IDataRepository<T> where T : class
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; private readonly NORTHWNDContext _context;

&nbsp; &nbsp; &nbsp; &nbsp; public DataRepository(NORTHWNDContext context)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context = context;
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; public void Add(T entity)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context.Set<T>().Add(entity);
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; public void Update(T entity)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context.Set<T>().Update(entity);
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; public void Delete(T entity)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context.Set<T>().Remove(entity);
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; public async Task<T> SaveAsync(T entity)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; await _context.SaveChangesAsync();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return entity;
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; }
}

Next, open and edit again Startup.cs then add this line inside ConfigureServices bracket.

services.AddScoped(typeof(IDataRepository < > ), typeof(DataRepository < > ));

Also, import the required classes and libraries.

7. Create Controller for CRUD Operations

Now, we will show you how the API available via Controller. We will generate a controller for Supplier model. Before generate, we have to install the tools for it. Run this command in the terminal of Visual Studio Code.

dotnet tool install --global dotnet-aspnet-codegenerator --version 2.2.1

Just run this command to generate it.

dotnet aspnet-codegenerator controller -name SupplierController -api -async -m AspNetAngular.Models.Suppliers -dc NORTHWNDContext -namespace AspNetAngular.Controllers -outDir Controllers

Now, we have a Supplier’s controller that looks like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using AspNetAngular.Models;

namespace AspNetAngular.Controllers
{
&nbsp; &nbsp; [Route("api/[controller]")]
&nbsp; &nbsp; [ApiController]
&nbsp; &nbsp; public class SupplierController : ControllerBase
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; private readonly NORTHWNDContext _context;

&nbsp; &nbsp; &nbsp; &nbsp; public SupplierController(NORTHWNDContext context)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context = context;
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; // GET: api/Supplier
&nbsp; &nbsp; &nbsp; &nbsp; [HttpGet]
&nbsp; &nbsp; &nbsp; &nbsp; public IEnumerable<Suppliers> GetSuppliers()
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return _context.Suppliers;
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; // GET: api/Supplier/5
&nbsp; &nbsp; &nbsp; &nbsp; [HttpGet("{id}")]
&nbsp; &nbsp; &nbsp; &nbsp; public async Task<IActionResult> GetSuppliers([FromRoute] int id)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var suppliers = await _context.Suppliers.FindAsync(id);

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (suppliers == null)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return NotFound();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return Ok(suppliers);
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; // PUT: api/Supplier/5
&nbsp; &nbsp; &nbsp; &nbsp; [HttpPut("{id}")]
&nbsp; &nbsp; &nbsp; &nbsp; public async Task<IActionResult> PutSuppliers([FromRoute] int id, [FromBody] Suppliers suppliers)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (id != suppliers.SupplierId)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return BadRequest();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context.Entry(suppliers).State = EntityState.Modified;

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; await _context.SaveChangesAsync();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; catch (DbUpdateConcurrencyException)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!SuppliersExists(id))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return NotFound();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return NoContent();
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; // POST: api/Supplier
&nbsp; &nbsp; &nbsp; &nbsp; [HttpPost]
&nbsp; &nbsp; &nbsp; &nbsp; public async Task<IActionResult> PostSuppliers([FromBody] Suppliers suppliers)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context.Suppliers.Add(suppliers);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; await _context.SaveChangesAsync();

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return CreatedAtAction("GetSuppliers", new { id = suppliers.SupplierId }, suppliers);
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; // DELETE: api/Supplier/5
&nbsp; &nbsp; &nbsp; &nbsp; [HttpDelete("{id}")]
&nbsp; &nbsp; &nbsp; &nbsp; public async Task<IActionResult> DeleteSuppliers([FromRoute] int id)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var suppliers = await _context.Suppliers.FindAsync(id);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (suppliers == null)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return NotFound();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _context.Suppliers.Remove(suppliers);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; await _context.SaveChangesAsync();

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return Ok(suppliers);
&nbsp; &nbsp; &nbsp; &nbsp; }

&nbsp; &nbsp; &nbsp; &nbsp; private bool SuppliersExists(int id)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return _context.Suppliers.Any(e => e.SupplierId == id);
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; }
}

As you see that generated Controllers using original fields from the Suppliers Model. Next, we will use our previous created DTO for custom Object save, edit and their response. Open and edit Controllers/SupplierController.cs then add this variable after context variable.

private readonly IMapper _mapper;
private readonly IDataRepository<Suppliers> _repo;

Also, inject to the SupplierController constructor.

public SupplierController(NORTHWNDContext context, IMapper mapper, IDataRepository<Suppliers> repo)
{
&nbsp; &nbsp; _context = context;
&nbsp; &nbsp; _mapper = mapper;
&nbsp; &nbsp; _repo = repo;
}

Don’t forget to import all required libraries or classes. Next, replace the HTTPPost method with this codes.

// POST: api/Supplier
[HttpPost]
public async Task<IActionResult> PostSuppliers([FromBody] AddSupplierDto addSupplierDto)
{
&nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; }

&nbsp; &nbsp; var preSupplier = _mapper.Map<Suppliers>(addSupplierDto);
&nbsp; &nbsp; _repo.Add(preSupplier);
&nbsp; &nbsp; var saveSupplier = await _repo.SaveAsync(preSupplier);
&nbsp; &nbsp; var supplierResponse = _mapper.Map<SupplierResponseDto>(saveSupplier);

&nbsp; &nbsp; return StatusCode(201, new { supplierResponse });
}

Next, replace the HTTPPut method with this codes.

// PUT: api/Supplier/5
[HttpPut("{id}")]
public async Task<IActionResult> PutSuppliers([FromRoute] int id, [FromBody] EditSupplierDto editSupplierDto)
{
&nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; }

&nbsp; &nbsp; if (id != editSupplierDto.SupplierId)
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; return BadRequest();
&nbsp; &nbsp; }

&nbsp; &nbsp; var preSupplier = _mapper.Map<Suppliers>(editSupplierDto);
&nbsp; &nbsp; _repo.Update(preSupplier);
&nbsp; &nbsp; await _repo.SaveAsync(preSupplier);

&nbsp; &nbsp; return NoContent();
}

Finally, replace the HTTPDelete method with this because we will delete manually busing SQL Query the related table.

// DELETE: api/Supplier/5
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteSuppliers([FromRoute] int id)
{
&nbsp; &nbsp; if (!ModelState.IsValid)
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; return BadRequest(ModelState);
&nbsp; &nbsp; }

&nbsp; &nbsp; var suppliers = await _context.Suppliers.FindAsync(id);

&nbsp; &nbsp; if (suppliers == null)
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; return NotFound();
&nbsp; &nbsp; }

&nbsp; &nbsp; _context.Database.ExecuteSqlCommand("DELETE FROM [Order Details] WHERE ProductID IN (SELECT ProductID FROM Products WHERE SupplierID = @supplierId)",
&nbsp; &nbsp; &nbsp; &nbsp; new SqlParameter("@supplierId", suppliers.SupplierId));

&nbsp; &nbsp; _context.Database.ExecuteSqlCommand("DELETE FROM Products WHERE SupplierID = @supplierId",
&nbsp; &nbsp; &nbsp; &nbsp; new SqlParameter("@supplierId", suppliers.SupplierId));

&nbsp; &nbsp; _context.Suppliers.Remove(suppliers);
&nbsp; &nbsp; await _context.SaveChangesAsync();

&nbsp; &nbsp; return Ok(suppliers);
}

8. Test API using Postman

Now, we have to run the ASP.NET Core Web API from the Terminal by typing this command.

dotnet watch run

Watch keyword is the additional command for monitoring any change in the codes then reloading the ASP.NET Core Web API application. Next, open or run the Postman application. Use the GET method and fill the right column after the GET method with localhost:5000/api/Supplier, Headers key with Content-Type, Headers value with application/json.

Now, click the Send button and you should see this result for successful GET Supplier.

[
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; "supplierId": 1,
&nbsp; &nbsp; &nbsp; &nbsp; "companyName": "Exotic Liquids",
&nbsp; &nbsp; &nbsp; &nbsp; "contactName": "Charlotte Cooper",
&nbsp; &nbsp; &nbsp; &nbsp; "contactTitle": "Purchasing Manager",
&nbsp; &nbsp; &nbsp; &nbsp; "address": "49 Gilbert St.",
&nbsp; &nbsp; &nbsp; &nbsp; "city": "London",
&nbsp; &nbsp; &nbsp; &nbsp; "region": null,
&nbsp; &nbsp; &nbsp; &nbsp; "postalCode": "EC1 4SD",
&nbsp; &nbsp; &nbsp; &nbsp; "country": "UK",
&nbsp; &nbsp; &nbsp; &nbsp; "phone": "(171) 555-2222",
&nbsp; &nbsp; &nbsp; &nbsp; "fax": null,
&nbsp; &nbsp; &nbsp; &nbsp; "homePage": null,
&nbsp; &nbsp; &nbsp; &nbsp; "products": []
&nbsp; &nbsp; },
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; "supplierId": 2,
&nbsp; &nbsp; &nbsp; &nbsp; "companyName": "New Orleans Cajun Delights",
&nbsp; &nbsp; &nbsp; &nbsp; "contactName": "Shelley Burke",
&nbsp; &nbsp; &nbsp; &nbsp; "contactTitle": "Order Administrator",
&nbsp; &nbsp; &nbsp; &nbsp; "address": "P.O. Box 78934",
&nbsp; &nbsp; &nbsp; &nbsp; "city": "New Orleans",
&nbsp; &nbsp; &nbsp; &nbsp; "region": "LA",
&nbsp; &nbsp; &nbsp; &nbsp; "postalCode": "70117",
&nbsp; &nbsp; &nbsp; &nbsp; "country": "USA",
&nbsp; &nbsp; &nbsp; &nbsp; "phone": "(100) 555-4822",
&nbsp; &nbsp; &nbsp; &nbsp; "fax": null,
&nbsp; &nbsp; &nbsp; &nbsp; "homePage": "#CAJUN.HTM#",
&nbsp; &nbsp; &nbsp; &nbsp; "products": []
&nbsp; &nbsp; },
&nbsp; &nbsp; ...
]

To get single supplier by supplier ID, just change the URL to this localhost:5000/api/Supplier/2. If found, you will see the result of a single supplier. Next, for POST a Supplier changes the method to POST, leave URL same as the GET supplier list then fill the body with a raw JSON object.

{
&nbsp; "CompanyName": "Djamware Inc.",
&nbsp; "ContactName": "Didin J.",
&nbsp; "ContactTitle": "CEO",
&nbsp; "Address": "Whereever Road 123",
&nbsp; "City": "Bandung",
&nbsp; "Region": "JBR",
&nbsp; "PostalCode": "12345",
&nbsp; "Country": "Indonesia",
&nbsp; "Phone": "(022) 123-4567890",
&nbsp; "Fax": "(022) 123-4567890",
&nbsp; "HomePage": "https://www.djamware.com"
}

You will see this result with status 201.

{
&nbsp; &nbsp; "supplierResponse": {
&nbsp; &nbsp; &nbsp; &nbsp; "companyName": "Djamware Inc.",
&nbsp; &nbsp; &nbsp; &nbsp; "contactName": "Didin J.",
&nbsp; &nbsp; &nbsp; &nbsp; "contactTitle": "CEO",
&nbsp; &nbsp; &nbsp; &nbsp; "address": "Whereever Road 123",
&nbsp; &nbsp; &nbsp; &nbsp; "city": "Bandung",
&nbsp; &nbsp; &nbsp; &nbsp; "region": "JBR",
&nbsp; &nbsp; &nbsp; &nbsp; "postalCode": "12345",
&nbsp; &nbsp; &nbsp; &nbsp; "country": "Indonesia",
&nbsp; &nbsp; &nbsp; &nbsp; "phone": "(022) 123-4567890",
&nbsp; &nbsp; &nbsp; &nbsp; "fax": "(022) 123-4567890",
&nbsp; &nbsp; &nbsp; &nbsp; "homePage": "https://www.djamware.com"
&nbsp; &nbsp; }
}

To update the Supplier data, change the method to PUT and URL to localhost:5000/api/Supplier/30 then add a supplier id field to the raw body.

{
&nbsp; "SupplierId": 30,
&nbsp; "CompanyName": "Djamware.com",
&nbsp; "ContactName": "Didin J.",
&nbsp; "ContactTitle": "Engineer",
&nbsp; "Address": "Whereever Road 123",
&nbsp; "City": "Garut",
&nbsp; "Region": "JBR",
&nbsp; "PostalCode": "12345",
&nbsp; "Country": "Indonesia",
&nbsp; "Phone": "(022) 123-4567890",
&nbsp; "Fax": "(022) 123-4567890",
&nbsp; "HomePage": "https://www.djamware.com"
}

The response should be 204 (No Content) for a successful request. Next, for delete a supplier, simply change the method to DELETE, keep the URL as the previous method and change the body to none. You should see the 200 response and data that deleted in the response body.

9. Install or Update Angular 7 CLI and Create Application

Before installing the Angular 7 CLI, make sure you have installed Node.js https://nodejs.org and can open Node.js command prompt. Next, open the Node.js command prompt then type this command to install Angular 7 CLI.

npm install -g @angular/cli

Next, create an Angular 7 application by typing this command in the root of ASP.NET Core application/project directory.

ng new client

Where client is the name of the Angular 7 application. You can specify your own name, we like to name it client because it’s put inside ASP.NET Core Project directory. If there’s a question, we fill them with Y and SASS. Next, go to the newly created Angular 7 application.

cd client

Run the Angular 7 application for the first time.

ng serve

Now, go to localhost:4200 and you should see this page.

To stop the Angular 7 application, just press CTRL+C keys.

10. Add Routes for Navigation between Angular 7 Pages/Component

On the previous steps, we have to added Angular 7 Routes when answering the questions. Now, we just added the required pages for CRUD (Create, Read, Update, Delete) Supplier data. Type this commands to add the Angular 7 components or pages.

ng g component supplier
ng g component supplier-detail
ng g component supplier-add
ng g component supplier-edit

Open src/app/app.module.ts then you will see those components imported and declared in @NgModule declarations. Next, open and edit src/app/app-routing.module.ts then add this imports.

import { SupplierComponent } from './supplier/supplier.component';
import { SupplierDetailComponent } from './supplier-detail/supplier-detail.component';
import { SupplierAddComponent } from './supplier-add/supplier-add.component';
import { SupplierEditComponent } from './supplier-edit/supplier-edit.component';

Add these arrays to the existing routes constant.

const routes: Routes = [
&nbsp; {
&nbsp; &nbsp; path: 'supplier',
&nbsp; &nbsp; component: SupplierComponent,
&nbsp; &nbsp; data: { title: 'List of Suppliers' }
&nbsp; },
&nbsp; {
&nbsp; &nbsp; path: 'supplier-details/:id',
&nbsp; &nbsp; component: SupplierDetailComponent,
&nbsp; &nbsp; data: { title: 'Supplier Details' }
&nbsp; },
&nbsp; {
&nbsp; &nbsp; path: 'supplier-add',
&nbsp; &nbsp; component: SupplierAddComponent,
&nbsp; &nbsp; data: { title: 'Add Supplier' }
&nbsp; },
&nbsp; {
&nbsp; &nbsp; path: 'supplier-edit/:id',
&nbsp; &nbsp; component: SupplierEditComponent,
&nbsp; &nbsp; data: { title: 'Edit Supplier' }
&nbsp; },
&nbsp; { path: '',
&nbsp; &nbsp; redirectTo: '/supplier',
&nbsp; &nbsp; pathMatch: 'full'
&nbsp; }
];

Open and edit src/app/app.component.html and you will see existing router outlet. Next, modify this HTML page to fit the CRUD page.

<div style="text-align:center">
&nbsp; <h1>
&nbsp; &nbsp; Welcome to {{ title }}!
&nbsp; </h1>
&nbsp; <img width="300" alt="Angular Logo" src="data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHZpZXdCb3g9IjAgMCAyNTAgMjUwIj4KICAgIDxwYXRoIGZpbGw9IiNERDAwMzEiIGQ9Ik0xMjUgMzBMMzEuOSA2My4ybDE0LjIgMTIzLjFMMTI1IDIzMGw3OC45LTQzLjcgMTQuMi0xMjMuMXoiIC8+CiAgICA8cGF0aCBmaWxsPSIjQzMwMDJGIiBkPSJNMTI1IDMwdjIyLjItLjFWMjMwbDc4LjktNDMuNyAxNC4yLTEyMy4xTDEyNSAzMHoiIC8+CiAgICA8cGF0aCAgZmlsbD0iI0ZGRkZGRiIgZD0iTTEyNSA1Mi4xTDY2LjggMTgyLjZoMjEuN2wxMS43LTI5LjJoNDkuNGwxMS43IDI5LjJIMTgzTDEyNSA1Mi4xem0xNyA4My4zaC0zNGwxNy00MC45IDE3IDQwLjl6IiAvPgogIDwvc3ZnPg==">
</div>

<div class="container">
&nbsp; <router-outlet></router-outlet>
</div>

Open and edit src/app/app.component.scss then replace all SCSS codes with this.

.container {
&nbsp; padding: 20px;
}

11. Create Service for Accessing RESTful API

To access ASP.NET Core Web API from Angular 7 application, we have to create an Angular 7 Service first. Type this command to create it.

ng g service api

We have to register the HttpClient module before using it in the Service. Open and edit src/app/app.module.ts then add this import.

import { FormsModule } from '@angular/forms';
import { HttpClientModule } from '@angular/common/http';

Add them to the `@NgModule` imports array.

imports: [
&nbsp; BrowserModule,
&nbsp; FormsModule,
&nbsp; HttpClientModule,
&nbsp; AppRoutingModule
],

To specify the type of response object from the ASP.NET Core Web API, we have to create a class for it. Create a new file src/app/supplier.ts then add this codes.

export class Supplier {
&nbsp; &nbsp; supplierId: number;
&nbsp; &nbsp; companyName: string;
&nbsp; &nbsp; contactName: string;
&nbsp; &nbsp; contactTitle: string;
&nbsp; &nbsp; address: string;
&nbsp; &nbsp; city: string;
&nbsp; &nbsp; region: string;
&nbsp; &nbsp; postalCode: string;
&nbsp; &nbsp; country: string;
&nbsp; &nbsp; phone: string;
&nbsp; &nbsp; fax: string;
&nbsp; &nbsp; homePage: string;
}

Next, open and edit src/app/api.service.ts then add this imports.

import { Observable, of, throwError } from 'rxjs';
import { HttpClient, HttpHeaders, HttpErrorResponse } from '@angular/common/http';
import { catchError, tap, map } from 'rxjs/operators';
import { Supplier } from './supplier';

Add these constants before the @Injectable.

const httpOptions = {
&nbsp; headers: new HttpHeaders({'Content-Type': 'application/json'})
};
const apiUrl = 'http://localhost:5000/api/';

Inject HttpClient module to the constructor.

constructor(private http: HttpClient) { }

Add the error handler function.

private handleError<T> (operation = 'operation', result?: T) {
&nbsp; return (error: any): Observable<T> => {

&nbsp; &nbsp; // TODO: send the error to remote logging infrastructure
&nbsp; &nbsp; console.error(error); // log to console instead

&nbsp; &nbsp; // Let the app keep running by returning an empty result.
&nbsp; &nbsp; return of(result as T);
&nbsp; };
}

Add all CRUD (create, read, update, delete) functions of suppliers data.

getSuppliers (): Observable<Supplier[]> {
&nbsp; return this.http.get<Supplier[]>(apiUrl)
&nbsp; &nbsp; .pipe(
&nbsp; &nbsp; &nbsp; tap(heroes => console.log('fetched Suppliers')),
&nbsp; &nbsp; &nbsp; catchError(this.handleError('getSuppliers', []))
&nbsp; &nbsp; );
}

getSupplier(id: number): Observable<Supplier> {
&nbsp; const url = `${apiUrl}/${id}`;
&nbsp; return this.http.get<Supplier>(url).pipe(
&nbsp; &nbsp; tap(_ => console.log(`fetched Supplier id=${id}`)),
&nbsp; &nbsp; catchError(this.handleError<Supplier>(`getSupplier id=${id}`))
&nbsp; );
}

addSupplier (supplier: any): Observable<Supplier> {
&nbsp; return this.http.post<Supplier>(apiUrl, supplier, httpOptions).pipe(
&nbsp; &nbsp; tap((supplierRes: Supplier) => console.log(`added Supplier w/ id=${supplierRes.supplierId}`)),
&nbsp; &nbsp; catchError(this.handleError<Supplier>('addSupplier'))
&nbsp; );
}

updateSupplier (id: number, supplier: any): Observable<any> {
&nbsp; const url = `${apiUrl}/${id}`;
&nbsp; return this.http.put(url, supplier, httpOptions).pipe(
&nbsp; &nbsp; tap(_ => console.log(`updated Supplier id=${id}`)),
&nbsp; &nbsp; catchError(this.handleError<any>('updateSupplier'))
&nbsp; );
}

deleteSupplier (id: number): Observable<Supplier> {
&nbsp; const url = `${apiUrl}/${id}`;
&nbsp; return this.http.delete<Supplier>(url, httpOptions).pipe(
&nbsp; &nbsp; tap(_ => console.log(`deleted Supplier id=${id}`)),
&nbsp; &nbsp; catchError(this.handleError<Supplier>('deleteSupplier'))
&nbsp; );
}

12. Display List of Suppliers using Angular 7 Material

To display a list of suppliers to the Angular 7 template. First, open and edit src/app/supplier/supplier.component.ts then add this imports.

import { ApiService } from '../api.service';

Next, inject the API Service to the constructor.

constructor(private api: ApiService) { }

Next, for user interface (UI) we will use Angular 7 Material and CDK. There’s a CLI for generating a Material component like Table as a component, but we will create or add the Table component from scratch to existing component. Type this command to install Angular 7 Material.

ng add @angular/material

If there are some questions, answer them like below.

? Choose a prebuilt theme name, or "custom" for a custom theme: Purple/Green &nbsp; &nbsp; &nbsp; [ Preview: https://material.angular.i
o?theme=purple-green ]
? Set up HammerJS for gesture recognition? Yes
? Set up browser animations for Angular Material? Yes

Next, we have to register all required Angular Material components or modules to src/app/app.module.ts. Open and edit that file then add this imports.

import {
&nbsp; MatInputModule,
&nbsp; MatPaginatorModule,
&nbsp; MatProgressSpinnerModule,
&nbsp; MatSortModule,
&nbsp; MatTableModule,
&nbsp; MatIconModule,
&nbsp; MatButtonModule,
&nbsp; MatCardModule,
&nbsp; MatFormFieldModule } from '@angular/material';

Also, modify FormsModule import to add ReactiveFormsModule.

import { FormsModule, ReactiveFormsModule } from '@angular/forms';

Register the above modules to @NgModule imports.

imports: [
&nbsp; BrowserModule,
&nbsp; FormsModule,
&nbsp; HttpClientModule,
&nbsp; AppRoutingModule,
&nbsp; ReactiveFormsModule,
&nbsp; BrowserAnimationsModule,
&nbsp; MatInputModule,
&nbsp; MatTableModule,
&nbsp; MatPaginatorModule,
&nbsp; MatSortModule,
&nbsp; MatProgressSpinnerModule,
&nbsp; MatIconModule,
&nbsp; MatButtonModule,
&nbsp; MatCardModule,
&nbsp; MatFormFieldModule
],

Next, back to src/app/supplier/supplier.component.ts then add this imports.

import { Supplier } from '../supplier';

Declare the variables of Angular Material Table Data Source before the constructor.

displayedColumns: string[] = ['supplierId', 'companyName', 'contactName'];
data: Supplier[] = [];
isLoadingResults = true;

Modify the ngOnInit function to get list of suppliers immediately.

ngOnInit() {
&nbsp; this.api.getSuppliers()
&nbsp; .subscribe(res => {
&nbsp; &nbsp; this.data = res;
&nbsp; &nbsp; console.log(this.data);
&nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; }, err => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; });
}

Next, open and edit src/app/supplier/supplier.component.html then replace all HTML tags with this Angular 7 Material tags.

<div class="example-container mat-elevation-z8">
&nbsp; <div class="example-loading-shade"
&nbsp; &nbsp; &nbsp; &nbsp;*ngIf="isLoadingResults">
&nbsp; &nbsp; <mat-spinner *ngIf="isLoadingResults"></mat-spinner>
&nbsp; </div>
&nbsp; <div class="button-row">
&nbsp; &nbsp; <a mat-flat-button color="primary" [routerLink]="['/supplier-add']"><mat-icon>add</mat-icon></a>
&nbsp; </div>
&nbsp; <div class="mat-elevation-z8">
&nbsp; &nbsp; <table mat-table [dataSource]="data" class="example-table"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;matSort matSortActive="CompanyName" matSortDisableClear matSortDirection="asc">

&nbsp; &nbsp; &nbsp; <!-- Supplier ID Column -->
&nbsp; &nbsp; &nbsp; <ng-container matColumnDef="supplierId">
&nbsp; &nbsp; &nbsp; &nbsp; <th mat-header-cell *matHeaderCellDef>Supplier ID</th>
&nbsp; &nbsp; &nbsp; &nbsp; <td mat-cell *matCellDef="let row">{{row.supplierId}}</td>
&nbsp; &nbsp; &nbsp; </ng-container>

&nbsp; &nbsp; &nbsp; <!-- Company Name Column -->
&nbsp; &nbsp; &nbsp; <ng-container matColumnDef="companyName">
&nbsp; &nbsp; &nbsp; &nbsp; <th mat-header-cell *matHeaderCellDef>Company Name</th>
&nbsp; &nbsp; &nbsp; &nbsp; <td mat-cell *matCellDef="let row">{{row.companyName}}</td>
&nbsp; &nbsp; &nbsp; </ng-container>

&nbsp; &nbsp; &nbsp; <!-- Contact Name Column -->
&nbsp; &nbsp; &nbsp; <ng-container matColumnDef="contactName">
&nbsp; &nbsp; &nbsp; &nbsp; <th mat-header-cell *matHeaderCellDef>Contact Name</th>
&nbsp; &nbsp; &nbsp; &nbsp; <td mat-cell *matCellDef="let row">{{row.contactName}}</td>
&nbsp; &nbsp; &nbsp; </ng-container>

&nbsp; &nbsp; &nbsp; <tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
&nbsp; &nbsp; &nbsp; <tr mat-row *matRowDef="let row; columns: displayedColumns;" [routerLink]="['/supplier-details/', row.supplierId]"></tr>
&nbsp; &nbsp; </table>
&nbsp; </div>
</div>

Finally, to make a little UI adjustment, open and edit src/app/supplier/supplier.component.css then add this CSS codes.

/* Structure */
.example-container {
&nbsp; &nbsp; position: relative;
&nbsp; &nbsp; padding: 5px;
&nbsp; }

&nbsp; .example-table-container {
&nbsp; &nbsp; position: relative;
&nbsp; &nbsp; max-height: 400px;
&nbsp; &nbsp; overflow: auto;
&nbsp; }

&nbsp; table {
&nbsp; &nbsp; width: 100%;
&nbsp; }

&nbsp; .example-loading-shade {
&nbsp; &nbsp; position: absolute;
&nbsp; &nbsp; top: 0;
&nbsp; &nbsp; left: 0;
&nbsp; &nbsp; bottom: 56px;
&nbsp; &nbsp; right: 0;
&nbsp; &nbsp; background: rgba(0, 0, 0, 0.15);
&nbsp; &nbsp; z-index: 1;
&nbsp; &nbsp; display: flex;
&nbsp; &nbsp; align-items: center;
&nbsp; &nbsp; justify-content: center;
&nbsp; }

&nbsp; .example-rate-limit-reached {
&nbsp; &nbsp; color: #980000;
&nbsp; &nbsp; max-width: 360px;
&nbsp; &nbsp; text-align: center;
&nbsp; }

&nbsp; /* Column Widths */
&nbsp; .mat-column-number,
&nbsp; .mat-column-state {
&nbsp; &nbsp; max-width: 64px;
&nbsp; }

&nbsp; .mat-column-created {
&nbsp; &nbsp; max-width: 124px;
&nbsp; }

&nbsp; .mat-flat-button {
&nbsp; &nbsp; margin: 5px;
&nbsp; }

13. Show and Delete Supplier Details using Angular 7 Material

To show supplier details after click or tap on the one of a row inside the Angular 7 Material table, open and edit src/app/supplier-detail/supplier-detail.component.ts then add this imports.

import { ActivatedRoute, Router } from '@angular/router';
import { ApiService } from '../api.service';
import { Supplier } from '../supplier';

Inject above modules to the constructor.

constructor(private route: ActivatedRoute, private api: ApiService, private router: Router) { }

Declare the variables before the constructor for hold supplier data that get from the API.

supplier: Supplier = {
&nbsp; supplierId: null,
&nbsp; companyName: '',
&nbsp; contactName: '',
&nbsp; contactTitle: '',
&nbsp; address: '',
&nbsp; city: '',
&nbsp; region: '',
&nbsp; postalCode: '',
&nbsp; country: '',
&nbsp; phone: '',
&nbsp; fax: '',
&nbsp; homePage: ''
};
isLoadingResults = true;

Add a function for getting Supplier data from the API.

getSupplierDetails(id) {
&nbsp; this.api.getSupplier(id)
&nbsp; &nbsp; .subscribe(data => {
&nbsp; &nbsp; &nbsp; this.supplier = data;
&nbsp; &nbsp; &nbsp; console.log(this.supplier);
&nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; });
}

Call that function when the component is initiated.

ngOnInit() {
&nbsp; this.getSupplierDetails(this.route.snapshot.params['id']);
}

Add this function for delete supplier.

deleteSupplier(id: number) {
&nbsp; this.isLoadingResults = true;
&nbsp; this.api.deleteSupplier(id)
&nbsp; &nbsp; .subscribe(res => {
&nbsp; &nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; &nbsp; &nbsp; this.router.navigate(['/supplier']);
&nbsp; &nbsp; &nbsp; }, (err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; );
}

For the view, open and edit src/app/supplier-detail/supplier-detail.component.html then replace all HTML tags with this.

<div class="example-container mat-elevation-z8">
&nbsp; <div class="example-loading-shade"
&nbsp; &nbsp; &nbsp; &nbsp;*ngIf="isLoadingResults">
&nbsp; &nbsp; <mat-spinner *ngIf="isLoadingResults"></mat-spinner>
&nbsp; </div>
&nbsp; <div class="button-row">
&nbsp; &nbsp; <a mat-flat-button color="primary" [routerLink]="['/supplier']"><mat-icon>list</mat-icon></a>
&nbsp; </div>
&nbsp; <mat-card class="example-card">
&nbsp; &nbsp; <mat-card-header>
&nbsp; &nbsp; &nbsp; <mat-card-title><h2>Supplier ID: {{supplier.supplierId}}</h2></mat-card-title>
&nbsp; &nbsp; &nbsp; <mat-card-subtitle>Company Name: {{supplier.companyName}}</mat-card-subtitle>
&nbsp; &nbsp; </mat-card-header>
&nbsp; &nbsp; <mat-card-content>
&nbsp; &nbsp; &nbsp; <dl>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Contact Name:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.contactName}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Contact Title:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.contactTitle}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Address:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.address}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>City:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.city}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Region:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.region}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Postal Code:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.postalCode}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Country:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.country}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Phone:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.phone}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Fax:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.fax}}</dd>
&nbsp; &nbsp; &nbsp; &nbsp; <dt>Home Page:</dt>
&nbsp; &nbsp; &nbsp; &nbsp; <dd>{{supplier.homePage}}</dd>
&nbsp; &nbsp; &nbsp; </dl>
&nbsp; &nbsp; </mat-card-content>
&nbsp; &nbsp; <mat-card-actions>
&nbsp; &nbsp; &nbsp; <a mat-flat-button color="primary" [routerLink]="['/supplier-edit/', supplier.supplierId || 'all']"><mat-icon>edit</mat-icon></a>
&nbsp; &nbsp; &nbsp; <a mat-flat-button color="warn" (click)="deleteSupplier(supplier.supplierId)"><mat-icon>delete</mat-icon></a>
&nbsp; &nbsp; </mat-card-actions>
&nbsp; </mat-card>
</div>

Finally, open and edit src/app/supplier-detail/supplier-detail.component.css then add these lines of CSS codes.

/* Structure */
.example-container {
&nbsp; position: relative;
&nbsp; padding: 5px;
}

.example-loading-shade {
&nbsp; position: absolute;
&nbsp; top: 0;
&nbsp; left: 0;
&nbsp; bottom: 56px;
&nbsp; right: 0;
&nbsp; background: rgba(0, 0, 0, 0.15);
&nbsp; z-index: 1;
&nbsp; display: flex;
&nbsp; align-items: center;
&nbsp; justify-content: center;
}

.mat-flat-button {
&nbsp; margin: 5px;
}

14. Add a Supplier using Angular 7 Material

To add a new supplier, we have to create Angular 7 reactive form. Open and edit src/app/supplier-add/supplier-add.component.ts then add this imports.

import { Router } from '@angular/router';
import { ApiService } from '../api.service';
import { FormControl, FormGroupDirective, FormBuilder, FormGroup, NgForm, Validators } from '@angular/forms';

Inject above modules to the constructor.

constructor(private router: Router, private api: ApiService, private formBuilder: FormBuilder) { }

Declare variables for the Form Group and all of the required fields inside the form before the constructor.

supplierForm: FormGroup;
companyName = '';
contactName = '';
contactTitle = '';
address = '';
city = '';
region = '';
postalCode = '';
country = '';
phone = '';
fax = '';
homePage = '';
isLoadingResults = false;

Add initial validation for each field.

ngOnInit() {
&nbsp; this.supplierForm = this.formBuilder.group({
&nbsp; &nbsp; 'companyName' : [null, Validators.required],
&nbsp; &nbsp; 'contactName' : [null, null],
&nbsp; &nbsp; 'contactTitle' : [null, null],
&nbsp; &nbsp; 'address' : [null, null],
&nbsp; &nbsp; 'city' : [null, null],
&nbsp; &nbsp; 'region' : [null, null],
&nbsp; &nbsp; 'postalCode' : [null, null],
&nbsp; &nbsp; 'country' : [null, null],
&nbsp; &nbsp; 'phone' : [null, null],
&nbsp; &nbsp; 'fax' : [null, null],
&nbsp; &nbsp; 'homePage' : [null, null]
&nbsp; });
}

Create a function for submitting or POST supplier form.

onFormSubmit(form: NgForm) {
&nbsp; this.isLoadingResults = true;
&nbsp; this.api.addSupplier(form)
&nbsp; &nbsp; .subscribe((res: { [x: string]: any; }) => {
&nbsp; &nbsp; &nbsp; &nbsp; const supplier = res['supplierResponse'];
&nbsp; &nbsp; &nbsp; &nbsp; const id = supplier['supplierId'];
&nbsp; &nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; &nbsp; &nbsp; this.router.navigate(['/supplier-details', id]);
&nbsp; &nbsp; &nbsp; }, (err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; &nbsp; });
}

Next, add this import for implementing ErrorStateMatcher.

import { ErrorStateMatcher } from '@angular/material/core';

Create a new class after the end of this class bracket.

/** Error when invalid control is dirty, touched, or submitted. */
export class MyErrorStateMatcher implements ErrorStateMatcher {
&nbsp; isErrorState(control: FormControl | null, form: FormGroupDirective | NgForm | null): boolean {
&nbsp; &nbsp; const isSubmitted = form && form.submitted;
&nbsp; &nbsp; return !!(control && control.invalid && (control.dirty || control.touched || isSubmitted));
&nbsp; }
}

Instantiate that MyErrorStateMatcher as a variable in main class.

matcher = new MyErrorStateMatcher();

Next, open and edit src/app/supplier-add/supplier-add.component.html then replace all HTML tags with this.

<div class="example-container mat-elevation-z8">
&nbsp; <div class="example-loading-shade"
&nbsp; &nbsp; &nbsp; &nbsp;*ngIf="isLoadingResults">
&nbsp; &nbsp; <mat-spinner *ngIf="isLoadingResults"></mat-spinner>
&nbsp; </div>
&nbsp; <div class="button-row">
&nbsp; &nbsp; <a mat-flat-button color="primary" [routerLink]="['/supplier']"><mat-icon>list</mat-icon></a>
&nbsp; </div>
&nbsp; <mat-card class="example-card">
&nbsp; &nbsp; <form [formGroup]="supplierForm" (ngSubmit)="onFormSubmit(supplierForm.value)">
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Company Name" formControlName="companyName"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; &nbsp; <mat-error>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span *ngIf="!supplierForm.get('companyName').valid && supplierForm.get('companyName').touched">Please enter Company Name</span>
&nbsp; &nbsp; &nbsp; &nbsp; </mat-error>
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Contact Name" formControlName="contactName"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Contact Title" formControlName="contactTitle"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Address" formControlName="address"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="City" formControlName="city"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Region" formControlName="region"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Postal Code" formControlName="postalCode"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Country" formControlName="country"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Phone" formControlName="phone"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Fax" formControlName="fax"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Home Page" formControlName="homePage"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <div class="button-row">
&nbsp; &nbsp; &nbsp; &nbsp; <button type="submit" [disabled]="!supplierForm.valid" mat-flat-button color="primary"><mat-icon>save</mat-icon></button>
&nbsp; &nbsp; &nbsp; </div>
&nbsp; &nbsp; </form>
&nbsp; </mat-card>
</div>

Finally, open and edit src/app/supplier-add/supplier-add.component.css then add this CSS codes.

/* Structure */
.example-container {
&nbsp; position: relative;
&nbsp; padding: 5px;
}

.example-form {
&nbsp; min-width: 150px;
&nbsp; max-width: 500px;
&nbsp; width: 100%;
}

.example-full-width {
&nbsp; width: 100%;
}

.example-full-width:nth-last-child() {
&nbsp; margin-bottom: 10px;
}

.button-row {
&nbsp; margin: 10px 0;
}

.mat-flat-button {
&nbsp; margin: 5px;
}

15. Edit a Supplier using Angular 7 Material

We have put an edit button inside the Supplier Detail component for call Edit page. Now, open and edit src/app/supplier-edit/supplier-edit.component.ts then add this imports.

import { Router, ActivatedRoute } from '@angular/router';
import { ApiService } from '../api.service';
import { FormControl, FormGroupDirective, FormBuilder, FormGroup, NgForm, Validators } from '@angular/forms';
import { ErrorStateMatcher } from '@angular/material/core';

Inject above modules to the constructor.

constructor(private router: Router, private route: ActivatedRoute, private api: ApiService, private formBuilder: FormBuilder) { }

Declare the Form Group variable and all of the required variables for the supplier form before the constructor.

supplierForm: FormGroup;
companyName = '';
contactName = '';
contactTitle = '';
address = '';
city = '';
region = '';
postalCode = '';
country = '';
phone = '';
fax = '';
homePage = '';
isLoadingResults = false;
matcher = new MyErrorStateMatcher();

Next, add validation for all fields when the component is initiated.

ngOnInit() {
&nbsp; this.getSupplier(this.route.snapshot.params['id']);
&nbsp; this.supplierForm = this.formBuilder.group({
&nbsp; &nbsp; 'companyName' : [null, Validators.required],
&nbsp; &nbsp; 'contactName' : [null, null],
&nbsp; &nbsp; 'contactTitle' : [null, null],
&nbsp; &nbsp; 'address' : [null, null],
&nbsp; &nbsp; 'city' : [null, null],
&nbsp; &nbsp; 'region' : [null, null],
&nbsp; &nbsp; 'postalCode' : [null, null],
&nbsp; &nbsp; 'country' : [null, null],
&nbsp; &nbsp; 'phone' : [null, null],
&nbsp; &nbsp; 'fax' : [null, null],
&nbsp; &nbsp; 'homePage' : [null, null]
&nbsp; });
}

Create a function for getting supplier data that filled to each form fields.

getSupplier(id: number) {
&nbsp; this.api.getSupplier(id).subscribe(data => {
&nbsp; &nbsp; this.supplierId = data.supplierId;
&nbsp; &nbsp; this.supplierForm.setValue({
&nbsp; &nbsp; &nbsp; companyName: data.companyName,
&nbsp; &nbsp; &nbsp; contactName: data.contactName,
&nbsp; &nbsp; &nbsp; contactTitle: data.contactTitle,
&nbsp; &nbsp; &nbsp; address: data.address,
&nbsp; &nbsp; &nbsp; city: data.city,
&nbsp; &nbsp; &nbsp; region: data.region,
&nbsp; &nbsp; &nbsp; postalCode: data.postalCode,
&nbsp; &nbsp; &nbsp; country: data.country,
&nbsp; &nbsp; &nbsp; phone: data.phone,
&nbsp; &nbsp; &nbsp; fax: data.fax,
&nbsp; &nbsp; &nbsp; homePage: data.homePage
&nbsp; &nbsp; });
&nbsp; });
}

Create a function to update the supplier changes.

onFormSubmit(form: NgForm) {
&nbsp; this.isLoadingResults = true;
&nbsp; this.api.updateSupplier(this.supplierId, form)
&nbsp; &nbsp; .subscribe(res => {
&nbsp; &nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; &nbsp; &nbsp; this.router.navigate(['/supplier']);
&nbsp; &nbsp; &nbsp; }, (err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; &nbsp; this.isLoadingResults = false;
&nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; );
}

Add a function for handling show supplier details button.

supplierDetails() {
&nbsp; this.router.navigate(['/supplier-details', this.supplierId]);
}

Add a class that implementing ErrorStateMatcher after the current class.

/** Error when invalid control is dirty, touched, or submitted. */
export class MyErrorStateMatcher implements ErrorStateMatcher {
&nbsp; isErrorState(control: FormControl | null, form: FormGroupDirective | NgForm | null): boolean {
&nbsp; &nbsp; const isSubmitted = form && form.submitted;
&nbsp; &nbsp; return !!(control && control.invalid && (control.dirty || control.touched || isSubmitted));
&nbsp; }
}

Next, open and edit src/app/supplier-edit/supplier-edit.component.html then replace all HTML tags with this.

<div class="example-container mat-elevation-z8">
&nbsp; <div class="example-loading-shade"
&nbsp; &nbsp; &nbsp; &nbsp;*ngIf="isLoadingResults">
&nbsp; &nbsp; <mat-spinner *ngIf="isLoadingResults"></mat-spinner>
&nbsp; </div>
&nbsp; <div class="button-row">
&nbsp; &nbsp; <a mat-flat-button color="primary" [routerLink]="['/supplier']"><mat-icon>list</mat-icon></a>
&nbsp; </div>
&nbsp; <mat-card class="example-card">
&nbsp; &nbsp; <form [formGroup]="supplierForm" (ngSubmit)="onFormSubmit(supplierForm.value)">
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Company Name" formControlName="companyName"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; &nbsp; <mat-error>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span *ngIf="!supplierForm.get('companyName').valid && supplierForm.get('companyName').touched">Please enter Company Name</span>
&nbsp; &nbsp; &nbsp; &nbsp; </mat-error>
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Contact Name" formControlName="contactName"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Contact Title" formControlName="contactTitle"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Address" formControlName="address"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="City" formControlName="city"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Region" formControlName="region"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Postal Code" formControlName="postalCode"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Country" formControlName="country"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Phone" formControlName="phone"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Fax" formControlName="fax"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <mat-form-field class="example-full-width">
&nbsp; &nbsp; &nbsp; &nbsp; <input matInput placeholder="Home Page" formControlName="homePage"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[errorStateMatcher]="matcher">
&nbsp; &nbsp; &nbsp; </mat-form-field>
&nbsp; &nbsp; &nbsp; <div class="button-row">
&nbsp; &nbsp; &nbsp; &nbsp; <button type="submit" [disabled]="!supplierForm.valid" mat-flat-button color="primary"><mat-icon>update</mat-icon></button>
&nbsp; &nbsp; &nbsp; </div>
&nbsp; &nbsp; </form>
&nbsp; </mat-card>
</div>

Finally, open and edit src/app/supplier-edit/supplier-edit.component.css then add this lines of CSS codes.

/* Structure */
.example-container {
&nbsp; position: relative;
&nbsp; padding: 5px;
}

.example-form {
&nbsp; min-width: 150px;
&nbsp; max-width: 500px;
&nbsp; width: 100%;
}

.example-full-width {
&nbsp; width: 100%;
}

.example-full-width:nth-last-child() {
&nbsp; margin-bottom: 10px;
}

.button-row {
&nbsp; margin: 10px 0;
}

.mat-flat-button {
&nbsp; margin: 5px;
}

16. Run and Test the ASP.NET Core Web API and Angular 7 CRUD Web Application

Before running the ASP.NET Core Web API that consumes by Angular 7 application, make sure ASP.NET Core Web API serve by HTTP only. Just open Properties\launchSettings.json then remove https from the applicationUrl. Next, type this command in the Visual Studio Code Terminal.

dotnet watch run

To run the Angular 7 application, type this command from the Node.js Command Prompt.

ng serve

Now, you will see this Angular 7 pages when pointing your browser to localhost:4200.

You can just navigate through the whole Angular 7 application.

That it’s, the tutorial of Building Web App using ASP.NET Web API Angular 7 and SQL Server. You can find the fully working source code from our GitHub.

Learn More

Angular 7 (formerly Angular 2) - The Complete Guide

Learn and Understand AngularJS

Angular Crash Course for Busy Developers

The Complete Angular Course: Beginner to Advanced

Angular (Angular 2+) & NodeJS - The MEAN Stack Guide

The Complete ASP.NET MVC 5 Course

Build a Real-world App with ASP.NET Core and Angular 2 (4+)

ASP NET Core (ASP.NET 5),MVC 6,C#,Angular2 & EF Crash Course

React Native Tutorial: SQLite Offline Android/iOS Mobile App

React Native Tutorial: SQLite Offline Android/iOS Mobile App

React Native Tutorial: SQLite Offline Android/iOS Mobile App

Table of Contents:
  • Install React App Creator and Create App
  • Add Navigation Header and required Screen
  • Install and Configure React Native SQLite Storage
  • Show List of Product
  • Show Product Details and Delete Product
  • Add Product
  • Edit Product
  • Run and Test React Native and SQLite Offline Mobile App

The following tools, frameworks, and modules are required for this tutorial:

  • Install React App Creator and Create App
  • Add Navigation Header and required Screen
  • Install and Configure React Native SQLite Storage
  • Show List of Product
  • Show Product Details and Delete Product
  • Add Product
  • Edit Product
  • Run and Test React Native and SQLite Offline Mobile App

Before start to the main steps, make sure that you have installed Node.js and can run npm in the terminal or command line. To check the existing or installed Node.js environment open the terminal/command line then type this command.

node -v
v10.15.1
npm -v
6.8.0
yarn -v
1.10.1

1. Install React App Creator and Create App

The Create React Native App is a tool for creating a React Native App. To install it, type this command in your App projects folder.

sudo npm install -g react-native-cli

Then create a React Native App using this command.

react-native init reactOffline

That command will create a React Native app then install all required modules. The app or project folder will contain these folders and files.

Next, go to the newly created React App folder.

cd reactSqlite

To run on iOS device or simulator run this command.

react-native run-ios

The simulator will open along with the new terminal window. Go to the project folder from the new terminal window then run this command.

react-native start

Right after iOS build success and React Native started, you will see this view in the iOS Simulator.

To run on Android device or simulator, make sure ADB detected the device.

adb devices
List of devices attached
J8AXGF0194047T6&nbsp;&nbsp; &nbsp;device

Next, type this command to run on the Android device or simulator.

react-native run-android

It will open the new terminal windows. Just go to the project folder then type this command.

react-native start

You will see this app in your Android device.

Sometimes, if running React Native app faster than starting React-Native Javascript bundler you see this red-screen of error.

No bundle URL present.

Make sure you’re running a packager server or have included a .jsbundle file in your application bundle.

RCTFatal
__28-[RCTCxxBridge handleError:]_block_invoke
_dispatch_call_block_and_release
_dispatch_client_callout
_dispatch_main_queue_callback_4CF
__CFRUNLOOP_IS_SERVICING_THE_MAIN_DISPATCH_QUEUE__
__CFRunLoopRun
CFRunLoopRunSpecific
GSEventRunModal
UIApplicationMain
main
start
0x0

Don’t worry, just start the Metro Bundler in the newly opened terminal window after you go to the project folder. After Metro Bundler started completely, refresh the React Native app on your device or simulator. In iOS Simulator you will see this error after refresh.

Attempting to reload bridge before it's valid: <RCTCxxBridge: 0x7ff34bc00510>. Try restarting the development server if connected.

-[RCTCxxBridge reload]
&nbsp; &nbsp; RCTCxxBridge.mm:986
-[RCTRedBox reloadFromRedBoxWindow:]
-[RCTRedBoxWindow reload]
-[UIApplication sendAction:to:from:forEvent:]
-[UIControl sendAction:to:forEvent:]
-[UIControl _sendActionsForEvents:withEvent:]
-[UIControl touchesEnded:withEvent:]
-[UIWindow _sendTouchesForEvent:]
-[UIWindow sendEvent:]
-[UIApplication sendEvent:]
__dispatchPreprocessedEventFromEventQueue
__handleEventQueueInternal
__handleEventQueueInternal
__CFRUNLOOP_IS_CALLING_OUT_TO_A_SOURCE0_PERFORM_FUNCTION__
__CFRunLoopDoSources0
__CFRunLoopRun
CFRunLoopRunSpecific
GSEventRunModal
UIApplicationMain
main
start
0x0

Just reload again the React Native app, you will get your React Native app running.

2. Add Navigation Header and required Screen

Above generated React Native App just show blank app with plain text. Now, we will show you how to add the Navigation Header and Home Screen for your app. So, it will look like the Native App. In the terminal or command line, type this command to install React Navigation module and don’t forget to stop the running Metro Bundler before installing the modules.

yarn add react-navigation --save
yarn add react-native-gesture-handler --save
react-native link react-native-gesture-handler

Next, create a folder for components and components files in the root of the app folder.

mkdir components
touch components/ProductScreen.js
touch components/ProductDetailsScreen.js
touch components/ProductAddScreen.js
touch components/ProductEditScreen.js

Open and edit components/ProductScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Product List',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Product List</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Details"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('ProductDetails')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Add Product"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('AddProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Edit Product"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('EditProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Open and edit components/ProductDetailsScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductDetailsScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Product Details',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Product Details</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Details... again"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.push('ProductDetails')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Home"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('Product')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go back"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.goBack()}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Open and edit components/ProductAddScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductAddScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Add Product',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Add Product</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Add Product... again"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.push('AddProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Home"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('Product')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go back"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.goBack()}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Open and edit components/ProductEditScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductEditScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Edit Product',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Add Product</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Edit Product... again"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.push('EditProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Home"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('Product')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go back"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.goBack()}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Next, open and edit App.js then add replace all codes with this.

import React from 'react';
import { StyleSheet, Text, View } from 'react-native';
import { createAppContainer, createStackNavigator } from 'react-navigation';
import ProductScreen from './components/ProductScreen';
import ProductDetailsScreen from './components/ProductDetailsScreen';
import ProductAddScreen from './components/ProductAddScreen';
import ProductEditScreen from './components/ProductEditScreen';

const RootStack = createStackNavigator(
&nbsp; {
&nbsp; &nbsp; Product: ProductScreen,
&nbsp; &nbsp; ProductDetails: ProductDetailsScreen,
&nbsp; &nbsp; AddProduct: ProductAddScreen,
&nbsp; &nbsp; EditProduct: ProductEditScreen,
&nbsp; },
&nbsp; {
&nbsp; &nbsp; initialRouteName: 'Product',
&nbsp; &nbsp; navigationOptions: {
&nbsp; &nbsp; &nbsp; headerStyle: {
&nbsp; &nbsp; &nbsp; &nbsp; backgroundColor: '#777777',
&nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; headerTintColor: '#fff',
&nbsp; &nbsp; &nbsp; headerTitleStyle: {
&nbsp; &nbsp; &nbsp; &nbsp; fontWeight: 'bold',
&nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; },
&nbsp; },
);

const RootContainer = createAppContainer(RootStack);

export default class App extends React.Component {
&nbsp; render() {
&nbsp; &nbsp; return <RootContainer />;
&nbsp; }
}

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; backgroundColor: '#fff',
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center',
&nbsp; },
});

After Re-run the React Native app on the iOS/Android Device/Simulator you will see this updated views.

3. Install and Configure React Native SQLite Storage

Before creating an offline CRUD application using local data, we have to install the react-native-sqlite-storage and required UI/UX module.

yarn add react-native-sqlite-storage --save
yarn add react-native-elements --save
react-native link

We will use separate Class for accessing SQLite and do some CRUD (create, read, update, delete) operations. For that, create a new Javascript file on the root of the project folder.

touch Database.js

Open and edit Database.js then add this SQLite import with the configuration.

import SQLite from "react-native-sqlite-storage";
SQLite.DEBUG(true);
SQLite.enablePromise(true);

Add constant variable after that.

const database_name = "Reactoffline.db";
const database_version = "1.0";
const database_displayname = "SQLite React Offline Database";
const database_size = 200000;

Give this file a class name.

export default class Database {

}

Inside the class bracket, add a function for Database initialization that creates Database, tables, etc.

initDB() {
&nbsp; let db;
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; console.log("Plugin integrity check ...");
&nbsp; &nbsp; SQLite.echoTest()
&nbsp; &nbsp; &nbsp; .then(() => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log("Integrity check passed ...");
&nbsp; &nbsp; &nbsp; &nbsp; console.log("Opening database ...");
&nbsp; &nbsp; &nbsp; &nbsp; SQLite.openDatabase(
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_name,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_version,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_displayname,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_size
&nbsp; &nbsp; &nbsp; &nbsp; )
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .then(DB => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db = DB;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Database OPEN");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.executeSql('SELECT 1 FROM Product LIMIT 1').then(() => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Database is ready ... executing query ...");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).catch((error) =>{
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Received error: ", error);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Database not yet ready ... populating data");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('CREATE TABLE IF NOT EXISTS Product (prodId, prodName, prodDesc, prodImage, prodPrice)');
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).then(() => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Table created successfully");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(error);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(db);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(error);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log("echoTest failed - plugin not functional");
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; });
};

Add a function for close Database connection.

closeDatabase(db) {
&nbsp; if (db) {
&nbsp; &nbsp; console.log("Closing DB");
&nbsp; &nbsp; db.close()
&nbsp; &nbsp; &nbsp; .then(status => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log("Database CLOSED");
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; this.errorCB(error);
&nbsp; &nbsp; &nbsp; });
&nbsp; } else {
&nbsp; &nbsp; console.log("Database was not OPENED");
&nbsp; }
};

Add a function to get the list of products.

listProduct() {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; const products = [];
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('SELECT p.prodId, p.prodName, p.prodImage FROM Product p', []).then(([tx,results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Query completed");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var len = results.rows.length;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (let i = 0; i < len; i++) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let row = results.rows.item(i);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(`Prod ID: ${row.prodId}, Prod Name: ${row.prodName}`)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; const { prodId, prodName, prodImage } = row;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; products.push({
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodId,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodName,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodImage
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(products);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(products);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to get Product by ID.

productById(id) {
&nbsp; console.log(id);
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('SELECT * FROM Product WHERE prodId = ?', [id]).then(([tx,results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(results);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(results.rows.length > 0) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let row = results.rows.item(0);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(row);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to save a new product to the SQLite database.

addProduct(prod) {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('INSERT INTO Product VALUES (?, ?, ?, ?, ?)', [prod.prodId, prod.prodName, prod.prodDesc, prod.prodImage, prod.prodPrice]).then(([tx, results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(results);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to update a product.

updateProduct(id, prod) {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('UPDATE Product SET prodName = ?, prodDesc = ?, prodImage = ?, prodPrice = ? WHERE prodId = ?', [prod.prodName, prod.prodDesc, prod.prodImage, prod.prodPrice, id]).then(([tx, results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(results);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to delete a product.

deleteProduct(id) {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('DELETE FROM Product WHERE prodId = ?', [id]).then(([tx, results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(results);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(results);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

4. Show List of Product

To show or display the list of product, open and edit components/ProductScreen.js then replace all imports with these imports.

import React, { Component } from 'react';
import { StyleSheet, FlatList, ActivityIndicator, View, Text } from 'react-native';
import { ListItem, Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable before the class name.

const db = new Database();

Next, replace navigationOptions with these.

static navigationOptions = ({ navigation }) => {
&nbsp; return {
&nbsp; &nbsp; title: 'Product List',
&nbsp; &nbsp; headerRight: (
&nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; buttonStyle={{ padding: 0, backgroundColor: 'transparent' }}
&nbsp; &nbsp; &nbsp; &nbsp; icon={{ name: 'add-circle', style: { marginRight: 0, fontSize: 28 } }}
&nbsp; &nbsp; &nbsp; &nbsp; onPress={() => {&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; navigation.navigate('AddProduct', {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onNavigateBack: this.handleOnNavigateBack
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; }}
&nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; ),
&nbsp; };
};

Add a constructor function.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; isLoading: true,
&nbsp; &nbsp; products: [],
&nbsp; &nbsp; notFound: 'Products not found.\nPlease click (+) button to add it.'
&nbsp; };
}

Add a function to initialize the screen.

componentDidMount() {
&nbsp; this._subscribe = this.props.navigation.addListener('didFocus', () => {
&nbsp; &nbsp; this.getProducts();
&nbsp; });
}

Add a function to get the product list from Database class.

getProducts() {
&nbsp; let products = [];
&nbsp; db.listProduct().then((data) => {
&nbsp; &nbsp; products = data;
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; products,
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; }
&nbsp; })
}

Add a variable to iterate the listed product in the view.

keyExtractor = (item, index) => index.toString()

Add a function to render the List Item.

renderItem = ({ item }) => (
&nbsp; <ListItem
&nbsp; &nbsp; title={item.prodName}
&nbsp; &nbsp; leftAvatar={{
&nbsp; &nbsp; &nbsp; source: item.prodImage && { uri: item.prodImage },
&nbsp; &nbsp; &nbsp; title: item.prodName[0]
&nbsp; &nbsp; }}
&nbsp; &nbsp; onPress={() => {
&nbsp; &nbsp; &nbsp; this.props.navigation.navigate('ProductDetails', {
&nbsp; &nbsp; &nbsp; &nbsp; prodId: `${item.prodId}`,
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }}
&nbsp; &nbsp; chevron
&nbsp; &nbsp; bottomDivider
&nbsp; />
)

Add a function to render the rest of List view.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff"/>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; if(this.state.products.length === 0){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; <Text style={styles.message}>{this.state.notFound}</Text>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <FlatList
&nbsp; &nbsp; &nbsp; keyExtractor={this.keyExtractor}
&nbsp; &nbsp; &nbsp; data={this.state.products}
&nbsp; &nbsp; &nbsp; renderItem={this.renderItem}
&nbsp; &nbsp; />
&nbsp; );
}

Finally, add a stylesheet for the whole screen after the class bracket.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp;flex: 1,
&nbsp; &nbsp;paddingBottom: 22
&nbsp; },
&nbsp; item: {
&nbsp; &nbsp; padding: 10,
&nbsp; &nbsp; fontSize: 18,
&nbsp; &nbsp; height: 44,
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; },
&nbsp; message: {
&nbsp; &nbsp; padding: 16,
&nbsp; &nbsp; fontSize: 18,
&nbsp; &nbsp; color: 'red'
&nbsp; }
});

5. Show Product Details and Delete Product

From the list of product view, you will see that list item has an action button to show the product details. Next, open and edit components/ProductDetailsScreen.js then replace the imports with these imports.

import React, { Component } from 'react';
import { ScrollView, StyleSheet, Image, ActivityIndicator, View, Text } from 'react-native';
import { Card, Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable.

const db = new Database();

Add a function as the constructor.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; isLoading: true,
&nbsp; &nbsp; product: {},
&nbsp; &nbsp; id: '',
&nbsp; };
}

Add a function to initialize the screen.

componentDidMount() {
&nbsp; this._subscribe = this.props.navigation.addListener('didFocus', () => {
&nbsp; &nbsp; const { navigation } = this.props;
&nbsp; &nbsp; db.productById(navigation.getParam('prodId')).then((data) => {
&nbsp; &nbsp; &nbsp; console.log(data);
&nbsp; &nbsp; &nbsp; product = data;
&nbsp; &nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; &nbsp; product,
&nbsp; &nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; &nbsp; &nbsp; id: product.prodId
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; })
&nbsp; });
}

Add a function to delete a product data.

deleteProduct(id) {
&nbsp; const { navigation } = this.props;
&nbsp; this.setState({
&nbsp; &nbsp; isLoading: true
&nbsp; });
&nbsp; db.deleteProduct(id).then((result) => {
&nbsp; &nbsp; console.log(result);
&nbsp; &nbsp; this.props.navigation.goBack();
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; }
&nbsp; })
}

Add a function to render the whole Product Details view.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff" />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <ScrollView>
&nbsp; &nbsp; &nbsp; <Card style={styles.container}>
&nbsp; &nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Image
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; style={{width: 150, height: 150}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; source={{uri: this.state.product.prodImage}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product ID: {this.state.product.prodId}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product Name: {this.state.product.prodName}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product Desc: {this.state.product.prodDesc}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product Price: {this.state.product.prodPrice}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; <View style={styles.detailButton}>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; backgroundColor={'#CCCCCC'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'edit'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Edit'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; this.props.navigation.navigate('EditProduct', {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodId: `${this.state.id}`,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }} />
&nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; <View style={styles.detailButton}>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; backgroundColor={'#999999'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; color={'#FFFFFF'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'delete'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Delete'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.deleteProduct(this.state.id)} />
&nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; </Card>
&nbsp; &nbsp; </ScrollView>
&nbsp; );
}

Finally, add the stylesheet for this screen after the class bracket.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; padding: 20
&nbsp; },
&nbsp; subContainer: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; paddingBottom: 20,
&nbsp; &nbsp; borderBottomWidth: 2,
&nbsp; &nbsp; borderBottomColor: '#CCCCCC',
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; },
&nbsp; detailButton: {
&nbsp; &nbsp; marginTop: 10
&nbsp; }
})

6. Add Product

To add or save a new Product, open and edit the components/ProductAddScreen.js then replace all imports with these imports.

import React, { Component } from 'react';
import { StyleSheet, ScrollView, ActivityIndicator, View, TextInput } from 'react-native';
import { Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable.

const db = new Database();

Add a constructor inside the class bracket after the navigationOptions.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; prodId: '',
&nbsp; &nbsp; prodName: '',
&nbsp; &nbsp; prodDesc: '',
&nbsp; &nbsp; prodImage: '',
&nbsp; &nbsp; prodPrice: '0',
&nbsp; &nbsp; isLoading: false,
&nbsp; };
}

Add a function to update the input text values.

updateTextInput = (text, field) => {
&nbsp; const state = this.state
&nbsp; state[field] = text;
&nbsp; this.setState(state);
}

Add a function to save a product to the SQLite table.

saveProduct() {
&nbsp; this.setState({
&nbsp; &nbsp; isLoading: true,
&nbsp; });
&nbsp; let data = {
&nbsp; &nbsp; prodId: this.state.prodId,
&nbsp; &nbsp; prodName: this.state.prodName,
&nbsp; &nbsp; prodDesc: this.state.prodDesc,
&nbsp; &nbsp; prodImage: this.state.prodImage,
&nbsp; &nbsp; prodPrice: this.state.prodPrice
&nbsp; }
&nbsp; db.addProduct(data).then((result) => {
&nbsp; &nbsp; console.log(result);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; &nbsp; this.props.navigation.state.params.onNavigateBack;
&nbsp; &nbsp; this.props.navigation.goBack();
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; })
}

Add a function to render the whole add product view.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff"/>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <ScrollView style={styles.container}>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product ID'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodId}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodId')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Name'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodName}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodName')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; multiline={true}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; numberOfLines={4}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Description'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodDesc}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodDesc')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Image'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodImage}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodImage')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Price'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodPrice}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; keyboardType='numeric'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodPrice')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.button}>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'save'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Save'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.saveProduct()} />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; </ScrollView>
&nbsp; );
}

Finally, add the style for the whole screen.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; padding: 20
&nbsp; },
&nbsp; subContainer: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; marginBottom: 20,
&nbsp; &nbsp; padding: 5,
&nbsp; &nbsp; borderBottomWidth: 2,
&nbsp; &nbsp; borderBottomColor: '#CCCCCC',
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; }
})

7. Edit Product

To edit a product, open and edit components/ProductEditScreen.js then replace all imports with these imports.

import React, { Component } from 'react';
import { StyleSheet, ScrollView, ActivityIndicator, View, TextInput } from 'react-native';
import { Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable.

const db = new Database();

Add the constructor after the navigationOptions function.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; prodId: '',
&nbsp; &nbsp; prodName: '',
&nbsp; &nbsp; prodDesc: '',
&nbsp; &nbsp; prodImage: '',
&nbsp; &nbsp; prodPrice: '0',
&nbsp; &nbsp; isLoading: true,
&nbsp; };
}

Add a function to initialize the screen that will get product data.

componentDidMount() {
&nbsp; const { navigation } = this.props;
&nbsp; db.productById(navigation.getParam('prodId')).then((data) => {
&nbsp; &nbsp; console.log(data);
&nbsp; &nbsp; const product = data;
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; prodId: product.prodId,
&nbsp; &nbsp; &nbsp; prodName: product.prodName,
&nbsp; &nbsp; &nbsp; prodDesc: product.prodDesc,
&nbsp; &nbsp; &nbsp; prodImage: product.prodImage,
&nbsp; &nbsp; &nbsp; prodPrice: product.prodPrice,
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; }
&nbsp; })
}

Add a function to update the input text value.

updateTextInput = (text, field) => {
&nbsp; const state = this.state
&nbsp; state[field] = text;
&nbsp; this.setState(state);
}

Add a function to update the product data.

updateProduct() {
&nbsp; this.setState({
&nbsp; &nbsp; isLoading: true,
&nbsp; });
&nbsp; const { navigation } = this.props;
&nbsp; let data = {
&nbsp; &nbsp; prodId: this.state.prodId,
&nbsp; &nbsp; prodName: this.state.prodName,
&nbsp; &nbsp; prodDesc: this.state.prodDesc,
&nbsp; &nbsp; prodImage: this.state.prodImage,
&nbsp; &nbsp; prodPrice: this.state.prodPrice
&nbsp; }
&nbsp; db.updateProduct(data.prodId, data).then((result) => {
&nbsp; &nbsp; console.log(result);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; &nbsp; this.props.navigation.state.params.onNavigateBack;
&nbsp; &nbsp; this.props.navigation.goBack();
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; })
}

Add a function to render the whole Edit Product screen.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff"/>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <ScrollView style={styles.container}>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product ID'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodId}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodId')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Name'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodName}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodName')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; multiline={true}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; numberOfLines={4}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Description'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodDesc}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodDesc')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Image'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodImage}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodImage')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Price'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodPrice}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; keyboardType='numeric'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodPrice')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.button}>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'save'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Save'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.updateProduct()} />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; </ScrollView>
&nbsp; );
}

Finally, add the stylesheet after the class bracket.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; padding: 20
&nbsp; },
&nbsp; subContainer: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; marginBottom: 20,
&nbsp; &nbsp; padding: 5,
&nbsp; &nbsp; borderBottomWidth: 2,
&nbsp; &nbsp; borderBottomColor: '#CCCCCC',
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; }
})

8. Run and Test React Native and SQLite Offline Mobile App

As we show you at the first step, run the React Native and SQLite app using this command.

react-native run-android
react-native run-ios

After the new terminal window open, just go to the project folder then run this command.

react-native start

Now, you will see the whole application in the Android/iOS Device.

That it’s, the React Native and SQLite Offline Mobile App. You can get the full source code from our GitHub.

How to CRUD Operations using Reactjs and ASP.NET Web API

How to CRUD Operations using Reactjs and ASP.NET Web API

In this article, I'm going to perform CRUD operations using React.js, ASP.NET Web API and SQL Server. ReactJS is an open-source JavaScript library that is used for creating user interfaces. It is developed and maintained by Facebook.

Introduction

In this article, I'm going to perform CRUD operations using  React.js and ASP.NET Web API. Reactjs is an open-source JavaScript library that is used for creating user interfaces. It is developed and maintained by Facebook. Learn more about React here.

Prerequisites

  • We should have the basic knowledge of React.js and Web API.
  • Visual Studio and Visual Studio Code IDE should be installed on your system.
  • SQL Server Management Studio.

Technologies we will use:

  • ASP.NET Web API.
  • ReactJS
  • SQL Server.
  • React strap.
  • Bootstrap

You may also like: Angular vs React vs Vue: Which one will be popular in 2020.

Step 1: Create a Table in the Database

Open SQL Server Management Studio, create a database named "CrudDemo," and in this database, create a table. Give that table a name like "studentmaster."

USE [CrudDemo]  
GO  
CREATE TABLE [dbo].[studentmaster](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Name] [varchar](50) NULL,  
    [RollNo] [varchar](50) NULL,  
    [Class] [varchar](50) NULL,  
    [Address] [varchar](50) NULL,  
 CONSTRAINT [PK_studentmaster] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  

GO  
Create a New Web API Project

Step 2: Create a New Project.

Creating a new project in VS Code

_Creating a new project in VS Code_

Step 3: Change the Name to CrudUsingReact

Changing project name to CrudUsingReact

_Changing project name to CrudUsingReact_

Step 4: Choose the Web API Template

Selecting Web API template

_Selecting Web API template_

Step 5

Right-click the Models folder from Solution Explorer and go to Add >> New Item >> data.

Adding data

_Adding data_

Step 6

Click on the "ADO.NET Entity Data Model" option and click "Add."

Adding Entity Data Model

_Adding Entity Data Model_

Step 7

Select EF Designer from the database and click the "Next" button.

Choosing EF Designer

_Choosing EF Designer_

Step 8

Add the connection properties and select database name on the next page and click OK.

Adding connection properties to db

_Adding connection properties to db_

Step 9

Check the "Table" checkbox. The internal options will be selected by default. Now, click the "Finish" button.

Finishing project setup

_Finishing project setup_

Step 10

Right-click on Models folder and add two classes — Student and Response. Now, paste the following codes in these classes:

public class Student  
   {  
       public string Name { get; set; }  
       public string Rollno { get; set; }  
       public string Class { get; set; }  
       public string Address { get; set; }  
   }  
public class Response  
   {  
       public string Status { get; set; }  
       public string Message { get; set; }  
   }  

Step 11

Right-click on the Controllers folder and add a new controller. Name it "Student controller" and add the following namespace in the Student controller.

using CrudUsingReact.Models;  

Step 12

Now, add a method to insert and update data into the database.

[Route("AddotrUpdatestudent")]  
       [HttpPost]  
       public object AddotrUpdatestudent(Student st)  
       {  
           try  
           {  
               if (st.Id == 0)  
               {  
                   studentmaster sm = new studentmaster();  
                   sm.Name = st.Name;  
                   sm.RollNo = st.Rollno;  
                   sm.Address = st.Address;  
                   sm.Class = st.Class;  
                   DB.studentmasters.Add(sm);  
                   DB.SaveChanges();  
                   return new Response  
                   {  
                       Status = "Success",  
                       Message = "Data Successfully"  
                   };  
               }  
               else  
               {  
                   var obj = DB.studentmasters.Where(x => x.Id == st.Id).ToList().FirstOrDefault();  
                   if (obj.Id > 0)  
                   {  

                       obj.Name = st.Name;  
                       obj.RollNo = st.Rollno;  
                       obj.Address = st.Address;  
                       obj.Class = st.Class;  
                       DB.SaveChanges();  
                       return new Response  
                       {  
                           Status = "Updated",  
                           Message = "Updated Successfully"  
                       };  
                   }  
               }  
           }  
           catch (Exception ex)  
           {  
               Console.Write(ex.Message);  
           }  
           return new Response  
           {  
               Status = "Error",  
               Message = "Data not insert"  
           };  

       }  

Step 13

Add other methods to delete and fetch data respectively from the database.

[Route("Deletestudent")]  
      [HttpDelete]  
      public object Deletestudent(int id)  
      {  
          var obj = DB.studentmasters.Where(x => x.Id == id).ToList().FirstOrDefault();  
          DB.studentmasters.Remove(obj);  
          DB.SaveChanges();  
          return new Response  
          {  
              Status = "Delete",  
              Message = "Delete Successfuly"  
          };  
      }  

[HttpGet]  
      public object   Studentdetails()  
      {  

              var a = DB.studentmasters.ToList();  
              return a;  
      }  

Complete Student Controller Code

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Net;  
using System.Net.Http;  
using System.Web.Http;  
using CrudUsingReact.Models;  

namespace CrudUsingReact.Controllers  
{  
    [RoutePrefix("Api/Student")]  
    public class studentController : ApiController  
    {  
        CrudDemoEntities DB = new CrudDemoEntities();  
        [Route("AddotrUpdatestudent")]  
        [HttpPost]  
        public object AddotrUpdatestudent(Student st)  
        {  
            try  
            {  
                if (st.Id == 0)  
                {  
                    studentmaster sm = new studentmaster();  
                    sm.Name = st.Name;  
                    sm.RollNo = st.RollNo;  
                    sm.Address = st.Address;  
                    sm.Class = st.Class;  
                    DB.studentmasters.Add(sm);  
                    DB.SaveChanges();  
                    return new Response  
                    {  
                        Status = "Success",  
                        Message = "Data Successfully"  
                    };  
                }  
                else  
                {  
                    var obj = DB.studentmasters.Where(x => x.Id == st.Id).ToList().FirstOrDefault();  
                    if (obj.Id > 0)  
                    {  

                        obj.Name = st.Name;  
                        obj.RollNo = st.RollNo;  
                        obj.Address = st.Address;  
                        obj.Class = st.Class;  
                        DB.SaveChanges();  
                        return new Response  
                        {  
                            Status = "Updated",  
                            Message = "Updated Successfully"  
                        };  
                    }  
                }  
            }  
            catch (Exception ex)  
            {  
                Console.Write(ex.Message);  
            }  
            return new Response  
            {  
                Status = "Error",  
                Message = "Data not insert"  
            };  

        }  
        [Route("Studentdetails")]  
        [HttpGet]  
        public object   Studentdetails()  
        {  

                var a = DB.studentmasters.ToList();  
                return a;  
        }  

        [Route("StudentdetailById")]  
        [HttpGet]  
        public object StudentdetailById(int id)  
        {  
            var obj = DB.studentmasters.Where(x => x.Id == id).ToList().FirstOrDefault();  
            return obj;  
        }  
        [Route("Deletestudent")]  
        [HttpDelete]  
        public object Deletestudent(int id)  
        {  
            var obj = DB.studentmasters.Where(x => x.Id == id).ToList().FirstOrDefault();  
            DB.studentmasters.Remove(obj);  
            DB.SaveChanges();  
            return new Response  
            {  
                Status = "Delete",  
                Message = "Delete Successfuly"  
            };  
        }  
    }  
}  

Step 14

Now, let's enable CORS. Go to Tools, open NuGet Package Manager, search for CORS, and install the "Microsoft.Asp.Net.WebApi.Cors" package. Open Webapiconfig.cs and add the following lines:

EnableCorsAttribute cors = new EnableCorsAttribute("*", "*", "*");    
config.EnableCors(cors);   
Create React.js Project

Step 15

To create a new ReactJS project, open the command prompt, and enter the following command:

npx create-react-app crudwithceactjs  

Open the newly created project in Visual Studio Code and install Reactstrap and Bootstrap by using the following commands:

npm install --save bootstrap  
npm install --save reactstrap react react-dom  

Step 16

Now, go to the "src" folder and add a new folder "Student" and four new components:

  1. Addstudent.js
  2. Studentlist.js
  3. Editstudent.js
  4. Table.js

Adding components to Student folder

_Adding components to the Student folder_

Step 17

Install the Axios library by using the following command. Learn more about Axios library

npm install --save axios  

Step 18

Add routing: Use the following command to add routing in React.

npm install react-router-dom --save    

Step 19

Now, open the Addstudent.js file and add the following code:

import React from 'react';  
import axios from 'axios';  
import '../Student/Addstudent.css'  
import { Container, Col, Form, Row, FormGroup, Label, Input, Button } from 'reactstrap';  
class Addstudent extends React.Component{  
constructor(props){  
super(props)  
this.state = {  
Name:'',  
RollNo:'',  
Class:'',  
Address:''  
}  
}   
Addstudent=()=>{  
  axios.post('http://localhost:52564/Api/Student/AddotrUpdatestudent/', {Name:this.state.Name,RollNo:this.state.RollNo,  
  Class:this.state.Class, Address:this.state.Address})  
.then(json => {  
if(json.data.Status==='Success'){  
  console.log(json.data.Status);  
  alert("Data Save Successfully");  
this.props.history.push('/Studentlist')  
}  
else{  
alert('Data not Saved');  
debugger;  
this.props.history.push('/Studentlist')  
}  
})  
}  

handleChange= (e)=> {  
this.setState({[e.target.name]:e.target.value});  
}  

render() {  
return (  
   <Container className="App">  
    <h4 className="PageHeading">Enter Student Informations</h4>  
    <Form className="form">  
      <Col>  
        <FormGroup row>  
          <Label for="name" sm={2}>Name</Label>  
          <Col sm={10}>  
            <Input type="text" name="Name" onChange={this.handleChange} value={this.state.Name} placeholder="Enter Name" />  
          </Col>  
        </FormGroup>  
        <FormGroup row>  
          <Label for="address" sm={2}>RollNo</Label>  
          <Col sm={10}>  
            <Input type="text" name="RollNo" onChange={this.handleChange} value={this.state.RollNo} placeholder="Enter RollNo" />  
          </Col>  
        </FormGroup>  
        <FormGroup row>  
          <Label for="Password" sm={2}>Class</Label>  
          <Col sm={10}>  
            <Input type="text" name="Class" onChange={this.handleChange} value={this.state.Class} placeholder="Enter Class" />  
          </Col>  
        </FormGroup>  
        <FormGroup row>  
          <Label for="Password" sm={2}>Address</Label>  
          <Col sm={10}>  
            <Input type="text" name="Address" onChange={this.handleChange} value={this.state.Address} placeholder="Enter Address" />  
          </Col>  
        </FormGroup>  
      </Col>  
      <Col>  
        <FormGroup row>  
          <Col sm={5}>  
          </Col>  
          <Col sm={1}>  
          <button type="button" onClick={this.Addstudent} className="btn btn-success">Submit</button>  
          </Col>  
          <Col sm={1}>  
            <Button color="danger">Cancel</Button>{' '}  
          </Col>  
          <Col sm={5}>  
          </Col>  
        </FormGroup>  
      </Col>  
    </Form>  
  </Container>  
);  
}  

}  

export default Addstudent;  

Add a new file  Addstudet.css file and add the following CSS classes. Import this file in the  Addstudent.js component.

.PageHeading    
{    
  margin-top: 10px;    
  margin-bottom: 10px;    
  color :black !important;    
}    

Step 20

Now, add a Table.js file and add the following code:

import React, { Component } from 'react';  
import axios from 'axios';  
import { Link } from 'react-router-dom';  
class Table extends Component {  
  constructor(props) {  
    super(props);  
    }  

    DeleteStudent= () =>{  
     axios.delete('http://localhost:52564/Api/Student/Deletestudent?id='+this.props.obj.Id)  
    .then(json => {  
    if(json.data.Status==='Delete'){  
    alert('Record deleted successfully!!');  
    }  
    })  
    }  
  render() {  
    return (  
        <tr>  
          <td>  
            {this.props.obj.Name}  
          </td>  
          <td>  
            {this.props.obj.RollNo}  
          </td>  
          <td>  
            {this.props.obj.Class}  
          </td>  
          <td>  
            {this.props.obj.Address}  
          </td>  
          <td>  
          <Link to={"/edit/"+this.props.obj.Id} className="btn btn-success">Edit</Link>  
          </td>  
          <td>  
            <button type="button" onClick={this.DeleteStudent} className="btn btn-danger">Delete</button>  
          </td>  
        </tr>  
    );  
  }  
}  

export default Table;  

Step 21

Now, add a studentlist.js file and add the following code.

import React, { Component } from 'react';  
import axios from 'axios';  
import Table from './Table';  

export default class Studentlist extends Component {  

  constructor(props) {  
      super(props);  
      this.state = {business: []};  
    }  
    componentDidMount(){  
      debugger;  
      axios.get('http://localhost:52564/Api/Student/Studentdetails')  
        .then(response => {  
          this.setState({ business: response.data });  
          debugger;  

        })  
        .catch(function (error) {  
          console.log(error);  
        })  
    }  

    tabRow(){  
      return this.state.business.map(function(object, i){  
          return <Table obj={object} key={i} />;  
      });  
    }  

    render() {  
      return (  
        <div>  
          <h4 align="center">Student List</h4>  
          <table className="table table-striped" style={{ marginTop: 10 }}>  
            <thead>  
              <tr>  
                <th>Name</th>  
                <th>RollNo</th>  
                <th>Class</th>  
                <th>Address</th>  
                <th colSpan="4">Action</th>  
              </tr>  
            </thead>  
            <tbody>  
             { this.tabRow() }   
            </tbody>  
          </table>  
        </div>  
      );  
    }  
  }  

Step 22

Add EditStudent.js file with the following code:

import React from 'react';   
import { Container, Col, Form, Row, FormGroup, Label, Input, Button } from 'reactstrap';  
import axios from 'axios'  
import '../Student/Addstudent.css'  
class Edit extends React.Component {  
    constructor(props) {  
        super(props)  

    this.onChangeName = this.onChangeName.bind(this);  
    this.onChangeRollNo = this.onChangeRollNo.bind(this);  
    this.onChangeClass = this.onChangeClass.bind(this);  
    this.onChangeAddress = this.onChangeAddress.bind(this);  
    this.onSubmit = this.onSubmit.bind(this);  

         this.state = {  
            Name: '',  
            RollNo: '',  
            Class: '',  
            Address: ''  

        }  
    }  

  componentDidMount() {  
      axios.get('http://localhost:52564/Api/Student/StudentdetailById?id='+this.props.match.params.id)  
          .then(response => {  
              this.setState({   
                Name: response.data.Name,   
                RollNo: response.data.RollNo,  
                Class: response.data.Class,  
                Address: response.data.Address });  

          })  
          .catch(function (error) {  
              console.log(error);  
          })  
    }  

  onChangeName(e) {  
    this.setState({  
        Name: e.target.value  
    });  
  }  
  onChangeRollNo(e) {  
    this.setState({  
        RollNo: e.target.value  
    });    
  }  
  onChangeClass(e) {  
    this.setState({  
        Class: e.target.value  
    });  
}  
    onChangeAddress(e) {  
        this.setState({  
            Address: e.target.value  
        });  
  }  

  onSubmit(e) {  
    debugger;  
    e.preventDefault();  
    const obj = {  
        Id:this.props.match.params.id,  
      Name: this.state.Name,  
      RollNo: this.state.RollNo,  
      Class: this.state.Class,  
      Address: this.state.Address  

    };  
    axios.post('http://localhost:52564/Api/Student/AddotrUpdatestudent/', obj)  
        .then(res => console.log(res.data));  
        debugger;  
        this.props.history.push('/Studentlist')  
  }  
    render() {  
        return (  
            <Container className="App">  

             <h4 className="PageHeading">Update Student Informations</h4>  
                <Form className="form" onSubmit={this.onSubmit}>  
                    <Col>  
                        <FormGroup row>  
                            <Label for="name" sm={2}>Name</Label>  
                            <Col sm={10}>  
                                <Input type="text" name="Name" value={this.state.Name} onChange={this.onChangeName}  
                                placeholder="Enter Name" />  
                            </Col>  
                        </FormGroup>  
                        <FormGroup row>  
                            <Label for="Password" sm={2}>RollNo</Label>  
                            <Col sm={10}>  
                                <Input type="text" name="RollNo" value={this.state.RollNo} onChange={this.onChangeRollNo} placeholder="Enter RollNo" />  
                            </Col>  
                        </FormGroup>  
                         <FormGroup row>  
                            <Label for="Password" sm={2}>Class</Label>  
                            <Col sm={10}>  
                                <Input type="text" name="Class" value={this.state.Class} onChange={this.onChangeClass} placeholder="Enter Class" />  
                            </Col>  
                        </FormGroup>  
                         <FormGroup row>  
                            <Label for="Password" sm={2}>Address</Label>  
                            <Col sm={10}>  
                                <Input type="text" name="Address"value={this.state.Address} onChange={this.onChangeAddress} placeholder="Enter Address" />  
                            </Col>  
                        </FormGroup>   
                    </Col>  
                    <Col>  
                        <FormGroup row>  
                            <Col sm={5}>  
                            </Col>  
                            <Col sm={1}>  
                          <Button type="submit" color="success">Submit</Button>{' '}  
                            </Col>  
                            <Col sm={1}>  
                                <Button color="danger">Cancel</Button>{' '}  
                            </Col>  
                            <Col sm={5}>  
                            </Col>  
                        </FormGroup>  
                    </Col>  
                </Form>  
            </Container>  
        );  
    }  

}  

export default Edit;  

Step 23

Open the App.js file with the following code in it.

import React from 'react';  
import Addstudent from './Student/Addstudent';  
import Studentlist from './Student/Studentlist';  
import EditStudent from './Student/EditStudent';  
import { BrowserRouter as Router, Switch, Route, Link } from 'react-router-dom';  
import './App.css';  
function App() {  
  return (  
    <Router>  
      <div className="container">  
        <nav className="navbar navbar-expand-lg navheader">  
          <div className="collapse navbar-collapse" >  
            <ul className="navbar-nav mr-auto">  
              <li className="nav-item">  
                <Link to={'/Addstudent'} className="nav-link">Addstudent</Link>  
              </li>  
              <li className="nav-item">  
                <Link to={'/Studentlist'} className="nav-link">Student List</Link>  
              </li>  
            </ul>  
          </div>  
        </nav> <br />  
        <Switch>  
          <Route exact path='/Addstudent' component={Addstudent} />  
          <Route path='/edit/:id' component={EditStudent} />  
          <Route path='/Studentlist' component={Studentlist} />  
        </Switch>  
      </div>  
    </Router>  
  );  
}  

export default App;  

Add the following CSS classes in the App.css file.

.App {    
  text-align: center;    
}    
.navheader{    
  margin-top: 10px;    
  color :black !important;    
  background-color: #b3beca!important    
}    

Now, run the application by using the  npm start command and check the result.

Student List application view

Click on the Add Student button to add a new record into the database.

Adding a new student into the database

Click on the Edit button to update a record.

Updating a record in the database

Click on the Delete button to delete a record.

Deleting a record in the database

Summary

In this article, we learned how to perform CRUD operations using React, Asp.net Web API, and SQL Server.

Thank for reading. Please share if you liked it!

Build a CRUD App with ASP.NET Core 2.2 and SQL Server

Build a CRUD App with ASP.NET Core 2.2 and SQL Server

​ I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created *JudgeMyTaste*, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly. ​ The combination of ASP.NET and SQL Server is probably the most common pairing in the enterprises that use ASP.NET. With ASP.NET Core and SQL Server both being cross-platform, you don’t *have* to run this combination on Windows anymore! I’ll show you how to create a basic CRUD application using ASP.NET Core 2.2 and SQL Server 2017. I’ll be running on Linux, but with the free tools used here, it won’t matter what operating system you’re using! ​ The tools I’ll be using that are available for all platforms are: * SQL Server 2017 (I’ll be running on Ubuntu 18.04) * Visual Studio Code * Azure Data Studio * ASP.NET Core 2.2 ​ Once you’ve got all the tools installed for your platform, let’s rock and roll! ​ ​ ​ ## Scaffold Your ASP.NET Core 2.2 Application ​ No matter the platform you’re on, the ```dotnet``` CLI is available. The commands used here should be the same for everyone. To scaffold the ASP.NET Core 2.2 MVC application, create a new folder for it: I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created JudgeMyTaste, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly.

I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created JudgeMyTaste, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly.

The combination of ASP.NET and SQL Server is probably the most common pairing in the enterprises that use ASP.NET. With ASP.NET Core and SQL Server both being cross-platform, you don’t have to run this combination on Windows anymore! I’ll show you how to create a basic CRUD application using ASP.NET Core 2.2 and SQL Server 2017. I’ll be running on Linux, but with the free tools used here, it won’t matter what operating system you’re using!

The tools I’ll be using that are available for all platforms are:

  • SQL Server 2017 (I’ll be running on Ubuntu 18.04)
  • Visual Studio Code
  • Azure Data Studio
  • ASP.NET Core 2.2

Once you’ve got all the tools installed for your platform, let’s rock and roll!

Scaffold Your ASP.NET Core 2.2 Application

No matter the platform you’re on, the dotnet CLI is available. The commands used here should be the same for everyone. To scaffold the ASP.NET Core 2.2 MVC application, create a new folder for it:

mkdir JudgeMyTaste

Change into that new directory:

cd JudgeMyTaste

Then run the following command:

dotnet new mvc

Then open the new application in VS Code.

code .

When you open the new application in VS Code, you should get a warning in the bottom right corner asking to add some missing assets. Go ahead and add the missing assets. You’ll see the .vscode folder added with a launch.json and a tasks.json file.

These will allow you to run the application from VS Code. To verify that everything scaffolded properly, run the base application by typing F5. This will build the application, run it, and open it in a new browser window.

You may notice a strange error page come up, if you’ve never run an ASP.NET Core 2.x application before. By default ASP.NET Core wants to run on HTTPS. This is a recommended practice for web applications. You could avoid this message by removing the redirect to HTTPS in your Startup.cs or by generating a certificate for your local machine, but this error screen only comes up once in a great while, so I just side step it by clicking on Advanced and telling the browser that it’s okay to visit this site even though there is no certificate for it.

For your daily work, it will probably behoove you to create a local certificate for development so that you never have to see this message again.

Create Your SQL Server Database

Open Azure Data Studio and connect to your localhost server with the SA password you created when installing SQL Server on your machine. You’ll notice it is arranged very much like VS Code. In the Connections Explorer, you will see localhost as a connection. Right-click on the connection and choose New Query, which will open a new query window on the right side. Start typing the word CREATE and an intellisense drop down will open and one of the choices will be sqlCreateDatabase. Choose that option and a query will be scaffolded with the database name highlighted in the three places that it occurs in the query. You can just start typing the database name “JudgeMyTaste” and it will be replaced in all three places so that the final query looks like this.

-- Create a new database called 'JudgeMyTaste'
-- Connect to the 'master' database to run this snippet
USE master
GO
-- Create the new database if it does not exist already
IF NOT EXISTS (
  SELECT [name]
    FROM sys.databases
    WHERE [name] = N'JudgeMyTaste'
)
CREATE DATABASE JudgeMyTaste
GO

Now you can just click the green Run arrow at the top of the window to create the database. Simple, no?

Now when you expand the Databases folder in the Connection Explorer, you will see the JudgeMyTaste database in the list. Right-click on the new database and choose New Query again. Start typing CREATE again and this time choose sqlCreateTable from the options presented. Again, you can start typing the table name FavoriteBands and it will be filled in all the places it occurs in the query.

You’ll also need to add some other columns to the table. Add the columns for Id, Name, EnteredBy, and EnteredOn so that the query looks like this:

-- Create a new table called '[FavoriteBands]' in schema '[dbo]'
-- Drop the table if it already exists
IF OBJECT_ID('[dbo].[FavoriteBands]', 'U') IS NOT NULL
DROP TABLE [dbo].[FavoriteBands]
GO
-- Create the table in the specified schema
CREATE TABLE [dbo].[FavoriteBands](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](255) NULL,
  [EnteredBy] [varchar](255) NULL,
  [EnteredOn] [date] NULL
);
GO

Then run the query by clicking the green Run arrow as before.

It’s good practice to create a user specifically for your application to connect with the database. One that only has the permissions that it will need to interact with your database. Here’s a script to create a login and a user for the database and assign that user dbo permissions to the database.

USE master

GO

CREATE LOGIN webapp WITH PASSWORD=N'[email protected]!', DEFAULT_DATABASE=JudgeMyTaste

GO

ALTER LOGIN webapp ENABLE

GO

USE JudgeMyTaste

GO

CREATE USER webapp FOR LOGIN webapp
EXEC sp_addrolemember 'db_owner', 'webapp'

GO

It might seem like a lot going on here, but it simply creates a login for SQL Server, makes that login a user for the JudgeMyTaste database, and add it to the db_owner role for the database. This will allow that login to do all the CRUD operations that the application will need. Now your database is ready to be used by your application!

Connect SQL Server to Your ASP.NET Core 2.2 MVC Application

Before anything else, you’ll need the Entity Framework Core NuGet package. To install it, run the following command in the terminal.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 2.2.4

Start by adding the connection string to your appsettings.json file in the root of your MVC project, so that it looks like this:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "JudgeMyTasteDatabase": "Server=.;Database=JudgeMyTaste;user id=webapp;[email protected]!"
  }
}

In the Models folder, create a class file called FavoriteBand.cs.

using System;
using System.ComponentModel.DataAnnotations;

namespace JudgeMyTaste.Models
{
  public class FavoriteBand
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string EnteredBy { get; set; }
    public DateTime EnteredOn { get; set; }
  }
}

This class will be used to work with the FavoriteBand entries.

In the root of the project, create a folder called Data to house the database context for the application. Create a C# file called JudgeMyTasteContext.cs with the following contents:

using JudgeMyTaste.Models;
using Microsoft.EntityFrameworkCore;

namespace JudgeMyTaste.Data
{
  public class JudgeMyTasteContext : DbContext
  {
    public JudgeMyTasteContext(DbContextOptions<JudgeMyTasteContext> options) : base(options)
    {
    }

    public DbSet<FavoriteBand> FavoriteBands { get; set; }
  }
}

In your Startup.cs file, in the ConfigureServices() method, right before the services.AddMvc()... line, add the newly created context with the connection string.

services.AddDbContext<JudgeMyTasteContext>(options => options.UseSqlServer(Configuration.GetConnectionString("JudgeMyTasteDatabase")));

Now your database is all hooked into your application. All you need to do is create some way for the user to enter their favorite bands. To get some more scaffolding goodness for the CLI, install the Code Generation tool.

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

Now you can scaffold a controller to handle all the CRUD operations for the FavoriteBand class by running the following command from the terminal.

dotnet aspnet-codegenerator controller -name FavoriteBandsController -async -m JudgeMyTaste.Models.FavoriteBand -dc JudgeMyTaste.Data.JudgeMyTasteContext -namespace Controllers -outDir Controllers -udl

This is a long one but if you break it down into its component pieces, it’s easier to understand.

The first part just calls the dotnet CLI’s new aspnet-codegenerator command for a controller. You want the controller’s name to be “FavoriteBandsController” and for the controller actions to all be -async. The model being used to generate the controller is the JudgeMyTaste.Models.FavoriteBand class, and the database context will be the JudgeMyTaste.Data.JudgeMyTasteContext class you just created. The namespace and output directory for the controller will be Controllers and the -udl switch tells the generator to use the default layout for the views it will generate (yeah, it’s going to generate views for everything too!). Pretty cool, right?

Once you run the command, you should see the controller and all it’s views show up. The only thing left is to create a link so that users can get to the favorite bands section of the site easily.

In the Views/Shared folder open the Layout.cshtml file and add a link to the menu to get to the new section of the site.

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

Now when you run the application, you can click on the Favorite Bands menu item and see a list of all the favorite bands that have been entered. Of course there aren’t any right now, so add one using the Create New link at the top of the page and see it show up in the listing.

Now it’s a little cumbersome to add the EnteredOn value manually, and the code generator you used can’t know that you can just add that field to the entry as it’s being saved, so change the Create() method of the FavoriteBandController to add it automatically.

// POST: FavoriteBands/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,Name,EnteredBy")] FavoriteBand favoriteBand)
{
  if (ModelState.IsValid)
  {
    favoriteBand.EnteredOn = DateTime.Now;
    _context.Add(favoriteBand);
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
  }
  return View(favoriteBand);
}

The only things that have changed is that I removed the EnteredOn field from the Bind statement in the method signature, and I added the value DateTime.Now as the value right before saving it to the database.

Add Authentication to Your ASP.NET Core 2.2 MVC + SQL Server Application

What you have now is okay, but there’s currently no way to keep users from editing other user’s entries. We want to make sure to judge people for their favorite band that they actually entered, right?

No reason to write this yourself. You can easily integrate Okta to handle the authentication for you and easily:

Sign up for a forever-free developer account (or log in if you already have one).

Once you have signed up and logged in, you’ll be taken to your dashboard. Make note of your Org URL in the top right corner.

Click on the Applications menu item at the top, click Add Application, and from the first page of the wizard choose Web and click Next.

On the next screen, change the application name to “Judge My Taste App” and update the Base URIs value and the Login Redirect URIs to reflect to the correct port and the fact that you’re running on the HTTPS scheme.

Then click Done and you’re taken to the application page. On the General Settings tab click Edit and add a URL to the Logout Redirect URIs with a value of <a href="https://localhost:5001/signout/callback" target="_blank">https://localhost:5001/signout/callback</a>. This is where Okta will redirect back to after the logout call. This is handled by the ASP.NET OIDC Middleware.

Configure Your ASP.NET Core 2.2 MVC Application for Authentication

Now you need to tell your application how to use Okta for authentication. The easiest way is to use the ASP.NET SDK from Okta. You can install it from NuGet using the following command:

dotnet add package Okta.AspNetCore --version 1.1.5

Add some configuration values to your appsettings.json file to that the final file looks like this:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "JudgeMyTasteDatabase": "Server=.;Database=JudgeMyTaste;user id=webapp;[email protected]!"
  },
  "Okta": {
    "ClientId": "{yourClientId}",
    "ClientSecret": "{yourClientSecret}",
    "OktaDomain": "https://{yourOktaDomain}",
    "PostLogoutRedirectUri": "https://localhost:5001/"
  }
}

This PostLogoutRedirectUri is the URL that the middleware will redirect to once Okta has redirected back to the signout/callback URL. You can use any valid URL in the MVC application. Here, I am just redirecting to the root of the application.

Back in the Startup.cs file, add the following using statements:

using Okta.AspNetCore;
using Microsoft.AspNetCore.Authentication.Cookies;

Then at the very beginning of the ConfigureServices() method add:

var oktaMvcOptions = new OktaMvcOptions();
Configuration.GetSection("Okta").Bind(oktaMvcOptions);
oktaMvcOptions.Scope = new List<string> { "openid", "profile", "email" };
oktaMvcOptions.GetClaimsFromUserInfoEndpoint = true;

services.AddAuthentication(options =>
{
  options.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme;
  options.DefaultSignInScheme = CookieAuthenticationDefaults.AuthenticationScheme;
  options.DefaultChallengeScheme = OktaDefaults.MvcAuthenticationScheme;
})
.AddCookie()
.AddOktaMvc(oktaMvcOptions);

This is a pretty dense chunk of code, but most of it is boilerplate for the OIDC middleware that the Okta SDK is built on. The first part just binds all of those configuration values you just added in appsettings.json to the oktaMvcOptions. It also adds the scopes you want to receive (which are the OpenID information, the user’s profile, and the user’s email address). It also tells the middleware that it can get the claims from the user info endpoint, which all OIDC identity providers have.

When the code adds authentication, it tells the OIDC provider to use cookies for storing tokens and that you’ll be sending users to Okta from an MVC application.

To actually wire up authentication, you need to tell the Configure() method to use this service you just configured. Right before the app.UseMvc(...) line, add:

app.UseAuthentication();

Okta is now configured in your application! You still need to set up your application to challenge the user (send them to Okta to authenticate).

Create a new controller in the Controllers folder called AccountController with the following code:

using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Mvc;
using Okta.AspNetCore;

namespace JudgeMyTaste.Controllers
{
  public class AccountController : Controller
  {
    public IActionResult Login()
    {
      if (!HttpContext.User.Identity.IsAuthenticated)
      {
        return Challenge(OktaDefaults.MvcAuthenticationScheme);
      }
      return RedirectToAction("Index", "Home");
    }

    public IActionResult Logout()
    {
      return new SignOutResult(new[]
      {
        OktaDefaults.MvcAuthenticationScheme,
        CookieAuthenticationDefaults.AuthenticationScheme
      });
    }
  }
}

This will give you a Login() and Logout() method to wire up some menu items. Speaking of which, add a new view in Views/Shared called _LoginPartial.cshtml. This will house all the code for the login menu items.

@if (User.Identity.IsAuthenticated)
{
  <ul class="navbar-nav ml-auto">
    <li>
      <span class="navbar-text">Hello, @User.Identity.Name</span> &nbsp;
      <a onclick="document.getElementById('logout_form').submit();" style="cursor: pointer;">Log out</a>
    </li>
  </ul>
  <form asp-controller="Account" asp-action="Logout" method="post" id="logout_form"></form>
}
else
{
  <ul class="navbar-nav">
    <li><a asp-controller="Account" asp-action="Login">Log in</a></li>
  </ul>
}

Change the main menu in Views/Shared/_Layout.cshtml to add this in and move the main menu to the left and have the login menu on the far right. The final div that houses the menu should look like this:

<div class="navbar-collapse collapse justify-content-between">
  <ul class="navbar-nav mr-auto">
    <li class="nav-item">
      <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
    </li>
    <li class="nav-item">
      <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
    </li>
    <li class="nav-item">
      <a class="nav-link text-dark" asp-area="" asp-controller="FavoriteBands" asp-action="Index">Favorite Bands</a>
    </li>
  </ul>
  <partial name="_LoginPartial" />
</div>

The class list for the navbar-collapse has changed to add the justify-content-between class that will keep the menus apart. The ul’s class also changed to mr-auto which will help keep it left. Lastly, the login partial is added at the end of the menu.

Don’t just sit there, fire this thing up and judge me for liking Nickleback!

Now you have a complete CRUD slice built in an ASP.NET Core 2.2 MVC application saving data to a SQL Server database! Now you can take the same path to add things like favorite movie, favorite food, and favorite beverage so that you can easily and completely judge people for their taste online!!!