CRUD Operations using ASP.NET Core MVC, Entity Framework and SQL Server | Create Read Update delete

in this video I will show you how to perform cude operations on products using asp.net MVC SQL Server database and Entity framework so I will show you how to create products how to read products how to update products and also how to delete products so to create a new product we can click on this button then let's fill the form let's select the image then submit so here we can see that we have this new product and to update this product we can click on this edit button so let's update the name the price and the description then let's upload a new image then submit so here we can see that the name the price and the image have been updated and to delete the product we can click on this delete button let's confirm and here we can see that the product has been deleted now let's create a new project so here let's select C all platforms and then web and then let's select asp.net with MVC which is this option so it is asp.net core web app with MV VC let's select it then next let's call it best store MVC then next so here we can choose the version of the net SDK for the authentication let's select none let's select this box to use https then let's click on create now the project has been created successfully so we need to connect to the database so we can click on server Explorer then let's create create a new connection in the data source we need Microsoft SQL Server if you don't have this value then we can click on change we can select Microsoft SQL Server then okay and the server is available on my computer so just here we have to write dot we will use the windows authentication and here we have to provide the name of the database we can click on this drop- down list to select one database among the available databases but in my case I want to create a new one and let's call it best store DB also we need to trust the self-signed certificate of the SQL Server so let's click on Advanced then under security we have to set the value of trust server certificate to true then okay then okay so this database does not exist and we need to create it so let's click on yes now this connection has been created let's expand it let's expand tables and for the moment we don't have any table now we need the connection string that allows us to connect to the new database so we can make a right click then properties and let's copy the connection string and let's save it in the configuration file of our application which which is up settings. gzone so here let's create a new key it is called connection strings then let's define our first connection so we can call it default connection or first connection or we can give it any name so I will call it default connection and let's paste the connection string so for the data source it is the local machine the database is best store DB we we will use Windows authentication so here we have Integrated Security equal true and of course we will trust the server certificate let's save the file and let's close it so in this application we will connect to the database using Entity framework so we need to install the required packages of Entity framework so we can make a right click on our application then manage new get packages then let's click on browse then we need to connect to the SQL server using Entity framework so we need to install the package Entity framework core SQL Server if you don't have this option here then just here in this field we can type SQL server and we have this package so let's select it and because I created an application using Net 7 I will choose version 7 of Entity framework core so just here let's select the version that is compliant with the net that I have which is 7 then install let's click on okay then I accept now the package is installed correctly so we need to install another package which is from Entity framework which is also called tools so let's delete this text and let's write tools and we need this package which is from Entity framework core so this package allows us to use these commands so it allows us to create migrations and also it allows us to update the database so we need to install a version that is compliant with the net that I have then install let's click on okay then I accept so now the package is installed correctly and you can see the available installed packages in the installed tab so let's click on installed let's delete this text and these are the installed packages let's close this window now we need to create the application DB context class that allows us to connect to the database using Entity framework so this class is a service because it will be used by other classes so let's create a new folder in our application and let's callate Services then let's create a new class and let's col it application DB context so this class extends the DB context class of Entity framework so just here let's add colum then DB context let's import the required package which is from Entity framework core then let's create the Constructor so in the Constructor we need a parameter of type DB context options let's call it options and let's pass it to the base class let's save the file and let's add this class to the service container of our application so let's go to program.cs and let's add application TB context to the service container so here let's write Builder do services do add DB context the type of the DB context that we will add is application DB context then let's provide this method with an row function for the configuration it requires a parameter that you can call options then let's read the connection string that we added to the file up settings. gone so we can create a variable called connection strings it is equal to builder. configuration. getet connection string and this is the name of the connection string that we defined in up settings. Jon so here we can see that we have this connection that is called default connection so after reading the connection string we have to provide it to the application DB context so let's call options do use SQL Server and let's provide it with a connection string now the application Tob context is configured correctly to use this connection string and to connect to the SQL server and also we added it to the service container like this it can be used by any other class of our application let's save the file now we need to create a domain model which is also called entity model that allows us to create a table in the database and to read and write the data from the database so in this course we will perform crude operations on products so let's create a domain model called Product let's create it in the models folder and let's add a new class let's call it product then let's add the different properties that correspond to the different colons of the products table that we will create in the database so first we need the product ID we need also the product name and to get rid of this warning we can initialize the name with an empty string then we need the brand the category the price the description the image file name and the date of creating the product we can also annotate these properties to limit their length in the database so we can limit the length of the name in the database to 100 characters using the max length attribute let's add the package of this attribute we can do the same thing with the brand so we can use max length we can limit the length of the category so for the description it can be longer than 100 characters so we can provide it with a maximum length and for the image file name it can be limited to 100 characters so here for the price we are using the Precision attribute let's add the required package now let's save the file and let's add a property to the application DB context that allows us to create a table called products in the database so this property is of type DB set and the model that will be used is the product model that we have just created and let's call this property products so products will be the name of the table in the database now let's save the file and let's create the migration file so we can go to package manager console and here let's type add hyphen migration and the name of the migration file let's create a migration file called first migration let's press enter so the migration file has been created correctly and this is the migration file that has been created and it will allow us to create the products table so this migration file is available in the new folder called migrations so here we have the first migration that has been created now let's update the database to create the products table so here let's type update database so the table has been created successfully let's take a look at it but here we can see the tables is always empty so let's refresh the connection and now we can see that we have the products table which contains these colons let's take a look at the data of this table so we can make a right click then show table data so for the moment the table is empty and you can fill it with some data so let's make a right click on our connection then new query and here let's write the SQL query that allows us to fill the table so we will insert into the products table the product name the brand category the price the description the image file and the date so for the date it will be the current date so here we are calling get date to provide the current date and of course we are providing the image file name names so to execute this query we can click on this button and here we can see that 30 rows have been inserted now let's see the data of the products table again so let's make a right click on products then show table data and now we can see that we have 30 products now let's add the product images to our application so I already prepared this folder that contains the product images let's copy it and let's add it to our application so we will add it to the public folder of our application which is called 3w root so here we can make a right click then paste and now we have this products folder that contains the different images now let's create the products controller that allows us to perform cow operations on products let's create it in the controllers folder so let's select MVC controller empty and here let's select MVC controller empty and let's call it products controller so we can see that by default we have the index method which is called the action that will display a view called index. CSH HTML so the view index. CSH HTML should be available in the products folder in the views folder so this is the views folder but for the moment we don't have the products folder so products is the name of the controller without the controller word and we need to create the view so to create the view we can make a right click anywhere inside this method so we can click here for example then let's make a right click then add view let's select razor view empty then add and the view should be called as the name of the action and here the action is called index so the view should be called index c s HTML so let's click on ADD so this view has been added so it is available in the views folder into a folder called products and it is called index. CSH HTML now in the index action we need to read the list of products from the database and we have to pass this list to the view so to read the data from the database we need our application DB context which is is this application DB context that we already added to the service container and to request it from the service container we need to create the Constructor of this class so as a parameter of this Constructor we need to request the application DB context let's call it context and let's save it into a field so we can use this button then create and assign field context now we can can use this context to read the products from the database so here let's create a variable let's call it products and it is equal to context do the name of the table in the database which is products dot to list now if we put the cursor here we can see that products is of type list of product objects and we need to pass this list to the view so just here let's write products and to read the list of products in the view let's go back to the view which is index. CSH HTML and here let's say that we need a model so let's add at model and the model that we need is a list of product objects so let's provider type which is list of product objects now let's create the view so first let's display the title of the page and we can display the text list of products and to display it in the center we can use this bootstrap class which is text Center we can also add a margin bottom of five units then let's add a button that allows us to create new products so we can create a div of type bootstrap Pro that contains two columns this is the first colum and this is the second one so the second colum is empty and in the First Column we have this link which is a bootstrap button and it has the text new product so when we click on this button we will execute the action create of the controller products so here we can see that we are using uh tag helpers so we can use Tag helpers instead of the traditional ashf attribute then let's create a table so here we have a Bo strap table that has the table head and the table body in the table head we have seven columns we have six columns to display the product details so we will display the product ID the name the brand category the price the image and the date and in this colum we will display two buttons the edit button and the delete button now let's complete the body to display the different products so we can use for each Loop and for every product in the model so our model is a list of product objects so for every product in the model we will create a table row so this is a table Row in the first cell of the row we will display the product ID so we have to use at then the name of the variable which is this variable which is of type product then the name of the property which is ID then we will display the product name the brand the category and for the price we are using the explicit razor syntax so here we are using parenthesis and after the parenthesis we are adding this character which is the unit then here in this cell we are displaying the product image so we are using the IMG element the source is the public folder SL the name of the folder that contains the images which is products so this is the public folder and this is the products folder then here we are displaying the name of the product and of course we are using the width attribute to define the width of the image so we can set the image width to 100 pixels then in this cell we display the date of creating the product so to limit the length of the date we are using to string and we are using this format so we will display the month then the day then the year and in the last cell we have two buttons so this is the first button it is called edit and this is the second button which is called delete so when we click on the edit button we will execute the edit action of the products controller and we will provide it with a parameter called ID that is equal to the product ID so for the moment we don't have the edit action in the products controller and of course we need to create it lat we will do the same thing with the delete button so when we click on this button we will call the delete action of the products controller and we will provide it with a parameter called ID which is the product ID now we need to add a link in the Navar that allows us to access to the list of products so let's go to the Views folder then here we have shared then let's open the layout file so this is the Navar of the application and for the moment we can see that we have two items this is the first item which is the home item and this is the second item which is the Privacy item so we can copy the home item let's paste it here let's change the text of the item and let's write products and let's change the name of the controller so the controller that we want to execute is the products controller so here we have to write products the action method that we want to execute is the index action so we don't need to change the name of this action now let's save the different files and let's test the application so let's click on products and here we have the list of products so we can see that we have all the products which are 30 products but here we can see that we are displaying the products in the ascending order of ID which means that we will display the oldest products first and to display the newest products first we have to reverse the order so let's go to products controller and just here let's reverse the order so we have to add order by descending and we have to provide the name of the colum which is ID let's save the file let's click on products and this time we can see that they are ordered in the descending order of ID which means that we have the newest products first now I will show you how to create new products so to create a new product we need a model of type DTU which means data transfer object that allows the user to submit the product details so we need to create a new model and let's create it in the models folder let's call it product DTU so this model will be very similar to the product model but we don't need the product ID because the user is not allowed to define the product ID but we need the name the brand the category the price the description and here we don't need the image file name but we need the image file itself also we don't need the date because the date will be generated on the server so we can copy all of these properties let's paste them in product DTU so we don't need the image file name but we need the image file itself so we can delete this part let's rename the property we can delete this attribute and it is of type iform file so we will use product dto to create new products but also to update products when we create a new product the image file is required but when we update the product the image file is optional so let's declare this property as optional and later when we create a new product we have to validate the image file manually also we don't need this attribute because it is for the database so we can delete it from here and we can delete the package from here then let's add some validation annotations so the name is required the same for the brand which is required the category is required as well and the price is required and the description is required let's save the file so in the page that allows us to display the list of products when we click on new product we will execute the create action of the products controller so now we need to create this action let's go to product text controller we can copy this line and let's create the create action so let's call it create and it returns a view so we need to create the view and it should be called create. CSH HTML so we can make a right click here then add view let's select razor view empty then add and let's call it create. CS HTML so this view has been created and of course it is available in the views folder and the products and it is called create. CSH HTML so this view requires a model of type product DTU so just here let's delete this command let's replace it with at model then the name of the model which is produ DTU so we need this model because we need to bind it to the form and of course the form will submit an object of type produ to the server now let's create our form so first we can create a row that contains one column that will be displayed in the center of the page then we can add a rounded border to this column and we can add some paddings and here we have the title of the page so the title will be new product and it will be displayed in the center of the page and of course here we have a bottom Mar then let's create the form and the form will be submitted using the post method so let's define the method which is the post method also the form will submit an image to the server so we have to define the ink type attribute which is is equal to multiart form data then let's create the first field of this form so here we have a label which is called the name and we have another div of type colum that contains the input field and a span for displaying any validation error so this input field will be bound to the name property of product DTU so name here is a property of product DTU and here we are using this tag hel asp4 then here this pan will display the validation errors related to the name if of course we have some validation errors we are using this bootstrap class to display the validation errors using the red color then let's create another field that allows the user to provide the product brand so we can copy this row let's paste it here in the label let's write brand then the input field will be bound to product dto brand so let's delete name and let's replace it with brand and of course the span will display validation errors related to the brand then let's create another row in this form that allows the user to select the category so here we have a label with a text C and we have a select element that is bound to product d. category so here we can see that we have several options in this drop down list so these are the values that will be displayed to the user and these are the values that can be submitted to the server then let's create the other fields of this form so here we will display the price and this is the input field that is bound to produ D price and this is span for the validation errors here we have the description and the description will be displayed into a text area which is bound to product dto do description and of course we have a span to display any validation errors then we have a label for the image and this is the input field that is bound to product dto image file then we need to create the last row that will contain two buttons the submit button and the cancel button so this is the last row it contains two columns this is the First Column that will be displayed after n of set and it has a button of type submit and the text is submit then we have the second column that will display another button of type link it is a Bo strap button and it is called consel so when we click on the consel button we will execute the index method of the products controller this means that when we click on consel we will display the list of products again now let's test the application and let's click on new product and we obtain this view so the URL is/ product/ create so we will execute the create method of the products controller and here we have the form so we can provide the product name the brand here we have the list of Cate atories here we can provide the price then the description and finally we can provide the image so if we click on Console we are back on the list of products and if we click on submit nothing happens so we need to create another action in the products controller that will handle the submit request now let's create another action method called create in the products controller so so we can copy this method let's paste it here this method will be accessible using the HTTP post method so we have to decorate it with the HTTP post attribute and it requires an object of type product dto which is the submitted data so just here let's add a parameter of type product DTU first we need to validate the image file manually because in product dto the image file is considered as optional so if product dto image file is null then we will add an error to the model state which is related to the image file property of product dto and the error message will be the image file is required now let's check if we have any validation error in product DTU so if the model state is not valid then we will return the view which is create. CSH HTML and we will provide The View with this object that contains the submitted datea so it is product D so this object is already required by The View and is already bound to the form so if the submitted data is not valid then we will return the create view with the submitted data otherwise we can redirect the user to the list of products so we can delete this statement and let's redirect the user to the index action of the products controller that allows us to display the list of products let's test the application let's click on new product let's provide the name then submit so here we don't have any validation error for the name because we already have the product name but we don't have the product brand so we have the validation error the category other is valid the price is not valid so here we have the validation error for the description and also we have a validation error for the image now let's fill the form let's click on submit without providing the image so here we can see that we still have the form data because product dto is already bound to the form but we have the validation error for the image so let's provide the image then submit and because the submitted data is valid we are redirected to the list of products now we need to save the products so we need to save the product image and also we need to save the product in the database first we need to save the product image so we need to save the product image in the products folder of this public folder so we need need an object of type iweb OST environment that is available in the service container that allows us to obtain the full path of this folder so we can request it in the Constructor of this class then let's save it into a field so we can select create and assign field environment so to save the image file we need first to define a unique name to this image file so the unique name of this image file will be the current date using this format so the year then the month then the day then the hours the minutes the seconds and the milliseconds and in addition to the current date we will add the file extension to this name so file extension is available in product d. image file do the file name and we will get the extension from the original file name using the method method get extension and we will add this extension to the file name that we are defining just here then we need the full path where we will save the image so the full path where we will save the image will be the full path of the public folder of our application which is this folder plus products which is the folder where we will save the images plus the unique file name of the image so this statement allows us to save the received image to this path then we need to save the new product in the database so in the database we can add objects of type product but we received an object of type product DTU so we need to create an object of type product that we will save in the database using the receiv data which is of type product DTU so here we create an object of type product we set the name using the product d. name we said the brand the category the price and the description using the received data of product dto for the image file it will be the unique file name that we created just here and for the date it will be the current date now we need to save this object in the database so we can use our context so we have to write context do the name of the table which is products do add and we will add this product to the table and of course we need to save the modifications so we have to call context do safe changes Let's test the application let's create a new product then let's fill the form then submit so we are redirected to the list of products and we can see that we have this new product so the these are the product details and this is the product image now in products controller we need to create the action method that allows us to update the product so if we go to the index page we can see that when we click on the edit button we will execute the edit action of the products controller so now we need to create an action called edit in the products controller so this action requires a parameter called ID which is the product ID so this ID is already added to the URL so if we go to index. CSH HTML we can see that when we click on edit we will add the parameter called ID to the URL so this ID is the product ID so we need to read the product having this ID from the database and we can use our context to read the product so let's create a a variable which is called Product and it is equal to our context products. find and find requires the product ID which is this variable so this product is of type product that can be null so if we don't find a product with this ID then product will be null so now let's check if product is null or not if product is null then we will redirect the user to the list of products so the action is index and the controller is products otherwise if we found a product then we will create an object of type product dto using the data of this product so this is an object of type product dto that is filled using the data of the product object that we obtained from the database then we have to return a view and we have to provide The View with this object then let's create this view so it should be called edit. CSH HTML and it will be available in the products folder under views so we can make a right click here then add view let's select Razer view empty then add and let's call it edit. CSH HTML so this edit view will be very similar to the create view so we can copy all of the source code of the create View let's delete this code and let's paste the source code so this is the product dto that we are receiving from the controller which is this product and it is already bound to the firm let's change the title of the page so here we can write edit product then we need to display the current ID of the product so in this form we can copy this row let's paste it here in the label let's write ID we don't need this span so we can delete it and in the input field we don't need to bind it to any property so we can delete the tag Helper and let's replace it with the value attribute so product dto does not contain the product ID so we cannot display the product ID using product DTU in instead we can add the product ID into a dictionary called view data and we can display the ID from the view data so let's go back to the controller and just here so we can add the product ID to the view data under the key product ID we can also add the image file name to the view data and this is the key and also we can add the date of creating the product to the the view data so now we can display the product ID using this dictionary so we can copy this text and let's display it here so we have to add at then let's paste the code then we need to display the current image and the date of creating the product so just above the row that allows us to provide the new image we can create another row so this is a bootstrap row that contains only one colum but this colum will be displayed after some space then in this colum we will display the current image so the source of the current image will be the public folder of our application SL products slash the image file name which is available in the view data dictionary under the key image file name we can add the width to define the width of the image and we can set the width to 150 pixels then after the image we can display the date of creating the product so we can add this row which contains a label of the text created at we have this input field which is of type read only it is not bound to any property of product dto instead we have the value which is available in the view data dictionary under the key created at so here we are using this bootstrap class and we are using readon attribute let's copy this text and let's update the input field that that displays the product ID so we can delete the class and and we can use this Bo strap class in addition to the attribute read only let's test the application let's click on edit of the product to the id31 and we obtain this interface so here we have the product ID which is of type read only we have the current name the brand the category the price and the description this is the current image and this is the date of creating the product so in the URL we have/ products sledit and we have the product ID so we are executing the edit action of the products controller now if we click on submit nothing happens and we are always at the same URL so to edit the product we need to create a new action in the products controller that is called edit it requires the product ID that we want to update in addition to the product details which are of type product DTU now let's create a new action that allows us to update the product so we are in products controller let's copy the edit action so this action will be accessible using the post method so here let's add HTTP post attribute and of course we need the submitted data so the submitted data is of type product DTU so first we need to read the product having this ID from the database so here we will read the product from the database using our context if product is null this means that we did not find a product with this ID so in this case we will redirect the user to the list of products otherwise we have to check if the submitted data is valid or not so if the model state is not valid this means that we have some validation errors in this case we can return the view which is edit. CSH HTML and we can provide it with a product dto object which is this object of course we have to provide The View with additional data which is this data so we can copy these statements and we can add them just here but if the submitted data is valid then we need to update the product image if we have a new image and we need to update the product in the database so first we need to update the image file if we have a new submitted image file so we can create a variable called new file name which is initialized with the old file name then if we have a new image file then we will update the value of the new file name so the new new value will be the current date plus the received file extension then we will create the full path where we will save the new image so it will be the full path of the public folder plus SL products plus the new file name that we defined just here then we will create a stream and we will save the received file into this tree and of course we need to delete the old image so the old image is available at the path of the public folder SL products and this is the old file name so this statement the delete statement allows us to delete the image that is available at this path then after updating the image we need also to update the product in the database so we will update the product name the brand the category the price and the description from the submitted data which is available in product DTU and we will update the image file using new file name that is initialized here and updated here so this is the product that we received from the database just here and we need to update it so to update it in the database we have to call our context do save changes and after saving the product we will redirect the user to the list of products let's test the application let's update the product to the [Music] id31 let's update the name the price and the description then submit so here we can see that we have a new name and a new price now let's update the image then submit and this time we have a new image now I will show you how to delete a product so when we click on the delete button we can see that we sent a request to/ product/ delete and then we have the product ID so now we need to create the delete action in the products controller so this is products controller let's create the delete action and the delete action requires the product ID so we can read it from the URL so first let's read the product with this ID from the database if we don't find a product to this ID in the database then we will redirect the user to the list of products then let's delete the product image so this is the full path of the image and this statement allows us to delete the image that is available at this path then we need to delete this product from the database so we can use our context do the name of the table which is products and we will delete this product then we need to save the modifications and finally we need to redirect the user so we can copy this statement and let's paste it here let's save the file and let's test the application let's delete the product to the id31 and here we can see that it has been deleted we can also add a JavaScript code that allows the browser to ask the user to confirm the delete request so let's go to index. cshtml that displays the list of products and this is the delete button so just here we can add the onclick attribute and we can add this JavaScript code so here we will call the confirm method and we will ask the user whether he wants to confirm the delete request or not let's save the file and let's test the application let's click on the delete button of the product with the id30 and here we have this alert are you sure if I click on cancel nothing happens and if I click on delete the product with the id30 has been deleted

Channel: Codinblue

Views: 3,832

Keywords:

Id: SfWuOFEatYc

Length: 51min 35sec (3095 seconds)

Published: Tue Feb 27 2024