WebAPI is a framework for building HTTP services that can be consumed by a wide range of clients like browsers, mobiles, desktop, tablets etc. Aurelia is a modern, open source client side JavaScript framework for web and mobile application development. It emphasis on simple conventions and ES6/ES7 support. It is written using ECMAScript 2016. Using Aurelia, we can build applications using modules, classes, decorators etc. In this article we will look into the interaction between both these technologies with a step by step guideance by using Aurelia-fetch-client.
Introduction
WebAPI is a framework for building HTTP services that can be consumed by a wide range of clients like browsers, mobiles, desktop, tablets etc. Aurelia is a modern, open source client side JavaScript framework for web and mobile application development. It emphasis on simple conventions and ES6/ES7 support. It is written using ECMAScript 2016. Using Aurelia, we can build applications using modules, classes, decorators etc. In this article we will look into the interaction between both these technologies with a step by step guidance by using Aurelia-fetch-client.
Creating the WebAPI 2.0 project
Fire up Visual Studio and then File -> New -> Project -> Web -> ASP.NET Web Application.
The next screen that pops up display's a list of templates and we need to choose Web API template.
Also set the authentication mode to No Authentication
Clicking on the OK button creates the Web API project whose structure is as under
Now let us first create the Employee model as under
namespace WebApplication1.Models
{
public class Employee
{
public int EmployeeID { get; set; }
public string EmployeeName { get; set; }
public string EmailAddress { get; set; }
public string PhoneNumber { get; set; }
}
}
Our Employee model is a simple Employee Class which has some scalar properties like EmployeeID,EmployeeName,EmailAddress,PhoneNumber.
Now let us create the DBOperations class inside the Model Folder that will perform the CRUD operation by using ADO.NET and Stored Procedures.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication1.Models
{
public class DBOperations
{
/// <summary>
/// Function: GetEmployees
/// Get the Employee Records
/// </summary>
/// <returns></returns>
public static List<Employee> GetEmployees()
{
List<Employee> lstEmployees = new List<Employee>();
try
{
string ConnectionPath = ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
using (var sqlCon = new SqlConnection(ConnectionPath))
{
using (SqlCommand cmd = new SqlCommand("usp_GetEmployees", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
lstEmployees
.Add
(
new Employee
{
EmployeeID = Convert.ToInt32(dr["EmployeeID"].ToString())
,
EmployeeName = dr["EmployeeName"].ToString()
,
EmailAddress = dr["EmailAddress"].ToString()
,
PhoneNumber = dr["PhoneNumber"].ToString()
}
);
}
dr.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return lstEmployees;
}
/// <summary>
/// Function: InsertEmployee
/// Insert an Employee record
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
public static int InsertEmployee(Employee emp)
{
var result = 0;
try
{
string ConnectionPath = ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
using (var sqlCon = new SqlConnection(ConnectionPath))
{
using (SqlCommand cmd = new SqlCommand("usp_InsertEmployee", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmployeeName", SqlDbType.VarChar).Value = emp.EmployeeName;
cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar).Value = emp.EmailAddress;
cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar).Value = emp.PhoneNumber;
sqlCon.Open();
result = cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
/// <summary>
/// Function: UpdateEmployee
/// Update an Employee record
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
public static int UpdateEmployee(Employee emp)
{
var result = 0;
try
{
string ConnectionPath = ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
using (var sqlCon = new SqlConnection(ConnectionPath))
{
using (SqlCommand cmd = new SqlCommand("usp_UpdateEmployee", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = emp.EmployeeID;
cmd.Parameters.Add("@EmployeeName", SqlDbType.VarChar).Value = emp.EmployeeName;
cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar).Value = emp.EmailAddress;
cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar).Value = emp.PhoneNumber;
sqlCon.Open();
result = cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
/// <summary>
/// Function: DeleteEmployee
/// Deletes an Employee record
/// </summary>
/// <param name="employeeID"></param>
/// <returns></returns>
public static int DeleteEmployee(int employeeID)
{
var result = 0;
try
{
string ConnectionPath = ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
using (var sqlCon = new SqlConnection(ConnectionPath))
{
using (SqlCommand cmd = new SqlCommand("usp_DeleteEmployee", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employeeID;
sqlCon.Open();
result = cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
}
}
Now expand the Controllers folder and create EmployeeController.cs and add the following code
using System.Collections.Generic;
using System.Web.Http;
using WebApplication1.Models;
namespace WebApplication1.Controllers
{
[RoutePrefix("Employee")]
public class EmployeeController : ApiController
{
[Route("EmployeeRecords")]
[HttpGet]
public List<Employee> GetEmployeeRecords()
{
return DBOperations.GetEmployees();
}
[Route("InsertEmployeeRecord")]
[HttpPost]
public List<Employee> InsertEmployeeRecords(Employee emp)
{
return DBOperations.InsertEmployee(emp) == -1 ? DBOperations.GetEmployees() : null;
}
[Route("UpdateEmployeeRecord")]
[HttpPut]
public List<Employee> UpdateEmployeeRecords(Employee emp)
{
return DBOperations.UpdateEmployee(emp) == -1 ? DBOperations.GetEmployees() : null;
}
[Route("DeleteEmployeeRecord")]
[HttpDelete]
public List<Employee> DeleteEmployeeRecords(int employeeId)
{
return DBOperations.DeleteEmployee(employeeId) == -1 ? DBOperations.GetEmployees() : null;
}
}
}
Now from the Nu-get package manager issue
Install-Package Microsoft.AspNet.WebApi.Cors
Once done, then open WebApiConfig.cs file and enable the CORS as under
The complete code is as under
using System.Web.Http;
using System.Web.Http.Cors;
namespace WebApplication1
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
config.EnableCors();
config.EnableCors(new EnableCorsAttribute("*", "*", "*"));
// Web API routes
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
}
}
Run the application and click the API link in the browser to receive the below image
Now we can test the API Services of the EmployeeController.cs through PostMan .
Design the Database Table and Stored Procedures
Next execute the below database script that will create both the table (say tblEmployees) and the insert/update/delete/fetch stored procedures.
USE [YOURDATABASE]
GO
/****** Object: Table [dbo].[tblTestEmployee] Script Date: 8/24/2016 12:12:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTestEmployee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[usp_DeleteEmployee] Script Date: 8/24/2016 12:12:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: RNA Team
-- Description: Delete an employee
-- exec [dbo].[usp_DeleteEmployee] 1
-- =============================================
CREATE PROCEDURE [dbo].[usp_DeleteEmployee]
-- Add the parameters for the stored procedure here
(
@EmployeeID INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DELETE
FROM [dbo].[tblEmployees]
WHERE EmployeeID = @EmployeeID
END
GO
/****** Object: StoredProcedure [dbo].[usp_GetEmployees] Script Date: 8/24/2016 12:12:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: RNA Team
-- Description: Get employee records
-- exec [dbo].[usp_GetEmployees]
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetEmployees]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
EmployeeID
,EmployeeName
,EmailAddress
,PhoneNumber
FROM [dbo].[tblEmployees]
END
GO
/****** Object: StoredProcedure [dbo].[usp_InsertEmployee] Script Date: 8/24/2016 12:12:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: RNA Team
-- Description: Insert an employee
-- exec [dbo].[usp_InsertEmployee] 'Test1','TestEmail1','1234'
-- =============================================
CREATE PROCEDURE [dbo].[usp_InsertEmployee]
-- Add the parameters for the stored procedure here
(
@EmployeeName VARCHAR(50)
,@EmailAddress VARCHAR(50)
,@PhoneNumber VARCHAR(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [dbo].[tblEmployees]
([EmployeeName]
,[EmailAddress]
,[PhoneNumber])
VALUES
(@EmployeeName
,@EmailAddress
,@PhoneNumber)
END
GO
/****** Object: StoredProcedure [dbo].[usp_UpdateEmployee] Script Date: 8/24/2016 12:12:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: RNA Team
-- Description: Update an employee
-- exec [dbo].[usp_UpdateEmployee] 1,'Test2','TestEmail2','12342'
-- =============================================
CREATE PROCEDURE [dbo].[usp_UpdateEmployee]
-- Add the parameters for the stored procedure here
(
@EmployeeID INT
,@EmployeeName VARCHAR(50)
,@EmailAddress VARCHAR(50)
,@PhoneNumber VARCHAR(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE [dbo].[tblEmployees]
SET
[EmployeeName] = @EmployeeName
,[EmailAddress] = @EmailAddress
,[PhoneNumber] = @PhoneNumber
WHERE EmployeeID = @EmployeeID
END
GO
Consuming services through Aurelia-fetch-client
Create a folder say AureliaExperiment at your favourite location.
Then download the basic Aurelia project setup zipped file from here.
Extract the Zipped file and copy it under AureliaExperiment folder. It looks as under
At first, we need to import two statements.
import 'fetch';
import {HttpClient} from 'aurelia-fetch-client';
Then invoke the fetch method on the httpClient instance as shown below in the app.js file.
app.js
---------
import 'fetch';
import {HttpClient} from 'aurelia-fetch-client';
let httpClient = new HttpClient();
export class App {
constructor() {
this.empRecords = null;
this.fetchEmployees();
}
fetchEmployees()
{
httpClient.fetch('http://localhost:22900/Employee/EmployeeRecords')
.then(response => response.json())
.then(data => {
this.empRecords = data;
});
}
insertEmployee()
{
var employee = {
"EmployeeName": this.empName,
"EmailAddress": this.empEmail,
"PhoneNumber": this.empMobileNumber
};
httpClient.fetch('http://localhost:22900/Employee/InsertEmployeeRecord', {
method: "POST",
headers: {
'content-type': 'application/json'
},
body: JSON.stringify(employee)
})
.then(response => response.json())
.then(data => {
this.empRecords = data;
});
}
updateEmployee()
{
var employee = {
"EmployeeID" : this.empID,
"EmployeeName": this.empName,
"EmailAddress": this.empEmail,
"PhoneNumber": this.empMobileNumber
};
httpClient.fetch('http://localhost:22900/Employee/UpdateEmployeeRecord', {
method: "PUT",
headers: {
'content-type': 'application/json'
},
body: JSON.stringify(employee)
})
.then(response => response.json())
.then(data => {
this.empRecords = data;
});
}
deleteEmployee()
{
var employeeID = this.empID;
httpClient.fetch('http://localhost:22900/Employee/DeleteEmployeeRecord?employeeId='+employeeID, {
method: "DELETE",
headers: {
'content-type': 'application/json'
}
})
.then(response => response.json())
.then(data => {
this.empRecords = data;
});
}
}
fetchEmployees method
By default, fetch makes GET requests. All calls to fetch return a Promise object which is used for asynchronous computations. This Promise object will resolve to a Response object. Using this Response object, we parse content and assign it to the UserRecords property.
insertEmployee/updateEmployee/deleteEmployee method
First we are forming the object that needs to be send to the API. Next we are setting the method as "POST"/"PUT"/"DELETE" since it is a HttpPost/HttpPut/HttpDelete operation respectively. We equally need to set the entity's media type to json
headers: {
'content-type': 'application/json'
}
Now let's open the app.html file and write the below template
app.html
---------
<template>
<h2><font color="green">CRUD using Aurelia-fetch-client and WebAPI - Example</font></h2>
<table border="1">
<tr>
<td>
<h1 style="color:blueviolet">Add/Update/Delete Employee</h1>
<table border="1">
<tr>
<td>Employee Id</td>
<td><input id="txtEmployeeId" type="text" value.bind = "empID" /></td>
</tr>
<tr>
<td>Employee Name</td>
<td><input id="txtEmployeeName" type="text" value.bind = "empName" /></td>
</tr>
<tr>
<td>Email Address</td>
<td><input id="txtEmail" type="text" value.bind = "empEmail"/></td>
</tr>
<tr>
<td>Mobile Number</td>
<td><input id="txtMobile" type="text" value.bind = "empMobileNumber"/></td>
</tr>
</table>
</td>
</tr>
</table>
<table border="1">
<tr>
<td><input type="button" id="btnInsert" value="Add Employee" click.delegate = "insertEmployee()"/></td>
<td>
<input type="button" id="btnUpdate" value="Update Employee" click.delegate = "updateEmployee()"/>
<input type="button" id="btnDelete" value="Delete Employee" click.delegate = "deleteEmployee()"/>
</td>
</tr>
</table>
<br />
<h2><font color="blue"><u>Display Employee Records</u></font></h2>
<table border="1">
<thead>
<tr>
<td><b>EmployeeID</b></td>
<td><b>EmployeeName</b></td>
<td><b>EmailAddress</b></td>
<td><b>PhoneNumber</b></td>
</tr>
</thead>
<tbody>
<tr repeat.for="emp of empRecords">
<td>${emp.EmployeeID}</td>
<td>${emp.EmployeeName}</td>
<td>${emp.EmailAddress}</td>
<td>${emp.PhoneNumber}</td>
</tr>
</tbody>
</table>
</template>
Kindly note the line
value.bind = "empID"
and
"EmployeeID": this.empID
It is the two way binding in Aurelia.
The purpose of Repeat.for is to iterate over objects. After we iterate the UserRecords array, we have bound the properties of the object by using the {object.property} syntax of Aurelia.
Now run the application. The initial load screen will be as under
Now let's add an employee
Add one more record
Now perform an update
Finally perform a delete
Reference
HTTP Services
Conclusion
In this article we have performed the CRUD operation using Aurelia-fetch-client and WebAPI. Hope this will be useful. Thanks for reading. Zipped file attached.