Full CRUD Operations Using ASP.NET Core And ADO.NET | CRUD with SQL Stored Procedure | .Net 7.0

hi friends welcome to my channel in today's session we are going to discuss about crowd operations in asp.net core MVC by using ado.net here we are not going to use Entity framework we are going to use stored processor to perform the crowd operations so this is the previous example where we have performed crud operations by using Entity framework core code first approach so the same example we are going to perform crud operations by using stored procedures before continuing with this session I would request you all please subscribe to my channel and click on Bell icon for notification alerts I'm going to use Visual Studio 2022 for this example and database I am going to use SQL Server 2019 I am going to create new project for this I am selecting asp.net core web application with model view controller we are going to create MVC project here so this is my project name I'm going to store in this location click on next I am going to use latest framework here I am going to select https if you want you can uncheck so that you can use the HTTP protocol so our project has been created here so I have multiple project under same solution I am going to set this project as start a project right click on this project set as dot a project and here I am going to create one model this is my employee model I am creating here here we have to declare our properties before that we will create table for employee in our SQL Server this is my database so here I'm going to create new database this is my database name click on OK so my database has been created here we will create a new table click on new query so this is the table I am going to create it this is my table name here I am taking IDs primary key and it will be identity first name last name date of birth and email salary so we are going to create employees table with these fields ID will be primary key so we now need to pass any data here because its identity automatically it will increment with one execute here so our table is created to perform crowd operations our model should match with these fields here I am creating properties ID this will be primary key so we have to import namespace here first name so these are all the fields we have created in our database first name will be mandatory for this we have to decorate with required field so that we can validate at the time of form submission last name also mandatory and date of birth also I want to change display name here while displaying in the view it will be displayed like first name same thing I am going to copy it here this will be last name here while displaying we are not going to display first name and last name both fields we are going to join both first name and last name we will display in the index page for that I will create one more property this will be string this will be full name and here I am going to join first name and last name it will return first name here we will add on space then last name and also we no need to map these fields otherwise we will get the model validation errors for that we have to decorate with not mapped now we have to create a connection string for this open app settings.json already in our previous example we use the connection string app settings.json you can copy it from here here we have to change our database so this is our database name in previous example we have created all these things how to create connection string and how to read the connection string handle if you are new to my Channel please watch my previous video video link will be shared in the description now to perform the crowd operations we have to create stored procedures click on new query first we will create a stored procedure to read our employees create here I mentioned schema name here I am defining as USB it is a user different stored procedure here we have to write our select query select start from dbo Dot employees with Noda here instead of specifying all we will specify ID first name last name data but so instead of specifying all here we can specify required field names this will be the best practice in SQL Server you can execute now what store procedure is created here you can select the stored procedure name and click on execute currently we don't have data that's why the data is not displayed here we are going to create one more stored procedure to read the data based on ID read I'll make it as gate by ID I am going to copy it get employee by ID here we have to pause where condition where ID equal to here we have to pass one parameter I'll declare parameter here and delete ID data type will be integer here we have to declare the data type this is the input parameter we are expecting from the user and here we have to pass that ID so select this and execute it so one more store procedure is created here and we need another stored procedure to insert the data insert here I am going to create I'll copy the same thing this will be insert employee here we need parameter like first name we can copy it here first name last name date of birth and here we have to declare data types it will be worker of 50. this length should match with our table structure you can check it here of 50 and last name also it's 50 and date of birth and data type and length of the data type should match with our table last name also 50 date of birth is date and email is 50 characters and salaries float so now we can insert insert into so this is our table name you have to pause all field names which we need to insert and values we have to posit here values I'll copy it from here this order should match first field will be first name then it should be first name here last name date of birth email and salary it should match with the columns which we mentioned in the insert query these are all the columns these are all the values which we are expecting from the user select this store procedure execute it is created and then we have to create another store crochet to update the data this is going to be update here along with this first name last name date of birth email and salary we need another input ID So based on ID only we are going to update the row ID this data type will be integer so before updating we have to check whether that ID is valid or not for that I'll declare one variable here integer I'll set the value as 0 first I have to check whether the data is available with that ID or not for that I'll write a select query here select here we have to pause ID whether this ID is valid or not we have to check for that we are writing the select query where we are validating this ID so if any data is available we will get one here otherwise we will get the zero so if it is 1 then only we will update the data otherwise we will not update it and one more thing we have to add here is we have to check is there any error in the query for that I will try begin try and I'll add it as entry so like in C sharp how we will write try catch here SQL also we can use the try catch here here I have to write begin catch and end catch here I can roll back the transaction if there is any error suppose if draw count is greater than 0 that means this ID is valid then I have to update the record begin transaction commit transaction here we have to write our update query update employees set here we have to update the values I'll copy these things here and we have to pause where condition here where ID equal to the rate ID here we have to pause comma yes like this in insert query also we have to check is there any error for that begin try and it's going to end here and try bacon catch and and catch if there is any error then roll back the transaction begin transaction and after inserting we have to commit the transaction so we have to alter this execute it now we have to create update one execute it and finally we have to write another stored processor for deletion I'll write it here delete here we are going to pause only ID to delete the record so here also I am going to check row count if row count is greater than 0 then only I am going to delete the data delete from table where ID is passed by the user execute all our stored procedures are ready now now here I am going to create one folder for data access layer data access layer I'll create a class for data access I will make it as employee Del firstly here I need SQL connection for this we have to import the namespace and you can install the latest version I need SQL command also I am declaring SQL connection and SQL command here and I need one property for configuration to read the connection string here we are going to create a method to read the connection string private it will return the string get connection string where Builder configuration Builder from App settings.json we are going to read the connection string for that I am creating baseboard here which will be directory Dot get current directory from here we have to pass the Json file add Json file and copying this name and passing this we have to build it here to read the connection string now here we can get configuration Dot get connection string here we have to pass connection string name this is our connection string name here we will get connection string here I am going to create one method to read the employees which will return list of employees here I am going to pass employee get all so it will return all the employees here I am declaring list of employees it will be employee list here we have to pass connection we have to pass the connection string you have to pause command now create command you have to Define command type here then we have to Define command text so in our case our stored procedure name we have to pause it here to read all employees this is the stored procedure we are using you have to paste it here this no need of system.data now we have to open the connection here we have to import namespace system.data now we have to read the SQL data for that we will use SQL data reader so if there is any data is available then it will comes into our while loop and here we are going to read all employees one by one and we will attach to our list of employees here we will get employee ID will be ID first name last name date of birth email and finally salary also here we have to convert so because this ID is integer so we have to convert into integer for that convert I'm converting into integer and string I will convert into string this is also string and email also string this I am going to convert as date time and from this we need only date and this one will be float we have to convert it double so finally we have to attach this to employee list we have to add add pause the employee finally we have to return employee list here after reading the data we have to close the connection here we have to pause command dot executed not this one command dot execute Trader okay we will create a controller to read the data go to controllers create a controller here I am going to create one empty controller here add I'll name it as employee controller so this is my employee controller call employee dial which is going to be data access layer and I can make it as private read only and here I will generate the Constructor for this now here I am going to read the data from data access layer so by default this is going to be get if you want you can decorate with http get here it will return list of employees for the time making as employee list here get all here I am going to make it I'll keep this inside try catch block so that if there is any unhandled exceptions are there we can find it easily so round with try catch block so if there is any error we have to display to use a further I'll store it in temp data this is going to be error message and pausing exception Dot message here if the data is available then we have to pause employees to view I'll create a view here right click on the action method add View click on razor view empty and I'm going to make it as index here in this session we are not going to design any of the view because last session we already explained how to create views manually step by step if you are new to my Channel please watch my previous video link will be shared in the description this is the view which we used in the previous session I am going to change the model name alone employee and nothing here here if there is any error message we are storing in the temp data like which we are shown here and if there is any error message we are going to read it here so if it is not null then we are going to display in the index page like that if there is any success message if you want to display here we can read here and we can display in the index page so this is what we explained step by step in the previous session if you want you can watch that video that will be shared in the video description so this is simple table created these are all the table header and this is the table body also here we can validate model is not equal to null and model dot any so if model contains any data then only we have to display it here so if there is no data is no employees available at this moment this message will be displayed in the table build the application once so there is one error here so here this name space we used with previous sessions example that is crud with code first here we have to change that that will be called with ado.net that was the error here build the application so builds succeeded here I am going to change it to IIs run the application here we have to call our controller that is employee controller enter it so here there is a error so while attempting to activate our employee dial there is error here we missed one thing here we have to add employee dial to our program dot CS we have to register it here Builder Dot services here we have to pass our employee dial run the application now here we have to add namespace dull we will add employee Master to navigation menu for that I'll go to shade folder layout here we will add one nav item say employee controller action will be index this name will be employee master so I will click on employee master it's coming to my index action method you will keep one debug Point here also in our data access layer here we are able to read our connection string currently we don't have any data that's why it's not written in any employees so there is no employees that's why we are displaying messages like no employees available at this moment we will try to insert some dummy entries from backend for testing purpose we are entering few the main Trace from database so ID will be identity column and primary key we now need to insert any values here first name I will make it as David I am saving here we'll go to application this time I will cancel early debug points I'll disable it and click on employee must continue now we are able to read the data from the database here I am going to create one action method to insert the data oblique it is going to be create and here first we have to design the viewport for here it will be empty View right click add View create a empty razor View this is going to be create the same example we are going to do it with ado.net that's why I am not going to explain about this view part view part already we explained in the code first approach session here I am going to change my project name it is going to be edible.net and my model will be employee model so this is only one change remaining things we no need to change because we are going to use same things here here we have to create another action method for CE the data here it will receive the employee model I'll make it as model here from controller to data access layer we have to pass the model for that we have to create one method public inset this is going to be method to inside the data so here we will receive the data as model here if it is successfully inserted we will written as two otherwise we will written as false using here we have to pause connection you have to get the connection string here we have to pause command type so this is our stored procedure name to inside the data I'm copying from here here we have to pause parameters along with values first parameter will be first name which will be received from which we can receive it from model DOT first name last name then date of birth this is not required finally we have to open the connection to perform the insert operation execute non-query and once it is executed we have to close the connection so finally we have to return if it is successfully inserted we have to written as true otherwise we have to written as false for that I'll declare a local variable here which is ID 0 so I am going to store the ID here as once it is Data is successfully inserted we will get as one otherwise we will get as 0. here we have two writtenly Boolean value true or false for that if ID is greater than 0 then I am going to make it as true otherwise it will be false so this is I'm going to return so now we can call this from our controller go to the controller from here we can call our insert method so before inserting the data we have to check the model state is valid or not if model state is valid first we will check if it is not valid then we have to display the error message to the user model data is invalid so this is the message we will display to the user else we have to insert the data so which will be returning true or false we will store it in result Del dot insert here we have to pass model suppose if it is not inserted then we will return as false in that case we have to return message as error message unable to save the data this is the message I'm going to display and I will return to this same view else I can save it in temp data that is Success message employee details saved this is a message we are going to display if it is inserted successfully so finally I am going to return to if it is successfully inserted then I am going to redirect into index View now run the application click on create employee button so this is the view for creating a new employee so without entering any data we will try to submit it so all the validations are working fine I'm submitting here so employee details saved this bootstrap alert we have implemented in the previous sessions also we have implemented after seconds they should close automatically that is not working because we have to implement our JavaScript functionality here if you close it here it will be closed for that you have to call one JavaScript function which we have to implement it here in previous example we have implemented in the code first approach so I am going to take it from that so this is the custom JS we used I'll copy it from here and I can paste it inside our ado.net project I will paste it here we set the timeout for five seconds after 5 Seconds this alert will be closed automatically this we have to add it inside the shared layout folder we have to go here and you can drag it you can drop it here you will run the application again now this alert should close automatically after five seconds yeah it's closing and it's working fine we will create another employee so this message will be displayed and after five seconds it will close automatically and if you want you can close manually also in previous session we have missed you to add the try catch block to the create action method for that I am going to select entire code here right click go to the snippet surround width here I am going to add X and I am going to add this exception message here so if there is any exception it is going to return to the same view which is create View to update the data we have to create two action methods one is to get the data based on the ID another one is post action method to update the data first we will create a get action method to get the data based on ID this is going to be edit and I will pause ID here to read the employee data based on ID we have to create one method for reading the data based on the ID here we have to create one method to read the data based on the ID I'm going to copy this method I'm going to register here this will be get by ID this will return only single employee so we don't want list here it should be employee So based on ID we will get only one employee here we have to pass the stored procedure to read the data based on the ID for this we have created all the stored procedure in previous sessions so we have to copy the stored procedure name this will return employee details based on the ID I replace here and we have to pass one parameter here that is ID add width value this is the parameter name and value will be ID here it will give the employee details and this is not required here here it is going to return the employee here we are going to pass the employee ID to the stored procedure based on this ID it will filter the data and it will return the employee details here and finally we are going to return the employee here now we can call this from our controller employee get by ID and here it will expect ID here we have to validate whether employee data is available or not if employee dot ID is 0 that means there is no data in the database with that ID then we have to pass the message as error here we can Define the ID also ID so if there is no data with the ID which is passed by the user then we have to display the message like employee details not phone with ID which is passed by the user so this is going to be returned to the index action method else it will return to edit view with the employee details we have to keep entire things in try catch block if there is any exception we have to display this to user also we have to create another action method for post I'm going to copy this this will be post and here we have to pause the employee model these things we have to change it suppose modal state is not valid we have to validate that then we have to display this message to the user already we used here model data is invalid we will use the same thing here and it will be returned to the View yeah else we have to pass this data to data access layer for that we have to create one action method for update so we can reuse this insert method copying here you can paste it and I will make it as update so we have to pause stored procedure name here so this is the stored procedure name which we used for update so in previous sessions we have created all these stored procedures if you are new to my Channel please watch my previous videos and please subscribe to my channel also we have to pause one more parameter here called ID to update the data so this is the update method which is going to return bull so here we are passing the update stored procedure and along with all the input parameters so once it is executed we will get true or false So based on that we are going to display the message to the user here result here we have to call the dial dot update method and we have to pass the model so that here we will receive true or false if false then we have to display the message like unable to update the data already we used for create same thing we can reuse it here unable to update the data if it is Success then employee details updated so these are all the validations we are going to implement it here here we have to remove this block so if result is false we are going to display this message like unable to update the data if it is Success that means if it is result is true then we are going to display as employee details updated and finally once it is updated then we are going to redirect to index View now run the application so it is displaying all the details in the edit here I'm going to change mic and date of birth I'll make it as 1987 and salary I will make it as 66 000. update it so employee details updated you can see this details has been changed here so edit functionality is working as we expected first we will create a method to delete the data from database for that I am going to reuse update method before continuing this session please watch my previous video which we explained about ado.net and how to read the data in set update data under we have explained in the previous sessions please watch previous videos first so this I am making as delete method and here with no need of passing entire employee model because we are going to pass ID parameter to delete the data from database so we can remove all these parameters since we are going to pass only ID parameter as a input so we are going to delete remaining parameters here I am going to pause only ID to delete the data once this query is executed we will get number of rows affected that is 1 in our case we will get it as 1 then if it is 1 then we are passing as true that is record is deleted true otherwise we will send it as false here now we will implement this method in employee controller I'm going to copy these two here because it is similar to update functionality so I'm going to copy the edit functionality I'll make it as delete here So based on the ID we are going to get the data from the employee table if ID is 0 then we are going to display the error message as employee details not formed with the ID which is given by the user and we are going to redirect this to index action method that is index View if data is available then we will return to delete View if there is any error we are going to show the error message from the exception so here we are going to create another action method for delete confirmation so once it is confirmed then only we have to delete the data so this is the action method which we are going to use it for view purpose this is action method which we are going to use it to post the data to the data access layer here we will receive the employee model here we now need to validate any model State here I can remove it here here I can check for delete now so here I can pause it ID alone if result is false then we are going to display as unable to delete the data if it is Success then employee details deleted this is the message which we are going to display to the user and if there is any error here then we are going to display in the error message with the temp data so we will create a view for delete now right click on the action method add View here I am going to make it as delete I am going to remove and here the delete design we are going to use it from our previous example which we used for code first approach I'll copy it from here here I have to change the project reference and my model name will be employee so this is the title we are going to display to the form that is delete employee and if there is any error message we are going to store into the variable as error message and we are going to display in the bootstrap alert here so this is simple form which which we are going to use for delete purpose here we no need of this margin bottom I'll run the application so there is some error here okay we can't use already we declared ID here so we can't use it as ID so we will make it as you can make it as deleted row count here after executing this non-query we will get number of rows affected that will be one in our case because we are passing only single ID so that one record will be deleted and we will get it as 1. I'll run it now click on employee master so this record I am going to delete now click on delete So based on the ID it is displaying the employee details here suppose I am making ID as 20. so employee details not found with ID 20. so I will delete again now click on delete so it's redirecting again to delete here we have to change it here we have to change action name also because it is here we have provided as delete on post it's redirecting to delete so we have to change the action name also here it's going to be delete run the application again first we will check the employee details which available in the database first So currently we have two records we are going to delete the record which is having id2 go to the employee master click on delete we have disabled all the values here we now need to edit the data here that's why we disabled all the controls click on delete so here we have not changed the stored procedure name because this is again I will click it here so this is still exist as update employee we have to change the stored procedure name here go to the data access layer here we have to update our stored procedure name so this is stored procedure name which we used for delete the record I'm going to paste it here I'm going to delete this record now click on delete so this is the record ID is one so this is the record which we have id1 click on delete employee details deleted currently we don't have any employees here we can create another employee click on submit so employee details saved again we will try to delete this record click on delete this is record which we are going to delete click on delete employee details deleted we'll check the DB we don't have any records here so like this we can perform the crude operations in s plotted core by using ado.net that's it for today if you like this video please like And subscribe to my channel thanks for watching

Channel: CodeWithGopi

Views: 51,855

Keywords: .net core, .net core 6.0, .net core ado, Crud, ado dotnet, ado net, ado net 6, ado stored procedure, ado.net, asp ado net, asp mvc, asp mvc .net 6 ado .net, asp.net core, asp.net core 6.0, asp.net core mvc, asp.net core using ado.net, asp.net crud, asp.net mvc, c# ado net, code with gopi, codewithgopi, crud, crud ado net, dotnet, entity framework core, insert data, insert data into table, learn ado.net, mvc, mvc ado.net, mvc aspnet, mvc tutorial, net 6, visual studio 2022

Id: 7OjZf6IyFxU

Length: 65min 59sec (3959 seconds)

Published: Wed Feb 15 2023