CRUD Operations With ASP.NET Core Using Angular 5 and ADO.NET

DZone's Guide to

CRUD Operations With ASP.NET Core Using Angular 5 and ADO.NET

In this article, we will walk through, step-by-step, how to create an MVC web application in ASP.NET Core 2.0 with Angular 5.

by
Ankit Sharma
·
Jan. 08, 18 · Web Dev Zone
Free Resource

Get the senior executive’s handbook of important trends, tips, and strategies to compete and win in the digital economy.

Introduction

In this article, I am going to explain how to create an MVC web application in ASP.NET Core 2.0 with Angular 5. We will be creating a sample Employee Record Management system using Angular 5 at the front-end, Web API on the backend, and ADO.NET to fetch data from the database. We will use an Angular form with the required field validations for the input fields to get data from the user.

We will be using Visual Studio 2017 (Version 15.3.5 or above) and SQL Server 2008.

Prerequisites

  • Install the .NET Core 2.0.0 (or above) SDK from here.
  • Install Visual Studio 2017 Community Edition (Version 15.3.5 or above) from here.
  • Download and install the latest version of Node.js from here.

Source Code

Before proceeding, I would recommend you to get the source code from GitHub.

Now, let's proceed to create our tables and stored procedures using SQL Server. I am using SQL server 2008. You can use any version above that, also.

Creating Table and Stored Procedures

We will be using a DB table to store all the records of employees.

Open SQL Server and use the following script to create tblEmployee table.

Create table tblEmployee(      
    EmployeeId int IDENTITY(1,1) NOT NULL,      
    Name varchar(20) NOT NULL,      
    City varchar(20) NOT NULL,      
    Department varchar(20) NOT NULL,      
    Gender varchar(6) NOT NULL      
)

Now, we will create stored procedures to add, delete, update, and get employee data.

To Insert an Employee Record

Create procedure spAddEmployee         
(        
    @Name VARCHAR(20),         
    @City VARCHAR(20),        
    @Department VARCHAR(20),        
    @Gender VARCHAR(6)        
)        
as         
Begin         
    Insert into tblEmployee (Name,City,Department, Gender)         
    Values (@Name,@City,@Department, @Gender)         
End

To Update an Employee Record

Create procedure spUpdateEmployee        
(        
   @EmpId INTEGER ,      
   @Name VARCHAR(20),       
   @City VARCHAR(20),      
   @Department VARCHAR(20),      
   @Gender VARCHAR(6)      
)        
as        
begin        
   Update tblEmployee         
   set Name=@Name,        
   City=@City,        
   Department=@Department,      
   Gender=@Gender        
   where EmployeeId=@EmpId        
End

To Delete an Employee Record

Create procedure spDeleteEmployee       
(        
   @EmpId int        
)        
as         
begin        
   Delete from tblEmployee where EmployeeId=@EmpId        
End

To View All Employee Records

Create procedure spGetAllEmployees      
as      
Begin      
    select *      
    from tblEmployee   
    order by EmployeeId      
End

After clicking on OK, a new dialog will open asking you to select the project template. You can observe two drop-down menus at the top left of the template window. Select ".NET Core" and "ASP.NET Core 2.0" from these dropdowns. Then, select "Angular" template and press OK.

Now our project will be created. You can observe the folder structure in the solution explorer as shown in the below image.

Here we have our Controllers and Views folders. We won't be touching the Views folders for this tutorial since we will be using Angular to handle the UI. The Controllers folders will contain our Web API controller. The point of interest for us is the ClientApp folder where the client side of our application resides. Inside the ClientApp/app/components folder, we already have a few components created which are provided by default with the Angular template in VS 2017. These components won't affect our application, but for the sake of this tutorial, we will delete the fetchdata and counter folders from ClientApp/app/components.

Adding the Model to the Application

You can also observe that there is no Models folder in our application. So, we will create one by right-clicking on the solution name and then Add >> New Folder and name the folder, Models.

Right click on Models folder and select Add >> Class. Name your class Employee.cs. This class will contain our Employee model properties.

Add one more class file to the Models folder. Name it, EmployeeDataAccessLayer.cs. This class will contain our Database related operations.

Now, the Models folder has the following structure:

Open Employee.cs and put the following code in it. This is our Employee class having five properties of Employees.

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

namespace ASPCoreWithAngular.Models  
{  
    public class Employee  
    {  
        public int ID { get; set; }  

        public string Name { get; set; }  

        public string Gender { get; set; }  

        public string Department { get; set; }  

        public string City { get; set; }  
    }  
}

Open EmployeeDataAccessLayer.cs and enter the following code to handle database operations. Make sure to put your connection string.

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

namespace ASPCoreWithAngular.Models
{
    public class EmployeeDataAccessLayer
    {
        string connectionString = "Put Your Connection string here";

        //To View all employees details
        public IEnumerable<Employee> GetAllEmployees()
        {
            try
            {
                List<Employee> lstemployee = new List<Employee>();

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGetAllEmployees", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        Employee employee = new Employee();

                        employee.ID = Convert.ToInt32(rdr["EmployeeID"]);
                        employee.Name = rdr["Name"].ToString();
                        employee.Gender = rdr["Gender"].ToString();
                        employee.Department = rdr["Department"].ToString();
                        employee.City = rdr["City"].ToString();

                        lstemployee.Add(employee);
                    }
                    con.Close();
                }
                return lstemployee;
            }
            catch
            {
                throw;
            }
        }

        //To Add new employee record 
        public int AddEmployee(Employee employee)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spAddEmployee", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@Name", employee.Name);
                    cmd.Parameters.AddWithValue("@Gender", employee.Gender);
                    cmd.Parameters.AddWithValue("@Department", employee.Department);
                    cmd.Parameters.AddWithValue("@City", employee.City);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                return 1;
            }
            catch
            {
                throw;
            }
        }

        //To Update the records of a particluar employee
        public int UpdateEmployee(Employee employee)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@EmpId", employee.ID);
                    cmd.Parameters.AddWithValue("@Name", employee.Name);
                    cmd.Parameters.AddWithValue("@Gender", employee.Gender);
                    cmd.Parameters.AddWithValue("@Department", employee.Department);
                    cmd.Parameters.AddWithValue("@City", employee.City);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                return 1;
            }
            catch
            {
                throw;
            }
        }

        //Get the details of a particular employee
        public Employee GetEmployeeData(int id)
        {
            try
            {
                Employee employee = new Employee();

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    string sqlQuery = "SELECT * FROM tblEmployee WHERE EmployeeID= " + id;
                    SqlCommand cmd = new SqlCommand(sqlQuery, con);

                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        employee.ID = Convert.ToInt32(rdr["EmployeeID"]);
                        employee.Name = rdr["Name"].ToString();
                        employee.Gender = rdr["Gender"].ToString();
                        employee.Department = rdr["Department"].ToString();
                        employee.City = rdr["City"].ToString();
                    }
                }
                return employee;
            }
            catch
            {
                throw;
            }
        }

        //To Delete the record on a particular employee
        public int DeleteEmployee(int id)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spDeleteEmployee", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@EmpId", id);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                return 1;
            }
            catch
            {
                throw;
            }
        }
    }
}

Now, we will proceed to create our Web API Controller.

Adding the Web API Controller to the Application

Right click on Controllers folder and select Add >> New Item.

An "Add New Item" dialog box will open. Select ASP.NET from the left panel, then select "Web API Controller Class" from the templates panel, and set the name as EmployeeController.cs. Press OK.

Open the EmployeeController.cs file and put the following code into it:

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Threading.Tasks;  
using Microsoft.AspNetCore.Mvc;  
using System.Data.SqlClient;  
using System.Data;  
using ASPCoreWithAngular.Models;  

namespace ASPCoreWithAngular.Controllers  
{  
    public class EmployeeController : Controller  
    {  
        EmployeeDataAccessLayer objemployee = new EmployeeDataAccessLayer();  

        [HttpGet]  
        [Route("api/Employee/Index")]  
        public IEnumerable<Employee> Index()  
        {  
            return objemployee.GetAllEmployees();  
        }  

        [HttpPost]  
        [Route("api/Employee/Create")]  
        public int Create([FromBody] Employee employee)  
        {  
            return objemployee.AddEmployee(employee);  
        }  

        [HttpGet]  
        [Route("api/Employee/Details/{id}")]  
        public Employee Details(int id)  
        {  
            return objemployee.GetEmployeeData(id);  
        }  

        [HttpPut]  
        [Route("api/Employee/Edit")]  
        public int Edit([FromBody]Employee employee)  
        {  
            return objemployee.UpdateEmployee(employee);  
        }  

        [HttpDelete]  
        [Route("api/Employee/Delete/{id}")]  
        public int Delete(int id)  
        {  
            return objemployee.DeleteEmployee(id);  
        }  
    }  
}

Open the empservice.service.ts file and put the following code into it:

import { Injectable, Inject } from '@angular/core';  
import { Http, Response } from '@angular/http';  
import { Observable } from 'rxjs/Observable';  
import { Router } from '@angular/router';  
import 'rxjs/add/operator/map';  
import 'rxjs/add/operator/catch';  
import 'rxjs/add/observable/throw';  

@Injectable()  
export class EmployeeService {  
    myAppUrl: string = "";  

    constructor(private _http: Http, @Inject('BASE_URL') baseUrl: string) {  
        this.myAppUrl = baseUrl;  
    }  

    getEmployees() {  
        return this._http.get(this.myAppUrl + 'api/Employee/Index')  
            .map((response: Response) => response.json())  
            .catch(this.errorHandler);  
    }  

    getEmployeeById(id: number) {  
        return this._http.get(this.myAppUrl + "api/Employee/Details/" + id)  
            .map((response: Response) => response.json())  
            .catch(this.errorHandler)  
    }  

    saveEmployee(employee) {  
        return this._http.post(this.myAppUrl + 'api/Employee/Create', employee)  
            .map((response: Response) => response.json())  
            .catch(this.errorHandler)  
    }  

    updateEmployee(employee) {  
        return this._http.put(this.myAppUrl + 'api/Employee/Edit', employee)  
            .map((response: Response) => response.json())  
            .catch(this.errorHandler);  
    }  

    deleteEmployee(id) {  
        return this._http.delete(this.myAppUrl + "api/Employee/Delete/" + id)  
            .map((response: Response) => response.json())  
            .catch(this.errorHandler);  
    }  

    errorHandler(error: Response) {  
        console.log(error);  
        return Observable.throw(error);  
    }  
}

At this point, you might get an error that reads, "Parameter 'employee' implicitly has an 'any' type" in the empservice.service.ts file.

If you encounter this issue, add the following line inside the tsconfig.json file:

"noImplicitAny" : false 

Now we will proceed to create our components.

Creating Angular Components

We will be adding two Angular components to our application:

  • fetchemployee component - to display all the employee data and delete existing employee data.
  • addemployee component - to add a new employee data as well as to edit existing employee data.

Right-click on the ClientApp/app/components folder and select Add >> New Folder and name the folder, addemployee. Right-click on the addemployee folder and select Add >> New Item. An "Add New Item" dialog box will open. Select Web from the left panel, then select "TypeScript File" from the templates panel, and set the name as Addemployee.component.ts. Click Add. This will add a typescript file inside the addemployee folder

Right-click on the addemployee folder and selectAdd >> New Item. An "Add New Item" dialog box will open. Select ASP.NET Core from the left panel, then select "HTML Page" from the templates panel, and set the name as Addemployee.component.html. Press OK. This will add an HTML file inside the addemployee folder.

Similarly, a add fetchemployee.component.ts typescript file and a fetchemployee.component.html HTML file inside the fetchemployee folder. Now our ClientApp/app/components will look like the image below:

Open the fetchemployee.component.ts file and put the following code into it:

import { Component, Inject } from '@angular/core';  
import { Http, Headers } from '@angular/http';  
import { Router, ActivatedRoute } from '@angular/router';  
import { EmployeeService } from '../../services/empservice.service'  

@Component({  
    selector: 'fetchemployee',  
    templateUrl: './fetchemployee.component.html'  
})  

export class FetchEmployeeComponent {  
    public empList: EmployeeData[];  

    constructor(public http: Http, private _router: Router, private _employeeService: EmployeeService) {  
        this.getEmployees();  
    }  

    getEmployees() {  
        this._employeeService.getEmployees().subscribe(  
            data => this.empList = data  
        )  
    }  

    delete(employeeID) {  
        var ans = confirm("Do you want to delete customer with Id: " + employeeID);  
        if (ans) {  
            this._employeeService.deleteEmployee(employeeID).subscribe((data) => {  
                this.getEmployees();  
            }, error => console.error(error))   
        }  
    }  
}  

interface EmployeeData {  
    id: number;  
    name: string;  
    gender: string;  
    department: string;  
    city: string;  
}

Let's understand this code. At the very top, we have imported Angular modules and EmployeeService references. After this, we have the @Component decorator to define the selector and the template URL for our component

Inside the FetchEmployeeComponent class, we have declared an array variable, empList, of type EmployeeData where EmployeeData is an interface having the same properties as our Employee Model class. Inside the getEmployees method, we are calling the getEmployees method of our service, EmployeeService, which will return an array of employees to be stored in the empList variable. The getEmployees method is called inside the constructor so that the employee data will be displayed as the page loads.

Next, we have deleted the method which accepts employeeID as a parameter. This will prompt the user with a confirmation box and if the user selects 'yes' then it will delete the employee with this employeeID.

Open the fetchemployee.component.html file and paste the following code into it:

<h1>Employee Data</h1>  

<p>This component demonstrates fetching Employee data from the server.</p>  

<p *ngIf="!empList"><em>Loading...</em></p>  

<p>  
    <a [routerLink]="['/register-employee']">Create New</a>  
</p>  

<table class='table' *ngIf="empList">  
    <thead>  
        <tr>  
            <th>ID</th>  
            <th>Name</th>  
            <th>Gender</th>  
            <th>Department</th>  
            <th>City</th>  
        </tr>  
    </thead>  
    <tbody>  
        <tr *ngFor="let emp of empList">  
            <td>{{ emp.id }}</td>  
            <td>{{ emp.name }}</td>  
            <td>{{ emp.gender }}</td>  
            <td>{{ emp.department }}</td>  
            <td>{{ emp.city }}</td>  
            <td>  
            <td>  
                <a [routerLink]="['/employee/edit/', emp.id]">Edit</a> |  
                <a [routerLink]="" (click)="delete(emp.id)">Delete</a>  
            </td>  
        </tr>  
    </tbody>  
</table>

The code for this HTML file is pretty simple. On the top, it has a link to create a new employee record and, after that, it will have a table to display employee data and two links for editing and deleting each employee record.

import { Component, OnInit } from '@angular/core';  
import { Http, Headers } from '@angular/http';  
import { NgForm, FormBuilder, FormGroup, Validators, FormControl } from '@angular/forms';  
import { Router, ActivatedRoute } from '@angular/router';  
import { FetchEmployeeComponent } from '../fetchemployee/fetchemployee.component';  
import { EmployeeService } from '../../services/empservice.service';  

@Component({  
    selector: 'createemployee',  
    templateUrl: './AddEmployee.component.html'  
})  

export class createemployee implements OnInit {  
    employeeForm: FormGroup;  
    title: string = "Create";  
    id: number;  
    errorMessage: any;  

    constructor(private _fb: FormBuilder, private _avRoute: ActivatedRoute,  
        private _employeeService: EmployeeService, private _router: Router) {  
        if (this._avRoute.snapshot.params["id"]) {  
            this.id = this._avRoute.snapshot.params["id"];  
        }  

        this.employeeForm = this._fb.group({  
            id: 0,  
            name: ['', [Validators.required]],  
            gender: ['', [Validators.required]],  
            department: ['', [Validators.required]],  
            city: ['', [Validators.required]]  
        })  
    }  

    ngOnInit() {  
        if (this.id > 0) {  
            this.title = "Edit";  
            this._employeeService.getEmployeeById(this.id)  
                .subscribe(resp => this.employeeForm.setValue(resp)  
                , error => this.errorMessage = error);  
        }  
    }  

    save() {  

        if (!this.employeeForm.valid) {  
            return;  
        }  

        if (this.title == "Create") {  
            this._employeeService.saveEmployee(this.employeeForm.value)  
                .subscribe((data) => {  
                    this._router.navigate(['/fetch-employee']);  
                }, error => this.errorMessage = error)  
        }  
        else if (this.title == "Edit") {  
            this._employeeService.updateEmployee(this.employeeForm.value)  
                .subscribe((data) => {  
                    this._router.navigate(['/fetch-employee']);  
                }, error => this.errorMessage = error)   
        }  
    }  

    cancel() {  
        this._router.navigate(['/fetch-employee']);  
    }  

    get name() { return this.employeeForm.get('name'); }  
    get gender() { return this.employeeForm.get('gender'); }  
    get department() { return this.employeeForm.get('department'); }  
    get city() { return this.employeeForm.get('city'); }  
}
import { NgModule } from '@angular/core';  
import { EmployeeService } from './services/empservice.service'  
import { CommonModule } from '@angular/common';  
import { FormsModule, ReactiveFormsModule } from '@angular/forms';  
import { HttpModule } from '@angular/http';  
import { RouterModule } from '@angular/router';  

import { AppComponent } from './components/app/app.component';  
import { NavMenuComponent } from './components/navmenu/navmenu.component';  
import { HomeComponent } from './components/home/home.component';  
import { FetchEmployeeComponent } from './components/fetchemployee/fetchemployee.component'  
import { createemployee } from './components/addemployee/AddEmployee.component'  

@NgModule({  
    declarations: [  
        AppComponent,  
        NavMenuComponent,  
        HomeComponent,  
        FetchEmployeeComponent,  
        createemployee,  
    ],  
    imports: [  
        CommonModule,  
        HttpModule,  
        FormsModule,  
        ReactiveFormsModule,  
        RouterModule.forRoot([  
            { path: '', redirectTo: 'home', pathMatch: 'full' },  
            { path: 'home', component: HomeComponent },  
            { path: 'fetch-employee', component: FetchEmployeeComponent },  
            { path: 'register-employee', component: createemployee },  
            { path: 'employee/edit/:id', component: createemployee },  
            { path: '**', redirectTo: 'home' }  
        ])  
    ],  
    providers: [EmployeeService]  
})  
export class AppModuleShared {  
}

If we miss the data in any field while creating the employee record, we will get a required field validation error message.

After inserting the data in all the fields, click on the "Save" button. The new employee record will be created and you will be redirected to the /fetch-employee page, displaying records of all the employees. Here, we can also see the action methods Edit and Delete.

If we want to edit an existing employee record, then click the Edit action link. It will open the Edit page as below where we can change the employee data. Notice that we have passed  anemployee id in the URL parameter.

Here, we have changed the Department of employee Swati from Marketing to HR. Click on "Save" to return to the fetch-employee page to see the updated changes as highlighted in the image below.

If we miss any fields while editing employee records, then the Edit view will also throw a required field validation error message as shown in the image below.

Now, we will perform the Delete operation on an employee named Dhiraj, whos Employee ID is 2. Click on the Delete action link, which will open a JavaScript confirmation box asking for a confirmation to delete.

Once we click on Delete button the employee with the name Dhiraj will be deleted and we can see the updated list of employees as shown below.

Conclusion

We have created an ASP.NET Core application using Angular 5 on the front-end and Web API and ADO.NET on the backend, with the help of VS 2017. If you are a fan of Razor and do not want to use Angular for front-end development, then you can also create this same application using Razor.

To know more, please refer to my previous article CRUD Operation With ASP.NET Core MVC Web App Using ADO.NET

You can check my other articles on ASP .NET Core here.

Read this guide to learn everything you need to know about RPA, and how it can help you manage and automate your processes.

DOWNLOAD
Topics:
web dev ,angular 5 ,asp.net core ,web application development ,crud operations

Published at DZone with permission of Ankit Sharma, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.