.In this tutorial, you will build an Employee Tracker app for tracking employees Details, records or data from several branches, aggregate into a dashboard web-application that can be used to monitors employees data source in one place.

The first part of Building an Employee Tracker using Angular 6, Nvd3, D3 and ASP.NET.Core Web API Series.

In this Tutorial you will learn how to :

• Part 1- Create a web API with ASP.NET Core 2.* in c# with Database First   (This Post)
• Create-a-spa-in-angular-6-with-angular-Datatable-and-angular-nvd3-part-2Create

This tutorial’s demo-1   and demo-2, along with associated source code can be found here. A demo is hosted in Azure.

 

 Figure 1: ASP.NET Core Web API  endpoint employees

 

After building the employeetracker API, bellow is the frontend for the employeetracker web app we are going to build with angular 6 and nvd3  that consume our employee tracker API, the demo can be found here.

 Figure 2 A:  employeetracker dashboard frontend angular and nvd3 api.

 Figure 2 B: employeetracker employee table frontend with  datatable  J-query plugin API.

Part 1: Create a Web API Application with ASP.NetCore in C#

In this part, we will see how to create  (.NET Core 2.* project) and will set up the Employee API and we will explore the database first approach, generate a model with the scaffolding command from the existing Database set up.

Prerequisite:

• ASP.NET Core Web API 2.2
• SQL Server 2014
• SSMS 2014( SQL Server Management Studio)
• Visual Studio 2017,
• Microsoft  EntityFrameworkCore Tools

Step  1

1. Open up Visual Studio 2017 and let’s create a new ASP.NET Core Web Application project. To do this, select File > New > Project. In the New Project dialog select Visual C# > Web > ASP.NET Core Web Application.
2. name it EmployeeTracker, set the Solution name to EmployeeTrackerCore, choose a Location and click OK. and then select “ API” within ASP.NETCore templates as shown in the figure below. 
   

   Click to enlarge

    Figure 3: Creating ASP.NET Core Web Application from Project Template

   

   Click to enlarge

    

Step 2     Create Employees database

This tutorial uses a  employees database on your LocalDb instance as the existing database. For the purpose of this tutorial, we will run the SQL scripts which contains the existing database. You can find the SQL script here.

 Figure 4: Connecting to SQLServer 2014 

                            Figure 5: Running the SQL script from MSMS on the employeeTracker database

Our database is created and data populated in the employeetracker database, we are ready to start building our employeetracker web API.

Step 3  Install Entity Framework Core

 

                                                                                                                       Figure 6: Installing EFCore Packages from Nuget

Entity Framework Core is an object-relational mapper (O/RM) that enables .NET developers to work with a database using .NET objects. It eliminates the need for most of the data-access code that developers usually need to write. EF Core supports many database engines other than SQL Server.

To add packages in ASP.NET Core; you can use the Package Manager Console or via NuGet Package Manager (NPM). In this tutorial, we used NPM visual reference.

Considering that we are going to use the Database-First development approach to work with the existing database, we need to install the additional packages below as well:

• EntityFrameworkCore.Tools (v 2.1.4)
• EntityFrameworkCore.SqlServer (v 2.1.4)

Step 4  Creating Entity Models from Existing Database

Let create the Entity Framework models based on our existing database(employeeTracker) that we have just created earlier.

Option 1: Using Package Manager Console

1. Go to Tools –> NuGet Package Manager –> Package Manager Console

And then run the following command to create a model from the existing database:

1. The Server attribute above is the SQL server instance name. You can find it in SQL Server Explorer or SQL Management Studio by right clicking on your database. For this project, the server name is “local/MSSQLLocalDB”.
2. The Database attribute is your database name. In this case, the database name is “employeetracker”.
3. The –OutputDir attribute allows you to specify the location of the files generated. In this case, we’ve set it to Models in our project.

Here is the result :  

            Figure 7: result after running the scaffold command

 

 Figure 8: Creating the property for the connection string

The entity classes are simple C# objects that represent the data you will be querying and saving. Here are the  Employee, Position and Office entity classes. The context represents a session with the database and allows you to query and save instances of the entity classes.

The reverse engineer process created three entities employee.cs, office.cs and position.cs  and a derived context (EmployeeTrackerContext.cs) based on the schema of the existing database.  Result:

Step 5  Using Dependency Injection to register our DBContext.

A dependency is an object that another object requires. You can read more about Dependency Injection with ASP.NET Core here

The following step we are going to register our EmployeeTrackeContext class using Dependency Injection. To follow the ASP.NET Core configuration pattern, we will move the database provider configuration to Startup.cs. Here are the steps:

To install EntityFrameworkCore, you install the package for the EntityFrameworkCore Core database provider you want to target in this tutorial we used SQL server

When we create a project with Microsoft visual studio with Asp.netCore web Api, Microsoft visual studio create for us a  default web API fully functional that can be used as services.

After you run successfully your project  …..

• Open Models\EmployeeTrackerContext.cs file
• Remove the OnConfiguring() method and add the following code below.
  

  Click to enlarge

   Figure 9: Removing the default onconfiguring method

   

  Note that there are two the properties generated by scaffolding  command are different from our Models make change for those properties as shown in the figure below

  

  Click to enlarge

   

   Figure 10: Modifying the properties generated by scaffold command to match  Position and Office Entities.

• The constructor below will allow configuration to be passed into the context by dependency injection.
  

  Click to enlarge

   

   Figure 11: The constructor allows configuration to be passed into the context by dependency injection

• Open the appsettings.json file and add the following script for our database connection string below.

 Figure 12: Connection string.

• Open Startup.cs and   Add the following using statements at the start of the file,

Add the following lines of code within ConfigureServices() method,  below “services.AddMvc()”;

 Figure 13: Adding the DbContext in the startup using DI.

Step 6   Add Service to our project  EmployeeTracker.

Right Click your solution > select add >new  Folder> name  Services as shown below:

Inside the Service, folder creates an IEmployeeTrackerService interface and EmployeeTrackerService class.

Let create our IEmployeeTrackerService  : Right click  EmployeeTracker Project > select add> new Item > in the dialog box select interface name it  IEmployeeTrackerService click ok and add another Interface name it IDashBoardService, IDashBoardService will be Responsible for our Dashboard.

 

 

 Figure 14: Creating IEmployeeTrackerService and EmployeeTrackerService.

Step 7    Use of Dependency Injection for our  Web API

Why should we use  Dependency Injection?

Dependency injection addresses these problems through:

• The use of an interface to abstract the dependency implementation.
• Registration of the dependency in a service container. ASP.NET Core provides a built-in service container, IServiceProvider. Services are registered in the app’s methodStartup.ConfigureServices.
• Injection of the service into the constructor of the class where it’s used. The framework takes on the responsibility of creating an instance of the dependency and disposing of it when it’s no longer needed.

 

In our interface we  will create all the methods signature that we are going to use for the employeeTracker:

IDashBoardService

IDashBoardService

 

To create a pie, bar chart  we need to provide the data in form of an array of key-value pairs, we are going to create an entity of  type ChartData with the properties key and value in our EmployeeTracker/ Models :

The IEmployeeTrackerService interface defines methods that the service provides to the EmployeeTracker.

This interface is implemented by a concrete type, EmployeeTrackerService:

DashBoardService

EmployeeTrackerService and DashBoardService request EmployeeTrackerContext in the constructor. It’s not unusual to use dependency injection in a chained fashion. Each requested dependency, in turn, requests its own dependencies. The container resolves the dependencies in the graph and returns the fully resolved service. The collective set of dependencies that must be resolved is typically referred to as a dependency tree, dependency graph, or object graph.

IEmployeeTrackerService, IDashBoardService and EmployeeTrackerService, DashBoardService must be registered in the service container. IEmployeeTrackerService, IDashBoardService and EmployeeTrackerService, DashBoardService are registered inStartup.ConfigureServices

 

Step 8  Create Our Controllers  for  EmployeeTrackerWeb API

The default project created contained values controller as default controller. We are going to add two controllers Employees controller and Dashboard controller.

The employeesController is responsible to handle the request, response for employees from our EmployeeServices whereas the DashboardsController will be responsible for the dashboard data from the DashBoardService.

Right-click to our controller folder in the EmployeeTracker >  add > Controller> select Empty API Controller :

 Figure 15: Create the controllers.

Follow the same steps to create Dashboards Controller

Step 7  Inject an instance of the EmployeeService to the EmployeesControllers.

In a class where database access is required, an instance of the service is requested via the constructor and assigned to a private field for use by class methods. In the EmployeeTracker app, IEmployeeTrackerService is used to:

• Obtain a list of the employees from our data source.

IDashBoardService is used to

• Obtain the dashboard data from our data source.

Notice how the calling code only interacts with the interface’s implementation, EmployeeTrackerService. The calling code doesn’t use the EmployeeTrackerDbContext directly:

 

Here is the result using Postman

 Figure 16: Result of our api call using Postman.

The result for our EmployeeTracker / EmployeesController does not satisfy the response’s resource needed to return to the API,  here is employee object returned from the API call :

Here is our Employee Domain entity :

 

Our Domain Entity objects are a representation of the database that we are using to retrieve data used for the API business logic. The employee Entity object will contain the properties represented in our case the SQL table.

Solution

We need to create the ViewModels which will be used by the API layer for HTTP requests and responses as single objects or sets of objects.

The ViewModels are the extension of the EmployeeTracker Entities and help give more information for the consumer of the APIs.

Let create a ViewModel

Right click on the  Models folder adds a folder name it ViewModels, inside the ViewModel folder create to folders Employees and dashboard.

Inside Employees create a class file names it EmployeeAssetModel and EmployeeIndexMode:

As you can see our  ViewModel is an extension of our EmployeeTracker with the officeName, the postionName’s properties and EmployeeIndexModel return IEnumerable of EmployeeAssetModel named Employees.

Let modify our EmployeesController  to use VieuwModel EmployeeAssetModel  and EmployeeIndexModel.

Notes

Move the ChartData from  Models folder to Models/ViewModel/DashBoard and fixes the reference issues before continuing.

In the Code above we inject the IEmployeeTrackerService in our EmployeesConttoller for us to access the data layer.

In our EmployeesController where database access is required, an instance of the service is requested via the constructor and assigned to a private field for use by class methods IEmployeeTrackerService is used to obtain a list of employees  as response.

Notice how the calling code only interacts with the interface’s implementation, EmployeeTrackerService. The calling code doesn’t use the EmployeeTrackerDbContextdirectly, this is the advantage of using Dependency Injection for our service.

After getting the result from the data access layer,  we need to pass the data to the EmployeesAssetModel and EmployeesIndexModel as shown in the code above.

Result

 Figure 17: Result of employees response with ViewModel entities with the postman.

 

 Figure 16: Result of employees response with ViewModel entities with our endpoint.

Step 7  Inject an instance of the DashBoardService to the DashBoardsControllers.

To process the data for the dashboard chart we need to transform our data in an array of a key-value pair, the ChartData class represents property for the data to use with the chart NVd3 API.

Next, Create a class in the ViewModel /DashBoard folder called Dashboard.cs with the following properties:

Figure ……….

Step 9  Create Dashboards Controller

Right-click to our controller folder in the EmployeeTracker >  add > Controller> select Empty

API Controller: DashBoardsController 

 

 

 Figure 17 A:  employeetracker add contorller.

Figure …

In the Code above we inject the IDashBoardrService in our DashBoardsConttoller for us to access the data layer.

In our  DashBoardsController where database access is required, an instance of the service is requested via the constructor and assigned to a private field for use by class methods IDashBoardsService is used to obtain dashboard data  as response.

Notice how the calling code only interacts with the interface’s implementation, IDashBoardService The calling code doesn’t use the EmployeeTrackerDbContextdirectly, this is the advantage of using Dependency Injection for our service.

After getting the result from the data access layer,  we need to pass the data to the Dashboard entity ViewModel to return the response to the endpoint.

Step 10  Result of dashboard endpoint

 

 

 Figure 18: Result of Dashboard response with ViewModel entities with our endpoint.

NEXT Tutorial

• Part 2-Create a SPA in angular 6

 

Summary

In this part of the series, we’ve learned to build an  ASP.NetCore Web API application using Entity Framework Core with existing database reverse engineering. We also learned the power of scaffolding to quickly generate classes, properties and Dbcontext’s Class based on the existing Database in just a few clicks.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

basic data access using Entity Framework Core. You reverse engineer an existing database to create an Entity Framework model.