.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
- 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.
- 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.
Figure 3: Creating ASP.NET Core Web Application from Project Template
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
- Go to Tools –> NuGet Package Manager –> Package Manager Console
And then run the following command to create a model from the existing database:
1 2 3 |
Scaffold-DbContext "Server=(localdb/MSSQLLocalDB;Database=employeetracker;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models |
- 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”.
- The Database attribute is your database name. In this case, the database name is “employeetracker”.
- 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.
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.
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
Figure 10: Modifying the properties generated by scaffold command to match Position and Office Entities.
1 2 3 4 5 6 7 8 |
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { ///To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings. optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=EmployeeTracker;Trusted_Connection=True;"); } } |
- The constructor below will allow configuration to be passed into the context by dependency injection.
Figure 11: The constructor allows configuration to be passed into the context by dependency injection
1 2 3 4 |
public EmployeeTrackerContext(DbContextOptions<EmployeeTrackerContext> options) : base(options) { } |
- Open the appsettings.json file and add the following script for our database connection string below.
1 2 3 |
"ConnectionStrings": { "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=EmployeeTracker;Trusted_Connection=True;" } |
Figure 12: Connection string.
- Open Startup.cs and Add the following using statements at the start of the file,
1 |
using Microsoft.EntityFrameworkCore; |
Add the following lines of code within ConfigureServices() method, below “services.AddMvc()”;
1 2 3 4 |
public void ConfigureServices(IServiceCollection services) { services.AddDbContext<EmployeeTrackerContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); } |
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 method
Startup.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
1 2 3 4 5 6 7 |
public interface IEmployeeTrackerService.Services { IEnumerable<Employee> GetAllEmployee(); string GetOffice(int id); string GetPosition(int id); } |
IDashBoardService
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
using EmployeeTracker.Models; using EmployeeTracker.Models.ViewModels.Dashboards; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EmployeeTracker.Services { public interface IDashBoardService { //Employee GetById(int id); int TotalPositions(); int TotalOffices(); int TotalEmployees(); IEnumerable<ChartData> EmployeesPerYear(); IEnumerable<ChartData> EmployeesPerOffice(); } } |
1 |
Pie chart example data. Note how there is only a single array of key-value pairs. |
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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 |
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EmployeeTracker.Models { public class ChartData { public string Key { get; set; } public int Value { get; set; } } } |
The IEmployeeTrackerService interface defines methods that the service provides to the EmployeeTracker.
This interface is implemented by a concrete type, EmployeeTrackerService
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using EmployeeTracker.Models; using Microsoft.EntityFrameworkCore; namespace EmployeeTracker.Services { public class EmployeeTrackerService : IEmployeeTrackerService { // create an property of type EmployeeTrackContext and injected to the constructor private EmployeeTrackerContext _context; // constructor to intialize the context to be ready to connect and access the database public EmployeeTrackerService(EmployeeTrackerContext context) { _context = context; } public IEnumerable<Employee> GetAllEmployee() { return _context.Employee.ToList(); } // Get a specific office base on the ID passed public string GetOffice(int id) { return _context.Office.Single(o => o.OfficeId == id).OfficeName; } // Get the specific position base on the the given Id public string GetPosition(int id) { return _context.Position.Single(p => p.PositionId == id).PositionName; } } } |
DashBoardService
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
using EmployeeTracker.Models; using EmployeeTracker.Models.ViewModels.Dashboards; using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EmployeeTracker.Services { public class DashBoardService :IDashBoardService { private EmployeeTrackerContext _context; // constructor to intialize the context to be ready to connect and access the database public DashBoardService (EmployeeTrackerContext context) { _context = context; } public DashBoardService() { } // Get the specific employee base on the Id passed /* public Employee GetById(int id) { return GetAllEmployee().FirstOrDefault(emp => emp.EmployeeId == id); } */ public int TotalPositions() { return _context.Position.Count(); } // Get the total number of the office public int TotalOffices() { return _context.Office.Count(); } // Get the Total number of employee public int TotalEmployees() { return _context.Employee.Count(); } public IEnumerable<ChartData> EmployeesPerYear() { // return listof employees var listOfEmployee = _context.Employee; //Group the employees by startDate and the result be save in key value pair type of chartaData get the result key as Key and count the result as Value. var chartdata = listOfEmployee.AsEnumerable().GroupBy(a => new { a.StartDate.Year }).Select(result => new ChartData { Key = result.Key.Year.ToString(), Value = result.Count() }); return chartdata; } // Get employees by office public IEnumerable<ChartData> EmployeesPerOffice() { // GetA llEmployee included their respectif office var listOfEmployee = _context.Employee.Include(a => a.Office); // Group the employee by ofifice select result and save it to the chartData proprties as key, value pair. var chartdata = listOfEmployee.AsEnumerable().GroupBy(a => new { a.OfficeId, a.Office }).Select(result => new ChartData { Key = result.Key.Office.OfficeName.ToString(), Value = result.Count() }); return chartdata; } } } |
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
1 2 3 4 5 6 7 8 9 10 11 |
// This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddMvc(); services.AddScoped<IEmployeeTrackerService, EmployeeTrackerService>(); services.AddScoped<IDashBoardService, DashBoardService>(); services.AddDbContext<EmployeeTrackerContext>(options => options. UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); } |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[Produces("application/json")] [Route("api/Employees")] public class EmployeesController : Controller { private readonly IEmployeeTrackerService _IEmployeeTrackerService; public EmployeesController (IEmployeeTrackerService IEmployeeTrackerService) { _IEmployeeTrackerService = IEmployeeTrackerService; } // GET api/values [HttpGet] public ActionResult Get() { // // Get all the employees from the database refered to the domain model. var employeesList = _IEmployeeTrackerService.GetAllEmployee(); } return Ok(employeesList); } |
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 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
{ employeeId: 1, firstName: "Eric", lastName: "Smith", positionId: 1, officeId: 7, sex: "M", age: 62, startDate: "2012-08-26T00:00:00", salary: 170750, updatedUtc: "2018-10-03T10:00:48.6854765", office: null, position: null }, |
Here is our Employee Domain entity :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public partial class Employee { public int EmployeeId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public int PositionId { get; set; } public int OfficeId { get; set; } public string Sex { get; set; } public int Age { get; set; } public DateTime StartDate { get; set; } public decimal Salary { get; set; } public DateTime UpdatedUtc { get; set; } public Office Office { get; set; } public Position Position { get; set; } } |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EmployeeTracker.ViewModels.Employee { public class EmployeeAssetModel { // This is the model for the view different for the domain model. // Data to display in the view public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Position { get; set; } public string Office { get; set; } public string Sex { get; set; } public int Age { get; set; } public DateTime UpdatedUtc { get; set; } // System.DateTime moment = new DateTime(); public DateTime StartDate { get; set; } public decimal Salary { get; set; } } } |
1 2 3 4 5 6 7 |
namespace EmployeeTracker.ViewModels.Employees { public class EmployeeIndexModel { public IEnumerable<EmployeeAssetModel> Employees { get; set; } } } |
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.
Move the ChartData from Models folder to Models/ViewModel/DashBoard and fixes the reference issues before continuing.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
[Produces("application/json")] [Route("api/Employees")] public class EmployeesController : Controller { private readonly IEmployeeTrackerService _IEmployeeTrackerService; public EmployeesController (IEmployeeTrackerService IEmployeeTrackerService) { _IEmployeeTrackerService = IEmployeeTrackerService; } // GET api/values [HttpGet] public ActionResult Get() { // Get all the employees from the database refered to the domain model. var employeesList = _IEmployeeTrackerService.GetAllEmployee(); // We need to transform the domain model to our view model var employeeListResult = employeesList.Select(result => new EmployeeAssetModel { Id = result.EmployeeId, FirstName = result.FirstName, LastName = result.LastName, //We modefied the following 2 properties Position = _IEmployeeTrackerService.GetPosition(result.PositionId), Office = _IEmployeeTrackerService.GetOffice(result.OfficeId), Sex = result.Sex, Age = result.Age, Salary = result.Salary, StartDate = result.StartDate, UpdatedUtc = result.UpdatedUtc, }); // create the EmployeeIndexModel object that is IEnumerable of EmployeeAssetModel to return to the view var model = new EmployeeIndexModel { Employees = employeeListResult }; return Ok(model) ; } } |
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 EmployeeTrackerDbContext
directly, 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
using EmployeeTracker.Models.ViewModels.Dashboards; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace EmployeeTracker.Models.ViewModels { public class Dashboard { public int TotalPositions { get; set; } public int TotalOffices { get; set; } public int TotalEmployees { get; set; } public IEnumerable<ChartData> EmployeesPerYear { get; set; } public IEnumerable<ChartData> EmployeesPerOffice { get; set; } } } |
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.
1 2 3 4 5 6 7 8 9 10 |
public class DashBoardsController : Controller { // create the property of type IEmployeeService private readonly IDashBoardService _IDashBoardService; public DashBoardsController(IDashBoardService IdashBoardService) { _IDashBoardService = IdashBoardService; } } |
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 EmployeeTrackerDbContext
directly, 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
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.