Learn Angular 10, Web API & SQL Server by Creating a Web Application from Scratch

in this course you will learn how to develop a web application from scratch using popular technologies such as sql server for database dot net web api for backend development and the latest angular 10 for front-end web development we will first start with installing and setting up the environment needed for development then start creating databases and objects needed for our app then develop api endpoints using asp.net web api finally we will be using angular 10 to design the front end part of our app you will learn how to create the navigation menu and tables using bootstrap add routing to our angular app add modal pop-up windows with drop downs and date pickers [Music] and also add 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 i thank you for watching welcome to the course and happy learning let's install sql server which is needed to create databases and objects needed to store our app data just type install sql server and open the official download link download the developer version which is free to download and use run the installable file [Music] choose custom install click on installation and new sql server standalone installation let's choose database engine only for now and proceed with defaults choose mixed mode set the desired password also add the current user and make sure that the user is also admin now let's click on install now let's install the sql server management studio which is nothing but the user interface to work with databases just keep the defaults and install it now let's open sql management studio and check if everything is fine to open a local database just type dot in the server name and click on connect we can see some default system databases let's write a simple select query on one of the existing tables we have now successfully installed sql server and management studio now let's install visual studio which we will be using to create the api project just type download visual studio and follow the official website let's download the community version which is free to install run the installer file we need asp.net web development dot net desktop development let's also choose.net core development we have now installed visual studio now let's install visual studio code visual studio code as a popular lightweight open source code editor developed by microsoft we will be using it mainly to work with angular project just type install visual studio code and follow the official website download and install it with default settings we have successfully installed visual studio code now let's install postman which will be used to test the api methods just type install postman and go to official link download and install it with default settings you don't have to create an account just click on skip we have now successfully installed postman now let's install node.js which we will be using to create and run the angular project just type install node.js and go to official link download and install it with default settings you can open node.js command prompt to check the version and verify the installation we have now successfully installed node.js hello everyone in this video let's learn how to install angular cli angular cli is a command line utility which we will be using to create and run angular projects although angular can be installed only to a specific project it is recommended to install it globally let's open node.js command prompt type this command npm install hyphen g angular cli angular cli is now installed let's just verify the version of it just type ngspace-version we can see that we have installed version 10 of angular hello everyone in this video we are going to create the database and the tables required for our application let's first open up sql server management studio let's connect to local database to do that just type dot in the server name and click on connect we see that currently we don't have any user-created databases let's start creating the database and tables needed for our app just click on new query to open up a query editor to create a database just type create space database space the database name in our case the name of our database is employeedb once the query is written just select it and click on execute button the database is now created we can verify it by refreshing the databases now let's start creating the tables required for our app our app has mainly two screens a department screen and an employee screen so we need to create two tables let's start with department table the table will an integer id column with identity 1 comma one which basically means we don't have to pass values to this column it will start with value one and increments it each time a new record is inserted we will also add a column which stores department name let's execute this query let's write a select query on this table we see that we don't have any records in this table yet let's insert one by writing an insert statement we don't have to pass the value for department id since it will be inserted automatically after executing select query we can see that a row is now available with id equal to 1. now let's add another department we can see that the id of new department is two now let's start creating the employee table we need employee id column employee name column the department to which the employee belongs to the date of joining whose data type as a date and finally we will also store the uploaded profile photo name the actual photo will be stowed in the api app server let's write an insert query and a select query we have now successfully created the database and the required tables to build our application hello everyone in this video we are going to create the web api project open up visual studio click on create a new project type web api in search bar filter for c-sharp language choose the second option which is asp.net web application choose the folder where you want to create the project i am going to name this project as web api choose web api in the options we don't want https configuration for now let's take a look at some of the important files in this project web config is the place where we generally keep all the configurations we generally keep the database configurations here packages config has the information of all the dlls or the packages that are used in this project we keep all the models needed in the models folder in controller section we keep all api endpoint methods by default this project has two controllers we will create our own controllers in this folder and write the api methods in web api config.cs we can add the routing configurations by default we have a routing api controller optional id let's just test one of the method in the values controller and see if we get the response back when we call this api method this method will simply return list of two strings value 1 and value 2. you can choose the appropriate browser and run the project to call this method we need to simply attach api values to the api url we see that the method response is working as expected generally we need to test the api methods in a rest client such as postman this is because in browser we can test only api methods which belong to get verb category let's open postman to test the same method choose the appropriate verb paste the url click on send we have successfully created the api project and it works as expected hello everyone in this video we will force our api to return json response only currently we see that the response as in xml open web api config in the app start folder importsystem.net.http.headers and add this line config formatters json formatter supported media types add new media type header value text or html now let's make some changes to our method to return an anonymous type instead of list of strings change the return type create an anonymous type and let's return this value with an ok http status let's check this by calling the api method we can see the response as in json format hello everyone in this video we are going to enable cores for our api project by default the web api project comes with a security feature that blocks requests which are coming from different domains since we will be creating different projects for api and ui we may need to enable the cores for our ui in case it is deployed on a different server from that of the api server to enable it we need to install a nuget package onto our project which we can find in this web page just copy it open the project click on tools and open nuget package manager console just type the command and hit on enter once the package is installed open the web api config file in app start folder import system.web.http.cors and add this line config.enable course new enable cores attribute now i am writing star so that i can allow requests coming from any domain if you want to whitelist only a specific domain you can add it here instead of star for example you could whitelist only google.com to access your apis we have now enabled cores in our api project and we will not face any problems while consuming these apis from the ui project let's create the models required for our api project you can think of the models as a kind of blueprints which will help us in interacting with the database since we have two tables in our database we are going to create two models let's create the department model first right-click on models folder and add a file with namedepartment.cs make sure you type the property names in the model file exactly same as the column names in the table since department table has two columns department id and department name we are going to create two properties department id as integer and department name as a string similarly let's create a model for employee table employee table has four columns so we create four properties we have now successfully created the models needed for our api project there are four widely used verbs in http which are get put post and delete getverb is used to retrieve data post is used to insert data put is used to update data and delete is used to delete the data let's create the get api method to fetch data from the department table right click on controllers folder and add new controller keep the name as department let's start creating the get method to fetch data from department table make sure that the name of the method starts with get i am writing the query in raw format this is generally not recommended we have to create stored procedures and use them instead of raw queries like this to avoid sql injections to keep this tutorial simple and straightforward i may not be following some of the best practices so the query will be select department id and department name from dbo dot department let's create a data table variable to store the data coming from database table now we need to write code to execute this query before that let's add the connection string in web config server name would be dot which means the local db and database name would be employee db using the sql connection which connects to our new connection stream using the command which uses our select query and using the sql data adapter which helps to fill data into our data table variable we will execute this query and fill the data table variable let's return this table with success status code now let's test this api method using postman first run the app call the get api method of department in postman we are getting an exception in connection string we see that we are missing a space between initial and catalog let's correct and try again we see that we are getting data from our get api let's complete the post put and delete methods for department table let's start with post method we are going to return just the message whether it was successful or not also make sure to import the models let's write the insert query for the post method department name will be from the object which will be sent to this post method let's copy the method to execute query and return success message after inserting is done if error occurs we send failure message let's make a copy of this method to implement put method and write the update statement finally let's write the delete method which accepts just the id of the record to be deleted let's write the delete statement based on the input id now let's run the application and test the apis in postman let's run the get method we see currently we have two departments let's copy the url and open a new tab choose post click on body and click on raw enter the json input for post method it's okay if we just pass the department name we want to add bpo as a new department also choose json in dropdown let's click on send we see that it was added successfully let's verify it by executing the get api again we can see that the new department is added now let's try using put api to update we will just update department with id equal to 5 to new name called as finance we need to send both id and name to the put method update was successful and we can see the new name finally let's check the delete method we need to pass the id of the department to be deleted in the api url we see that the department with id5 is now deleted let's implement the api methods for employee table just the way we did it for the department table add a new controller with name employee let's import the necessary modules let's copy all the api methods from department controller and make changes in them according to employee table let's start with get method we just need to change the query to get data from employee table i am using convert function to extract only date part next let's modify the post method next is put method bye and finally delete method we also need a custom method to load all department names in a drop down so let's create it with a custom name i am selecting only department name from the department table since this is a custom method we need to map the required routing also add attribute to specify that it is a get method now let's test the apis in postman let's start with get method we just need to change the url to employee we see currently we have one record let's test post method by adding another employee we don't need id for post method let's execute we see that new record is added let's change the name of new employee using put method we see that it failed let's check the reason we see that we have missed to add commas let's add and retest we see that we are able to change the employee name next let's test delete method by deleting second employee delete method is also working finally let's test our custom method which return list of department names copy the api root path this is a get method we see that the custom method also works in our app we are also creating the feature to upload profile pictures so let's write an api method to save the uploaded photos first create a folder with name photos we can right-click on folder and click on open in folder file explorer to open the path of this folder we see currently it has no files now let's write the method to save the file i am declaring a variable http request to capture the current request we may need to import a module named system.web i am only considering only the first file upload in case multiple files are attached in the request i will save it in the newly created folder called as photos once saving is successful let's return back the file name if error we will return file name as anonymous.png let's also add custom root for this method now let's test the method by uploading a file through postman choose post option paste the api path choose form data choose file as key and upload the file we see that it was successful let's check the photos folder we can see that the file has been uploaded successfully let me give some basic introduction to angular project that we are going to create let's take example of the app that we are going to build it basically has two main screens one to show departments data and another one to show employees data also we will be adding pop-up screens to add or edit the departments or employees traditionally if we would like to build this just using html css and javascript as a single page application we would have to write all the features in one html file this would get complicated to maintain with angular we can make use of something called as components so that each component contains only the html needed for the specific feature for example for our app we will create two main components department component and employee component again inside each of these components we can add child components one child component to show or delete records another child component to add or update records and this is how the component folders actually looks in the project this is how the architecture of our angular project looks like we have something called a shared service whose main function is to send and receive data from the api all the components that we are going to create will make use of this shared service file to send or receive data from api let's create the angular project i am creating a new folder called ui in which i will create the angular project open command prompt in this path let's check the angular version installed we currently have angular 10 installed to create project just type ng-space new space the app name which i am giving as angular 10. let's keep the routing feature we can also add it later in case we select no at this point let's keep default styling once the command is completed a new folder with project name will be created with some files let's view them in visual studio code first navigate inside the project folder which in our case is called as angular 10. type code space dot we can see all the files in this project in the left side pane before viewing them let's simply run the project and see how the output looks like in the browser just type ng-space serve space-open this command will serve our application in the default browser we see that this angular project already has some content let's see where it is coming from just open the source folder inside this we have a file named index.html this is the final file which gets rendered from angular project inside body we have a custom html tag we call them as selectors this particular selector is inside apt folder open appcomponent.ts we can find the selector here the corresponding html content behind this selector can be found in appcomponent.html what you currently see on the screen is coming from this html file let's delete everything in this file and write hello world in h2 tag after saving the files the angular cli automatically reloads the view in browser now let's take a look at some of the important files package.json contains all the packages or modules or dependencies that are currently used or needed by this project to run angular.json contains configuration options for serving testing and building this project index.html is the final file which gets rendered when running the app app module contains an entry of all the modules new components and services that we create in this angular project app component as the root component we can add any number of child components all of them will reside inside the app folder appcomponent.html as the html content of root component an appcomponent.ts is the file which contains all the functionalities written in typescript or javascript language these functionalities will be used by app component html appcomponent.css contains all the styling needed for app component html if you need to add styling globally and not specifically to one component we can add them inside styles.css let's create the components and services needed for our app as already discussed previously we need two main components one for 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 apis let's create the components first just type ng-space generate space component space the name of the component we can see that the department component has been created in the app folder let's create the child components for department one for showing the department data or deleting it another one to add new department or edit next let's create the employee component and the child components of employee component now let's create the service file just type ng-space generate space service space the name of the service if we just open app module we can see that an entry has been made for all the components that we just created we also need to add an entry for service file else we may not be able to use it in other components just add the service name in providers section as well now we have successfully created all the components and services needed for our app let's complete adding the logic to consume all the api methods in shared service file first let's register the http client module in app module also let's import couple of other modules related to forms which we will be using in other components let's import the http client module and observables module observables are basically used to handle asynchronous requests and responses let's add the api url before that let's run the api project to get the api url let's add the api url also let's add the photo url which we will be using to display profile pictures in our angular app let's instantiate the http client in constructor let's firs add the method to consume the get department data api similarly let's add method to consume the post department api to add new department put department api to update the department details and finally delete to delete a department we will pass the id and url let's make a copy of all these methods to replicate for employee apis let's also add the method to save profile pictures also we have one more method to get all department names we have now added all the methods to consume all the api we have in out api project let's add routing to our app routing is basically the ability to navigate to a particular screen of an app by just using the url for example if we type department in the url we should be able to navigate to department screen and if we type employee we should be able to navigate to employee screen currently the department screen consists only this text department works same with employee screen to add routing just open the app.routing.ts file import the employee and department components let's now add the roots in the roots array if user types employee we should show employee screen if they type department they should see department screen also add this selector in appcomponent.html now let's test the app we have successfully added routing feature to our app let's add bootstrap to our application bootstrap helps us to build and style our web pages much faster there are basically two ways we can add bootstrap one is the traditional approach which you see on the screen bootstrap has also a version of it designed exclusively for angular projects to install that we need to just run the command that you see on the screen we are going with the traditional approach first copy the stylesheet link open index.html and paste it in the head section next copy the script tags and paste them just before the closing body tag we have now added bootstrap to our application let's add navigation menu to our application our navigation menu will basically have two options one to navigate to department screen and another one to navigate to the employee screen first open the appcomponent.html let's add a div with class container before adding navigation menu let's add some heading to our application i am using bootstrap class names for styling purposes bye now let's add the navigation menu inside the list item i am adding buttons which on click will activate the corresponding route which is mentioned in the router link attribute now let's test the navigation menu we can see that we are able to navigate to corresponding screens based on selection in navigation menu let's create the table to show department's data in the department screen open show department.ts file inside department component let's declare a variable to store department list and assign empty array as of now we need to use the api method written in shared service file to fill the department list array let's import the shared service in our component also instantiate it in the constructor now let's write the method to refresh the department list variable from the api method subscribe method make sure to wait till the response is received from api call and then only assign value to the department list variable this is an asynchronous operation also we need to call the refresh method in ng on init function ng on init is the first method that gets executed when this component is in scope now let's add the table in html file i am adding bootstrap styling for table we will have department id column department name column and also one more column called as options in which we will add feature to edit or delete the record now let's add the table body ng for as one of the directives in angular which can be used to replicate html elements based on array data you can think of it just like a for loop for example if we have two records in the department list we will have two trs the two curly braces you see is called as string interpolation we generally write variable name inside this it displays the corresponding values also for time being let's add two buttons to edit and delete records we shall complete their functionalities later we need to do one last thing just copy the selector from show department.ts file and paste it in the parent department component html file we can see that we have successfully populated the table with department data now let's add the modal pop-up window which we are going to use to add new department or edit existing department i am using the modal design that we can see on this official bootstrap website let's copy the code and paste it in the show department html we need to make some changes to it first we need to call our custom method on button click so that we can activate our add edit department component let's change the name of our button make it float on the right side of the screen the additional attributes data backdrop and data keyboard will make sure that user cannot click outside the modal window to close it next instead of the default title we will set the title using a variable that we will populate in typescript file because we need to use same modal window for both add and edit next we will call custom function on close so that we can deactivate the add edit component let's get rid of the modal footer and inside the body we need to copy and paste the selector of add edit department component we need to pass department object to our add edit component so that it knows whether to add new department or edit existing department and we are writing ng if directive so that we activate the component only on click of add or edit button now let's complete the add click function inside it we will set the title department object and variable for ng if let's declare the three variables to be set when adding new department we set department id to 0 to indicate the add edit component that we are trying to add new department also set the modal title to add department set activate add edit department component to true now let's complete the close function we just need to deactivate the add edit component and refresh the department list so that in case new department is added we can see it without refreshing the page let's finish the add and edit functionality for department screen we need to copy attributes from add button to edit button as well so that we can activate the pop-up on edit button click as well we need to pass the department item object which contains the details of the record to be edited on click of edit button we should send the department object details to add edit screen set the title to edit department and also activate the add edit department component since we are passing department object as input to the component we need to capture the same in the add edit component so we need to make use of input directive also when the add edit department component is activated the ng on init function will be called inside which we will initialize the department id and department name variables now let's add the controls needed for department add edit screen we just need one text box to capture department name since department id is non-editable ng model is used for two-way data binding for example if we enter some text in this text box the variable department name will be updated automatically with the text box text value we need to create two buttons one for add and one for update add button will be visible when department id is equal to zero an update button will be visible when department id is not equal to zero now let's complete the add and update methods we need to import the shared service file to call the ad and update api methods we need to create the department object in json format let's also instantiate the shared service we just need to call the add department api method and pass the new department object update will be implemented in a similar way only difference as the department id will not be zero we need to make the modal window to align in center and also make it look bigger let's make some changes in the modal window class now let's test the add and edit functionality we see that add and edit functionalities are working as expected now let's complete the feature to delete the department but first instead of these text buttons let's use the bootstrap icons just copy the code of required button design and paste it inside button tag bye now let's implement the delete function we need to call the delete department api method and pass just the department id once successful let's display the message also refresh the table let's test by deleting one record we see that the delete feature works as expected now let's complete the show employee table screen we just need to replicate the functionality of show department screen and the minor difference would be to simply change the variable names to employee instead of department let's copy the show department html first just change the department variable names to employee also the add edit component will now point to employee add edit component and we will have column names of employee table now let's copy the typescript file first copy the import statements next all code starting from constructor line the employee object will have a photo file name by default if no profile picture is uploaded we will show a default anonymous profile picture just change the api to employee methods we need to add the selector and employee main component now let's test the show employee screen we see that the show employee screen works let's complete the add and edit feature for employee screen and also upload profile picture functionality for employee screen first let's copy the typescript file content from add edit department screen and make changes to it accordingly let's declare the variables needed photo file path will be the final path of a photo that we use in the image tag to display it on the screen also we have an option to select department name from drop down let's declare an array to store the names and use it in the drop down in ng on init we need to first populate the department names variable and then assign the other employee record data to variables the photo url path is declared in service file we are going to make use of it to compute the final photo path let's complete the add and update methods for employee finally we are going to write method to save the uploaded profile picture we are going to create a form data and assign the file name and the file to it and then send this form data to api method let's now complete the add edit employee html file we need to show both employee details and profile picture in the screen so let's give 60 of space to employee details and 40 space for profile picture the picture will also have a upload button below it so that users can upload new pictures now let's add the text box for employee name drop down to select department name the values for drop down will be loaded using the variable that we are populating in ng on init method also let's add date field to choose date of joining let's make changes to the add and update buttons as well now let's test the functionality let's add a new employee let's enter details and upload a profile picture we see that new employee is saved successfully now let's edit it we see that image is not appearing let's check the value of image tag source we see that file name is not getting captured let's print the values in the console by using console.log method we see that the letter n is in lowercase let's make changes in the api let's retest now we are able to see the image let's change the name and try to save update also works as expected let's finally test delete we see that all the functionality of employee screen works as expected let's add a custom sorting and filtering option to department table let's start with filtering we will add a text box at the top of each column let's declare variables to capture user input to filter text boxes also let's add another variable which will have backup of department data now let's write the filter function which will apply the filter on backup data and assign it to the department list variable we are making use of includes method which will check if a record contains the filter text input now let's add the text boxes to capture filter text let's test the feature we see that the filtering feature works as expected now let's add sorting feature we need to add two buttons for each column the sorting method will accept two parameters one is column name and another one as boolean which indicates whether to sort in ascending or not we will have two buttons one to sort an ascending and another to sort and descending for buttons let's use bootstrap icons now let's complete the sorting method we will apply the sort on backup data and assign it back to the department list variable we will have separate logic for ascending and descending let's now test the feature we see that the sorting works as expected in this video we will take a look at how to get the deployable files for both api and ui project let's start with api project just right-click on the project and click on publish click on folder publish option the files will be published into bin folder click on publish published folder link is available let's navigate to that folder we now have api deploy files let's move to ui project now we just need to type this command ng-space build space-prod the publish files will be available inside dist folder you

Channel: Art of Engineer

Views: 554,884

Rating: 4.9190516 out of 5

Keywords: Angular, angular 10, ASP .NET Web API, .NET Web API, Microsoft SQL Server, SQL Server, Full-Stack Web Devleopment, Front-End End Development, Back-End Web Development, Database Development

Id: 4a9VxZjnT7E

Length: 97min 50sec (5870 seconds)

Published: Sat Aug 01 2020