Create ASP.NET Core Web Application With SQL Server Database Connection and CRUD Operations

hello everyone in this video i will show you how to create an asp.net core web application that connects to an sql server database and performs crowd operation this means that the application allows us to create new clients to read the list of clients from the database to update the client's data and also to delete clients so let's create a new client so here all the fields are required and if i click on submit without providing all the fields i will obtain this error message now let's complete the address and let's submit the date and here we can see that we have a new client now we can update the data of this client so let's click on edit and here we can for example change the email let's add number two we can also update the phone number and let's click on submit so here we can see that the email address has changed and also the phone number is modified now we can delete a client so let's click on this delete button and we can see that the client has been deleted let's create a new project then here let's select c-sharp and let's select web application then in this list you can select this option asp.net core web application so let's select it and let's click next let's call this project my store then let's click next in this window we can select this latest version of the net core framework and also we can disable https now let's click create now the project has been created so now we need to connect to the sql server and we need to create a database and a table so we can either click on server explorer or also we can click on view then server explorer and in this window we need to create a new connection so we can click on this button so in this window we have to obtain this value microsoft sql server sql client so if you don't have this value in this field then you have to click on change then we need to select microsoft sql server then let's click on ok so here we have to provide the server name and because the server is installed on my computer so i have to write localhost or also i can write dot then slash then the instance name so the instance name in my case is sql express so for more information how to find the instance name you can see the video link that is available in the description now here we have to provide the name of the database so i want to create a new database and i will call it my store now let's click on ok so this database does not exist and i want to create it so let's click on yes now we are connected to the database and we can expand this connection so we can expand tables and for the moment we don't have any table and to create a new table i can use the sql query so let's make a right click on the connection name then new query so here we can write the sql queries so i will write two queries that allows me to create a new table and to fill it with some rows this first query allows us to create a new table called clients this table contains the id which is auto incremental it contains the name the email the phone and the address it also contain the created add column so this column has a default timestamp and then i filled this table with six rows you can find these two queries in the description of this video now to execute these two queries we can use this green run button and we can see that the queries have been executed correctly now let's refresh the connection to find our table so here we can see that we have this table and to see its content we can make a right click then show table date and here we can see that our table contains these six rows now we need to add a new package to this project that allows us to connect to the sql server so this package is called sql client so let's make a right click on the project name then manage no get packages then let's click on browse and in the search field we have to write system.data.sqlclient then we have to select this first package now let's install it let's click on ok then i accept now the package is installed correctly so we can close this page now let's run the application so here we can either select the name of the application or also we can select iis express now let's click on this run button so this is the default application it contains this menu which contains two items home and privacy so let's click on privacy so we obtain this page which is accessible at this url which ends with privacy and privacy here is the name of the razer page without the extension now we need to add here a new item called clients which allows us to see the list of the clients in the database so let's close the browser and let's create a new razer page so we have to create it in the pages folder so here i want to organize my razer page into a new folder so to create a new folder i have to make a right click on pages then add then new folder and i will call this folder clients then let's create a new razer page inside this folder so we have to make right click then add then razer page let's select razor page empty then add then here let's select razor page empty and let's call it index.cshtml so let's click on add so here we can see that we have two new files index.cshtml and index.cshtml.cs so this is our page or also we can call it the view and this is our model so let's open the model and here in the model we have this method called onget so this method is executed when we access to the page using the http get method and in this method we need to access to the database and to read the data from the clients table so first let's create a new class called client info that will store the data of only one client from the database we can create the new class into this file just after the first class so just here i will create a new class then let's create public variables that will store the data from the database so for the id it is an integer into the database but here i will create a variable of type string so here let's create a string called id of course it is public so this class allows us to store the data of only one client and to store the data of all the clients we need to create a list so we need to create this list into the model and it will be a public variable so we can read it from the page so this is our list it is called list clients and it is a list of client info now we need to fill this list in the onget method so first we need to add a try catch block then in the try we need to connect to the database so first let's create the connection string so we can obtain this connection string from our connection to the database so let's click on server explorer then let's click on the name of this connection and here in the properties we need to copy this connection string let's paste it just here and here we have this error because we need to add a second slash now let's create our sql connection so here we can see that we have an error because we need to use the sql client namespace so let's add it just here now let's open the connection then let's create the sql query that allows us to read the data from the client table so this query allows us to read all the rows from the clients table now let's create the sql command so this command allows us to execute our sql query now let's execute this command and let's obtain the sql data reader then using a while loop we can read the data from the table and of course we need to save this data into a client info object so here as i told you id is of type string but in the database it is of type integer that's why we need to add an empty string to be able to convert this integer into a string also i did not create this variable into this class so let's create it and now let's add this object into our list so this is our list we can also add here statement that allows us to show the error on the console in case if we have an exception now let's go to the page so we can make a right click then go to page so in the page we have at page which means that we will not use any controller and here we have the name of the model which is associated to this page in this area we can write some c sharp code and here we can write the html code so first let's write the title of this page then let's add a button that allows us to create a new client so this button is called new client and it takes us to this link so this link for the moment does not exist and we need to create it late then let's create an html table then let's add the header row into this table we can also create the body of this table which is empty for the moment and now let's create an item of this page into the menu of the application so let's go to shared then layout and here let's copy this code so this code corresponds to the privacy item so let's copy it and let's paste it just here now let's rename the item so it will be clients and of course we have to change the link so the link is the razer page without the extension so here our page is called index.cshtml so the link will be only index but because this page is available in the clients folder then we have to start with the name of the folder so we have to write clients which is the name of the folder then the name of the razer page without the extension which is index now let's run the application then let's click on clients and here we have this title and we have this empty table now let's fill this table from the database so here in the body we need for each loop so in this loop we will read the list of the clients from this variable which is called list clients and which is defined in the model and to be able to access this variable in the page we have to use the model class so here each item corresponds to a client info object and we need to use it to fill the row so here let's create only one row so this row is for the moment empty and it contains six cells in addition to this cell number seven which contains two buttons this button is called edit and it allows us to edit the client and this is the delete button which allows us to delete the client so when we click on the edit button we will go to this link which is not defined for the moment and once we go to this link we have to provide it with the id of the client that we need to edit also in the delete page we have to go to this link and we have to provide this link with the id of the client that we need to edit now let's complete this row so we will complete it using this object and to be able to use this object we have to use the add character now let's run the application then let's click on clients and here we have the list of the clients from the database now let's create the new razer page that allows us to create a new client so let's make a right click on the clients folder then add then razor page so let's select razor page empty and here let's call it create so here we have the new razer page and let's create the title of this page then let's create the form that will allow us to send the data of the new client so here i will use the post method then let's create the different fields of this form so this is the first row which contains the first field and the first field is called name and also it is called the name so this is the name of the variable that will be sent to the server and initially it is empty so for the moment here we have the label which has this width and we have the input field which has this width which means that the input field will have a width which is twice the width of the label now let's copy this row and let's create the new row which is for the email so here we have to change the name of this field and let's call it email then let's create another row for the phone number so let's change the label text and also let's change the name of the input field now let's create a new row for the address and finally let's create another row for the submit button so here we have the submit button but also we have the cancel button which will take us to the main page so now let's go to the model we can make a right click then view code so in the in the model we can create a new variable of type client info of course it should be public so we can read it from the page and we can initialize it when we receive the data using the post method so here we need to create a new method called onpost which will be executed when we send the data of this form using the submit button so in this method we can read the data of the form and we can put this data into this object so first we can fill the name and we can fill the name from the request so here we have to provide the name of the input field that we will use to fill this variable now let's do the same thing with the other variables of the client info object so if any field is empty we can show an error message so let's create a new global variable that allows us to save the error message and then let's validate the different fields so if any field is empty we have to fill this variable with this error message and then we can exit this method otherwise we can save the data into the database so here let's write a command to say that here we need to save the data into the database so after saving the data into the database we can return a success message to the user so let's create a new global variable that allows us to return the success message then here let's clear the different fields of the client object and then let's return the success message so we can add the new client into the database later but for the moment let's display the error message if we have any empty field so let's go to the page and here just before the form we can check if we have any error message so if the error message is not empty then we have to display this alert also we have to fill the form with the old data if we have any error so here we can fill the value attribute and we can use the client info object so here we have to fill the name then here we have to fill the email then here we have to fill the phone and finally we have to fill the address also we can do the same thing if we have a success message so if we have a success message we can show the confirmation that the data is correct we can do this just before the two buttons so if the success message is not empty then we have to display this information to the user now let's test the application so let's run it let's click on clients then let's click on new client so we can fill this form let's write any name and let's click on submit so here we can see that we have this error message but we did not lose the old value of the name field so we can close this error message and let's provide the other fields now let's click on the submit button so here we can see that we have this success message and all the fields are clear now now let's add this user to the database so let's go to our model and here let's add the try catch block so in the catch block we have an exception this means that we have an error so we can fill the error message and we can exit this method so here we will fill the error message with the error of the exception and of course we need to exit the method now in the try we need to connect to the database so first let's create the connection string so let's fill this variable from the connection string of our connection to the database so let's select this connection then let's copy the connection string from the properties let's paste it just here and let's add a second slash to correct the error now let's create the sql connection and of course we need to add the sql client namespace now let's open the connection then let's create the sql query so here i will add a new client into the clients tab and i will add the name the email the phone and the address and then i have to replace these parameters with the data that i received from the form so here we need to create an sql command and this command will execute this sql query then we have to replace these parameters with the data that we received from the form and finally we have to execute the sql query so if the new client is added correctly to the database we can either display this success message or also we can redirect the user to the list of the clients so we have just to add this statement which makes the redirection of the user now let's test the application let's click on clients then let's create a new client let's click on the submit button so we have this error message because we have to provide the address so let's complete the address then let's click on submit so here we have this error so this is an sql exception error because i declared the email to be unique but this email is already used so to fix this i have just to change the email so let's hide the alert and let's change the email let's write 2 for example and let's click on submit so we can see that this row has been added and we can see that the email is bob2 this is because we have this email already now i will show you how to edit the client so let's close the application and let's create a new eraser page into the clients folder so let's call it edit so in this page we can make a copy of the form of the create form so let's go to the create form and we can copy everything we can copy from here to the end and then let's paste it here so of course we need to change the title of the page and because this form is used to edit the client then we have to add a hidden input into the form that will contain the id of the client so this page requires some variables it requires the error message the client info and also it requires the success message so let's go to the model and let's create these variables as public variables so we can access the edit page using two methods the get method which allows us to see the data of the current client and the post method which allows us to update the data of the client so here we need to create the onpost method now in the onget method we have to read the id of the client and we need to fill this object which will be displayed into the page so to read the id we have to use the statement and we have to provide the name of the parameter which is the id now let's connect to the database so in the catch we can display the error message and in the try we need to connect to the database and to fill the client info object so here we have our connection string then we created the sql connection then we opened the connection then we selected the client with the corresponding id so this id is what we have received from the request then we have to create the sql command that allows us to execute the sql query and then we have to replace this parameter with the id that we have received from the request then we have to create the sql data reader and we have to fill the client info with the data from the database so as i told you the id is of type string but getint32 will give us an integer so we have to add an empty string to convert this integer into a string but here we have some errors this is because we have to use the sql client namespace now let's implement the onpost method so here we have to fill our client info with the data that we received from the form so here we can use request.form and we have to provide the name of the input field then if any field is empty then we have to display an error message and we have to exit this function so here our error message is all fields are required now let's try to connect to the database so let's add a try catch block so in the catch we have to display the error message then we have to exit the method and in the try we have to connect to the database and to update the data of the client so this is our connection string this is the sql connection we need to open the connection and we have to create the sql query so this query allows us to update the client and of course we will update the name the email the phone and the address using these parameters and the condition is that the id is the same as the id of the client of course we have to replace these parameters and the id also using the data that we received from the form so we can do this here and finally we have to execute the sql query so if the data is updated correctly we can redirect the user so just after the try catch we have to add the following statement which allows us to redirect the user to the list of clients now let's test the application let's click on clients then let's edit this last client so let's click on edit so here we have the data of the selected client and if we take a look on the url we can see that it ends with the id of this client now let's clear the address for example and let's click on submit but here we have this exception because i did a mistake into the form so we can stop the application now let's go to the page and here we can see that i did not write the name input correctly so let's connect this and let's run the application again then let's click on client and let's edit the last client let's make the address clear and let's click on submit so we can see that we have this error message so let's provide a valid address and also we can change the name now let's click on submit so here we can see that the name has been modified and also the address has changed now i will show you how to delete a client so let's close the application and let's create a new razer page so i will create it into the clients folder let's select razer page empty then add so for the delete page i don't need the model i only need the page so i will select razer view and i will call it delete so here we have a command we can delete it and also this page does not need any controller so we have to add at page then we need to use the sql client namespace so we have to add at using and the namespace then here we can write the c-sharp code that allows us to delete the current client so here we will read the id of the client that we need to delete then here we have our connection string then this is the sql connection of course we need to open the connection and then this is the sql query that allows us to delete the client then we have to execute this query using an sql command so this is the sql command and here we will execute this command of course here we will replace the id by the id that we have received into the request so after removing the client we will redirect the user to the list of clients now let's test the application let's click on clients then here let's delete the last client so we can see that the last client has been deleted and also we are redirected to the list of clients

Channel: BoostMyTool

Views: 368,076

Keywords:

Id: T-e554Zt3n4

Length: 36min 12sec (2172 seconds)

Published: Fri May 13 2022