ASP.net Core Using ADO.net

hello all welcome to digital tech join in this video i'm going to show you how to use ado.net to connect to your database and perform database operation in this demo i'm going to use microsoft sql server database and use aru.net instead of entity framework so let's get started create new project click on asp.net core web application select asp.net core web application click next enter the project name and written as ado.net core select the location where you want to save it click on create select asp.net core web app model view controller for now untick https and click create while the project is getting set up i will show you the database i have created a learn sql database in my previous session where i discussed and taught about sql i would use this database this database has the following tables we are going to work with the customer table having few records in this database i am going to create procedure which will be called by the application so let's go ahead and create the required procedure so we are going to create two procedures one which should fetch all the records from the customer table and the second procedure we should take input parameters as a customer id and display the only the selected customer id information okay so a procedure get all customers select all from customers slots select learn expert and execute the question so the procedure is created if you want to test the procedure you can do it here simply right click on the procedure and say execute procedure click ok so you see all the records are displayed now we'll create another procedure which will take the parameter let's copy this create procedure get get customer details by id and now this takes a parameter as customer id as in now here you say select all from customers where customer id goes to customer id even this is executed let's execute and check execute it pass the value as one and you can see display so we have created both the procedure now let's go to our project our project is loaded the first thing we need to do is that add the library so go to manage nuget packages search for system data select system.data.sqlclient because our database is microsoft sql server database and click on install i accept yes done so that library is installed you can see it is available over here now we'll go ahead and enter the connection it is in the app dot app setting dot json file so it's a connection strings and already had the connections saved over here copy this and we pasted it over here so this is a connection string and this is a key name for the connector string default connection and we are connecting to our database learn sql so connection string are set up now we will create the model class so click go to the models folder add a class call customers and add the properties so we have id customer id then drop test customer name email address and mobile and mobile this is a primary key so we need to reference the class fine so our data model is ready now we create a data layer and we are going to use the idio.net instead of entity framework so let's create a folder called data layer and add a class customer dan now here what we are going to do is that you will be fetching the connection string so let's create a variable called public string connection this will hold the connection information now let's create a constructor where we will okay so this constructor what this constructor will do is pull in the connection string from the app setting dot json file and assign assign it to the variable cnn so let's do that so we need to reference the extension class we'll just microsoft using microsoft extension configuration because we are dealing with the configuration file so we need to reference that library it is already done it's over here now set b spark directory dot get current directory here i think so we need to refer system.io file yes dot json file and file name is app settings dot json and dot build so now this will this this builder variable as the instance of the app settings rotation file now we can go ahead and access that so we say cnn and we pull in the connection string so builder dot gets section and we say connection strings colon default connection dot value so connection string and deforester as you see connection string and then different connection similarly we mentioned with the connection string so now we have the connection string ready now let's go ahead and create a method to pull all the customer information as i mentioned using adio.net for that the first thing we need to do is refer the using system dot data dot sql client so we create a method as public list we call the customers object model we refer data dot model yes models class and then we say get all customers now let's write the code so list customers list of customers equation will return this subject list object once you fetch the data from the database let's use a code using sql connection cn equals to new sql connection and pass in the connection string then use a command object sql command cmd cursor new sql command and here we specify the procedure name so what was the procedure name it was it is get all customers just copy this use this over here and pass the connection string so first we define the connection to database then we define the command object and we tell the command object to call the get all customers by passing the connection string now we check the connection string is open as a good practice c n dot state state is closed if it is close cn dot open now we execute the execute the command and put all the data into the reader class so we say i data you don't require this just i data reader reader cmd dot execute reader now this will execute the database procedure over here and now pass in all the data to the reader class now we need to loop in and create the since it is returned in data set format we need to list format we require a list format so we need to build up the list objects okay so we use while reader dot trend list of customers dot add new customers and we are the semiconductor and now here we'll pass the data we'll do the binding over here customer name and then email and mobile file so this is building up the list object once the data is set up and all the data is pulled from the reader class to the list object we return this variable back to the calling method return clear files our code is ready so this is the code you can do is you can go ahead and check the functionality by going to the home controller going to the index so in the index what are we going to do we're going to first reference data length sorry we will pull in the necessary references dial references data layer and we say list customers customer list and customer list equals to underscore customer get all customers and then we are going to return the list object to the view now let's go ahead in the view and set up the data binding so he has default so i have already saved this this year we are reference we are referring the model class that is customer and this is our customers page and here we are we have placed two buttons one to search and want to pull all the information and then we are binding the data by iterating the model so we are showing customer id name or by an email address let's go ahead and see how the function works in actions let's put a day point fight so we'll step through each code so the procedure is called over here i guess our connection listing is also retrieved from the database now this method calls get all customers so first it will create the connection the connection is created it is calling the command object and passing the stored procedure with the connection string connective string is opened successfully without any error and then finally the command object gets executed now here it loops through all the record yes and then click on next yes so you can see we were able to pull in all the information from the database now let's add a functionality of searching the customer based on the id now in this process what we are going to do is that we are already create a procedure which takes input as customer id we are going to show you how to pass parameters using ado.net so let's go ahead into the dial layer now we will copy the same method but here i'll show you how do you pass the parameters so i copy this change the method name to get customer by id and then pass it in customer id okay and this all remains the same you have to change the procedure name so now this becomes get customer details by id over here before we proceed what we need to do is that we need to pass the parameters because this procedure over here expects a input parameter customers so how do we do that so we simply say command object dot parameters dot add and we say customer id which is defined over here this one customer id and we tell sql we tell idio.net that is this variable type of film second we pass the value which we got it in the variable customer id so we see parameters and customer id dot value equals to customer id customer id which is passed in a parameter over here same we pass it to this value and finally the most important thing we say command type first to system command type is command type dot store procedure that's it we need to only pass this variable parameter variable and the value rest code remains the same now let's use it in our own class go to the home class and create a method post method say so this is a post method and this will say get search customer and pass in the customer id customer id now this all code remains the same only thing we have to change this get customer id and pass in this variable and then pass the view to the class now this is only change and in the in our html we have already placed two buttons one was for searching and second button was to fetch all the records now if you notice there are two buttons both the type of submit however if you see in one of the submit button i have added the form action so this button fetches all the information that is by form action equals to index because in our own controller the method that fetches all the record is index default index so here i i mentioned form action as index however in this case i have not mentioned the formation because by default the forms get submitted to the search customer method so when you click on this method it will take the input parameters and call this method to search the customer based on record let's go ahead and check a functionality so this is called this is calling all the method by default when the page is loaded fine now let's type in three which is richard and click on search so here you can see three number it passed now let's go ahead to the main method in a data layer now see if you notice customer id was passed this is the parameter we say it's a end parameter we pass in the customer id and pass in the value command time is procedure open the connection execute the query and we got the result search customer okay okay but i think search customers and in the index post method okay okay so there is so sorry so i am supposed to redirect it to index and then pass in the object so that was a mistake it was searching for a view as search customers but we are supposed to show the result in the same page where we are showing all the records so i am supposed to pass return view index because index is our default page and then the customer list object so now this should work so it was all the record and searching three search now i remove the breakpoint this time yes you are able to search and then click on show all you'll be able to see all the records so guys in this video i will demonstrate how to create procedure in microsoft exclusive database how to call the procedure in asp.net core and we'll see using ado.net how to call the procedure by passing the parameters using adio.net so guys i hope you like this video please do subscribe my channel and like this video thanks a lot

Channel: Digital TECHJOINT

Views: 259

Keywords: asp.net, asp.net core, .net 5, asp.net core mvc, asp.net mvc, learn ADO.net, ADO.net, Learn Asp.net, Learn ASP.net mvc, MSSQL, Microsoft SQL Server Database, appSettingJSON, create Procedure, Learn SQL procedure, learn SQL Procedure, asp.net core using ADO.net, asp.net ADO.net, ADONET, Visual Studio 2019, MS Visual Studio 2019, DotNet Framework 5, Asp.net CRUD, Database using MSSQL, Microsoft Technology, Learn ASP.net, mvc ado.net, windows, learn dotnet, connection string

Id: ox3x3JQHCgo

Length: 25min 51sec (1551 seconds)

Published: Sun Nov 07 2021