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

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');

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();
            });
        }
    }
}

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; }
    }
}

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

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.