.Net core web api crud with mysql | c# data access #1

in this video we will see how we can use my SQL Server database with net core application we will create a net core web API application and we will perform all the card operations and we will use NT framework core as an or in this project if you find this video helpful then please hit the like button and make sure to subscribe this channel to get more videos like this so let's get it started without any delays so let's create a new project click here in the create new project section and here I'm going to search web API and I will select this template asp.net core web API just click on next and here let's change its name so it will be net my SQL EF so we are going to use Entity framework in this project so that's why I'm naming it like this and here I'm just going to change uh location of the project so if you want to change you can you can copy the path of the location and paste it here now it will be our solution name and our project name should be like this and solution name will be like this so because we are going to create one more project in the future that's why I am keeping the solution name and project name different okay so click on the next and here we will have some options which framework we are using right now Net 7 is our latest framework but yeah within a 2 month it will be AET 8 so if you are using net 8 and using this tutorial then it will be okay there should not be too much difference in Net 7 and 8 yeah of course if you are using net 6 then it will also be okay but if you're using uh Net 5 or less than Net 5 then your configuration will be little bit different in the program.cs file and the code will be the same so let's create a project okay it will take a while and and so here we have successfully created our project so inside this solution we have only one project so we are going to create another one right click on the solution and click on add new project and here we will select class Library so it will be just a simple class Library project so click on this one and click on next and our project will be what was its name let's copy this guy it was the name of our solution dot data or data access whatever you want to name it you can keep it so click on next now framework will be the same as the previous project and here now we have created two projects successfully so let's delete this guy and now one thing we have to do here right click on this oops right click on this net MySQL ef. API project and here you will see an ADD and here we'll find somewhere project reference so click on this section project reference this one project reference okay and here you will see the list of project right now we have only one project so we are going to select it so what it will basically do we will reference this project to our API project so then we can use the functionality of this data project so whatever defined public in this the data project you can access it in the API project but you have to pass the reference of this data project so I'm going to reference it okay okay now you can access the functionality of data inside the API project otherwise you are not going to not going to uh what we say access the functionality of data project so how do we know we have successfully referenced our project so double click on this project it will open it CS Pro File or whatever it is and here you will see that item group section and you will see that we have included this project paths net e net MySQL ef. data and here it is the path of this project so and we are using these these packages inside this project so first thing we have to do here just right click on the my SQL EF API and here you'll see manage new get packages and we're going to install package here so here so we will install this package microsoft. Entity framework core. design so make sure you have to install it inside the API project so here install this package click on the install and click on the accept and we will be fine within a few seconds so let's wait for it so here as you can see that we have successfully installed this package so here and you can also check it here that we have successfully installed this package or not so double click on this API project and here you will see that now we should have one more package here so this one yeah so that's it now we do not have to install any packages in this project now we will install few packages on the data project so right click on this data project and click on the manage new get packages and here we will browse a package name here we will use it tools so this package is useful when we are using Entity framework migration commands so if you haven't installed this package you won't be able to use Microsoft sorry yeah identity framework migration commands like add uh add migration or update database so make sure to install this package and install it accept and yeah it is installed here is one more way to install a package you can go here and package inside the tools and now here manage n package manager sorry new get package manager and package manager console and here you can just type install package and you can install any package you want suppose you want to install this package so just type install package and it will be installed inside this project so you can also change the project so if you want to install this package inside the data project so let's make it simple and just install it from here so that you won't be confused I was just saying that you can also install a package from the package manager console so here we are going to install one more package so install this package microsoft. extensions. configuration do abstractions so just install this package and it is successfully installed install one more package this one will be the last one so this one will be my SQL do Entity framework cool and here it's author is Oracle it version is 7.05 plus my SQL 8.10 connector so let's install this guy and it will be our final package so let's wait for it accept it okay now we are good that's it now we have installed our packages so let's double click on this project and you we will see which packages we have installed right now so one package is here microsoft. entityframework code. tools another one is this one microsoft. extension. configuration. abstraction and this one is microsoft. Entity framework code sorry my SQL Entity framework code so this one is for accessing SQL server and this one is for accessing the data inside the app setting. Json so if you want to access data from the app settings. Json you need this package okay so that's it now one more thing we will do here it is very crucial thing we will Define a connection string here so when the app settings. Json file and just typee here connection is string and here it name will be default and database equals to name of our database so let's before before database we need to name a server so our server so our server will be simply Local Host and database so you can name anything to database so let's name it net EF just net EF okay netf DB or demo something like this okay so now our user user ID equals to root and password equals to it is my password okay so it is our connection string now let's close this guy and right click here and create a new folder here and let's name it models create another folder here oops and let's name it repository so so click on this let's name it repo Z trees okay inside the model folder we will create a new class so it will be a person dot CS just remove this junk we do not need it okay I have put a semicolon here and now we are fine so let's make it public okay here we will create few properties so first property will be ID it will be our primary key another one will be uh name and another one will be a email and here I will put some validation logic so it will be uh required okay and this one will also be a required and here just name it table equals to person oops I guess it should be like this okay now we should be fine so here it is the schema of our it is a structure of our table schema now we will create another class here it will be a person context. Cs and it will be our database context class so I'm just going to remove these things we do not need them right now so here now what we will just do here we will create a DB set so it will be uh public DB set person and it should be a people so with the help of this dite we can interact to our database now here we will write protect Ed override on configuring something like this on configuring oops it should be like this void on configuring and here we will pass the DB context option Builder and this one and here it should be like option Builder do use my SQL and here we will Define our connection hisory and the major mistake I have done here I haven't inherited with DB context now we are fine so it is our public class it should be our public class okay protected override white and here we will Define the connection string so how we will get this connection string so first we will create a Constructor so just type here c r enter and it will create a Constructor for us now here we will type private read only and and person sorry should be I configuration okay configuration so it have added this package here microsoft. extensions. configuration one more thing here we will Define another private field here private read only and string should be uh connection string and here we will write High configuration configuration that's it configuration equals to configuration now connection string equals to configuration. get connection a string and key will be default and we have defined this key here here so its name is default okay so whatever name you have choose here you will choose it here also so those those two names should be same now after that what we will do for configuration is completely perfect so here what we will do now go to the program. Cs file and here we going to add it to our di container so Builder do services. transient and it will be a person Contex text okay that's good now here inside the repository class we will create a new class so it will be our person repository click on enter and here we will have public person repository first we will create a Constructor and we will inject our what it was uh person context person context CTX here and just press control and Dot and you will see that we should have few things here okay so it is not working right now so we will do it manually let's remove underscore from here and just copy this guy here private read only context underscore CTX and here CTX equals to CTX so we are injecting our person context to this person repository class and here we will Define few methods so first method or will be public as sync task I enumerable person get people as sync okay so here we will just return return CTX X do people dot to list okay CDX do people. to list and here where people equals to await and it should be to list as sync so for this we have to use this package remove these packages we do not need it so just put a semicolon here okay now we are fine we have less code here and we will simply return people okay so we'll create another M here just going to copy this guy and get people by ID I think in ID and here what we will do uh we will just remove this guy and we'll type here find ID and rather than a people it should be a person or we can just directly return it return AWA people. find asnc it should be like something wait find asnc and ID okay okay error is here it written type should be this now we are fine let's create few more methods here so public as sync task another one will be uh create person or just add person or whatever it is let's name it create person s sync it will it will have a person object as a parameter so here we will just write C [Music] TTX do people. add here will just pass the person and CTX do save changes asnc that is fine okay another one is our update method so let's name it update person I sync that's it and it should be be the similar but here we will just type date person and it is okay but we are going to change this method little bit so it will return a newly created person so here we'll just write person equals to blah blah blah okay let's name it created person okay and here we will return the created person so we will add a person to the database and we will return a newly created person okay so why it is yelling at me create person okay it is yelling for the nullability reason so if I do it like this like this create person so I guess I'm doing some terrible mistake here it should be like this and we will just return this person and we will see it in future is it working fine or not so I guess it will automatically update this person to a newly created person now we have four methods now we need only one method here so I'm just going to copy this guy and paste it here oops I have pasted it in a wrong place so just going to copy this guy and here okay now we are fine just name it delete person I think and here there will be uh remove okay now we are fine so here we have removed this person updated this person created a new person find by ID get people Ling and so now here one pro tip so let's right click sorry not right click press control dot here so press control and this dot symbol it mean period symbol control+ period symbol okay so just press control Dot and you will see this list here click on the extract interface and it will be the name of our interface and click on okay what it will do here it will inherit this interface and it will also create an interface here can see it has automatically created interface for us now what we will do here we will just write inside the program. Cs file write something here the same thing and it will be I person repository and person Repository Tre that's it now we are fine so what we will do now so open the tools go here in the new get package manager package manager console and here we will write add migration in it press enter and here I am doing something wrong so I'm just going to go here in the person contact Section and here let's see yeah here this one it should not be hardcoded so we will use this connection string here oops okay now we should be fine here so press control PC and here I will just write that command again and let's see it is fine or not yeah again it is a error so let's see what is happening here let's just trying to run this project and you just wait and watch how to tackle the error so right now here I am getting the error message fail to load configuration from blah blah blah do Json it means uh error should be inside the app setting. Json file and yeah here is a green line and here should be uh comma so let's try to run this project again and let's see do we have any error let's see do we have any error now okay I'm hoping that we do not have any error okay right now now we do not have any error so let's close this guy and open the package manager console again and let's that let's run this command again so now we have successfully created our migration file we will just write here update database and we have successfully created our database we can also check it from here my SQL workbench just connect to this guy and you can run this command so databases and click click here you will see that now we have this project sorry use this database use this database and now we can use the table select asri from person so we have created this table so you can see we have this table ID name and email so we are done with our database connectivity now we are going to create a controller method so that we can test these things so here we have this controller section first of all I'm going to delete this default weather forecast controller and I'm going to delete this one also now we do not have any controllers okay now we will create create our own controller so create a new controller go to the API section and API controller empty okay or you just create it with read right ACC and so but I'm going to create it empty okay now yeah let's change its name and let's name it person controller that's fine and here I'm going to delete this guy and just going to name it API people so our endpoint will be started with API SL people that's okay okay now here we will create a Constructor C and we are going to inject our repository here so private read on the uh I person repository person and let's just name it person repo that's it and press a control dot here and here we will see the option add generate something generate Oops why I'm not getting it's okay I will create it manually so it will be I person repository and person repo and below it we will have person repo equals to person repo we are just going to inject our default logger so private private re read only I logger and here will be our controller name Person controller and it will be uh logger let's see press control Dot and uh just see what is happening here no nothing is happening okay so I will do it manually so just copy this guy here and paste this guy here and name it logger and here we will see sorry not see we will write logger equals to logger and I am having trouble here let's see what it is yeah I'm using an angular bracket here okay so we are fine and it should be uh logger not capital L it should be in a small case okay we will create our few methods here so first one will be our HTTP post and it will be private sorry not private public async task I acon result and here we will have uh add book book oops not add book add person person person okay person and below here we'll write some logic so first of all I'm going to implement a try catch here try and catch exception ex and here we will just type logger do log error ex do message that's it and here we will return status code 5 or we can just return status codes dot internal server error 5 internal server error and here we will pass ex. message as error message okay now here we will Implement some functionality so just type here our created person and person repo Dot and it will have a method create person as sync okay and we will return created at accon and here will be uh name of and here we will pass the name of our method and here we will pass created op person object so it will return 2011 status code along with this uh created person object okay now we done here so I'm just going to copy this guy and here oops I have done something wrong here okay now just copy and here just paste let's just name it update person that's fine and just name it person to update okay and let's remove this guy and this line also before that we will write something like first we will check that this record exists or not so we will find uh this person exists in our database or not so just type here where existing person and equal to let's just say person no no no no person repo but it is uh wait and person repo dot get people by ID I Sy and here we will pass person to update. ID and here we will see if existing person existing person equals to null then we will written uh something like that status code uh no we will return not found that's it and here we'll return Anonymous object so it will be status code equals to 404 and message equals to it should be a small s okay and message equals to not found or just record not found and here we need to pass this guy also so I'm just going to copy this guy and we are going to pass it here also so rather than just passing a error message we'll pass a value so sorry some meaningful object okay and here we will pass ex. message and we will do the same thing here also so just going to remove this guy and we are fine okay now we can focus here so we have checked whether record exist or not now if records exist then then we will just update person to update sorry existing person do name equals to person to update do name okay and here we will just change it to email okay and we will pass it here okay and here we will write a wait okay and here it should also be await okay now we are good so now we will just return no content sorry no content oops what it is no content I'm just forgetting what it is I'm sure it is no content but why it is not showing here no content yeah okay so it will just return a empty status code I guess it is 204 I guess okay so we are done here so we have successfully created this record okay so we will create uh one more method here just going to copy this guy and here we will work on some delete functionality so here and it should be HTTP put and this one HTTP post it is HTTP put and this one will be [Music] HTTP delete and here we will pass an ID here okay and just change its name to delete person okay and and here we will just pass we will just check existing person and here it will be ID that's good if it is yeah and here we will delete a person so we'll call delete person I think existing person and it will also be a no content okay and this one will be the same okay now just copy this method and create uh one more method here it will be uh HTTP get it will be HTTP get method and here remove this guy and let's name it get people okay and here we do not need all of this rather than we will just type here people equals to get people s sync and I'm going to remove this guy and here we will pass uh people object inside this okay method so it will return a two okay status code okay okay we need one last method so it will be uh HTTP k and here we will just pass ID and we will pass that ID here also okay and it will be a int that's good now get people and okay we are just going to copy some code okay we are just going to copy this guy and I'm going to copy it here so we'll find the person existing person we can just change it to person and here just change it to ID and it will get as a person object so we will check if person is null then we will return return H not found along with this object otherwise we will return this person okay so we should be fine now so I'm going to run this project and let's see we are actually fine or not so before that let me check one thing here inside the program.cs file okay inside the program.cs file we have done this configuration I person repository person repository okay we are fine now so it is our API end points first we we will create a new API here so let's see what will happen so let's name it John and let's change it email John gmail.com and here we will just execute this guy and let's see what will happen okay we have got some response here so yeah here ID one John and John gmail.com let's create another one let's name it with my name ravind gmail.com and yeah we created let's create one more it one more record here and let's just name it a random name so it will be created as a third record okay now we will update this record so yeah just cancel this guy and go here try it out just paste it here and name it jackob and let's change it to jackup let's see what will happen here so I'm hoping for the positive everything will be okay but yeah no would not save changes please configure your entity type accordingly okay so I guess I have messed up with some so I'm going to stop this project and here it is my update person and okay yeah that happens when you copy paste the project sorry the code and you do the lots of mistakes okay so now we should be fine okay so let's run it again let's execute it again and let's see what is happening here okay so we can see that we have 204 and yeah it means we have successfully executed our put end point so we will see what is happening here let's just execute this guy and here we will see the list of records and here is the third one Z up zup gmail.com so let's see what will happen here if I type here API SL people and you will see that I have three records here and let's see person three and now it is the record from the third person the person with ID three so if I dive here four then you will see that status code 404 and record not found so if I do it here let's see yeah this one so if I pass here the fourth ID you will see uh 404 status code and this status message so right now what I'm trying to do I'm going to delete this record so I'm going to just go here and here I'm just going to pass the three ID and you will see that we have 204 it means we have successfully deleted so if I do it now you will see that we have no record with ID3 and that's how we can say that we have successfully tested all the end points okay okay so so far we have connected our mySQL database with ournet code project and in the upcoming videos I will try to make a video on how to do same thing with Tapper there we will not use the Entity framework and we will use net core along with Dapper and in the back end not in backend in the database we will use my SQL database so that's it for now see you next time

Channel: Ravindra Devrani

Views: 716

Keywords:

Id: zJ4SZNKLAog

Length: 50min 10sec (3010 seconds)

Published: Thu Sep 28 2023