Developing A Web App Using Angular 12, ASP.NET Core Web API And SQL Server

Introduction

In this article we will develop a Web application from scratch using the latest technologies in an easy and understandable way. 

• Back-end = ASP.NET Core Web API
• Database = SQL Server 
• Front-end = ANGULAR 12.

First, we will create a database, tables and insert some data.

Second, we develop APIs using ASP.NET Core Web API.

Third, we develop the frontend using angular 12.

Step 1 - (Database Related Activities)

After opening SQL Server Management Studio and connecting to a local database, create a database named StudentDB.

CREATE DATABASE StudentDB;	
CREATE TABLE dbo.Student (
    StudentId int IDENTITY(1,1) NOT NULL,
    FullName nvarchar(max) NULL,
    Class nvarchar(max) NULL
);
	
INSERT INTO dbo.Student VALUES ('Litan Sarker', 'Nine');	
INSERT INTO dbo.Student VALUES ('Rohit Sarker','Six');

Step 2 - (Web API Related Activities)

Now, create a new project after opening Visual Studio 2019.

Step 3 - Now change the startup class

Firstly, install these,

• Microsoft.AspNetCore.Mvc.NewtonsoftJson
• System.Data.SqlClient

from Manage Nuget Packages for JSON Serialization and database.

We enable CORS to disable the security and allowing requests from other domains. After installing NewtonSoft.json, we use JSON serializer as our default.

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Newtonsoft.Json.Serialization;

namespace WebAPI
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //Enable Cors
            services.AddCors(c =>
            {
                c.AddPolicy("AllowOrigin", options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
            });

            //Json Serializer
            services.AddControllersWithViews()
                .AddNewtonsoftJson(options =>
                options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                ).AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver
                = new DefaultContractResolver());

            services.AddControllers();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

Step 4 

Now, create a folder named Models and add two class files named Student.cs, department.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebAPI.Models
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FullName { get; set; }
        public string Class { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebAPI.Models
{
    public class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
    }
}

Now, open the appsettings.json file and replace it with the following,

{
  "ConnectionStrings": {
    "StudentAppCon": "Data Source=.; Initial Catalog=StudentDB; Integrated Security=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Step 6

Now, add StudentController.

Now, make the following changes in the StudentController, here we implement dependency injection and perform CRUD operations using raw SQL queries.

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using WebAPI.Models;

namespace WebAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class StudentController : ControllerBase
    {
        private readonly IConfiguration _configuration;
        public StudentController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        [HttpGet]
        public JsonResult Get()
        {
            string query = @"Select StudentId, FullName, Class from dbo.Student";
            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("StudentAppCon");
            SqlDataReader myReader;
            using(SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);

                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult(table);
        }


        [HttpPost]
        public JsonResult Post(Student objStudent)
        {
            string query = @"Insert into dbo.Student values 
                ('" + objStudent.FullName + "','" + objStudent.Class + "')";
            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("StudentAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);

                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult("Added Successfully");
        }

        [HttpPut]
        public JsonResult Put(Student objStudent)
        {
            string query = @"Update dbo.Student set 
                FullName = '" + objStudent.FullName + @"',
                Class='" + objStudent.Class + "' where StudentId = " + objStudent.StudentId;
            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("StudentAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);

                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult("Updated Successfully");
        }


        [HttpDelete("{id}")]
        public JsonResult Delete(int id)
        {
            string query = @"Delete from dbo.Student where StudentId = " + id;
            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("StudentAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);

                    myReader.Close();
                    myCon.Close();
                }
            }

            return new JsonResult("Deleted Successfully");
        }

    }
}

Step 7 - Angular (Front End) related activities

Now, let's create an Angular project.

At first, install Node.js from https://nodejs.org/en/download/ and angular CLI by following way,

npm install -g @angular/cli
        
Now in a specific folder, open a command prompt and create an angular project by following ways,

ng new angular12

? Would you like to add Angular routing? (y/N) y

> CSS 

It will take few minutes to complete.

Now, create components by following commands,

• ng g c student
• ng g c student/show-stu
• ng g c student/add-edit-stu

Then, open app.module.ts and modify it with the following code,

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';

import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { StudentComponent } from './student/student.component';
import { ShowStuComponent } from './student/show-stu/show-stu.component';
import { AddEditStuComponent } from './student/add-edit-stu/add-edit-stu.component';

import { HttpClientModule } from "@angular/common/http";
import { SharedService } from "./shared.service";
import { FormsModule,ReactiveFormsModule } from "@angular/forms";
import { DepartmentComponent } from './department/department.component';
import { ShowDepComponent } from './department/show-dep/show-dep.component';
import { AddEditDepComponent } from './department/add-edit-dep/add-edit-dep.component';

@NgModule({
  declarations: [
    AppComponent,
    StudentComponent,
    ShowStuComponent,
    AddEditStuComponent,
    DepartmentComponent,
    ShowDepComponent,
    AddEditDepComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    HttpClientModule,
    FormsModule,
    ReactiveFormsModule
  ],
  providers: [SharedService],
  bootstrap: [AppComponent]
})
export class AppModule { }

Now, let's create a shared service.

ng g s shared

Import the HttpCient and observables modules. Observables are used to handle asynchronous requests and responses.

import { Injectable } from '@angular/core';
import { HttpClient } from "@angular/common/http";
import { Observable } from "rxjs";

@Injectable({
  providedIn: 'root'
})
export class SharedService {
    readonly APIUrl = "http://localhost:5000/api";
    constructor(private http: HttpClient) {}
    getStudentList(): Observable < any[] > {
        return this.http.get < any > (this.APIUrl + '/Student');
    }
    addStudent(val: any) {
        return this.http.post(this.APIUrl + '/Student', val);
    }
    updateStudent(val: any) {
        return this.http.put(this.APIUrl + '/Student', val);
    }
    deleteStudent(id: any) {
        return this.http.delete(this.APIUrl + '/Student/' + id);
    }
    getDepartmentList(): Observable < any[] > {
        return this.http.get < any > (this.APIUrl + '/Department');
    }
    addDepartment(val: any) {
        return this.http.post(this.APIUrl + '/Department', val);
    }
    updateDepartment(val: any) {
        return this.http.put(this.APIUrl + '/Department', val);
    }
    deleteDepartment(id: any) {
        return this.http.delete(this.APIUrl + '/Department/' + id);
    }
}

Step 9

Now, make the following changes in the app.routing.ts file.

import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';
import { StudentComponent } from "./student/student.component";
import { DepartmentComponent } from "./department/department.component";

const routes: Routes = [
  {path:'student', component:StudentComponent},
  {path:'department', component:DepartmentComponent}
];

@NgModule({
  imports: [RouterModule.forRoot(routes)],
  exports: [RouterModule]
})
export class AppRoutingModule { }

Step 10

Add the bootstrap files for the design.

Open index.html and changes it with BootStrap installation.

<!doctype html>
<html lang="en">
<head>
  <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
  <meta charset="utf-8">
  <title>Angular12</title>
  <base href="/">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
</head>
<body>
  <app-root></app-root>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script>
</body>
</html>

 Now open student.component.html and replace it with the below code,

<app-show-stu></app-show-stu>

Now, open show-stu.component.ts and replace it with the below code

import { Component, OnInit } from '@angular/core';
import { SharedService } from "src/app/shared.service";

@Component({
  selector: 'app-show-stu',
  templateUrl: './show-stu.component.html',
  styleUrls: ['./show-stu.component.css']
})
export class ShowStuComponent implements OnInit {
  studentList:any = [];
  modalTitle:any;
  activateAddEditStuCom:boolean = false;
  student:any;
  
  constructor(private sharedService: SharedService) { }

  ngOnInit(): void {
    this.refreshStudentList();
  }

  refreshStudentList() {
    this.sharedService.getStudentList().subscribe(data =>{
      this.studentList = data;
    });  
  }

  AddStudent(){
    this.student={
      StudentId:0,
      FullName:"",
      Class:""
    }
    this.modalTitle = "Add Student";
    this.activateAddEditStuCom = true;
  }

  EditStudent(item: any){
    this.student = item;
    this.activateAddEditStuCom = true;
    this.modalTitle = "Update Student";
  }

  deleteClick(item: any){
    if(confirm('Are you sure??')){
      this.sharedService.deleteStudent(item.StudentId).subscribe(data =>{
        alert(data.toString());
        this.refreshStudentList();
      })
    }
  }

  closeClick(){
    this.activateAddEditStuCom=false;
    this.refreshStudentList();
  }
}

Now, open show-stu.component.html and replace it with the below code

<!-- Button trigger modal -->
<button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#exampleModal"
(click)="AddStudent()" data-keyborad="false">
  Add Student
</button>

<!-- Modal -->
<div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">{{modalTitle}}</h5>
        <button type="button" class="btn-close" (click)="closeClick()" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <app-add-edit-stu [student]="student" *ngIf="activateAddEditStuCom">          
        </app-add-edit-stu>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
        <button type="button" class="btn btn-primary">Save changes</button>
      </div>
    </div>
  </div>
</div>

<table class="table table-striped">
    <thead>
      <tr>
          <th>Student ID</th>
          <th>Full Name</th>
          <th>Class</th>
          <th>Options</th>
      </tr>
    </thead>
    <tbody>
      <tr *ngFor="let dataItem of studentList">
        <td>{{dataItem.StudentId}}</td>
        <td>{{dataItem.FullName}}</td>
        <td>{{dataItem.Class}}</td>
        <td>
          <button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#exampleModal"
          (click)="EditStudent(dataItem)">Edit</button>
            <button type="button" class="btn btn-primary" (click)="deleteClick(dataItem)">Delete</button>
        </td>
      </tr>
    </tbody>
  </table>

Now, open add-edit-stu.component.ts and replace it with the below code

import { Component, OnInit, Input } from '@angular/core';
import { SharedService } from "src/app/shared.service";

@Component({
  selector: 'app-add-edit-stu',
  templateUrl: './add-edit-stu.component.html',
  styleUrls: ['./add-edit-stu.component.css']
})
export class AddEditStuComponent implements OnInit {

  @Input() student:any;
  StudentId:string = "";
  FullName: string ="";
  Class: string ="";

  constructor(private service: SharedService) { }

  ngOnInit(): void {
    this.StudentId = this.student.StudentId;
    this.FullName = this.student.FullName;
    this.Class = this.student.Class;
  }

  addStudent(){
    var val = {StudentId:this.StudentId,
      FullName:this.FullName,
      Class:this.Class};
      this.service.addStudent(val).subscribe(res =>{
        alert(res.toString());
      })
  }

  updateStudent(){
    var val = {StudentId:this.StudentId,
      FullName:this.FullName,
      Class:this.Class};
      this.service.updateStudent(val).subscribe(res =>{
        alert(res.toString());
    })
  }
}

Now, open add-edit-stu.component.html and replace it with the below code

<div class="form-group row">
    <label class="col-sm-2">Student FullName</label>
    <div class="col-sm-10">
        <input type="text" class="form-control" [(ngModel)]="FullName"
        placeholder="Enter Full Name">
    </div>
</div>

<div class="form-group row">
    <label class="col-sm-2">Class</label>
    <div class="col-sm-10">
        <input type="text" class="form-control" [(ngModel)]="Class"
        placeholder="Enter Class">
    </div>
</div>

<button (click)="addStudent()" *ngIf="student.StudentId==0" class="btn btn-primary">
    Add
</button>

<button (click)="updateStudent()" *ngIf="student.StudentId!=0" class="btn btn-primary">
    Update
</button>

Conclusion

In this article, we discussed step by step how to build an application using  .Net core angular 12 and SQL server. Hopefully, you will be benefitted from this article by reading and learning.