My Journey to DLithe Bootcamp(.NET Full Stack Week 6(Feb 28-March 6)

Deepak Mishra
11 min readMar 6, 2022

--

Assignment no :DLithe_BC_NFS_T_Task34_SQLSERVER

28/02/2022

Task Details:

1. Stored Procedures
2. Functions

1) Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Code Example :

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Code Example :

Printing the value less than 4 of ID in table

Altering :

2. Functions

There are two types of SQL functions, aggregate functions, and scalar(non-aggregate) functions. Aggregate functions operate on many records and produce a summary, works with GROUP BY whereas non-aggregate functions operate on each record independently.

There are so many built-in functions in SQL to do various calculations on data.

Simple Function which returning addition of two numbers

Calculating Age in Function :

Inline Table Values Example:

Multivalue Function

Github:

Assignment no : 35 DLithe_BC_NFS_T_Task35_SQLSERVER_ANGULAR

Task Details:

  1. Triggers
    2. CREATE in Angular API

1 Triggers

A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will “do something” in a database when a change occurs on a database table such as a record is inserted or updated or deleted. For example, a trigger can be set on a record insert in a database table. For example, if you want to increase the count of blogs in the Reports table when a new record is inserted in the Blogs table, we can create a trigger on the Blogs’ table on INSERT and update the Reports table by increasing blog count to 1.

Types of Triggers

There are two types of triggers:

  1. DDL Trigger
  2. DML Trigger

DDL Triggers

The DDL triggers are fired in response to DDL (Data Definition Language) command events that start with Create, Alter and Drop, such as Create_table, Create_view, drop_table, Drop_view and Alter_table.

DML Triggers

The DML triggers are fired in response to DML (Data Manipulation Language) command events that start with Insert, Update, and Delete. Like insert_table, Update_view and Delete_table.

There are two types of DML triggers

AFTER Triggers

AFTER triggers are executed after the action of an INSERT, UPDATE, or DELETE statement.

INSTEAD Of Triggers

It will tell the database engine to execute the trigger instead of executing the statement. For example an insert trigger executes when an event occurs instead of the statement that would insert the values in the table .

2) Developing the angular API:

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.

Step 2

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

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;

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. namespace WebAPI
  16. {
  17. public class Startup
  18. {
  19. public Startup(IConfiguration configuration)
  20. {
  21. Configuration = configuration;
  22. }
  23. public IConfiguration Configuration { get; }
  24. // This method gets called by the runtime. Use this method to add services to the container.
  25. public void ConfigureServices(IServiceCollection services)
  26. {
  27. //Enable CORS
  28. services.AddCors(c =>
  29. {
  30. c.AddPolicy(“AllowOrigin”, options => options.AllowAnyOrigin().AllowAnyMethod()
  31. .AllowAnyHeader());
  32. });
  33. //JSON Serializer
  34. services.AddControllersWithViews()
  35. .AddNewtonsoftJson(options =>
  36. options.SerializerSettings.ReferenceLoopHandling = Newtonsoft
  37. .Json.ReferenceLoopHandling.Ignore)
  38. .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver
  39. = new DefaultContractResolver());
  40. services.AddControllers();
  41. }
  42. // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
  43. public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
  44. {
  45. app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
  46. if (env.IsDevelopment())
  47. {
  48. app.UseDeveloperExceptionPage();
  49. }
  50. app.UseRouting();
  51. app.UseAuthorization();
  52. app.UseEndpoints(endpoints =>
  53. {
  54. endpoints.MapControllers();
  55. });
  56. app.UseStaticFiles(new StaticFileOptions
  57. {
  58. FileProvider = new PhysicalFileProvider(
  59. Path.Combine(Directory.GetCurrentDirectory(),”Photos”)),
  60. RequestPath=”/Photos”
  61. });
  62. }
  63. }
  64. }

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. namespace WebAPI.Models
  6. {
  7. public class Department
  8. {
  9. public int DepartmentId { get; set; }
  10. public string DepartmentName { get; set; }
  11. }
  12. }
  13. using System;
  14. using System.Collections.Generic;
  15. using System.Linq;
  16. using System.Threading.Tasks;
  17. namespace WebAPI.Models
  18. {
  19. public class Employee
  20. {
  21. public int EmployeeId { get; set; }
  22. public string EmployeeName { get; set; }
  23. public string Department { get; set; }
  24. public string DateOfJoining { get; set; }
  25. public string PhotoFileName { get; set; }
  26. }
  27. }

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. namespace WebAPI.Controllers
  12. {
  13. [Route(“api/[controller]”)]
  14. [ApiController]
  15. public class DepartmentController : ControllerBase
  16. {
  17. private readonly IConfiguration _configuration;
  18. public DepartmentController(IConfiguration configuration)
  19. {
  20. _configuration = configuration;
  21. }
  22. [HttpGet]
  23. public JsonResult Get()
  24. {
  25. string query = @”
  26. select DepartmentId, DepartmentName from dbo.Department”;
  27. DataTable table = new DataTable();
  28. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  29. SqlDataReader myReader;
  30. using(SqlConnection myCon=new SqlConnection(sqlDataSource))
  31. {
  32. myCon.Open();
  33. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  34. {
  35. myReader = myCommand.ExecuteReader();
  36. table.Load(myReader); ;
  37. myReader.Close();
  38. myCon.Close();
  39. }
  40. }
  41. return new JsonResult(table);
  42. }
  43. [HttpPost]
  44. public JsonResult Post(Department dep)
  45. {
  46. string query = @”
  47. insert into dbo.Department values
  48. (‘“+dep.DepartmentName+@”’)
  49. “;
  50. DataTable table = new DataTable();
  51. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  52. SqlDataReader myReader;
  53. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  54. {
  55. myCon.Open();
  56. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  57. {
  58. myReader = myCommand.ExecuteReader();
  59. table.Load(myReader); ;
  60. myReader.Close();
  61. myCon.Close();
  62. }
  63. }
  64. return new JsonResult(“Added Successfully”);
  65. }
  66. [HttpPut]
  67. public JsonResult Put(Department dep)
  68. {
  69. string query = @”
  70. update dbo.Department set
  71. DepartmentName = ‘“+dep.DepartmentName+@”’
  72. where DepartmentId = “+dep.DepartmentId + @”
  73. “;
  74. DataTable table = new DataTable();
  75. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  76. SqlDataReader myReader;
  77. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  78. {
  79. myCon.Open();
  80. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  81. {
  82. myReader = myCommand.ExecuteReader();
  83. table.Load(myReader); ;
  84. myReader.Close();
  85. myCon.Close();
  86. }
  87. }
  88. return new JsonResult(“Updated Successfully”);
  89. }
  90. [HttpDelete(“{id}”)]
  91. public JsonResult Delete(int id)
  92. {
  93. string query = @”
  94. delete from dbo.Department
  95. where DepartmentId = “ + id + @”
  96. “;
  97. DataTable table = new DataTable();
  98. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  99. SqlDataReader myReader;
  100. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  101. {
  102. myCon.Open();
  103. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  104. {
  105. myReader = myCommand.ExecuteReader();
  106. table.Load(myReader); ;
  107. myReader.Close();
  108. myCon.Close();
  109. }
  110. }
  111. return new JsonResult(“Deleted Successfully”);
  112. }
  113. }
  114. }
  115. using System;
  116. using System.Collections.Generic;
  117. using System.Linq;
  118. using System.Threading.Tasks;
  119. using Microsoft.AspNetCore.Http;
  120. using Microsoft.AspNetCore.Mvc;
  121. using Microsoft.Extensions.Configuration;
  122. using System.Data.SqlClient;
  123. using System.Data;
  124. using WebAPI.Models;
  125. using System.IO;
  126. using Microsoft.AspNetCore.Hosting;
  127. namespace WebAPI.Controllers
  128. {
  129. [Route(“api/[controller]”)]
  130. [ApiController]
  131. public class EmployeeController : ControllerBase
  132. {
  133. private readonly IConfiguration _configuration;
  134. private readonly IWebHostEnvironment _env;
  135. public EmployeeController(IConfiguration configuration, IWebHostEnvironment env)
  136. {
  137. _configuration = configuration;
  138. _env = env;
  139. }
  140. [HttpGet]
  141. public JsonResult Get()
  142. {
  143. string query = @”
  144. select EmployeeId, EmployeeName, Department,
  145. convert(varchar(10),DateOfJoining,120) as DateOfJoining
  146. ,PhotoFileName
  147. from dbo.Employee
  148. “;
  149. DataTable table = new DataTable();
  150. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  151. SqlDataReader myReader;
  152. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  153. {
  154. myCon.Open();
  155. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  156. {
  157. myReader = myCommand.ExecuteReader();
  158. table.Load(myReader); ;
  159. myReader.Close();
  160. myCon.Close();
  161. }
  162. }
  163. return new JsonResult(table);
  164. }
  165. [HttpPost]
  166. public JsonResult Post(Employee emp)
  167. {
  168. string query = @”
  169. insert into dbo.Employee
  170. (EmployeeName,Department,DateOfJoining,PhotoFileName)
  171. values
  172. (
  173. ‘“ + emp.EmployeeName + @”’
  174. ,’” + emp.Department + @”’
  175. ,’” + emp.DateOfJoining + @”’
  176. ,’” + emp.PhotoFileName + @”’
  177. )
  178. “;
  179. DataTable table = new DataTable();
  180. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  181. SqlDataReader myReader;
  182. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  183. {
  184. myCon.Open();
  185. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  186. {
  187. myReader = myCommand.ExecuteReader();
  188. table.Load(myReader); ;
  189. myReader.Close();
  190. myCon.Close();
  191. }
  192. }
  193. return new JsonResult(“Added Successfully”);
  194. }
  195. [HttpPut]
  196. public JsonResult Put(Employee emp)
  197. {
  198. string query = @”
  199. update dbo.Employee set
  200. EmployeeName = ‘“ + emp.EmployeeName + @”’
  201. ,Department = ‘“ + emp.Department + @”’
  202. ,DateOfJoining = ‘“ + emp.DateOfJoining + @”’
  203. where EmployeeId = “ + emp.EmployeeId + @”
  204. “;
  205. DataTable table = new DataTable();
  206. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  207. SqlDataReader myReader;
  208. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  209. {
  210. myCon.Open();
  211. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  212. {
  213. myReader = myCommand.ExecuteReader();
  214. table.Load(myReader); ;
  215. myReader.Close();
  216. myCon.Close();
  217. }
  218. }
  219. return new JsonResult(“Updated Successfully”);
  220. }
  221. [HttpDelete(“{id}”)]
  222. public JsonResult Delete(int id)
  223. {
  224. string query = @”
  225. delete from dbo.Employee
  226. where EmployeeId = “ + id + @”
  227. “;
  228. DataTable table = new DataTable();
  229. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  230. SqlDataReader myReader;
  231. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  232. {
  233. myCon.Open();
  234. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  235. {
  236. myReader = myCommand.ExecuteReader();
  237. table.Load(myReader); ;
  238. myReader.Close();
  239. myCon.Close();
  240. }
  241. }
  242. return new JsonResult(“Deleted Successfully”);
  243. }
  244. [Route(“SaveFile”)]
  245. [HttpPost]
  246. public JsonResult SaveFile()
  247. {
  248. try
  249. {
  250. var httpRequest = Request.Form;
  251. var postedFile = httpRequest.Files[0];
  252. string filename = postedFile.FileName;
  253. var physicalPath = _env.ContentRootPath + “/Photos/” + filename;
  254. using(var stream = new FileStream(physicalPath, FileMode.Create))
  255. {
  256. postedFile.CopyTo(stream);
  257. }
  258. return new JsonResult(filename);
  259. }
  260. catch (Exception)
  261. {
  262. return new JsonResult(“anonymous.png”);
  263. }
  264. }
  265. [Route(“GetAllDepartmentNames”)]
  266. public JsonResult GetAllDepartmentNames()
  267. {
  268. string query = @”
  269. select DepartmentName from dbo.Department
  270. “;
  271. DataTable table = new DataTable();
  272. string sqlDataSource = _configuration.GetConnectionString(“EmployeeAppCon”);
  273. SqlDataReader myReader;
  274. using (SqlConnection myCon = new SqlConnection(sqlDataSource))
  275. {
  276. myCon.Open();
  277. using (SqlCommand myCommand = new SqlCommand(query, myCon))
  278. {
  279. myReader = myCommand.ExecuteReader();
  280. table.Load(myReader); ;
  281. myReader.Close();
  282. myCon.Close();
  283. }
  284. }
  285. return new JsonResult(table);
  286. }
  287. }
  288. }

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. import {EmployeeComponent} from ‘./employee/employee.component’;
  4. import {DepartmentComponent} from ‘./department/department.component’;
  5. const routes: Routes = [
  6. {path:’employee’,component:EmployeeComponent},
  7. {path:’department’,component:DepartmentComponent}
  8. ];
  9. @NgModule({
  10. imports: [RouterModule.forRoot(routes)],
  11. exports: [RouterModule]
  12. })
  13. export class AppRoutingModule { }

Step 10

Add the bootstrap files for design.

--

--

Deepak Mishra
Deepak Mishra

No responses yet