CRUD Operations Using ASP.NET Core And ADO.NET | With SQL Stored Procedures | .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 cloud 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'm 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 crud 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 am mentioned schema name here I am defining as USB it is a user defined 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 date of birth 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 get by ID I am going to copy it get employee by ID here we have to pass 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 the store procedure execute it it's created then we have to create another store plus here to update the data this is going to be update here along with this first name last name data but 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 br2 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 proc here 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 I wait 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 I'm 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 you 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 the 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 on 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 control here we'll 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 surround with try catch block so if there is any error we have to display to user for that I will 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 add I am 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 this name space we used with previous sessions example that is credit with code first here we have to change that that will be occurred 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 we 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 returning 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 so like this we can read the data from database by using ado.net in asp.net core MVC in next session we are going to insert the data into database by using stored procedure that's it for today thanks for watching foreign

Channel: CodeWithGopi

Views: 8,700

Keywords: .net core 6.0, .net core ado, ado, ado dotnet, ado net, ado net 6, ado stored procedure, adonet, asp ado net, asp mvc, asp mvc .net 6 ado .net, asp.net ado.net, asp.net core, asp.net core mvc, asp.net core tutorial, asp.net core using ado.net, asp.net crud, asp.net mvc, asp.net tutorial, c#, c# ado net, crud ado net, learn ado.net, mvc 6, mvc ado.net, mvc aspnet, mvc tutorial, net 6, visual studio 2022, .net core, asp.net, asp.net core 6.0, mvc, entity framework core

Id: M1d-DdD067s

Length: 37min 15sec (2235 seconds)

Published: Mon Jan 16 2023