ASP.NET Core Web API + Entity Framework Core : Database First - EP01

Hey guys today I'm going to talk about Entity Framework Core. Entity Framework is .NET's ORM framework which lets you map your C# models to database tables and it makes it easier to query and update your database tables there are two approaches the first approach is code first approach where you design your C# models and apply migration to create your database and the other approach is database first approach where you have database and you run scaffolding to create models and dbcontext in your dotnet project and I'm gonna show you demo on database first approach, because most of the projects start they have database team and they design database and they give the database work on it so for this project I'm going to follow this architecture where I'm gonna store my database and SQL Server Express and then I'm gonna create a Web API to show results in JSON the reason why I'm gonna create a Web API because it takes the JSON and returns JSON as an input and output and it gives me freedom to create my UI in any of this technologies because these technologies handle can JSON or any technology that you desire which handle JSON you can work with Web API. OK so after i create web api i'm gonna install Entity Framework in my Web API and then run scaffolding to create DB context and models. For my demo I'm going to use bookstore as a database and this books have publishers and they have authors and then will maintain sales of these books in our store and to get into the system we have user table and it has user ID and password if you do not have a database you can totally find this database in my GitHub link here I'm gonna put this link in the video description so if you want to practice with me you can totally take this back up and attach in your SQL Express and you know follow this demo so let's go ahead and connect to our database so i'm going to create i'm gonna go to server explorer and add a connection and here i would like to connect to my SQL Express so I'm gonna say .\sqlexpress and I'm going to look in my drop down here for selecting the database and you can see that I have a bookstore bookstores database here and I'm gonna connect to my bookstore here so I'm going to open and see all the tables you can see all the tables are listed down here then I'm gonna I want to look up the some of the details in my authors table here you can see author has first name last name phone address city state and setup these are the columns and some of the data this is all of the data in authors table so alright so I would like to show this authors table in JSON format so let's go ahead and create a Web API project I'm gonna say it's a public project and I'm gonna put it in public folder so that you guys can have access to and I'm gonna say it's a book stores Web API project alright and Cree and it's gonna ask me what template I want to create it has an empty template and it has web application MVC format angular react so I'm gonna select API here because I want to show the results in in JSON format this will create book stores Web API and out-of-the-box it gives me a weather forecast controller here which returns which returns weather for the next five days let's run this and see how it looks like so when I run you can see that it's returning weather forecast for the next five days in JSON format I would like to show my authors data in JSON format here so the first thing that I'll have to do to in order to connect to my database first thing I'm going to need Entity Framework Core SQL Server package and other package I'm going to need is Entity Framework Code Tools so that I can scaffold my database and create models in my .NET project here so let's go ahead and browse these packages and Entity Framework and as I'm using SQL server I'm going to install the SQL Server let's select proper version here and I'm gonna install Microsoft Entity Framework SQL Server so that I can connect to my database I'm a package that I'm gonna need is Entity Framework Tool, which somewhere here into the framework core tool and again let's select proper version um I'm I created the Web API in asp.net core 3 that's the reason I'm selecting this version and let's accept this alright so now we have both of these dependencies you can see that we have SQL Server and Tools now let's go ahead and scaffold our database or authors database so that it creates models and DBContext I'm gonna copy this piece of command here and paste it here you can see that it's a pretty simple command I'm gonna paste this in the video description what it's doing is it's using its using the command scaffold DBContext I'm passing the connection string of the database you can get the connection string of the database from here you can go to properties and get connection string from here and then I'm passing the DB provider how you can connect to the database and then I'm saying that output directories models so what what it did it created the DB context and models all the models for my database tables in this models folder here so it I'm just saying the output directory is models alright let's look at this DB context this dbcontext has created some properties these properties are same as my database tables so that if I want to update anything or select anything then I can totally use these DB Context and then here on configuring is using the connection string here alright so let's go ahead and show the the database table author data in in JSON format to do that I'm gonna copy this weather forecast and create another controller here and I'm gonna name it as author controller author controller and let's go ahead and change that name here and I do not need all these so I'm gonna get rid of that I'm gonna get rid of all this here nice so now instead of showing whether I would like to show others here authors in my when I return JSON format here let's add the namespace for this cool so to get data from the database the only thing that you have to do is you have to create an instance of your context your bookstore DB context then you can use the DB sets in it to pull the data from it so what I'm gonna do here I'm gonna see using and create a context instance of box store for bookstore DB context and then and then return context author to list see that's the only thing that I have to do to get all to get all authors that's the only thing I have to do so I opened a connection I created dbcontext and I use one of the DBSets from the context to return all the authors from all the authors from my database sweets let's run this and see how it looks like so instead of going to weather forecast I would like to go to authors and this will connect to my database and list down all the authors for my database awesome so what if I would like to return only one author so what if I want to get author by ID to do that I'm gonna comment this piece of code and these DB sets are like collections so you can put where condition on it you can use link to get author get author who has author ID == 1 and let's return list because it's it's certain in innumerable as as this property so we'll let's just we'll just return to list all right so when I run this you can see that it will return only one author here now all right what I'm gonna do I'm going to go in my launch settings here and instead of making weather forecasts as default launch URL I would like to make it as author so next time when I run it we'll go to author controller instead of going to weather forecasts controller also so what we did here we returned all authors and then we returned author by ID what I would like to do now I would like to add an author in my database and so to add a author in my database I should create an instance of author first a new author because we are adding any author and then I'm gonna say author this author has first name John and author has last name as Smith okay and to add this instance in my database what I'm going to do is I'm going to say context author add and then add this author and to insert this record in my database I'm just gonna say Save Changes so what I'm doing here I created a model then I added that model in my DB set and to persist the changes in the database I'm gonna say save changes and if I now I would like to see this John Smith in JSON format cool so what I'm doing here I created author added that in the database and now I'm pulling that author from my database to show it on the screen sweetly so let's run this and see how it looks like so now it's going to author link here now instead of going to weather forecast and you can see that it created a author ID 28 John Smith the phone numbers unknown and other fields are no that's that's because we did not assign these fields when we added the author in my database okay so let's go ahead and update this author know that now that we added this author I would like to update this author saying that this author has a phone number so first thing first thing I will have to do, I will have to get this author I will have to get this author saying that I would like to get this author in my model here model instance author and then you can make changes to your author your author instance which you got it from the database and I'm gonna say this author has phone number the phone number is the 777 the luckiest number ever and the only thing that I have to do now I just have to call Save Changes in order to update my author in my database sweet let's run this and see if this works so when I run it it will pull author from the database because it's the same author you can see 28 and John Smith and now you can see it updated the phone number in database and now it's showing on the screen awesome so now that we added we updated let's go ahead and remove this delete this author from from you from a database before I delete it I would like to show this author in my database so when I go to show data you can see this John Smith in my database who has phone number 777 and the rest of the fields for NULL alright now let's go ahead and remove this table and this author from for my database so here we updated now I would like to remove author and I would like to remove the same author the only thing that'll have to do I will have to say context author the DB set dot remove author this which we just pulled from database so what I'm doing here is I'm removing this author and to persist the changes I will just have to call Save Changes and that will remove the author from the database and then I'm again showing I'm trying to pull the same person and and show it in the JSON format and this should not return anything because we just removed the same author that be pulling to show it in JSON format all right let's run this and see the switch so no it shouldn't return anything because we just deleted John from our database sweet so this is how you can do the CRUD operations create read update delete there are few things that I do not like when we scaffolded the database one is it's creating the DB set it's not the name is not plural because it should be plural because you know it's a DB set and it's holding a lot of authors so it should be named as authors and another thing is it's storing the connection string in my DB Context I that's not safe so i'm going to save this connection string in somewhere in the configuration manager and pull it from that and i'm gonna talk about how you can in my next video i'm gonna talk about how you can delete rows if you delete a column or update your database then how you can update your models in your dotnet project so yeah so stay tuned thanks for watching this video see you soon

Channel: CuriousDrive: Solve Coding Challenges & Win Prizes

Views: 216,147

Keywords: .net, vsual studio, programming, c#, visual studio code, .net core, msbuild, web, asp.net, developer, code, librararies, .netstandard, javascript interop, windows, linux, macOS, open source, free, app, ecosystem, performance, reliability, security, classes, assemblies, MVC, dotnetconf, web api, api, REST, asp.net core, serialization, Authentication, authorization, Routing, HTTPS, JSON, Entity Frramework Core, SQL, Microsoft.EntityFrameworkCore.SqlServer, Scaffold, HTTP Methods, Microsoft.EntityFrameworkCore.Tools

Id: CLVJVA9cTuU

Length: 17min 12sec (1032 seconds)

Published: Wed Nov 20 2019