Post
Ask Question

Create An App Using Angular 10, .NET Core Web API And SQL Server

In this article, we will learn how to develop a Web application from scratch using popular technologies such as SQL Server for database, dot net core, Web API for Backend Development and the latest ANGULAR 10 for Front-End Web development. We will first start creating databases and objects needed for our app, then develop API end points
using dot net core web API. Finally, we will be using angular 10 to design the front end part of our app.
 
We will learn how to create the navigation menu and tables using bootstrap and routing to our angular app. We will add a modal pop up window with drop downs and date pickers, and also add and upload profile picture functionality and store it in our app. We will also learn how to add custom filtering and sorting features without using any third party packages.

Step 1
 
Let's first open up SQL Server Management Studio. Let's connect to a local database to do that, just type dot in the server name and click on Connect.
 
Now let's create the database named EmployeeDB.
  1. Create Database EmployeeDB    
  2.     
  3. Create table dbo.Department(    
  4.    DepartmentID int identity(1,1),    
  5.    DepartmentName varchar(500)    
  6. )    
  7.     
  8. Create table dbo.Employee(    
  9.    EmployeeId int identity(1,1),    
  10.    EmployeeName varchar(500),    
  11.    Department varchar(500),    
  12.    DateOfJoining date,    
  13.    PhotoFileName varchar(500)    
  14. )    
  15. insert into dbo.Department('Accounts')    
  16.     
  17. insert into dbo.Employee values('Sagar','IT','2020-04-20','anonymous.png')    

Step 2
 
Now Let's open up VISUAL STUDIO 2019 and click on create a new project.

 
Select ASP.Net core Web Application and click on next.

 


Select API and click on create

 

The program.cs contains the main program which is the entry point of our project also it creates web host which basically helps the app to listen to http requests
 
The startup class configures all the services required for our app services which are basically reusable components that can be used across our app using the dependency injection. It also contains the configure method which creates our app's request processing pipeline.
 
Step 3 - Let's make couple of changes to the start up class

As below we have enabled cors. By default all web api projects come with a security which blocks requests coming from different domains.
 
Now lets disable the security and allow the requests to be served.
 
Lets also modify the serializer class to keep the json serializer as our default .To do that install the Nuget package

  1. using System;    
  2. using System.Collections.Generic;    
  3. using System.Linq;    
  4. using System.Threading.Tasks;    
  5. using Microsoft.AspNetCore.Builder;    
  6. using Microsoft.AspNetCore.Hosting;    
  7. using Microsoft.AspNetCore.Mvc;    
  8. using Microsoft.Extensions.Configuration;    
  9. using Microsoft.Extensions.DependencyInjection;    
  10. using Microsoft.Extensions.Hosting;    
  11. using Microsoft.Extensions.Logging;    
  12. using Newtonsoft.Json.Serialization;    
  13. using System.IO;    
  14. using Microsoft.Extensions.FileProviders;    
  15.     
  16. namespace WebAPI    
  17. {    
  18.     public class Startup    
  19.     {    
  20.         public Startup(IConfiguration configuration)    
  21.         {    
  22.             Configuration = configuration;    
  23.         }    
  24.     
  25.         public IConfiguration Configuration { get; }    
  26.     
  27.         // This method gets called by the runtime. Use this method to add services to the container.    
  28.         public void ConfigureServices(IServiceCollection services)    
  29.         {    
  30.             //Enable CORS    
  31.             services.AddCors(c =>    
  32.             {    
  33.                 c.AddPolicy("AllowOrigin", options => options.AllowAnyOrigin().AllowAnyMethod()    
  34.                  .AllowAnyHeader());    
  35.             });    
  36.     
  37.             //JSON Serializer    
  38.             services.AddControllersWithViews()    
  39.                 .AddNewtonsoftJson(options =>    
  40.                 options.SerializerSettings.ReferenceLoopHandling = Newtonsoft    
  41.                 .Json.ReferenceLoopHandling.Ignore)    
  42.                 .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver    
  43.                 = new DefaultContractResolver());    
  44.     
  45.             services.AddControllers();    
  46.         }    
  47.     
  48.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.    
  49.         public void Configure(IApplicationBuilder app, IWebHostEnvironment env)    
  50.         {    
  51.             app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());    
  52.     
  53.             if (env.IsDevelopment())    
  54.             {    
  55.                 app.UseDeveloperExceptionPage();    
  56.             }    
  57.     
  58.             app.UseRouting();    
  59.     
  60.             app.UseAuthorization();    
  61.     
  62.             app.UseEndpoints(endpoints =>    
  63.             {    
  64.                 endpoints.MapControllers();    
  65.             });    
  66.     
  67.     
  68.             app.UseStaticFiles(new StaticFileOptions    
  69.             {    
  70.                 FileProvider = new PhysicalFileProvider(    
  71.                     Path.Combine(Directory.GetCurrentDirectory(),"Photos")),    
  72.                 RequestPath="/Photos"    
  73.             });    
  74.         }    
  75.     }    
  76. }    
Step 4
 
Lets create a model used for our app .Create a folder name Models and add a class file named department.cs ,Employee.cs and add properties
  1. using System;    
  2. using System.Collections.Generic;    
  3. using System.Linq;    
  4. using System.Threading.Tasks;    
  5.     
  6. namespace WebAPI.Models    
  7. {    
  8.     public class Department    
  9.     {    
  10.         public int DepartmentId { get; set; }    
  11.     
  12.         public string DepartmentName { get; set; }    
  13.     }    
  14. }    
  1. using System;    
  2. using System.Collections.Generic;    
  3. using System.Linq;    
  4. using System.Threading.Tasks;    
  5.     
  6. namespace WebAPI.Models    
  7. {    
  8.     public class Employee    
  9.     {    
  10.         public int EmployeeId { get; set; }    
  11.         public string EmployeeName { get; set; }    
  12.         public string Department { get; set; }    
  13.         public string DateOfJoining { get; set; }    
  14.         public string PhotoFileName { get; set; }    
  15.     
  16.     }    
  17. }    
Step 5
 
Configure the database connection in appsettings.json
  1. {    
  2.   "ConnectionStrings": {    
  3.     "EmployeeAppCon": "Data Source=.;Initial Catalog=EmployeeDB; Integrated Security=true"    
  4.   },    
  5.   "Logging": {    
  6.     "LogLevel": {    
  7.       "Default": "Information",    
  8.       "Microsoft": "Warning",    
  9.       "Microsoft.Hosting.Lifetime": "Information"    
  10.     }    
  11.   },    
  12.   "AllowedHosts": "*"    
  13. }    
Step 6
 
Now lets add a controller to add api methods for department

 
To access the configuration from appsettings file lets make use of the dependency injection as below and lets add the api methods to perform CRUD operations.Avoid using raw sql queries and make use of Stored procedures or entity framework 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Http;  
  6. using Microsoft.AspNetCore.Mvc;  
  7. using Microsoft.Extensions.Configuration;  
  8. using System.Data.SqlClient;  
  9. using System.Data;  
  10. using WebAPI.Models;  
  11.   
  12. namespace WebAPI.Controllers  
  13. {  
  14.     [Route("api/[controller]")]  
  15.     [ApiController]  
  16.     public class DepartmentController : ControllerBase  
  17.     {  
  18.         private readonly IConfiguration _configuration;  
  19.   
  20.         public DepartmentController(IConfiguration configuration)  
  21.         {  
  22.             _configuration = configuration;  
  23.         }  
  24.   
  25.         [HttpGet]  
  26.         public JsonResult Get()  
  27.         {  
  28.             string query = @"  
  29.                     select DepartmentId, DepartmentName from dbo.Department";  
  30.             DataTable table = new DataTable();  
  31.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  32.             SqlDataReader myReader;  
  33.             using(SqlConnection myCon=new SqlConnection(sqlDataSource))  
  34.             {  
  35.                 myCon.Open();  
  36.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  37.                 {  
  38.                     myReader = myCommand.ExecuteReader();  
  39.                     table.Load(myReader); ;  
  40.   
  41.                     myReader.Close();  
  42.                     myCon.Close();  
  43.                 }  
  44.             }  
  45.   
  46.             return new JsonResult(table);  
  47.         }  
  48.   
  49.   
  50.         [HttpPost]  
  51.         public JsonResult Post(Department dep)  
  52.         {  
  53.             string query = @"  
  54.                     insert into dbo.Department values   
  55.                     ('"+dep.DepartmentName+@"')  
  56.                     ";  
  57.             DataTable table = new DataTable();  
  58.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  59.             SqlDataReader myReader;  
  60.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  61.             {  
  62.                 myCon.Open();  
  63.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  64.                 {  
  65.                     myReader = myCommand.ExecuteReader();  
  66.                     table.Load(myReader); ;  
  67.   
  68.                     myReader.Close();  
  69.                     myCon.Close();  
  70.                 }  
  71.             }  
  72.   
  73.             return new JsonResult("Added Successfully");  
  74.         }  
  75.   
  76.   
  77.         [HttpPut]  
  78.         public JsonResult Put(Department dep)  
  79.         {  
  80.             string query = @"  
  81.                     update dbo.Department set   
  82.                     DepartmentName = '"+dep.DepartmentName+@"'  
  83.                     where DepartmentId = "+dep.DepartmentId + @"   
  84.                     ";  
  85.             DataTable table = new DataTable();  
  86.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  87.             SqlDataReader myReader;  
  88.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  89.             {  
  90.                 myCon.Open();  
  91.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  92.                 {  
  93.                     myReader = myCommand.ExecuteReader();  
  94.                     table.Load(myReader); ;  
  95.   
  96.                     myReader.Close();  
  97.                     myCon.Close();  
  98.                 }  
  99.             }  
  100.   
  101.             return new JsonResult("Updated Successfully");  
  102.         }  
  103.   
  104.   
  105.         [HttpDelete("{id}")]  
  106.         public JsonResult Delete(int id)  
  107.         {  
  108.             string query = @"  
  109.                     delete from dbo.Department  
  110.                     where DepartmentId = " + id + @"   
  111.                     ";  
  112.             DataTable table = new DataTable();  
  113.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  114.             SqlDataReader myReader;  
  115.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  116.             {  
  117.                 myCon.Open();  
  118.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  119.                 {  
  120.                     myReader = myCommand.ExecuteReader();  
  121.                     table.Load(myReader); ;  
  122.   
  123.                     myReader.Close();  
  124.                     myCon.Close();  
  125.                 }  
  126.             }  
  127.   
  128.             return new JsonResult("Deleted Successfully");  
  129.         }  
  130.     }  
  131. }  
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Http;  
  6. using Microsoft.AspNetCore.Mvc;  
  7. using Microsoft.Extensions.Configuration;  
  8. using System.Data.SqlClient;  
  9. using System.Data;  
  10. using WebAPI.Models;  
  11. using System.IO;  
  12. using Microsoft.AspNetCore.Hosting;  
  13.   
  14. namespace WebAPI.Controllers  
  15. {  
  16.     [Route("api/[controller]")]  
  17.     [ApiController]  
  18.     public class EmployeeController : ControllerBase  
  19.     {  
  20.         private readonly IConfiguration _configuration;  
  21.         private readonly IWebHostEnvironment _env;  
  22.   
  23.         public EmployeeController(IConfiguration configuration, IWebHostEnvironment env)  
  24.         {  
  25.             _configuration = configuration;  
  26.             _env = env;  
  27.         }  
  28.   
  29.         [HttpGet]  
  30.         public JsonResult Get()  
  31.         {  
  32.             string query = @"  
  33.                     select EmployeeId, EmployeeName, Department,  
  34.                     convert(varchar(10),DateOfJoining,120) as DateOfJoining  
  35.                     ,PhotoFileName  
  36.                     from dbo.Employee  
  37.                     ";  
  38.             DataTable table = new DataTable();  
  39.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  40.             SqlDataReader myReader;  
  41.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  42.             {  
  43.                 myCon.Open();  
  44.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  45.                 {  
  46.                     myReader = myCommand.ExecuteReader();  
  47.                     table.Load(myReader); ;  
  48.   
  49.                     myReader.Close();  
  50.                     myCon.Close();  
  51.                 }  
  52.             }  
  53.   
  54.             return new JsonResult(table);  
  55.         }  
  56.   
  57.   
  58.         [HttpPost]  
  59.         public JsonResult Post(Employee emp)  
  60.         {  
  61.             string query = @"  
  62.                     insert into dbo.Employee   
  63.                     (EmployeeName,Department,DateOfJoining,PhotoFileName)  
  64.                     values   
  65.                     (  
  66.                     '" + emp.EmployeeName + @"'  
  67.                     ,'" + emp.Department + @"'  
  68.                     ,'" + emp.DateOfJoining + @"'  
  69.                     ,'" + emp.PhotoFileName + @"'  
  70.                     )  
  71.                     ";  
  72.             DataTable table = new DataTable();  
  73.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  74.             SqlDataReader myReader;  
  75.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  76.             {  
  77.                 myCon.Open();  
  78.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  79.                 {  
  80.                     myReader = myCommand.ExecuteReader();  
  81.                     table.Load(myReader); ;  
  82.   
  83.                     myReader.Close();  
  84.                     myCon.Close();  
  85.                 }  
  86.             }  
  87.   
  88.             return new JsonResult("Added Successfully");  
  89.         }  
  90.   
  91.   
  92.         [HttpPut]  
  93.         public JsonResult Put(Employee emp)  
  94.         {  
  95.             string query = @"  
  96.                     update dbo.Employee set   
  97.                     EmployeeName = '" + emp.EmployeeName + @"'  
  98.                     ,Department = '" + emp.Department + @"'  
  99.                     ,DateOfJoining = '" + emp.DateOfJoining + @"'  
  100.                     where EmployeeId = " + emp.EmployeeId + @"   
  101.                     ";  
  102.             DataTable table = new DataTable();  
  103.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  104.             SqlDataReader myReader;  
  105.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  106.             {  
  107.                 myCon.Open();  
  108.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  109.                 {  
  110.                     myReader = myCommand.ExecuteReader();  
  111.                     table.Load(myReader); ;  
  112.   
  113.                     myReader.Close();  
  114.                     myCon.Close();  
  115.                 }  
  116.             }  
  117.   
  118.             return new JsonResult("Updated Successfully");  
  119.         }  
  120.   
  121.   
  122.         [HttpDelete("{id}")]  
  123.         public JsonResult Delete(int id)  
  124.         {  
  125.             string query = @"  
  126.                     delete from dbo.Employee  
  127.                     where EmployeeId = " + id + @"   
  128.                     ";  
  129.             DataTable table = new DataTable();  
  130.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  131.             SqlDataReader myReader;  
  132.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  133.             {  
  134.                 myCon.Open();  
  135.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  136.                 {  
  137.                     myReader = myCommand.ExecuteReader();  
  138.                     table.Load(myReader); ;  
  139.   
  140.                     myReader.Close();  
  141.                     myCon.Close();  
  142.                 }  
  143.             }  
  144.   
  145.             return new JsonResult("Deleted Successfully");  
  146.         }  
  147.   
  148.   
  149.         [Route("SaveFile")]  
  150.         [HttpPost]  
  151.         public JsonResult SaveFile()  
  152.         {  
  153.             try  
  154.             {  
  155.                 var httpRequest = Request.Form;  
  156.                 var postedFile = httpRequest.Files[0];  
  157.                 string filename = postedFile.FileName;  
  158.                 var physicalPath = _env.ContentRootPath + "/Photos/" + filename;  
  159.   
  160.                 using(var stream = new FileStream(physicalPath, FileMode.Create))  
  161.                 {  
  162.                     postedFile.CopyTo(stream);  
  163.                 }  
  164.   
  165.                 return new JsonResult(filename);  
  166.             }  
  167.             catch (Exception)  
  168.             {  
  169.   
  170.                 return new JsonResult("anonymous.png");  
  171.             }  
  172.         }  
  173.   
  174.   
  175.         [Route("GetAllDepartmentNames")]  
  176.         public JsonResult GetAllDepartmentNames()  
  177.         {  
  178.             string query = @"  
  179.                     select DepartmentName from dbo.Department  
  180.                     ";  
  181.             DataTable table = new DataTable();  
  182.             string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");  
  183.             SqlDataReader myReader;  
  184.             using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
  185.             {  
  186.                 myCon.Open();  
  187.                 using (SqlCommand myCommand = new SqlCommand(query, myCon))  
  188.                 {  
  189.                     myReader = myCommand.ExecuteReader();  
  190.                     table.Load(myReader); ;  
  191.   
  192.                     myReader.Close();  
  193.                     myCon.Close();  
  194.                 }  
  195.             }  
  196.   
  197.             return new JsonResult(table);  
  198.         }  
  199.   
  200.   
  201.     }  
  202. }  
Step 7
 
Now lets create the angular project 

 
Type comand Code. It will open  Visual Studio code as shown below


Step 8
 
Now let's create the services and components required for the application based on the below architecture.

We need two main components, one for the department and another for employee. Again, each of these components will have two child components; one to show or delete and another one to add or update. Also we need one service file to consume the API's.
 
 
First create the components 
  1. ng generate component department    
  2. ng generate component department/show-dep    
  3. ng generate component employee    
  4. ng generate component employee/show-emp    
  5. ng generate component employee/add-edit-emp     
 
Now let's create the service file type.   
  1. ng generate service shared  
We can find all the components and an entry for service as below.
 
 
 
 Now let's consume the api in shared service file.

Import the http client module and observables module.Observables are used to handle asynchronous requests and responses
  1. import { Injectable } from '@angular/core';    
  2. import {HttpClient} from '@angular/common/http';    
  3. import {Observable} from 'rxjs';    
  4. @Injectable({  
  5.     providedIn: 'root'  
  6. })  
  7. export class SharedService {  
  8.     readonly APIUrl = "http://localhost:53535/api";  
  9.     readonly PhotoUrl = "http://localhost:53535/Photos/";  
  10.     constructor(private http: HttpClient) {}  
  11.     getDepList(): Observable < any[] > {  
  12.         return this.http.get < any > (this.APIUrl + '/department');  
  13.     }  
  14.     addDepartment(val: any) {  
  15.         return this.http.post(this.APIUrl + '/Department', val);  
  16.     }  
  17.     updateDepartment(val: any) {  
  18.         return this.http.put(this.APIUrl + '/Department', val);  
  19.     }  
  20.     deleteDepartment(val: any) {  
  21.         return this.http.delete(this.APIUrl + '/Department/' + val);  
  22.     }  
  23.     getEmpList(): Observable < any[] > {  
  24.         return this.http.get < any > (this.APIUrl + '/Employee');  
  25.     }  
  26.     addEmployee(val: any) {  
  27.         return this.http.post(this.APIUrl + '/Employee', val);  
  28.     }  
  29.     updateEmployee(val: any) {  
  30.         return this.http.put(this.APIUrl + '/Employee', val);  
  31.     }  
  32.     deleteEmployee(val: any) {  
  33.         return this.http.delete(this.APIUrl + '/Employee/' + val);  
  34.     }  
  35.     UploadPhoto(val: any) {  
  36.         return this.http.post(this.APIUrl + '/Employee/SaveFile', val);  
  37.     }  
  38.     getAllDepartmentNames(): Observable < any[] > {  
  39.         return this.http.get < any[] > (this.APIUrl + '/Employee/GetAllDepartmentNames');  
  40.     }  
  41. }   
Step 9
 
Now let's add routing in app.routing.ts file 
  1. import { NgModule } from '@angular/core';  
  2. import { Routes, RouterModule } from '@angular/router';  
  3.   
  4. import {EmployeeComponent} from './employee/employee.component';  
  5. import {DepartmentComponent} from './department/department.component';  
  6.   
  7.   
  8. const routes: Routes = [  
  9. {path:'employee',component:EmployeeComponent},  
  10. {path:'department',component:DepartmentComponent}  
  11.   
  12. ];  
  13.   
  14. @NgModule({  
  15.   imports: [RouterModule.forRoot(routes)],  
  16.   exports: [RouterModule]  
  17. })  
  18. export class AppRoutingModule { }  
Step 10
 
Add the bootstrap files for design. 

 

Conclusion


In this article, we discussed how to build an application using .Net core angular 10 and sql server . I hope you all enjoyed reading this and learned from it.


Similar Articles