Blazor +.NET Core + MS SQL full stack app

[Music] in this tutorial you will learn to create a full stack web application from scratch using microsoft sql server for the database dotnet core web api for the back end and blazer for the front end we will first start with creating the necessary tables in microsoft sql server then we create the web api project with the required rest api endpoints finally we will use blazer for creating the front end you will learn how to add routing for our app create bootstrap table with custom sorting and filtering capabilities [Music] add model popup window with dropdowns and datepickers we will also learn how to upload an image and store it in the backend server 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 dotnet cross platform development and click on install button which appears in the bottom right 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 is asking me to restart the pc so 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 and 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 dotnet core wave 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 folder contains controllers in which we write our api methods we generally keep the configuration details such as database details in appsettings.json file the program.cs contains the main program which is the entry point of our app also it creates web host 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 this startup class one is to enable the course by default all web appear 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 we might need to install a nuget package to do this 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 database connection string in the app settings json file now let's add an empty controller to write our api methods for department details screen let's name it as department controller to work with database we need to install a database client let's install it with a new get package let's also import the namespaces to read connection strings from app settings file we need to make use of dependency injection let's first create a variable of type i configuration it will be instantiated inside the constructor during runtime now let's write an api method to get all data from department table the attribute http get indicates that this method will only allow get requests the return value from this method will be json array i am writing raw queries here you can choose to write stored procedures or maybe use entity framework for this i am writing select query on the department table and select the two columns department id and department name i will be getting the data from the database into a data table object let's get the connection string from the app settings json file using the configuration instance we will be using data reader to fill the data table with data which comes from our database query we will be executing the database commands in the database within the using clause this will make sure to close the connection even if there are any exception we will first open the database connection and using the given query we create a command we execute the command using the execute reader method since we are expecting a return value from the select query and then we will fill the data into data table using data reader once this is done we close the data reader and the connection finally we will return the data table 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 the attribute will now be http post and instead of cell query we will now use insert query we will be sending the department object to our post method in the form body we will be passing the values to our command using command parameters this will avoid sql injections let's also create the put method to update records for update query we need to pass the new department name and also the id of which we are trying to update and finally let's implement the delete method the attribute will now be http delete and let's write our delete query and pass the id of which we need to delete 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 methods seems to be working let's add another department using post method looks like we are not sending the success message after adding let's try again we see that the record is added successfully let's verify this using get method now let's use the put method to update a record the boot method also works as expected now let's check the delete method we need to make some changes to the delete method attribute to accept id in the url we see that 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 bye now let's test our apis the get method works fine let's test the post method to insert a record post method also works 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 startup class now let's write the api method i am giving a custom route name for this method and this is a post method if there is any exception while saving the 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 we see that the upload was successful and we can see the image in the newly created path let's start creating the front-end project using blazer so let's first open up visual studio and click on create a new project there are basically two templates that we can use to create the user interface using blazer one is the blazer web assembly app which mostly runs on the browser and kind of reduces the load on the server and the second template we have is the blazer server app which mostly runs server side and it's kind of easy to handle the user interactions over a signalr connection and developing a user interface is pretty much similar in both these templates so let's just choose blazer server app and click on next and choose the appropriate folder to create our project let's click on next and we might not need to use https for now so let's just uncheck this and click on create so now let's take a look at some of the important files in this project all the packages or dependencies that this project needs can be found inside the dependencies folder and we have got some example model class and methods inside the data folder the components can be found inside the pages folder the components in blazer will have an extension of a dot razor host dot cs html is the root page and we can see the html with head and body section here counter dot razor is a sample component that's created in a blazer project this is available in route path slash counter the shade component contains the components that can be embedded in other components imports.razer contains common razor directives to include in the components the app.raiser is the root component of the app that sets up the client-side routing using the router component we can store our configuration details such as api urls in the appsettings.json file program.cs is the app's entry point that sets up asp.net core host the startup.cs contains the apps startup logic it has two methods configure services the configure services method configures the app's dependency injection services and the configure method configures the app's request handling pipeline let's simply run the project and see how it looks in the browser [Applause] we can see that we have a navigation menu and a default counter component so the count just increments for each button click here now let's add the routing and navigation to our application we will be using bootstrap for styling the application so let's import bootstrap first so let's open up host.cshtv now let's add bootstrap just click on get started and just copy this css and we can place the css in the head section and the bundle section can be added near the closing body tag now let's open the main layout dot raiser and we will just remove the default content and add our navigation menu using bootstrap so let's add a div with the class equal to container [Music] and we need to have the keyword body so during rendering the body will be replaced by the content of the layout let's add a heading tag with h3 and let's add some class to it to kind of align it to center bootstrap flex and we'll justify it to the center and let's add text blazer front end let's create the components before adding the navigation menu uh we need uh basically two pages for our app one to store the employee i mean one to manage the employee details and another one to manage the department details so let's create two components in the pages folder right click on it and click on add and razor page so we'll choose the razor component [Applause] and let's call it as department dot raiser and we can use the for for routing we can use the page keyword and we can map slash department to department component let's add another component for employee details and slash employee will be routed to employee component so now let's add the navigation menu so let's add buttons for navigation the button will have a margin of 1 and a light color so first one will be the home component so the home component can be found here [Music] so similarly we will have department and employee we have already defined the route paths so department will be just department and slash employee will be routed to employee page let's just check if this works so we see that the routing works as expected now let's create a bootstrap table and display the department's data in the bootstrap table so but before that let's first add the api urls in the appsettings.json file let's also add the photo url which contains the application path to the photos folder now let's add the bootstrap table i mean before adding the table let's uh import some of the modules that we will be needing so the first one that we will need is uh system dot text dot json which will mostly be using for passing the json data or maybe serialization [Applause] and using system dot text dot json dot serialization and to consume the api methods we need to import http client so let's import that [Applause] and also let's inject eye configuration to be able to read the configuration i mean the api urls from app settings [Applause] and finally let's import javascript runtime this is for executing the javascript functions using c sharp code [Applause] now inside the code we can write our methods and also declare variables or classes so let's first define the class that we will be using for departments data so let's define it with name department class it will have only two fields one is department id and another one is a department name let's define an array variable to store the department's data and use it inside the html let's call the name as departments and initialize it with an empty array as of now so let's write a method to consume the get api and fetch all the details and populate the department's array so this will be an async method let's call it as refresh list let's create an http request and this will be a get method next we need to specify the url which we will get from the configuration file or the appsettings.json file the key that we gave was api underscore url so we need to call the department api method now let's create a client instance [Applause] let's use the send a sync method and initiate the api method so once the response is available we will be dc realizing it i mean we will get the response from as json format and we will dc realize it to department class foreign so we will be digitalizing it to department class and will be assigning it to the department's array so we have a life cycle method in blazer called as on initialized async so once the page is initialized we will call the refresh method here so we'll refresh the departments array once the page has been initialized okay now we will make use of this department's array and display the data in a bootstrap table the table will have head section and a body section the head section will have three columns sorry it will have a first one is we have tr inside tr we will have our headers we will have three columns one for department id one for department name and last one will be an options column which will have options to edit or delete a department so we will be making use of the departments array and we'll be using the forage method to loop through the department's array and display the data so we'll be using td and display the department id department name and in the options column we will have two buttons one button for editing the department another one for deleting the department [Applause] let's use bootstrap icons for these buttons let's also add bootstrap icon for delete okay let's see how it looks [Applause] let's use chrome browser we can change the browser settings somewhere here yep we can choose chrome looks like we have some exception so looks like the exception is coming from the client factory import that we did we need to basically uh import it i mean add this added add the entry in configure services as well so let's go to configure services and we'll do services dot add http client okay the data is proper but i think we have some styling related issues so let's check [Applause] yep so we see that the data is now displayed in the grid so now let's add a model pop-up window which we will be using to add new department or maybe edit an existing department so we will basically use the same model window for adding a new department and also editing an existing department so we will define the title of the model through a variable so let's create a variable called model title and also in the form fields we need to have the department id and department name so we'll dynamically set the title and the department id department name based on whether we clicked on the add new department or update the existing department so let's write that method [Applause] so first one is the add click method so model title will be [Applause] add department department id equal to zero so if we are adding a new department we don't need to set the department id so we are setting it to 0 and department name will be empty [Applause] and during the edit department we will be passing the department object which we are supposed to edit so the title will be edit department department id will come from the object also the department name now let's add the model window which is basically the bootstrap styled model pop-up window class equal to [Applause] we'll set the id as example model [Applause] [Applause] so as i already discussed we will be using the title i mean we will be using variable for the title so it will be dynamically set based on whether we clicked on add department or did we click on button edit department button in the model body we will have only one form field which is department name so let's create an input group to display a text box for adding a new department or maybe editing an existing one so this will be a text box and we have a keyword in blazer called dot bind i mean we have a keyword called bind so it kind of provides a two-way bidding between the variable and the data that we enter inside the text box so next next we need to have button uh one button for adding a new department and another one for editing department so if the title is at department we will display the create button else we display the update button let's also add the method to add a new department or maybe update an existing one so let's copy the method where we are using consuming the api and let's rename it to create click so this will be using we will be using this method to add a new department using the post api method so we change the method to post and we need to send the new department object so let me define department object department class and the department name will be coming from the variable that we entered in the text box [Applause] [Applause] so let's attach this object to request dot content we need to serialize it as json so let's use string content json serializer node serialize department object will keep encoding as null and media type will be application json [Applause] so we will be getting the response as a string so let's see really deserialize it as a string instead of department class and finally we will also display the message that we got using the javascript involve invoke method [Applause] so we'll be displaying the message in the alert box also let us refresh the array so that the grid gets updated let's make a copy of it and just change it to update for editing an existing department so we need to pass the department id also along with department name to update the department name based on the given department id so this will be a put request and restore will remain the same let's use this method for our update and create buttons [Applause] so on click equal to create click and update click now we need to add a button to display i mean show the model window for adding the department so let's add a button here to open the model window [Applause] so this will be the same id that we are using for the model window [Applause] [Applause] so we need to execute the add click method on click of add department so this will basically set the model title and the form fields so let's name the button as add department so similarly we need to have the same functionality for department update [Applause] [Applause] so in the edit department we need to pass the department object which we are trying to edit [Applause] let us also implement the delete method [Applause] so for delete we will be passing only department id we don't need an object we need to call the delete method [Applause] and pass the id in the [Applause] url don't need content for delete so i think the rest all will remain same so let's just execute this and test the model window and the other features let's click on add department looks like we have some issue in the styling so let's check what is that [Applause] so input group let's try again okay the form fields looks fine we have the title also displayed correctly and we have a create button let's add a new department so we see that it was added successfully and on click of ok we see that the refresh also works now let's try to edit this we see that the update also works as expected final let's test the delete one so before delete i think we need to have a confirmation from the user so let's add a confirmation pop-up or a confirmation box before deletion so we are going to invoke a confirmation box and we will ask are you sure so if the value is false we will simply return [Music] so if they clicked on s we will execute the delete function okay i think this will be a lower case okay we see the confirmation window let's click on ok and we say that it was deleted successfully let's now complete the employee details screen [Applause] so let's just copy the contents of department details screen and then we make changes to it so let me copy everything from departments component [Applause] so this one will be add employee instead of add department and in the columns we have employee id employee name department date of joining let's first declare the variables that we need so let's define a class for employee details so it will have employee id [Applause] employee name department date of joining and photo file name which which is basically the uploaded profile picture file name and along with departments array let's also define employee array we're going to make use of the departments array in the drop down while adding a new employee we need to select department from the drop down so we'll be using it there so let's just keep it and we'll define another array for employees data and in the form fields we will have employee id employee name [Music] department date of [Music] joining so let's make the form field of date of joining as date time because we'll be binding this variable to a date picker in the form field [Music] photo file name and finally we will also define a variable to store the photo path which is basically the applications uh path to photos folder so let's get it from the config file which we already have put in app settings.json file so for the path will be coming from the config and the key name we gave was photo underscore url also let's div let's add a default picture file name as anonymous not png [Applause] [Applause] in the refresh list we need to refresh both the department's data as well as employee data okay so we'll be so we'll be updating both departments as well as the employees array and this is going to be employee class so this looks fine [Applause] [Applause] so in the create click we need to send the employee class let's convert the date to a specific format [Music] before sending [Music] [Music] okay so i think the rest of the things will be same let's copy it in the update object as well update method as well so in the update method we should also send employee id okay the delete should also be fine and in add click instead of add department the title will be add employee and we see reset the form fields of employee which is employee id employee name department let's set the date date of joining to current date time and photo file name will be the default picture file name [Applause] let's make changes to the edit click method [Applause] so this will be employee class edit employee employee id employee name date of joining so we need to convert this to a date time to bind it to the date time picker date picker now let's move to the html part we have employee id employee name department date of joining and okay this one is fine okay i think the bootstrap table should be fine now let's move to the model window in the model window we need to display both the employee details fields and also the employee profile picture so let's use a bootstrap flex to have a kind of two column view so in the left column we will be showing the employee fields go to 250. and in the right side we will show the product profile picture [Music] we will compute the source from the variables so the first thing is we have the photo path and we append a slash and then append the photo file name and the next next one we have is employee department and this will be a drop down instead of text box and we are going to bind the value to department variable we are going to make use of the array that we have and we have populated the department's details so we are going to make use of this array to display the options for the drop down so we'll display the department name and then finally we have the date of joining and date of joining will be a date field so we change the text to just date so let's see how this looks in the browser so seems like the table is displaying the data correctly and let's check the model window so the drop down seems to be fine and also the date picker also works as expected now let's add the method to upload the image file so we will add an upload button here to upload new profile picture to be able to work with image uploads we need to install a new get package so let's just first install it the package name is called blazer input file [Applause] we need to also add a reference javascript reference in the host html so let's just add it [Applause] let's import the module we'll import system.io as well because we need to convert the uploaded file to memory stream now let's write the method to upload the image to the post api method we will get the first file which was uploaded so we will upload only the first file i mean only one file i'm declaring a memory stream so that we convert our product file into a member stream so let me declare a multi-part form data content we are going to attach this to the post method i mean the upload file post method now let's copy the post method and make changes to it let's make change to the content that we are going to attach the file and once the response is available we are going to the response will be the file name itself so we are going to simply assign it back to the photo file name variable now let's invoke this method and add an upload button so we will add it near the image file i mean the image tag so input file margin 2 and we're going to write on change we will call the upload file method now let's test this method and the other features in the employee details screen so we see that the upload was successful let's give a name department so this should be department we'll change it later and here let's give date of joining as 14th and click on create so we see that it was added successfully let's change the field name so this should be department name let's test the edit method we see that edit also works and let's finally test the delete method we see that delete also works as expected let us now implement a custom sorting and filtering feature for our bootstrap table so we will be doing it only for the department's grid so let's first define a couple of variables to capture the the the filter text input from the users so let's define the department id filter and department name filter let's also define a variable to store the backup data or departments array without any filter and we need to populate both departments without filter and with filter in the refresh list [Applause] so let's first implement the filtering function so we are going to filter the department's array without filter which contains all the data and then reassign it to the departments variable so that it gets automatically updated in the screen so we're going to use link queue to filter from the text input so first we're going to filter based on the department id and we are going to use the contains built-in function and we're going to build a filter based on the department name as well so before checking let's convert into a lower case now let's add a text box to accept the input from the user for these filters we are going to have a text box along with a button in the same alignment so let's add the text box first and we are going to bind the department id filter now let's add a button to call the filter function [Applause] the text will be filter let's add the same thing for department name also [Applause] let's just test if this works so we see that the filtering function works as expected now let's implement the sorting function we will have two buttons for each column one to sort in ascending and another one to sort the descending so let's first implement the sorting function [Applause] so it will accept two parameters one is the property or the column name and another one indicates whether we need to sort it in the ascending order or do we need to sort it in descending order so let's write first for department id and if so we're going to pass the string value like whether to sort it in ascending or descending order so if you are passing the string as asc which means that we want to sort it by ascending order so let's sort our array [Applause] we are going to use the order by clause which is available in the linq query and we are going to pass the department id [Applause] and in the else section we need to sort it with descending order so we have the method called order by descending and we can use this so in the else part we'll be adding the logic for the second column which is the department name now let's add the buttons to sort it by ascending or by descending order we will have two buttons for each column so we need to pass the column name and the string ascending or descending another button for sorting and descending let's add bootstrap icons one icon for the ascending order and [Applause] another one for descending order let's add the same thing for department name [Applause] column and just change the property name so let's just test if sorting works so we see that the sorting also works as expected

Channel: Art of Engineer

Views: 12,736

Keywords:

Id: MeCffREH24Y

Length: 102min 24sec (6144 seconds)

Published: Fri Jul 29 2022