CRUD Operations In Angular Using AG Grid With Web API And SQL

In this article, I'm going to perform CRUD operations in Angular 9 with Ag-Grid using Web API with the help of an example. And the backend is a SQL Server database. Here I am using Web API to provide data connectivity between the database and the front end application. On the UI side, I will use bootstrap and ag-grid to create a rich, interactive , device-independent user experience for building a beautiful UI

I'm using Visual Studio Code as a tool to build my application. If you don't have Visual studio code in your system then first you have to download and install. Here is Visual Studio Code download link:Download Visual Studio Code Editor

First, take a look at our output,

CRUD Operations In Angular Using AG Grid With Web API And SQL

Intruduction of Ag-Grid

ag-Grid is a fully-featured and highly customizable JavaScript data grid. It is providing outstanding performance, has no third-party dependencies and integrates smoothly with Angular as Angular Component. Check out documentation for complete detail about ag-grid.

Standard features of ag grid

Column Interactions (resize, reorder, and pin columns)
Pagination
Sorting
Row Selection

Prerequisites

Visual studio
Sql Server
Node.js version > 10
Angular 9
Toastr(for display message)
Visual studio code
Bootstrap
Ag-Grid

Step 1: Create a database and table

Create a database. Open SQL Server and create a new database and table. As you can see from the following query, I have created a database table called User Details.

UserDetails

CREATE TABLE [dbo].[userdetails]
(
[userid] [INT] IDENTITY(1, 1) NOT NULL,
[username] [VARCHAR](50) NULL,
[emailid] [VARCHAR](150) NULL,
[gender] [CHAR](20) NULL,
[address] [VARCHAR](500) NULL,
[mobileno] [VARCHAR](15) NULL,
[pincode] [VARCHAR](10) NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED ( [userid] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]

Note
You can choose the size of the column according to your requirement.

Step 2: Create a Web API Project

Now, we will create a Web API with the functionality of binding records from a database. Go to Visual Studio >> File >> New >> Project, and select Web Application. After that, click OK and you will see the templates. Select the Web API template.

Click Ok

Click OK.

Step 3: Add ADO.NET Entity Data Model

Now, Select Models folder >> Right click >> Add >> New Item >> select Data in left panel >> ADO.NET Entity Data Model,

Click Add button

Click Next button

Give server name of SQL server and its credential then select database and test connection then click ok button.

Click Next button

Select UserDetails table and click Finish button

Step 4: Add API controller logic

Go to the Controller folder in our API Application and right-click >> Add >> Controller >> Select Web API 2 Controller-Empty

Click Add button

Now we will write the logic for performing CRUD operation. We will go to the controller class and set the routing to make it more user-friendly by writing the below code.

using System;
using System.Linq;
using System.Web.Http;
using AngularAPI.Models;
namespace AngularAPI.Controllers {
[RoutePrefix("Api/User")]
public class UserAPIController: ApiController {
AngularDBEntities objEntity = new AngularDBEntities();
[HttpGet]
[Route("GetUserDetails")]
public IQueryable < UserDetail > GetUser() {
try {
return objEntity.UserDetails;
} catch (Exception) {
throw;
}
}
[HttpGet]
[Route("GetUserDetailsById/{userId}")]
public IHttpActionResult GetUserById(string userId) {
UserDetail objUser = new UserDetail();
int ID = Convert.ToInt32(userId);
try {
objUser = objEntity.UserDetails.Find(ID);
if (objUser == null) {
return NotFound();
}
} catch (Exception) {
throw;
}
return Ok(objUser);
}
[HttpPost]
[Route("InsertUserDetails")]
public IHttpActionResult PostUser(UserDetail data) {
string message = "";
if (data != null) {
try {
objEntity.UserDetails.Add(data);
int result = objEntity.SaveChanges();
if (result > 0) {
message = "User has been sussfully added";
} else {
message = "faild";
}
} catch (Exception) {
throw;
}
}
return Ok(message);
}
[HttpPut]
[Route("UpdateEmployeeDetails")]
public IHttpActionResult PutUserMaster(UserDetail user) {
string message = "";
if (!ModelState.IsValid) {
return BadRequest(ModelState);
}
try {
UserDetail objUser = new UserDetail();
objUser = objEntity.UserDetails.Find(user.UserId);
if (objUser != null) {
objUser.UserName = user.UserName;
objUser.EmailId = user.EmailId;
objUser.Gender = user.Gender;
objUser.Address = user.Address;
objUser.MobileNo = user.MobileNo;
objUser.PinCode = user.PinCode;
}
int result = objEntity.SaveChanges();
if (result > 0) {
message = "User has been sussfully updated";
} else {
message = "faild";
}
} catch (Exception) {
throw;
}
return Ok(message);
}
[HttpDelete]
[Route("DeleteUserDetails")]
public IHttpActionResult DeleteEmaployeeDelete(int id) {
string message = "";
UserDetail user = objEntity.UserDetails.Find(id);
objEntity.UserDetails.Remove(user);
int result = objEntity.SaveChanges();
if (result > 0) {
message = "User has been sussfully deleted";
} else {
message = "faild";
}
return Ok(message);
}
}
}

Now, our API has been completed and as you may see from the above code, it has the functionality to add, replace, update, and delete records to the table.

Step 5: Install Angular CLI

Now we will install angular CLI through the below command. But before that just check Node and NPM installed or not. And also we are using Visual Studio code as writing Angular code for UI application so first, make sure it's installed. If you have not installed then go to this link for download.

Let's install CLI to open a cmd and run the following command to install

npm install -g @angular/cli

Step 6: Create am Anguglar project

Now, open Visual Studio Code and create a project.

Open TERMINAL in Visual Studio Code and type the following syntax to create a new project. We name it aggriddemo.

ng new Aggriddemo

After that, hit ENTER. It will take a while to create the project.

Once created, the project should look like this.

Step 7: Check React dependency

Go to in package.json file and check to react dependency.

Step 8: Install Ag-Grid

Let's install the ag-Grid NPM packages. Open new terminal and run the following command

npm install --save ag-grid-community ag-grid-angular

Open style.css and below themes

@import 'ag-grid-community/dist/styles/ag-grid.css';
@import 'ag-grid-community/dist/styles/ag-theme-blue.css';

Step 9: Install bootstrap

Now, we will install bootstrap to building a beautiful UI of our react application.

npm install bootstrap --save

npm install react-bootstrap bootstrap

Step 10: Install Toastr

We wil install toastr for displaying the rich and atractive UI message, so now go into terminal and type the following command:

npm i ngx-toastr

Open Angular.json and add the below toastr.css path in style

Step 11: Create Components

Now, we can create some components to provide the UI.

I'm going to create a new component, User and Add User.

Go to the TERMINAL and go our Angular project location using the following command,

ng g c user

ng g c user/AddUser

Step 12: Create Service

Now, we will create a service.

Open the TERMINAL and write the below command,

ng g s user

Press ENTER and you will see two service files.

Step 13:Create a model class

Now, we create a class like model class.

Open TERMINAL and write the below command,

ng g class user

export class User {
UserId: number;
UserName: string;
EmailId: string;
Gender: string;
Address: string;
MobileNo: string;
PinCode: string;
}

Step 14: Create a User Registration Page

Now we will write our logic to create a user register..

Go inside the user folder and open add-user.component.html file and write the below code for designing user registration.

<div class="container">
<div class="row">
<div class="col-md-8">
<h1>
<span class="badge badge-dark" id="header">User Registration</span>
</h1>
<hr>
<form [formGroup]="userForm" (ngSubmit)="onSubmit()">
<div class="row">
<div class="col-md-4">
<label for="firstName">User Name:</label>
</div>
<div class="col-md-6">
<input type="text" class="form-control" formControlName="UserName">
<div *ngIf="submitted && userForm.controls.UserName.errors" class="error">
<div *ngIf="userForm.controls.UserName.errors.required">First name is required</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label for="firstName">EmailId:</label>
</div>
<div class="col-md-6">
<input type="text" class="form-control" formControlName="EmailId">
<div *ngIf="submitted && userForm.controls.EmailId.errors" class="error">
<div *ngIf="userForm.controls.EmailId.errors.required">EmailId is required</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label for="firstName">Gender:</label>
</div>
<div class="col-md-6">
<input type="email" class="form-control" formControlName="Gender">
<div *ngIf="submitted && userForm.controls.Gender.errors" class="error">
<div *ngIf="userForm.controls.Gender.errors.required">Gender is required</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label for="firstName">Address:</label>
</div>
<div class="col-md-6">
<input type="text" class="form-control" formControlName="Address">
<div *ngIf="submitted && userForm.controls.Address.errors" class="error">
<div *ngIf="userForm.controls.Address.errors.required">Address is required</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label for="firstName">Mobile:</label>
</div>
<div class="col-md-6">
<input type="text" class="form-control" formControlName="MobileNo">
<div *ngIf="submitted && userForm.controls.MobileNo.errors" class="error">
<div *ngIf="userForm.controls.MobileNo.errors.required">Mobile number is required</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label for="firstName">Pin Code:</label>
</div>
<div class="col-md-6">
<input type="text" class="form-control" formControlName="PinCode">
<div *ngIf="submitted && userForm.controls.MobileNo.errors" class="error">
<div *ngIf="userForm.controls.PinCode.errors.required">PinCode is required</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4"></div>
<div class="col-md-4">
<button type="submit" class="btn btn-primary">Submit</button>
<input type="button" class="btn btn-warning" (click)="Cancel()" value="Cancel">
</div>
</div>
</form>
</div>
</div>
</div>

And now open add-user.component.ts and first import necessary library and then write the below code.

import { Component, OnInit } from '@angular/core';
import { UserService } from 'src/app/services/user.service';
import { FormControl, FormGroup, Validators, FormBuilder } from '@angular/forms';
import { Router } from '@angular/router';
import { ToastrService } from 'ngx-toastr';
@Component({
selector: 'app-add-user',
templateUrl: './add-user.component.html',
styleUrls: ['./add-user.component.css']
})
export class AddUserComponent implements OnInit {
submitted: boolean= false;
userForm: any;
constructor(private formBuilder: FormBuilder,private toastr: ToastrService,private userService: UserService,private router:Router) { }
ngOnInit(): void {
this.userForm = this.formBuilder.group({
"UserName": ["", Validators.required],
"EmailId": ["", Validators.required],
"Gender": ["", Validators.required],
"Address": ["", Validators.required],
"MobileNo": ["", Validators.required],
"PinCode": ["", Validators.required]
});
}
onSubmit() {
this.submitted = true;
if (this.userForm.invalid) {
return;
}
this.userService.addUser(this.userForm.value)
.subscribe( data => {
this.toastr.success("success", data.toString());
this.router.navigate(['users']);
});
}
Cancel()
{
this.router.navigate(['users']);
}
}

Open add-user.component.css and then write the below css code.

md-card{
width: 20em;
}
.error {
color: red;
}
.row{
padding: 7px;
}
.error-msg {
display: block;
position: absolute;
font-size: 75%;
bottom: -2em;
}
.mat-input-container {
margin-bottom: .5em;
}
#header{
width: 100%;
}

Step 15: Create User details page with ag grid

Now we will do the functionality for binding the data using ag grid and perform update, delete, sorting, pagination etc

Go inside the user folder and open user.component.html file and write the below code for designing user details page.

<div class="container">
<h2 class="header">AG Grid CRUD Operations Example in Angular</h2>
<hr>
<div class="row">
<div class="col-md-8"></div>
<div class="col-md-4">
<button class="btn btn-sm btn-success button" (click)="Add()">Add User</button>
<button class="btn btn-sm btn-primary" (click)="editUser()">Edit User</button>
<button class="btn btn-sm btn-danger" (click)="deleteUser()">Delete User</button>
</div>
</div>
<br>
<ag-grid-angular style="width: 100%; height: 200px;" class="ag-theme-blue" (gridReady)="onGridReady($event)"
[columnDefs]="columnDefs" [rowData]="users" rowSelection="single" pagination="true" paginationPageSize=5
></ag-grid-angular>
</div>

Go inside the user folder and open user.component.ts file and write the below code logic to perform the operation.

import {
Component,
OnInit
} from '@angular/core';
import {
User
} from '../model/user';
import {
ColDef,
GridApi,
ColumnApi
} from 'ag-grid-community';
import {
UserService
} from '../services/user.service';
import {
Router
} from '@angular/router';
import {
ToastrService
} from 'ngx-toastr';
@Component({
selector: 'app-user',
templateUrl: './user.component.html',
styleUrls: ['./user.component.css']
})
export class UserComponent implements OnInit {
// row data and column definitions
public users: User[];
public columnDefs: ColDef[];
// gridApi and columnApi
private api: GridApi;
private columnApi: ColumnApi;
constructor(private userService: UserService, private router: Router, private toastr: ToastrService) {
this.columnDefs = this.createColumnDefs();
}
ngOnInit() {
this.userService.getUsers().subscribe(data => {
this.users = data
})
}
// one grid initialisation, grap the APIs and auto resize the columns to fit the available space
onGridReady(params): void {
this.api = params.api;
this.columnApi = params.columnApi;
this.api.sizeColumnsToFit();
}
// create column definitions
private createColumnDefs() {
return [{
headerName: 'User Name',
field: 'UserName',
filter: true,
enableSorting: true,
editable: true,
sortable: true
}, {
headerName: 'Email Id',
field: 'EmailId',
filter: true,
editable: true,
sortable: true
}, {
headerName: 'Gender',
field: 'Gender',
filter: true,
sortable: true,
editable: true,
cellRenderer: '<a href="edit-user">{{email}}</a>'
}, {
headerName: 'Address',
field: 'Address',
filter: true,
editable: true,
sortable: true
}, {
headerName: 'Mobile',
field: 'MobileNo',
filter: true,
editable: true
}]
}
status: any;
//Update user
editUser() {
debugger;
const d = this.api.getEditingCells();
if (this.api.getSelectedRows().length == 0) {
this.toastr.error("error", "Please select a User for update");
return;
}
var row = this.api.getSelectedRows();
this.userService.updateUser(row[0]).subscribe(data => {
this.toastr.success("success", data);
this.ngOnInit();
});
}
//Delete user
deleteUser() {
debugger;
var selectedRows = this.api.getSelectedRows();
if (selectedRows.length == 0) {
this.toastr.error("error", "Please select a User for deletion");
return;
}
this.userService.deleteUser(selectedRows[0].UserId).subscribe(data => {
this.toastr.success("success", data);
this.ngOnInit();
this.api.refreshRows(null);
});
}
Add() {
this.router.navigate(['addUser']);
}
}

Step 16: Write logic of service for consuming api

Now, open user.service.tsand first import necessary class and libraries and then make calls to the WebAPI methods.

import {
Injectable
} from '@angular/core';
import {
User
} from '../model/user';
import {
HttpClient,
HttpHeaders
} from '@angular/common/http';
import {
Observable
} from 'rxjs';
@Injectable({
providedIn: 'root'
})
export class UserService {
apiUrl: string = "http://localhost:49850/Api/User/";
constructor(private http: HttpClient) {}
editUser(user: User) {
return this.http.put(this.apiUrl + 'UpdateEmployeeDetails/', user);
}
getUsers(): Observable < User[] > {
return this.http.get < User[] > (`${this.apiUrl}GetUserDetails`);
}
addUser(user: User): Observable < string > {
const httpOptions = {
headers: new HttpHeaders({
'Content-Type': 'application/json'
})
};
return this.http.post < string > (`${this.apiUrl}/InsertUserDetails/`, user, httpOptions);
}
updateUser(user: User): Observable < string > {
const httpOptions = {
headers: new HttpHeaders({
'Content-Type': 'application/json'
})
};
return this.http.put < string > (`${this.apiUrl}/UpdateEmployeeDetails/`, user, httpOptions);
}
deleteUser(userId: string): Observable < string > {
const httpOptions = {
headers: new HttpHeaders({
'Content-Type': 'application/json'
})
};
return this.http.delete < string > (`${this.apiUrl}/DeleteUserDetails?id=` + userId, httpOptions);
}
}

Finally, our coding part also has been completed.

Step 17: Set CORS (Cross-Origin Resource Sharing)

Go to the Web API project.

Download a NuGet package for CORS. Go to NuGet Package Manager and download the following file.

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

Add namespace

using System.Web.Http.Cors;

After that, add the below code inside Register method.

var cors = new EnableCorsAttribute("*", "*", "*");
config.EnableCors(cors);

Step 18: Run

We have completed all needed code functionality for our CRUD operations. Before running the application, first, make sure to save your work.

Now, let's run the app and see how it works.

Open TERMINAL and write the following command to run the program.

ng serve -o

The output looks like the following image. It's a stunning UI created with CRUD operations.

User Registration Page

Click without fill data

User details Page

Conclusion

We have completed how to perform a CRUD operation in Angular 9 using Ag-Grid and Web Api for user details, update details, insert details, sorting, paging, filtering and deleting details of a user.

We have started by installing and creating the create-angular-app then used it to create our Angular project. Next, we have installed bootstrap in our application. After that we installed the ag grid to display user details with sorting etc and used the get(),post(),delete() and put() methods to to HTTP request.

I hope you will enjoy this article. You are always welcome to ask any query and leave a comment.

3 Comments

Join the conversation! Your thoughts help the community grow.

carl vener3y ago
Awesome project are this process pretty similar Using Web Api instead of using aspNetCore. Are there any samples out there. Apologies fairly new on Angular projects
- Mithilesh Kumar3y ago
  Thank you...............
Former member6y ago
Hello This was an awesome tutorial . I was able to build and run the project but http://localhost:4200/ display a blank page. You created the WEB API in VS and the project in Visual code. Do you just compile the project in Visual code with ng serve ? Would you have to run the WEB API in VS as well before compiling in Visual code. I usually run both my web api and project in Visual code using ng serve and dotnet run respectively . So I am not sure how you would do this if you have the WEB API in VS and the project in Visual studio code