mithilesh kumar singh: CRUD Operation using Angular 2, MVC, Web API AND SQL in Visual Studio

Today, in this article, I will explain how to perform CRUD operation using Angular 2, MVC, Web API and SQL Server in Visual Studio. Simultaneously, we will see the Routing concept in Angular 2. Here, to perform the CRUD operation first we will create Web API service using Entity Framework and SQL. After that, we will create an MVC project for consuming the Web API service using Angular 2. So now, let us see step by step.

Step 1

First, we will create a table in SQL Database. So, we will open the SQL Database and then we have to create a table. Here I am creating an Employee table.

Step 2

Now, First, we will create Web API service for this we will open the Visual Studio and go to the File >> New >> Project >> Web Application

After that, it will open a the window then we will select Web API and press the OK button.

Step 3

Now we will add an API Controller. So Go the controller folder and right-click >> Add>> Controller >> Web Api2 Controller – Empty >> Add

Step 4

Here, we will add the table using Database First The approach in Entity Framework. So for that, we will go to the Model folder and Right click >> Add >> New Item >>Data >> ADO.NET Entity Data Model >> Add

Select EF Designer from the database and then we will click on Next Button. After that select New Connection

After open the window we will give Server Name >> SQL Authentication >> ID & Password >> Select Database>> Test Connection >> Ok >> Ok like in below image.

Click on the Next button. After that, we will check the Employee table and Click Finish Button like the below image.

Step 5

Now we will write the logic for Retrieve, Insert, Update and Delete operation to perform CRUD operation of Employee in Web API Controller. For this, Go in Controller folder and open EmployeeAPI Controller and Write below logic

[RoutePrefix("api/Employee")]

public class EmployeeAPIController : ApiController

{

ExampleDBEntities objEntity = new ExampleDBEntities();

//This method for retrive Employee

[HttpGet]

[Route("BindEmpDetails")]

public List<Employee> BindEmp()

{

List<Employee> empDetail = new List<Employee>();

empDetail = objEntity.Employees.ToList();

return empDetail;

}

//This method for Insert Employee

[HttpPost]

[Route("InsertEmp")]

public string InserEmp(Employee objEmployee)

{

objEntity.Employees.Add(objEmployee);

int i = objEntity.SaveChanges();

if (i > 0)

{

return "Insertion successfull";

}

else

{

return "Insertion Faild";

}

//This method for Update Employee

[HttpPut]

[Route("UpdateEmp")]

public string UpdateEmp(Employee data)

{

string objPost = string.Empty;

string message = "";

Employee objEmp = new Employee();

objEmp = objEntity.Employees.Find(data.EmployeeId);

if (objEmp != null)

{

objEmp.EmpName = data.EmpName;

objEmp.Address = data.Address;

objEmp.EmailId = data.EmailId;

objEmp.MobileNo = data.MobileNo;

objEmp.DeptName = data.DeptName;

}

int i = this.objEntity.SaveChanges();

if (i > 0)

{

message = "Updation success";

}

else

{

message = "Updation faild";

}

return message;

}

//This method for Delete Employee

[HttpDelete]

[Route("EmpDelete/{id}")]

public string EmployeeDelete(int id)

{

string strMsg = string.Empty;

var objEmp = objEntity.Employees.Find(id);

objEntity.Employees.Remove(objEmp);

int i = objEntity.SaveChanges();

if (i > 0)

{

strMsg = "Deletion Successful";

}

else

{

strMsg = "Deletion Faild";

}

return strMsg;

}

Now we completed API Part. We can see the folder architecture of the Web Api Project below.

Step 6

After completed API part. Now we will create a for consuming the web Api Service in MVC using Angular.

So, first we will add a MVC Project. Right click of Solution of AngularCRUDMVC >> Add >> New Project >> Asp.Net Web Application >> OK >> MVC >> Ok

Step 7

Now, we will configuration Angular 2 in Visual studio. Before the start the angular 2 functionality we have to install Node.js and npm if they are not already on your machine.

Now we will check Install Typescripts 2.2 for Visual Studio 2015 link if you have not installed typescript then follow this link.

Then we will download the QuickStart source from GitHub. For Details, you follow this link or for more details follow this link.

After download the QuickStart file we have to extract the file and after that, we will copy of the selected files in below image and paste in our visual studio root folder.

After that Open the src file and copy selected file in below image and paste in our visual studio root folder

Now check-in Visual Studio

Now Open the tsconfig.json file and add the below code

"noStrictGenericChecks": true

After that right click of package.json file and select Restore Package

After that wait few minutes it will install node_modules file

After that open the systemjs.config.js and replace with modifying with the below code or replace it according to our project structure.

/**

* System configuration for Angular samples

* Adjust as necessary for your application needs.

(function (global) {

System.config({

paths: {

// paths serve as alias

'npm:': '../node_modules/'

// map tells the System loader where to look for things

map: {

// our app is within the app folder

'app': '../app',

// angular bundles

'@angular/core': 'npm:@angular/core/bundles/core.umd.js',

'@angular/common': 'npm:@angular/common/bundles/common.umd.js',

'@angular/compiler': 'npm:@angular/compiler/bundles/compiler.umd.js',

'@angular/platform-browser': 'npm:@angular/platform-browser/bundles/platform-browser.umd.js',

'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/bundles/platform-browser-dynamic.umd.js',

'@angular/http': 'npm:@angular/http/bundles/http.umd.js',

'@angular/router': 'npm:@angular/router/bundles/router.umd.js',

'@angular/forms': 'npm:@angular/forms/bundles/forms.umd.js',

// other libraries

'rxjs': 'npm:rxjs',

'angular-in-memory-web-api': 'npm:angular-in-memory-web-api/bundles/in-memory-web-api.umd.js',

'ng2-bs3-modal': 'npm:/ng2-bs3-modal',

'Service': '/Service'

// packages tells the System loader how to load when no filename and/or no extension

packages: {

app: {

main: 'main.js', defaultExtension: 'js'

rxjs: {

defaultExtension: 'js'

'ng2-bs3-modal':

{ main: '/bundles/ng2-bs3-modal.js', defaultExtension: 'js' }

}

});

})(this);

Step 8

Now we add app folder in the root folder in of MVC Project and also we will add some more folder and files according to below image.

Now go to the app folder and right click >> add >> New Folder >> select folder and change name Model of the new folder and after that add employee.ts file. Right-click >> add >> Type Script File >> select file and change name employee.ts of the file and write below code

export interface IEmployee {

EmployeeId: number,

EmpName: string,

Address: string,

EmailId: string,

MobileNo: string,

DeptName: string

}

Now go to the app folder and right click >> add >> New Folder >> select folder and change name Service of the new folder and after that add employeeUrl.service.ts file.

Right click >> add >> TypeScript File >> select file and change name employeeUrl.service.ts of the file and add all CRUD related web api url.

export class MyApiUrl {

public static RetriveEmpDetails = 'http://localhost:62877/api/Employee/BindEmpDetails';

public static InsertEmpDetails = 'http://localhost:62877/api/Employee/InsertEmp';

public static UpdateEmpDetails = 'http://localhost:62877/api/Employee/UpdateEmp';

public static DeleteEmpDetails = 'http://localhost:62877/api/Employee/EmpDelete';

}

Again, we will add employee.service.ts. Right click >> add >> TypeScript File >> select file and change name employee.service.ts of the file and write below code.

import { Injectable } from '@angular/core';

import { Http, Response, Headers, RequestOptions } from '@angular/http';

import { Observable } from 'rxjs/Observable';

import 'rxjs/add/operator/map';

import 'rxjs/add/operator/do';

import 'rxjs/add/operator/catch';

@Injectable()

export class EmployeeService {

constructor(private _http: Http) { }

get(url: string): Observable<any> {

return this._http.get(url)

.map((response: Response) => <any>response.json())

.catch(this.handleError);

}

post(url: string, model: any): Observable<any> {

let body = JSON.stringify(model);

let headers = new Headers({ 'Content-Type': 'application/json' });

let options = new RequestOptions({ headers: headers });

return this._http.post(url, body, options)

.map((response: Response) => <any>response.json())

.catch(this.handleError);

}

put(url: string, id: number, model: any): Observable<any> {

let data = JSON.stringify(model);

let headers = new Headers({ 'Content-Type': 'application/json' });

let options = new RequestOptions({ headers: headers });

return this._http.put(url, data, options)

.map((response: Response) => <any>response.json())

.catch(this.handleError);

}

delete(url: string, id: number): Observable<any> {

let headers = new Headers({ 'Content-Type': 'application/json' });

let options = new RequestOptions({ headers: headers });

return this._http.delete(url + '/' + id, options)

.map((response: Response) => <any>response.json())

.catch(this.handleError);

}

private handleError(error: Response) {

console.error(error);

return Observable.throw(error.json().error || 'Server error');

}

Now go to the app folder and right click >> add >> TypeScript File >> select file and change name main.ts of the file and write below code.

import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';

import { AppModule } from './app.module';

platformBrowserDynamic().bootstrapModule(AppModule);

After that we will add components folder in-app folder so right click of app folder add a folder and change the folder name to components.

Now, we will add home.component.ts in Components folder, go to the Components folder and right click >> add >> TypeScript File >> select file and change name home.component.ts of the file and write below code.

import { Component } from "@angular/core";

@Component({

template: `<img src="../../images/home-page.jpg" style="text-align:center"/>`

})

export class HomeComponent {

}

Now, we will add aboutUs.component.ts in Components folder, go to the Components folder and right click >> add >> TypeScript File >> select file and change aboutUs.component.ts of the file and write the below code.

import { Component } from "@angular/core";

@Component({

template: `<img src="../../images/About-Us.jpg" style="text-align:center" width="800" height="400" />`

})

export class AboutUsComponent {

}

Now, we will add employee.component.ts in Components folder, go to the Components folder and right click >> add >> Type Script File >> select file and change employee.component.ts of the file and write below code.

import { Component, OnInit, ViewChild } from '@angular/core';

import { EmployeeService } from '../Service/employee.service';

import { FormBuilder, FormGroup, Validators } from '@angular/forms';

import { ModalComponent } from 'ng2-bs3-modal/ng2-bs3-modal';

import { IEmployee } from '../Models/employee';

import { Observable } from 'rxjs/Rx';

import { MyApiUrl } from '../Service/employeeUrl.service';

@Component({

templateUrl: '../app/Components/employee.component.html'

})

export class EmployeeComponent implements OnInit {

@ViewChild('modal') modal: ModalComponent;

employees: IEmployee[];

employee: IEmployee;

msg: string;

indLoading: boolean = false;

empFrm: FormGroup;

action: string;

modalTitle: string;

modalBtnTitle: string;

constructor(private fb: FormBuilder, private _employeeService: EmployeeService) { }

ngOnInit(): void {

this.empFrm = this.fb.group({

EmployeeId: [''],

EmpName: ['', Validators.required],

Address: [''],

EmailId: ['', Validators.required],

MobileNo: ['', Validators.required],

DeptName: ['', Validators.required]

});

this.LoadEmpDetails();

}

LoadEmpDetails(): void {

this.indLoading = true;

this._employeeService.get(MyApiUrl.RetriveEmpDetails)

.subscribe(employees => { this.employees = employees; this.indLoading = false; },

error => this.msg = <any>error);

}

addEmployee() {

this.action = "create";

this.SetControlsState(true);

this.modalTitle = "Add New Employee";

this.modalBtnTitle = "Add";

this.empFrm.reset();

this.modal.open();

}

editEmployee(id: number) {

this.action = "update";

this.SetControlsState(true);

this.modalTitle = "Edit Employee";

this.modalBtnTitle = "Update";

this.employee = this.employees.filter(x => x.EmployeeId == id)[0];

this.empFrm.setValue(this.employee);

this.modal.open();

}

deleteEmployee(id: number) {

this.action = "delete";

this.SetControlsState(false);

this.modalTitle = "Confirm to Delete?";

this.modalBtnTitle = "Delete";

this.employee = this.employees.filter(x => x.EmployeeId == id)[0];

this.empFrm.setValue(this.employee);

this.modal.open();

}

onSubmit(formData: any) {

this.msg = "";

switch (this.action) {

case "create":

this._employeeService.post(MyApiUrl.InsertEmpDetails, formData._value).subscribe(

data => {

this.msg = data;

this.LoadEmpDetails();

this.modal.dismiss();

error => {

this.msg = error;

}

);

break;

case "update":

this._employeeService.put(MyApiUrl.UpdateEmpDetails, formData._value.EmployeeId, formData._value).subscribe(

data => {

this.msg = data;

this.LoadEmpDetails();

this.modal.dismiss();

error => {

this.msg = error;

}

);

break;

case "delete":

this._employeeService.delete(MyApiUrl.DeleteEmpDetails, formData._value.EmployeeId).subscribe(

data => {

this.msg = data;

this.LoadEmpDetails();

this.modal.dismiss();

error => {

this.msg = error;

}

);

break;

}

SetControlsState(isEnable: boolean) {

isEnable ? this.empFrm.enable() : this.empFrm.disable();

}

Now, we will add employee.component.html in Components folder, go to the Components folder and right-click >> add >> TypeScript File >> select file and change employee.component.html of the file and write below code.

Employee Details

</div>

</div>

<div class="alert alert-info" role="alert" *ngIf="indLoading"><img src="../../images/3.gif" width="32" height="32" /> Loading...</div>

<div *ngIf='employees && employees.length==0' class="alert alert-info" role="alert">No record found!</div>

<tr>

<th>Employee Name</th>

<th>Address</th>

<th>EmailId</th>

<th>Mobile Number</th>

<th>Deptment Name</th>

<th>Action</th>

</tr>

</thead>

<tbody>

<td>{{emp.EmpName}}</td>

<td>{{emp.Address}}</td>

<td>{{emp.EmailId}}</td>

<td>{{emp.MobileNo}}</td>

<td>{{emp.DeptName}}</td>

<td>

<button title="Delete" class="btn btn-danger" (click)="deleteEmployee(emp.EmployeeId)">Delete</button>

</td>

</tr>

</tbody>

</table>

<div>

</div>

<span class="sr-only">Error:</span>

</div>

<modal-header [show-close]="true">

<h4 class="modal-title">{{modalTitle}}</h4>

</modal-header>

<modal-body style="background-color:white;">

<b>Employee Name</b>

</div>

</div>

<b>Address</b>

</div>

</div>

<b>EmailId</b>

</div>

</div>

<b>Mobile Number</b>

</div>

</div>

<b>Department Name</b>

</div>

</div>

</modal-body>

<modal-footer>

<div>

<a class="btn btn-warning" (click)="modal.dismiss()">Cancel</a>

<button type="submit" [disabled]="empFrm.invalid" class="btn btn-primary">{{modalBtnTitle}}</button>

</div>

</modal-footer>

</form>

</modal>

Again we will add app.routing.ts, go to the app folder and right click >> add >> TypeScript File >> select file and change name app.routing.ts of the file and write the below code.

import { ModuleWithProviders } from '@angular/core';

import { Routes, RouterModule } from '@angular/router';

import { EmployeeComponent } from './components/employee.component';

import { HomeComponent } from './components/home.component';

import { AboutUsComponent } from './components/aboutUs.component';

const appRoutes: Routes = [

{ path: '**', redirectTo: 'EmpDemo', pathMatch: 'full' },

{ path: 'home', component: HomeComponent },

{ path: 'employee', component: EmployeeComponent },

{ path: 'aboutUs', component: AboutUsComponent }

];

export const routing: ModuleWithProviders =

RouterModule.forRoot(appRoutes);

Again we will add app.component.ts, go to the app folder and right-click >> add >> TypeScript File >> select file and change name app.component.ts of the file and write the below code.

import { Component } from "@angular/core"

@Component({

selector: "emp-app",

template: `

<div>

<li><a [routerLink]="['employee']">Employee Details</a></li>

<li><a [routerLink]="['aboutUs']">About Us</a></li>

</ul>

</div>

</nav>

<router-outlet></router-outlet>

</div>

})

export class AppComponent {

}

Now, we will add app.module.ts, go to the app folder and right click >> add >> TypeScript File >> select file and change name app.module.ts of the file and write below code.

import { NgModule } from '@angular/core';

import { APP_BASE_HREF } from '@angular/common';

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

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

import { AppComponent } from './app.component';

import { Ng2Bs3ModalModule } from 'ng2-bs3-modal/ng2-bs3-modal';

import { HttpModule } from '@angular/http';

import { routing } from './app.routing';

import { EmployeeComponent } from './components/employee.component';

import { HomeComponent } from './components/home.component';

import { EmployeeService } from './Service/employee.service';

import { AboutUsComponent } from './components/aboutUs.component';

@NgModule({

imports: [BrowserModule, ReactiveFormsModule, HttpModule, routing, Ng2Bs3ModalModule],

declarations: [AppComponent, EmployeeComponent, HomeComponent, AboutUsComponent],

providers: [{ provide: APP_BASE_HREF, useValue: '/' }, EmployeeService],

bootstrap: [AppComponent]

})

export class AppModule { }

Our angular code has done.

Step 9

After completed Angular code, we will go in controller folder and a controller. Right click >> Add >> Controller >> MVC5 Controller Empty >> Add

Click Add Button

After click add button will check and also an images folder for add some image.

After that add an action method in EmpDemo controller for display our output

public ActionResult EmpDetails()

{

return View();

}

After that right click and add a view and write below code.

System.import('app').catch(function (err) { console.error(err); });

</script>

<body>

<emp-app>Loading…</emp-app>

</body>

Step 10

Let us run the project but we have to run both projects at one time so for this, so we have to set some changes.

Right-click on the solution project and go to properties. There, check the "Multiple startup projects" option and click "Apply".

Now, we can see the output. It is not giving the expected result but some error.

This error is called CORS. So first, we have to know what CORS is and how to resolve this problem.

According to Wikipedia,

Cross-origin resource sharing (CORS) is a mechanism that allows restricted resources (e.g. fonts) on a web page to be requested from another domain outside the domain from which the first resource was served.

For more details click this link.

Now, let us learn how to resolve this problem.

So for this, we have to download CORS in Web API project. Go to NuGet Package Manager and download the following file.

After that, goto App_Start folder in Web API project and then WebApiConfig.cs class. Here, modify the Register method with the below code.