Asp.Net MVC and Dapper ORM - CRUD Operation Using SQL Server Database

what's up YouTube welcome to dotnet mob this is a video on CC shaft tutorial in this video we are going to implement code operations using the upper a-arm in asp.net MVC application before starting this video I would like to ask you a favor if you found this video helpful please thumbs up this video and for more hours of videos like this please please subscribe to this channel god netbook as you know data is a micro IRM specially made for the better performance during the DB interaction and it is developed by stack over 40 without further ado let's get started this is my wishes to do 2013 first of all we have to create as p.net MVC application for that click on new project here or go to file then you then project you can use the shortcut ctrl shift and click on that select web under wishes C sharp then select asp.net web application and name your project here I will name it as DAP Oh MVC cool then select the location where you want to save this application then click on OK select MVC template key other options as it is click on OK so here we have a brand new asp.net MVC application open the solution Explorer first of all I am going to delete unwanted controllers and related views and model classes selecting our account and home controller right click then delete click on OK then modern glass account and identity model right click then delete click on ok then views selecting account and home folder right click then delete click on OK now we have to add a pour oil into the application for that right click on project then click on manage and you get packages then search for therefore here so here we have the oil a high-performance micro I'll click on install so here we have installed a PO into this application so you can see the depo reference here before continuing with this project we have to create database for this application let me switch to my management studio here first of all we have to create a database I click on databases a new database I will name this database as dapper DB click on OK inside the DB we have to create a table so right click on tablets the new table first column employee ID as in the job then we have name as work 50 then we have position as Walker 50 then we have a leech as integer finally we have salary as integer now I want to set this employee ID as the primary key and I want to set the same employee ID column as the edited specification for that go to column properties then expand this identity specification here then set is identity as yes so we don't want to insert values into employee ID ask yourself will take care of that it will start from 1 and incremented by 1 upon you record interest in order to save this table use the shortcut control s or you can click on this save icon here and I will name this table as employee so here we have the newly created mm glowing so we are going to do group operations on this table using stored procedures so we have to create few stored procedures for this table that we can create later now back to the we should studio now we need to add model class for this employee table for that right click on models then add class I will name this class as employee model click on add now we have to add properties for each of these columns inside embryo in tab so I am going to start this employee ID here my ID the name then we have position it's of the type string then we have age it is of the type integer finally we have salary salary so here we have the employee model class we will be using this model class for DB operations now we are going to create a special class in order interact with database using stored procedure so that right click on models then add class I will name this class as slap or atom click on add this is a static class static inside that I am going to create a string variable in autoStore character string private static string connection string for now I will copy paste the connection string for this database type or KP here now we are going to create three static functions in order to interact with database three situations first one public static static void so this function does not return anything I will name this function as execute with Lau we have two parameters for this function string procedure name second one dynamic parameters dynamic parameters for that we have to import this namespace demo pattern so this will be this trip passenger name so inside this dynamic parameter object we will pass the parameters and correspondingly now we have to import the namespace for SQL client using system dot data dot SQL client let me give the editor zoom level so that you can see what I am typing here I am going to declare a using statement here using inside that sq allocation object the scale connection as SQL corn please be equal to you SQL connection inside that we have to pass the culture string first of all I will open this connection here a scale called dot offer now in order to execute the stored procedure we have to call this function execute SQL corn dot execute first parameter will be the procedure name itself procedure name then we will pass the parameters after we have to set the command type as command type for that we have to import this namespace system data dot store procedure so this function will be called when there is no data to be returned from the database now we have to create the next function so that let me copy this function from here and basing here with this function we have to execute this route to CD and after we want to retain a scalar variable so I will name this function as execute Stella so I'm going to make this function as a general function so I want to add the key variable here and we will return the key data type here if you hover on this execute scalar function you can see that this function will return object we want to convert this object into this type so I will call this function convert each type can would change type and we have to pass the type here type of T and we want to cast that into T and we will turn this here return so basically this function is used to execute a short o sigdi and we want to return a scanner variable if you have a stored procedure that will return an integer variable then we can call this function pepper or M God execute scalar then we will pass the type here in the job then we will pass the circus video name here then we will pass the atom object here that's it so this function will execute this sort of seizure and it will return the integer datatype now we have to write one more function for that let me copy this function from here and be secure with this function we will return that us from astrology as a list so I will name this function as a list so I will make this function as a general function so that I will add the T type here and we want to put it int wearables collections T so P type T in order to execute this function we will call this function query here if you hover on this function you can see that it returns I am numerable collection you want to convert that into key types so we will pass the key here finally return the collection here suppose if we have a surface in here - Bertram or OPM buoys from the tab so we can call this function like this damper or our m dot return list then we will pass the employee monocalcium M going model so this function will return ienumerable collection of this type M go here now let's look how we can call this function impact again first of all we have to create foster procedures or that selected database then click on new query here then we can start like this create draws first or procedure name will be employee add or edit so with this your procedure we will do two operations insert and update first of all we have employee ID link employee ID it's of the type in the jump then we have name as what can 50 like a 50 now we have position position softly type were careful P then we have age as in the job finally we have salary salary as integer so store procedure will be like this first of all we will do the insert operation if employee ID is equal to 0 then we will do the insert operation insert into MD now we have to list the column names here name we don't want to insert values into employ ad since it is a identity specified column then we have position then age and salary values let me copy this from here and patient here and adding the activate symbol here if employee ID is not equal to zero then we will go for update operation so we can do that here update employee set let me copy this from here and paste in here name is equal to activate name then position is equal to add the rate position then we have age at T wait H finally we have salary salary is equal to activate salary employee IDs ID is equal to the even IP so with this purpose today we are going to do two operation insert and update some of you may create different services for in save and update if you can convince them like this you can reduce force in store procedure as well as efficient in order to execute this whole procedure click on this execute button here so here we have created the surface eg employee add or edit now we have to create three most all procedures next store passes it will be employee view for we don't have any parameter for this purpose feature and query will be like this select star from employee click on this execute one cont here next we have sir procedure for employee moved by ID we have a parameter for the sub procedure employee ID so the type integer then query will be like lift select star from employee work employee ID is equal to kill one PI D may copy this and paste in here not execute this click on execute button one more store procedure for delete employee delete by ID we have a parameter for this Rufus injure at employee ID has ended then query will be like this delete from Kimberly well employ ID is equal to given I do click on execute so here we have created for store procedure for the cocoa operation greatest or procedures can be seen under programmability then store procedure so in total we have created foster procedure and we add or edit employee delete by I TM go awol am going u by ID now back to vicious to you now we can create employee controller for that pi click on controllers then click on add then controller i will select MVC 5 controller empty click on add i will name this controller as employee controller click on add now I want to set this employ controller as the default controller so let me copy this employee from here and we can we paste it here inside hook config file replace home with employee now back to the employee controller here we have the default index action with this in this action we will list all of the employees inside the employee tab so we want to create the view for this index action so right click here then AdView keep review name as it is indexed then select lift tablet so we are going to list employees from the employee tab and we want to select the model class as employee model here I am going model and we want to use the default layout page click on add so here we have the index view it is created using scape for the mechanism at the top of the video here you can see be more die declaration we have to pass ienumerable collection of employee model class from this index view so I will call this function return list inside the class snap oil so let me copy this class name from here Deb or I are on for that we have to import it in space that / mbc could got models dot return list and we will pass the employee class here employee model then we have to pass the surface is your name from here and it will be m review or MBE view or as a second parameter we have to pass parameters for this purpose eg we don't have any parameters so I will pass null or we can set default pattern bandung Pastner if equal to null and we can remove this note from you we can do this default null value for all of the functions here so here we have passed ienumerable collection of i am going into the index view now let me run this application for that whole controls and press f5 so here we have the industry here you can see the navigation is from the default layout page I am NOT going to change the out page in this index view we want to make few modifications that we can do after insert operation next we are going to do operation so let's implement the inset operations for that I'm going to add to action method here public action results add or edit inside that it will return a beauty view like this we have to create one more action let me copy paste here this one is HTTP GET request and we have a parameter here in ID is equal to zero and we have a parameter for this HTTP POST action lateral so HTTP POST parameter will be employee model itself as annuity so with this get add or edit action method we will return view for insert or update operation for now I will do the insert operation we can discuss the update operation lead for insert operation URL will be like this for slash employee forward slash add or edit for a better operation URL like this /and way forward slash and or edit then ID of the am doing so we can discuss this after insert operation so let's discuss insert operation first of all we have to create a view for this add or edit action method or that right click here and then add view keep the view name as it is echo edit then I will select edit template from here and I will select mana class as my model click on add so here we have the error edit view build the solution now we are going to call this add or edit action method inside the index view here you can see a link for creating new inside index view you can see the code for the and attack here so this will be the text create new after that we have to pass the action method then the control early so in this case action method name will be add or edit then controller will be employed itself so we don't want to pass the controller name here now build this solution now click on this create new so here you can see the form for creating um voice I want to change this add or edit view first of all I am going to remove this add or edit Lambert from here and you want to delete this employee model as well so I will delete this one then instead of this employee ID text box I want to keep it as hidden pin so I am going to remove this employee ID controls from here instead of that I'm going to add HTML hidden field here hidden field for model such that model dogs employee ID okay we keep others as it is saving and reloading the speech it looks fine now after filling this form user will click on the submit button see so it will post data into this action method ad already of the type HTTP POST so values from this phone will be inside this variable e m you want to save these embroid ities into the employee table so for that we are going to use this employee add or edit through procedure so we can do that here first of all we have to create an object of dynamic parameter dynamic parameters or exactly have to import this namespace Napa as along difficulty newald dynamic parameters and we can pass parameters values into this object pattern dot add first of all we will pass the parameter we for add or edit store procedure in total we have five parameters first of all we will pass the value for this employee ID here employee ID and we will pass the values from 3 MP toward employee ID like this we have to pass values for remaining 4 parameters so I will copy this 9 and placing the load at 4 times and changing the nature now we can pass values for remaining parameters from respective properties name the position then is the salary so here we have added parameters and values into this dynamic parameter object path now we can execute the store procedure I'm going add or edit for that we can do this Stamper or Adam dot execute without return first of all we will pass ensure precision in here lambda we add for Eddie second parameter will give this atom itself so this thing in say um going into M blow it up finally we want to return into index action in order to see all of the employees so we can induce certain redirect to action we'll build this solution and we load this page now I am going to fill this form games suite software engineer then page will be to default and salary click on save so here we have added new employee phone so here we have added new record for Dave Smith let me check the database tab I click on the table employee then select table thousand rows so here you can see the record for games with now I want to edit this index view for that go to index view I want to change this index title as stem blowy list and let me get rid of this employee ID column for that we can avoid the first column from here th and corresponding KD element let me save this and reload this page next we are going to do edit operation let me take the last column of this table you don't need this detail hyperlink let me remove that and we have edit hyperlink here we have the edit hyperlink text will be edit then action name will be asked for edit now we need to pass this ID so we can uncomment this ID is equal to item door M go ahead so you can see that we are going to I plate to the employee collection so we can pass this item door employee ID let me save this and reload this page so if you inspect this edit so here you can see you are a as four slash employee for slash ad already one so I didn't read this action method add or edit of the type HTTP kit and we will get the ID inside this variable so when user click on this edit button we want to will turn this form by filling details of the employee James Smith so we want to edit this action method if ID is equal to zero then we will pass as it is as we want to return the view by passing the employee object for this ID so we can do that here daba AR m dot term list employee model and we will pass this opposition name here it will be employee view by ID my view by ID then we have to pass the dynamic parameter object let me put this else part in curly braces now I am going to dynamic parameter object here and having dynamic I am it is as param is equal to new parameters and we want to add a parameter atom dot add for this procedure we have parameter employee ID copying and you can use that here we'll pass the ID here so now we can pass this atom into this method so this will return ienumerable collection you want to return the first element so we need for the function force or before and we convert that it will really model build this solution click on edit so here we have the form filled with details of game speed now we can edit these details after that we can click on the submit button save now I am going to update this game Smith into games George if I click on the Save button will it update in the tower yes it will update let me check that click on save here so here you can see that James Smith is updated to change dots it will work because inside the add or edit view here we have a hidden field for my IP for insert operation this hidden field value will be 0 for update operation this hidden field value will be equal to selected employee ID inside the action add or edit we will pass the values here inside the app or edit store procedure we will check where the ID is equal to 0 or not if it is equal to 0 then we will do insert operation if it is not equal to 0 then we do the upgrade operation for the d1 ID so that is what happened here next we are going to do delete operation let me check this delete hyperlink inside the index view so here we have the action link action text will be delete then delete action method and we want to pass the ID of M go here M GUI ID let me create the action method delete inside I'm going controller here so action method be like this public action Rascals I can name will be village and we have a parameter inch I did so this ID is passed as employee ID from the index video first of all I am going to create an object of dynamic parameter like this and we want to call this for procedure babba or our m dot execute without orders store procedure name will be like this employee delete by ID and we have the parameters like this and we have to pass the parameters like this theorem so this will delete the employee with the given ID and finally we will redirect to index action let me copy this and paste it here build this solution let me add one more employee into this table for that click on this create new button here click on save so here we have add a record for free on a green in order to style alternative roles inside this table I am going to add one more class here double then tablet strip saving and pay loading this page so that's it now we want to delete this record for Fiona green for that click on delete so it is deleted the code for Fiona so in this tutorial we have discussed how to implement crew operation using diaper ORM and SQL Server slow procedure if you found this video helpful please thumbs up this video and for more videos like this please please subscribe to this channel door that move you can download this point a source code from the link given below in video description please like and share this video with your friends and colleagues let me know your comments and feedbacks in the comment box below have a nice day bye

Channel: CodAffection

Views: 49,815

Keywords: dapper c# tutorial, crud operation in dapper, dapper in asp.net mvc, dapper ORM tutorial, Dapper Mvc CRUD, add dapper to asp.net mvc, install dapper in asp.net mvc, using stored procedure in dapper, using SQl server database, Dapper CRUD - Insert Update Delete VIew All, Asp.Net CRUD Operation using Dapper, dapper crud example, dapper.net, micro dapper in c#, CodAffection

Id: bKB0TV_XbLA

Length: 33min 42sec (2022 seconds)

Published: Wed Aug 09 2017