.NET Core API + Vue JS + Microsoft SQL | full-stack app tutorial

[Music] hello everyone in this tutorial you will learn how to create a full stack web application from scratch using microsoft sql server for the database dot net core web api for the back end and view js for the front end we will first start with creating the necessary tables in microsoft sql server then create the web api project with the required rest api endpoints finally we will use vue.js for creating the front end you will learn how to add routing for our app [Music] create bootstrap table with custom sorting and filtering capabilities add modal pop-up windows with drop-downs and date pickers we will also learn how to upload an image and store it in the backend server welcome to the course in happy learning let's install visual studio you can choose to install the community version which is free to download and use for educational purposes run the installer file i have already installed visual studio but if you are installing for the first time you will be presented with this screen you can choose asp.net and web development dot net desktop development and dot net cross platform development and click on install button which appears in bottom right the installation will take quite some time as the ide is big let's install visual studio code visual studio code as a popular lightweight code editor from microsoft it is mostly used for front-end web development let's install postman we will be using postman to test our rest apis let's install sql server management studio which is used to work with sql server database run the installer file you can keep the default options and click on install it's asking me to restart the pc let me do it i am running the installer again after restarting the setup is now complete let me start sql server management studio i have already procured a microsoft sql server in a database using microsoft azure i will use the same you can also create a local server and database by installing the developer edition of sql server we are now ready to create the necessary database objects let's create the database tables required for our app we can see that currently we don't have any tables we need two tables for our app one to store department details and another one to store employee details let's start with department table this table will have two fields one to store an auto-generated table id and another one to store the department name let's insert some records using insert query now let's check the data in our table using select query let us similarly create table to store employee details we will be storing employee id employee name department to which the employee belongs to the date of joining and also the uploaded profile picture file name let's insert a record and check data using select query we have now successfully created the database objects needed for our app let's create the.net core web api project let's open up visual studio first click on create a new project choose asp.net web api core choose appropriate folder we might not need https for now now let's take a look at some of the important files all the dependencies and packages needed for our app can be found in the dependencies folder launch settings.json file contains the details of how the project should be started controllers folders contains controllers in which we write our api methods we generally keep the configuration details such as database details in appsettings.json the program.cs contains the main program which is the entry point of our app also it creates webhost which basically helps the app to listen to http requests the startup class configures all the services needed for our app services are basically reusable components that can be used across our app using dependency injection it also contains the configure method which creates our app's request processing pipeline we need to make a couple of changes in startup class one is to enable the course by default all web api projects come with a security which blocks requests coming from different domains we are basically writing instructions to disable that security and allow the requests to be served we are also making one more change to the serializer class to keep the json serializer as our default might need to install a nuget package to do this bye let's import the namespace now let's simply run the project and check if everything works fine we see that the project works as expected and we see the json result from one of the sample controller method now let's create the models needed for our app let's create a folder to add our models let's add the department model department model has two fields department id and department name next let's add the employee model it has five fields employee id employee name department date of joining and photo file name let's start creating the api methods let me first add the sql server connection string in app settings json file now let's add an empty controller to write our api methods for department details screen to work with sql server we need to install a nuget package the package name is system.data.sql client let's also import the namespaces to read the connection strings from app settings file we need to make use of dependency injection now let's write an api method to get all data from department table i am writing raw queries here you can choose to write stored procedures or maybe use entity framework for this i will be getting the data into a data table object using the given sql connection and command we will execute our query and fill the data into data table using sql data reader finally we will return this data in json format let's test this api method this seems to work let's now add the post method to insert new record into department table we will be sending the department object to our post method in the form body let's also create the put method to update records and finally let's implement the delete method now let's test our api methods we will be using postman to test since we can test only get method in the browser the get method seems to be working let's add another department using post method looks like we are not sending success message after adding let's try again we see that the record is added successfully let's verify this now let's use the put method to update a record the put method also works as expected now let's check the delete method we need to make some changes to the delete method to accept the id and url we see that the delete method also works as expected let's implement the api methods for employee details screen let's add an empty api controller let's copy the contents from department controller and make changes accordingly now let's test our apis the get method works fine let's test the post method to insert a record bye post method works fine as expected now let's test the put method and update a record and finally let's test the delete method the delete method also works as expected let's now write an api to upload the photos let's create a folder to store photos to be able to use this folder to store and retrieve photos we need to add some instructions in the startup class now let's write the api method i am giving a custom route name for this method and this as a post method if there is any exception while saving file i will simply return the default anonymous picture file name we are going to extract the first file which is attached to form data we need to again use the dependency injection to get the application path to photos folder once the file is saved we will simply return the file name let's now test the api by uploading an image file bye we see that the upload was successful and we can see the image in the newly created path let's now create the vue.js project navigate to the folder where we need the project and open visual studio code let's create an index.html file let's add bootstrap for styling our app the styling reference goes in the head section of our html and the bundle javascript reference can be added at the closing body tag we will be using the cdn version of vue.js for our front-end project so let me add the javascript reference for vue.js and view router js files view router as needed to add the routing capability for our app we also need to save the api url somewhere in our front end so let's create a variable.js file and save the urls in it let's also reference the variables.js file in index.html we also need to include axios library which will be used to consume the api methods let's add the routing and navigation to our app let's first add a div tag with ideas app and add some headers let's see how it looks by opening the html file now let's add the components or the pages needed for our app we will add three screens one for home one for department and one for employee we also need a root component inside which we will list the routing paths let's add it with nameapp.js let's add a simple template for each of these screens the html is written inside two backticks let's add the routing paths slash home will be given for home component and slash employee for employee component slash department for department component let's create a view router object and pass the routes array now let's create a view object this will be mounted to the element with id app this is the same id that we used for our div now let's add the navigation menu router link is the tag that will help with routing let's check if this works we need to add one more tag called router view to make this work let's add class container to our div looks like we have not referenced the js files let's do it we have not changed the component constant names in js files we see that the routing and navigation works as expected let's consume the get method and display the data in a grid inside data we can declare all the variables that we can use inside the template i am declaring an empty array which will be populated using the get api method in the methods section let's write a method to consume the get api method we are making use of axios to consume the api methods once the response is received we will assign the data to the department's array there is a life cycle method called mounted which will be called when this component is in scope we will call the refresh method here now let's add the bootstrap table to display our data we will loop through the department arrays using v for directive in the options column we will add two buttons one to edit the record and another one to delete a record let's add bootstrap icons inside these buttons let's check how it looks looks like we missed a closing t-head tag the grid seems to look fine now let's add the modal pop-up window to add and edit the department let's add the modal title from variable since we will be using same modal for adding and editing let's define the modal title variable also we need form fields department name and department id inside the modal let's add the text box for department name the v model directive gives us two-way binding so the value entered in the text box will be available in the department name variable let's add two buttons one to create new department another one to update the department the create button will be visible when department id is set to zero now let's add the button to open the modal window this button will be to add new department same functionality on click of edit department on add click we will set the title to add department and department id to xero and on edit click we set the title to edit department we see that modal window works as expected now let's add functionality to create and update departments using the api urls once response is available we display the message in alert box and also refresh the grid let's test the functionality we see that the create and update functionalities work as expected now let's complete the delete method we will pass the department id to the delete function we will take the confirmation before calling the delete api bye we see that delete also works as expected let's complete employee details screen we will copy the contents from department screen and make changes accordingly the modal window will now have more form fields and image to be displayed so let's use bootstrap flex department field will be a drop down the option values will come from departments array date of joining will be a date field now let's add a div to display profile picture the image source will come from variables let's also add upload button for uploading new images the photopath is already defined in variables.js file we will use the same in refresh data method we will refresh both employee and departments array let's now add method to upload the image we will attach the first file to form data once upload is successful we will replace the photo file name variable we see that the screen is now displaying data let's check the create and update functionalities finally let's test the delete method we see that all the functionalities in employee screen work as expected let's implement sorting and filter feature let's do it for departments table let's start with filtering we need text box to enter the filter text we will be using department id filter variable to store the filter text we will execute the filter function each time new letter is entered let's add a filter text box for department name column as well along with filter variables we also add one more array which will store backup data without any filters now let's implement the filter function we will be using includes method to check if the filter text matches with the array data let's check the functionality see that filtering works as expected now let's implement sorting feature we will add two buttons for sorting one to sort in ascending and another one to sort data in descending if we pass true it means we need to sort it by ascending let's add icons for the filter buttons now let's implement the sorting function let's check the result looks like we made an error while passing parameters we see that the sorting works fine now

Channel: Art of Engineer

Views: 71,172

Keywords: .net core web api, vue js, microsoft sql, full-stack, front-end, back-end

Id: qS833HGKPD8

Length: 64min 28sec (3868 seconds)

Published: Sun Jun 20 2021