CRUD operations in ASP.NET Core MVC (.NET 5) and SQL database | Insert | Read | Update | Delete

welcome to my channel i am rofyaseen and in this video tutorial we are going to perform crowd operations in asp.net core mvc.net 5 with sql server so uh before creating a project let's understand a quick uh i'm going to give you a quick introduction of dot net 5 which is the major major release of dot net core after uh dot net 3.1 dot net go 3.1 so basically.net core.net 5 is a unified variant of.net core 311 and the microsoft.net framework 4.8 and now in order to remove the complexity or misconvenient microsoft renamed the dotnet core variant uh asp.net core 5.0 is basically into dotnet 5 to avoid convenience between asp.net mvc5 asp.net core five and dot net core related things so basically uh dot net five is a unified very unified variant of uh uh microsoft dot net core 3.1 and the microsoft.net framework 4.8 and this is the major release and by the way this is open source framework and the complete source code of framework is available on github and more importantly it is cross platform so we are going to perform uh cred operations we are going to create a asp.net core mvc based application in which we will perform crowd operation which is uh create to interrupt delete and we will learn all the things related to dot net five entity framework and sql server will perform crowd operations using sql server databases so let's get started create a new project from here and from here we are going to select asp.net core mvc so basically this is the template we are going to use asp.net core web app model views controller click next and we're going to select the folder so and over here we'll write employee crud app okay and next configure for https it's okay we don't need any authentication but we we do not need this enable razer runtime compilation click create this will create a dot net 5 core mvc application for us so this is our app okay so this is basically a solution explorer where we contain a bunch of files related to dot and five so uh i'm going to give you a quick introduction of these files basically we have dependencies it means it contains all the installed new get packages whatever packages you will install from the new get will be residing here so if you come here in the packages you can see we we have selected dotnet core razor run time at the start up and it it automatically add that package in this section and we will install entity framework core uh that will also will be shown here so basically this dependency section contains all the external libraries packages you will download from many new get packages will be displayed here then we have over here is a properties and in in here we have launch settings.js and it basically contain uh the profiles of our application like we have ia s settings which will be our application url ssl port because we have configured https connection and we have this environment variable like we are currently in development mode and this is the razer runtime compilation and this is we have uh another uh basically a profile where we are setting launch browser and things like that so so basically this is a just the launch settings dot when these settings are applied when our application get launched from this visual studio on the browser and these settings are applied to the web application so next we have www root which basically it is a web root folder of asp.net core mvc application and we can put all the static files like html oh sorry javascript css images and any html template pages we can put inside here basically it contains static files so basically if you come here you can see we have css js and we have some external libraries like bootstrap jquery jquery validation and unobtrusive and after that we have controller obviously you are familiar with controllers we we have controllers where or and you end user will hit the request inside the action result of controller so this is basically a controller which contains controller classes and next thing is the model which is domain model of our application like products categories employees all the models we reside here and here is the views which contains basically views related to our application look we have con home controller then we have home folder here and all the views related to this controller will be in this folder and if it doesn't find any views called here in the home controller and if the view is not present in the home folder then it will look uh in the shared folder so that's it uh and next we have app settings dot json file basically it contains application configuration file uh application configuration which is to use like configure services uh sorry configure our connection string logging configuration and let's say we want to use some external key based key value pair things we can store here and after that we have this program dot cs which is the entry point of our application if you're familiar with the console applications you you you know every c sharp console application has a man method a program.cs and man method and which is the basically startup or entry point of our application and inside here it is calling create host builder and it is passing command line arguments and this is the create host builder method which is again calling another clause which is a startup so if you come here in the startup file it contains basically it contains a startup class then constructor for injecting a eye configuration and it basically it contains two methods configure services and configure so basically this is the container where we if we inject or register our services and this is the application middleware and the over here in this section order is very important you you'll have to set up things in in an order and this is the i service you can say i service collection container you can enter you can register your services at any order order doesn't matter in this method so but this is the middle where this is the pipeline of your application how your application goes through a pipeline so order matter here okay so basically that's it for the quick introduction of this dot net core mvc application uh but but we're going to perform cred operations so let's start and first of all we need a controller in order to perform crowd operations we need a controller so obviously we will create a controller and let's say we're going to perform crud operation with employee so i'm going to create empty controller mvc empty controller and i will rename this controller as employee controller that's it this is the simple controller which contains one basic index method but we index action result which will return view and obviously we don't have any view for this controller so we we can stay here so our controller work is completed now we can come here in the models and we will create a model class for employee and this class has some properties so we can create prop int id for employee id prop string first name last name and we can set string mobile number string email then we have address line one two city postcode country and that's it address line one dress line two city postcode country okay so this is basically uh we have only one model for the employees and this is basically uh will will be an employee table inside our database will configure entity framework in a minute so it will be like a employee model in the database okay so after that we have our model ready we have our controller now it's time to install entity framework so we'll go here in the employee cloud app and click here manage nuget packages and over here will browse for microsoft dot entity framework dot sql server so basically this is the framework which we are going to install microsoft identity framework code.sql server and we will select which is the latest stable variant 5.0.8 that's it click here to install and i accept another important package we'll have to install is this microsoft identity framework code.sql server.design which we basically will allow us to run our migrations and things on on within the entity framework so install this accept that's it now it's time to create our db context clause so i will go here and i will add a folder data and in this folder i will create my db context clause so i will write employee context and this is our basically this is our db context clause and before implementing this db context we'll have to create our connection string so we're going to create a connection string inside here in the app settings.json file so after that we can write here connection strings and over here we write the name of our connection string which is default connection and over here i will write server equals to which will be the name of my server then we have database which will be the name of my database and we have trustrate underscore connection equals to true and then we have multiple active result sets equals to true and over he will have to specify the name of our database let me open my sql server and this will be name of our server and our name of our database will be employee crud db so we'll copy the name of our server from here and we can paste name of our server here so this is our server name then we have database name employee credit db then trusted connection equals to true and multiple active results equals to true so that's it so we have set up our connection string between the inside app settings.json file and no no we are we are ready to set up our db context class so basically this is our employee context which will inherit from db context ctrl dot and import this name space using microsoft dot entity framework core so this is our db context class and inside here i will create a constructor employee context and inside here i will pass db context options db context options and option of type employee context options and we will pass these options to base constructor options and that's it for this constructor we don't need anything else for this constructor so what we can do we can move this to here okay so this is basically our db context class setup now we'll come here in the startup class and i already told you we'll have to set up every service within this ad services to this container so we will add our connection string service to this container to write services dot add db context and over here i will write the name of my context class which is employee context control dot and after that i will create some options options goes to this and that's it and over here i will register my sql server connection string options dot use sql server control dot and import this namespace and over here i will write configuration what is the name of our yeah configuration configuration dot get connection string and over here i will write the name of my connection string default connection okay remember this default connection we have defined in the app settings.json this is the name of our connection string and we are giving that name here so that's it for this section so we have set up our connection string within the startup.cs and appsettings.cs app settings.json file and we have created this uh employee contest class and we are passing that employee contacts to the base constructor clause okay so it's now it's time to set up our table so we have we already have created employee table the only thing we need to set up here property of db set employee control dot import the name space and over here we'll set the time of our table in the database employees perfect so quick review we have set up we have installed entity frameworks dot sql server then we created this context class we configured our connection string in the app settings.json file pause in the constructor class and set up in the startup dot cs class and this is the last thing we did to add adb setup for employees model now it's time to create our database so how we will create our database we'll come here in the package manager console and credib okay so over here i will add add migration and i will write name of my migration initial create you can write any name here look it is telling add migration is recognized not recognized blah blah blah and basically this is an important error so basically errors are very good you need to understand if you search this error basically it will tell you you need microsoft identity framework code or tools in order to run migration so we we have already installed one package which which was i remembered this design sql server design is required but we still need entity framework code or tools in order to run migration so we can install it no problem microsoft entity framework code or tool so we'll install this i accept perfect now we can come here and we we can again run that migration add migration initial create and it that's it you need you can see it is creating a create table which basically the name of our table is employee then it is setting up some properties of connect our table and that's it but the important thing here is to notice is it's created another folder inside the solution project and which is not a good thing basically we want to set up this migrations folder within our data folder which is basically related to our db contacts and things so we need to move this folder so what we can see look it is telling remove migrations in order to under undo so we can write here remove migration so basically it remove the created migration for us and if you come here look that migration folder is removed from here we can create again add migration initial create and this time we'll specify an output directory and which which will be our data folder okay so we want to run this migration inside our data folder so basically we will write here at migration the name of a migration initial create then we will specify an output directory which will be data folder this data folder and inside here we want to create another folder migrations okay so if you click here and add you can see in this time it creates migration within this folder and which is under the data folder okay now the only thing we need to write here is update database and hit enter perfect build succeeded now we can make sure our database is created or not and you can see here employee crud db and it is created an employees table for us perfect so we are good to go the next thing is to create a crud operation or basic setup is now ready so we can come here in the controllers folder and in the employees controller and over here inside the employee controller we basically will inject our db uh store context implies context class employee context which is our connection string context and we can write here control dot and created assign field app context and we can write here underscore in order to remove this complexity and underscore context by convention is a good thing so we have injected our connection string so next thing in the index section we will basically return the list you can rename this section if you want to do so but uh but i think it is good we don't need to change this index so we can here we can write here employees equals to underscore context dot employees dot to list so basically we are calling our database so it is best practice to use async method when you are calling to database in order to set up a 100 responsiveness of our application so we can set this method to async and we can write here await okay and after that or we can use basically this to link to list is not for the anything we can use to list to list async variant control dot yeah that's it and we can pass these these employees to our view so this basically this will be the list of our employees and if i come here right click add view razor view add and we will select a template from from here is a list and we'll set up the name of our model is employees and we can we can look here you cannot look here so we can click here to select the shared layout underscore layout.cshtml perfect and we don't need any reference libraries because this is not a form this is just a html table to display the list of our employees perfect so this scaffolding mechanism is automatically created our display page which is basically you understand we're passing list and it is already mapped that model into ie numerable and we have this table which contains some of the properties and we have here is the thing like table and this is for primary key we'll set up this in a minute so if you run your application at this stage and this is our application so if i click here and over here i will write the name of my controller employee slash name of my index action and this is our view the employee list view i'll change this view a little bit so we can come here and we'll use the benefit of our razor runtime compilation and i will here i will here i will change employee list and i will change this table to table dash script and we don't need this id column anymore here and we also we don't need this to display html 1 address line 1 and redux line 2 if you want to display you can do but i don't think so we should display here so i'll remove these two and if i come here in the browser again and refresh perfect you can see we have employee list here then this create new and we have first name last name mobile number email city postal code and country that's it so now we'll create click here if you click here look we don't have any create action inside our implies controller will create one in a so we have changed this create button too we applied this class btn btn primary to look like this a bit this link as a button so now we are going to create another action for basically displaying the form so we can click here public i action result create and over here you write simply return view we are not setting up this connection this a i action result into async because we don't need to call any databases this will be a basically simple http get call and the application will display the form of our employer so we can right click here add view razor view at and we need viewer name create then we will specify a model name create or template time create then model name employee model don't need to specific context class but we do need to specify this reference script libraries because we want to implement a client set so that's why reference script libraries are required and click add that's it it is created it has created a form for us and this is create we don't need this h1 and we can write here employee information and and an end and that's it now we can run this application and see what it is giving us and the important thing we don't have any links here so we we have to manually navigate to these action results so i'm going to create these links straight away so i can come here in the shared folder in the layout file and you can see over here we have a home and we can create here link employee list new employee and we will have to call employee controller and this will hit to index section of employee controller and this filter to create action of employer controller this makes sense so we can click here so that we we don't have to manually type in the url like employee controller and things we can click here and we can navigate to our controller so look employee list when i click here so this is basically our employee uh list form and if i click here it is opening this employee uh form for us so basically this is employee information this is an id column and we have first name last name mobile number email address and everything for what we have required here and this id column is by the way an auto incremented field by convention so we don't need this so we can come here in the create action we don't need this id column anymore we can remove this here so basically what it is doing for us is creating call md4 and all the form within that column d4 so what we can do we can click here column we can set column d6 here and this is our form this is our model and these are our controls so we can create here our own classes div class is equal to call md dash 4 this is our first class first column so we can copy this and paste this and better we can add if clause is equal to row and inside this row we can click here yeah that's it this makes sense because we we want to display okay we want to display in two controls in a line so this is for two two two okay so now we can cut here this is first name it it will come into the first four column of this row and we have this cut second four column of this row and cut and we have address line one address line two it's up to you if you don't want to change the layout you you don't have to change basically i want to change it got it and the next thing is this country and we don't need this column anymore so we can remove this and we can come here and if i go here in the browser and i refresh this page let's see what it is doing for us so look basically it is it display in two columns but very small column so basically what we can do we can come here and we can write here twelve we can set here six 6 6 6 so in each row we have 12 columns so we can set here 6 column for each control that's it come here and try to see if it is doing anything okay perfect so this is our employee form and form is ready if i click here back to the list it is navigating back to the list form and if i click here it is navigating to the employee form that's it now what i if i click here in the create action look nothing happening because we don't have any http post action for this uh create action so we we are going to create that http post action in the employee controller what i will write here http post and i will write here validate entity 4g token and over here this is basically you used to avoid hacking task of async task of i action result and we will write create and this create basically accept a form because our view is pointed with the model employee employ control dot this is our model and perfect and over here first of all we will check here if model state dot is valid then we will do here what we are going to do otherwise we will simply return view and we will pause the incoming model inside the view if anything happens validation or any issue it will simply return the view otherwise if model state is valid and then we will come here and we will do underscore context dot employees dot add employee and we'll write here context dot save changes async and we can set here a weight and after that we can write here return redirect to action and we can write here the name of our action name of index name of our action is index okay so basically this is uh you can say quick quickly it will add into the database and it will do whatever with telling it it is to do so let's start and see what it is doing for us so if i click here in the employee list we have nothing in the employees list because we don't have any employees here if i click here to create new and let's see if i writing something muhammad ahmad and some mobile so okay and if i click here in the create and perfect you can see it successfully added our employees but what we can do is see here it is showing us one in the list here so we need to change in the employer index so look we have this first for the first name and we have this for the id so we don't need to display this id and instead of separate first name and last name we can come here and we write here full name and over here we have this display we can cut here and we can write here we don't need this separate column okay so if you come here and refresh this so you can see we we have muhammad ahmad inside full name then mobile then email and all the things related to this so if you come here in the create new and click here look what it does it it it added an empty row to our implies you can see this is our empty row and if you write something here and click create and you can see this is empty row and we need to validate our data so how we can validate our data if you come here and this is our model where is our model and insert models further we have employees model and we want this field to be required so we write here required control dot import the name space and over here we can set error message equals to in first name is required okay and we can also set up an other data type here maximum length of 50 and we can specify error message first name should contain maximum 50 characters okay so this is how you can apply data annotations so let's say i want to apply these data annotations to first name last name and over here in the email i want to apply another data annotation data type data type dot email and and that's it this is just to show you how to you can apply data notation there are another properties of these data annotations maximum length minimum length data types regular expressions and now what we'll have to do in order to impact these things if you look here first name and we have maximum length 50 and if you come here in the sql server right click design and you can see all these string properties has an orchard of mac so we need to control this i have shown you how to you can control and in order to impact these changes to the database you will have to create another migration so what we can write here at migration and we can write the name of migration edit data annotations to employee model always use give useful name to your migration so i can click here to add and look it is telling last name property of our employees model and set its type to n watcher 50 instead of max old type look old type was inverter max and that's it and it is also setting null level equals to false which means that it is now required field we can write here we can click here update database and that's it if you come here in the sql server close this right click design it again and i think these changes are not applied refresh refresh plus design these changes are still not applied let me see what it is doing for us cannot insert the null value because we already have inserted the null value so this is the issue if you come here in the edit edit this table look we already inserted null value that's why these migrations are not applying so we can come here and we can remove these three rows so the database is empty now we can run the migration again update database and done if you come here and right click design it again and you can see first name and watch our 50 50 and allow knowledge is false now if you start your application at this stage after applying these data annotation and you remember we have set up when we was creating uh we created this form we could we clicked enable partial scripts so basically what it is doing it is doing scripts and render validation scripts partial so if you go here in the shared folder you can see validation parcel scripts and what it is including for us jquery validation and jquery earn update validation so insert here www these are all and these will basically validations are required to perform client-side validation so you can see its behavior if you come here in the employees list we don't have obviously we don't have any employee here and click here and click here create look validations are replied first name is required last name is required similarly you can write your own validations okay so i can write here again muhammad muhammad ahmad zero three four four okay click here and that's it now we have successfully created data uh insert operation display operation and client-side validation and data annotations now it's time to click here and write the edit action so if you click here look it is calling employee slash edit and it is not passing any id for the employee so if you come here in the list view the first thing we'll have to change this behavior to uncomment we don't need any details action by the way so we can remove here straight away and we we we will have to pass an id property of our employee so basically this is our parameter which will have to pass inside our controller so in order to see this impacting here we should have an id of employee after that edit action so look when i click here edit you can see slash four so we got an id and we'll have to pass this id to this edit action so we can come here in the employee controller sorry and we will create another action and public async task of i action result edit and it is expecting an id which will be an optional because we want to set this id as an optional endeavor he will check if id equals to null or id equals to zero or we can say id less than equals to zero and we can simply return here bad request we don't want to handle any request which comes with this without id or id is equal to zero and otherwise what we can do we can click here for employee in db equals to underscore context dot employees dot first or default async and over here we can pass here e goes to e dot id which is equals to id that's it and basically we're calling async method and we will have to use a weight keyword here that's it and after that we'll check here if employee in db is equal to null then we can click here return not found you can specify obviously you can specify a bad request view not phone views your customized views okay and after that what we want to do we want to return view if this imply exit exist so we can pause here employ in db so if you want to implement both actions in the create you can do no problem but in order to avoid complexity i am going uh to create another view for the edit so i can click here right click add view razer view head and i will create here edit view and we'll specify employees model and obviously this will also contain a form so reference script libraries is also included here so we can come here and set up this so this created us a form of all the required fields and that's it so basically this is also a form we don't need to we can set here employee information and we can write here update update employee information we don't need this edit here and that's it basically when we come here in the employee controller we need another action which will accept this update method so we can set task of i action result edit which will also accept an employee model and over here we will check if model state dot is valid and we can this time we can quickly return a view here by specifying not operator here so this is just to show you how you can use different approaches over here we're writing like this and we will be writing like this so we will simply return view employee okay and if this employee exists now what we can write here if this implies exist and it is like exist and it is valid so we can write here simply we can write here context dot employees dot context dot entry and we can specify entry of employee dot straight equals to entity state dot modified okay you can use this approach for updating you can use context dot employees dot update and you can pause employee like this you can use this way you can use this way otherwise if you want to update each property you can use that approach as well so it's up to you whatever you want to use i want to use this update approach and after that i will write here context dot save changes async and this is obviously a wait so after that i will return redirect to action of name of index okay i want to return to the index section and that's it this is the basically a detection let's see how it is working let's start the application employee list click here edit and we got an server the request match multiple endpoints edit edit and we need to see the request matches multiple endpoints employee slash edit slash 4 so if we come in the controller action and we can see what we what our request is doing so we can add breakpoint here oh basically we we don't have specified here an http post so basically we need to specify an http post here and we also need to specify validate entity forgery anti-forge forgery token let's start one more time so basically by convention this is a get request and this is a post request so get by default if you if you you are not specifying anything it will be considered as a get request and we we have two get request of edit and which this was a confusion click here in the employer list edit and perfect you can see this is our edit action and which is obviously which is a different action and if you want to display id you can display here but here user can change this id so we need to change this behavior we if we come here in the edit action edit view and this is an id column we can set here we can set here we don't need this here basically instead of this we can use and hidden attribute html dot hidden for m goes to or you can write e goes to e dot id that's it this will be a hidden field here and if you if you want to change the layout you can change because i already showed you how you can change the layout but this was an important thing to in order to avoid this id to be edited here so that's why i removed here and i set up this id column as a hidden field so let's say i want to write an email here info at ahmad.com and country uk save perfect you can see these changes are being applied inside our model so next thing is to create and delete action and obviously if you look at here downside when i move here you can see slash delete slash four so basically we need to create a delete action here in the controller so we can come here in employees controller and this delete action is uh basically so we can come here and we can write a public async task of i action result delete int of id check here if id equals to null or id less than equals to zero then we will return here return bet request okay so after that we'll check war employee in db equals to underscore context or you can write await underscore context dot employees dot first or default async e goes to e dot e dot id equals to id okay and after that we'll check if employee in db equals to null then we can return here return http or simply write return not found otherwise if we have we still have an implies we can write here context dot employees dot remove and we can pause imply in in db okay and after that we can write here await context dot save changes async and return redirect to action name of index okay so let's start and see if it is working or not and click here in the employee list and we don't have any enough employees so we can quickly create some employees create create and if you click here on the this employee look it is deleted if you click here delete it if you click here and write some address line this this this and click here and you can see all these things are updated so this was the real quick tutorial not exactly quick basically we have spent an hour in order to learn all this thing so this was important basically if you know at startup we'll have to spend some time for the connection string for the context db context entity framework configuration database thing migrations and everything so basically in startup you understand these things take take some time so we have successfully created cred application so now you in order to create another grid operations on some other uh you can say entity you can quickly create and you can run so this was just a quick tutorial uh to show you how to perform credit operations and we still are missing something we we do have implemented some these validations but we're still missing uh some of things uh so if you write here abc and click create look we still have write some validations but we are still missing we we we don't have implemented custom view so if you click here in the edit and you write here 10 id you you know we don't have any imply with the 10 id look no no web page for the phone for this because we we already have written http not found here we haven't supplied any custom views for this so if you write here a bad request here abc and look http 400 error bad request so if you want to see or want to learn how to handle these custom views custom error views how to handle exceptions and how to display custom views you can let me know in the comment section i will be reading your comment and if you are interested in these things i will extend this application instead of wasting again and again a new time and creating new application new things we can extend this application from here to add those custom error views and if you notice here if i click here on edit action and i edit something click here we are not getting any kind of notifications like things like alertify or toaster or any kind of notification we are not getting any kind of notification not displaying any kind of useful notification to the user so if you are interested to learn notifications how to manage notifications you can let me know in the comment section and one more thing which is the last thing if you want to implement the same thing using some of design patterns such as repository pattern or generic repository or things like specification pattern you're not a work pattern you can let me know in the comment section we can walk through and we can learn all those things with you okay so thank you so much for watching this video if you like this video you can obviously subscribe to my channel share these videos with your friends and family and click like share comment and all the things and more importantly if you like my efforts you can support me by giving me a buy me a coffee gift i'll put the description link in the description so and another important thing if you learn all the basics and advanced concepts of mvc you can watch my eight hours of course tutorial series of asp.net mvc i put that link in the description and we are creating an e-commerce application in dotnet 5 which is the separate series so you can if you you want to learn a project based tutorial you can watch that tutorial i'll put that link in the description box okay so thank you so much for watching i'll see you in the next video bye

Channel: Inspire Programmer

Views: 2,722

Rating: 5 out of 5

Keywords: crud operations in asp.net core mvc (.net 5) | create | read | update | delete | full crud tutorial, crud operations, crud operations using asp.net core mvc, asp.net core mvc crud tutorial, crud asp.net core mvc and sql, C# insert asp.net core mvc, C# update asp.net core mvc, C# delete asp.net core mvc, C# select asp.net core mvc, C# search asp.net core mvc, C# reset asp.net core mvc, insert asp.net core mvc, update asp.net core mvc, delete asp.net core mvc

Id: GPTQUpFwYGQ

Length: 62min 16sec (3736 seconds)

Published: Wed Jul 21 2021