SQLite + EF Core + ASPNET Core Web API + CRUD + Views + Loading Related Data

hello everyone in this video i'm going to talk about how you can perform database operations for sql live database using entity framework core and asp.net core web api project for this demo we are going to use northwind database i'll show where you can get this database in a moment here but we are going to use this database to perform crowd operations on this product table you're also going to call a view from rhp.net core web api project and show the data in json format we are also going to load some related data because sql lite is relational database management system and show the data in json format so to get this database you can go to myrepo which is ef code all databases considered we also have some demos for mysql and sql server you can also watch the youtube videos which are in the video description but if you want to get the database for sql lite you can click on the sqlite folder and then there is this database folder where you can get this northwind dot sql lite file i like sqlite file because you don't really need to install anything on your machine you can just directly use this file it's pretty easy to move this file but i wouldn't use this file if you're storing any sensitive information because you know it doesn't really have any security to secure your data so if you're using any authentication related data don't use sqlite use something else but sqlite is pretty easy to get started with if you have some project ideas so let's try to load this in our db viewer dbvar is one of the sequel light explorers i'm going to use that but you can use any other sql light explorer i'm going to create a connection here which is going to show a wizard to add sqlite connection i'm going to select sqlite click on next and then we will browse that file that we just saw so i'm going to select that file and click on finish and then you can see the tables and the views which are in this database this database also has some data if i click on view table then you'll see the data associated with this product table we also have a view if i click on view view then it will show the data which is in that for view okay so let's try to get this data in json format using hp.net code for that i'm gonna open visual studio 2022 and then create a new project and here in the templates i'm going to try and look up order web api and here i'm going to select hp.net code web api and then click on next and let's give a nice name which is going to be northwind dot super light and i'm gonna keep this project in the same folder sqlite folder so that you can you know compare your code with my code if you're practicing with me then i'm going to click on next here i'm not going to change anything i'm going to keep dot net 6 which is latest at this moment and i'm not going to change anything in this section and then i'm going to click on create this is going to create a template which is going to show weather forecast for the next five days let's run this template and see how that looks so when i click on this play button then it's going to build the project and then open a web web browser which is going to show weather forecast for the next five days in jason format it shows a nice ui because swagger comes out of the box for these project templates and you can see this weather forecast in a nice ui so if i click on execute you'll see random weather forecast for the next five days in json format so let's go ahead and check out the project structure like any other asp.net core project this project also starts with program.cs where we are creating a web application we are using a builder to first add some services swagger is one of them and then i'm using well the project template is using builder.build method to create an app and then we're using that app to configure the application and finally run the application and then we have this weather forecast entity which is helping us show the data in our json format and then we have a controller which is creating a web api to show random weather forecast for the next five days in json format uh so let's go ahead and use this project to show our sqlite data in json format so for that i'm going to use some commands which are in the same github repo you can get those commands from the steps text file and i have opened that text file here so first thing that we need to do is to add connection string in our app settings so the connection string is this data source and database sql light this is literally the path of the sql light this is a relative path but you can put full path if you would like this is the relative path from the project so i'm going to grab this connection string and go to my visual studio here i'm gonna add a connection string in asp.net this app settings.json file here i'm going to add a connection string which is going to be northwind db and the connection string is going to be data source database not northwind sqlite file that we just looked at now that we have the connection string we can run some commands to scaffold this database and create entities and dv context for that i'm gonna follow these commands like i mentioned so the first three commands are going to install some packages the first package is microsoft entity framework code design which is going to help us scaffold the database the second one is microsoft entity framework or sqlite which is going to help us connect to database and the third one is microsoft hp.net core mvc newton soft json which is going to help us load related data so let's install these packages so i'm going to copy these commands and then right click on my project and open terminal which is going to open a powershell window in my visual studio and here i'm going to paste those commands to add those packages for my project once i have these packages i can go to the project file and make sure that they have there so you can see these packages getting referenced in my project file now that we have packages we are going to install a tool which is going to help us scaffold our database the tool is [Music] dotnet ef so if i look at these commands again you can see that we're installing a tool which is dotnet ef if you do not have this tool you can use this dotnet tool install global.net ef command to install the tool i already have it it's going to show that you know i already have this tool but if you do not have it you can run this command to install that tool for you if you already have it make sure that you're installing the latest version you can you know install or update the latest version by running this one which is dotnet tool update global.net ef i'm going to copy that and paste it here and it's going to just reinstall the tool to make sure that i have the latest stable version now that we have the tool let's run a command which is going to help us scaffold the database and when i say scaffold the database that means it's going to connect to the database and it's going to create models or entities for the tables which are there in the database and it's also going to create a db context file which is going to help us create that relationship between the models and the database tables which are there in the database so let's break down this command the command goes like this this is going to use the tool that we just installed which is dotnet ef and we are asking it to create a db context and the db context could get scaffolded from this name northwind db and this is the same thing that we just added in our app settings so if you open this app setting this is the same name that you should use to scaffold your database and the provider that we are going to use is the same package that we just installed which is microsoft entity framework or sqlite and then i'm passing some parameters to make my solution look nicer so the output directory for the models is going to be models the output directory for my context file is going to be data the namespace for my models is going to be northwind models namespace for my context file is going to be northwind data and the context name the name of the context file is going to be northwind context and we're going to run this forcefully that means it's going to override all the files if they're already there so if you change anything in your database schema you can still run this command to update your models and then the final parameter is going to be no no on configuring because we are going to configure our connection by ourselves so i'm going to copy this command and then i'm going to put it in my terminal and then hit enter i want you to keep an eye on the solution this is going to build a solution and it's going to create two folders here one is going to be data and another is going to be context this is not going to run sometimes because i have my service running so make sure that you close your services before running this command i'm going to run this command again to try and generate our db context again so now it's going to build again it's going to succeed and now you can keep an eye on the solution to see that it generated these two folders one is data and another is models so if i open models and go to my product model here you can see that it created fields for the columns that i have for my database so if i go to my product table here you can see that the product name supplier id category id and other fields are there and it created the fields for them and it also created some references here sqlite database is a relational database management system and we do have references to our supplier and category table so it created that relationship too and it also has one-to-many relationship with or the details table so it created a collection for that so it does that for youtube and that's how it creates the model it also creates a db context file in the data folders if i open this northwind context file you can see that create a db sets which are going to help us perform database operations on our models which are there the tables which are there in the database and it also created the relationship it mapped the properties with the database columns so that it knows which field belongs to which column so it generates these two files now we are going to add a service in our program.cs which is going to create that relationship is going to add that service so that it knows which database to connect to and then you know perform those database operations so i'm going to go to my program.cs and right next to wherever we are adding some services here i'm going to add a service which is going to make that connection so i'm going to use some code snippets from my toolbox here you won't have this because you know i added these code snippets before the demo to make the demo go easier but you won't have it if you want to you know get the code you always have the github link in the video description you can check the code from that so i'm going to use the code snippet here and add two lines which are these two lines so what these two lines are doing it's adding a tv context which is not when context the file that it just generated this file and then we are asking it to use sqlite and the name is going to be not in db which is the same name which is there in our app settings so here it's throwing some errors because we don't have name spaces so i'm going to hit ctrl dot and add these namespaces to make these errors go away now we are good we don't have any errors now that we have the connection we can use our visual studio to create a controller to perform create read in a create read update and delete operations so i'm gonna right click on my controller and add a new controller which is going to create a pop-up and here i'm gonna select api and i'm gonna select api controller with actions using entity framework core and i'm gonna click on add here going it's going to ask me which model do i want to create the controller for i'm going to select product because we are going to deal with product table and it's going to ask us the db context which is going to be north bend context and the name of the controller is going to be products controller and once i click on add it's going to install some packages which is going to use for creating this controller it's going to connect to the database and it's going to check out you know what is the primary key of the table and is going to use all those details to create a controller which is going to help us perform create read update and delete operations it takes some time to generate the code but it will eventually generate the code which we are going to use to perform these operations so it generated a controller i will show i will you know explain what these methods are doing but let's first run this and see how it looks for that i'm going to click on this play button again it's going to build the project and it's going to show those web apis right over here so this is how easy it is to you know get started with sql lite and you know get these crud operations in web api so if i want to get all the products i'm gonna click on this get products and click on execute it's going to connect to the database and it's going to get all the products in json format you can see these products here which are the same products that you see here and you can also create a new product i'm going to copy this first object and then let's try to use this post method to create a new product i'm going to try that out it's going to be a new product instead of called chai i'm gonna say coffee [Music] and then i'm gonna execute which is going to create a new product in my system you can see that it was a success and it created a new product let's try to look it up by calling this get method i'm gonna put that id in here and then try and execute it and you can see that we are able to get that copy in json format you can see that in your db viewer too if i refresh this data and go down here you can see that coffee here at the end of the table and let's try to update this copy too for that i'm going to copy this again and then go to this put method which is going to help us update the copy so i'm going to put that jason object here again and i'm going to say this is going to be beats coffee just to update it and then i'm going to also pause the id here and then execute this and this is going to be a success 2 and it updated to beats coffee if i try to get the product again you can see that it's now getting the updated data you can of course also delete the data for that i'm going to grab the id and use this delete api and here i'm gonna put that id here and click on execute and this is going to be a success too and if i try to find that product again is going to say not found because the product we just deleted and if i try to look it up in my database you can see that it's gone now so this is how you can perform cloud operations using entity framework core and asp.net core web api let's try to call this view so we also have this view this is product details view let's try to call this in our web api too so if you look at this these models which are created there is one model which is product details and it has all the fields which are in the select statement so if i go to my view and um generate ddl you can see this is the schema for creating the view and here we are also loading suppliers and category for that product and all these fields are added in my model so if i go to my model all those fields are there in my model so if you want to call a view you can go to your controller and here we are going to add a web api let's first see how this controller is set up first right so the first thing is we are injecting the db context here we are injecting a db context which we just added so if i go to my program.cs this is the service that we injected right this is the service that we added and in the controller we are injecting that service and we're using that context to perform all the operations that we just executed so the first one is to get all the products i'm using the context and then the products which is adb set to get all the products we are also getting a product by its id so i'm using find async api to get a particular product from that tv set and then returning that product and then we have http put method to update the product where i'm passing the product to be updated and the id of it and then i'm using the state you know i'm setting the state to modify it then calling this save changes async method to update that product and then we have http post which is creating a new product it's taking a new product and adding that into idv set and then we are calling this context method to this save changes async method to add that product into my database and then finally we have this delete method which is taking the id we are finding that product and then calling remove method and passing that product to be removed in our db context and then calling save changes async to actually delete that part of so this is how we are performing all the card operations now let's go ahead and call this view for that i'm gonna again use a code snippet which is pretty simple it's just one line of code but i'm gonna use this quotes number which is loading from view this is http get method where i'm calling get product details which is the same name for my view here i'm using the model which was created by by my that db context scaffold command and then i'm just calling to list async so this is how easy it is to call up you you use the db set so this is the model product details model and if i go to my db context it has that db set you know this product details tv set and then i'm using that db set and calling to list async to get the data from my view let's run this and see if that works or not so for that i'm going to click on this play button again and go back to my browser it'll add this get web api and here i'm gonna just click on execute to get the data from my view so you can see that it's loading the category name category description supplier name supply region which is part of our view so this is how you can call a view from your asp.net core project now finally i'm gonna try and load related data so if i go to my sql script i have a script which is going to show how you can load related data so here i am inner joining my product table with audit details so that we could get some details for that other details for that product and i'm using product id 14 which is tofu so i'm gonna run this query to i'm going to select the database for this query and run this query to get order details for my tofu product and this is related data and i would like to get this data in json format so first thing that you need to do is you need to add a service in your program that's here so if you go to your program.cs right next to wherever we added this tv context i'm going to add a line which is going to add the newton soft service so if i go back to my steps here we added this package now we're going to use this package to load related data so to load related data we need to make sure that the reference loop handling is ignored and that's the reason why i'm adding this package adding the service and once we have these two lines then we should be able to get the related data in json format i just hit ctrl dot to get the name space for this reference looping and then we are adding a service which is builder.services add mvc and add newtonsoft json now that we have the service i'm going to add some code to load related data we're going to use entity framework core to load related data here the name of the web api is get product order details it's http get method it's taking product id as a parameter and here i'm using the db set and then i'm going to use include method to get the order details so if you look at the model that we created if i go to product here it created this collection order details collection so we are going to use this to get the related data this is one-to-many relationship but if you want to get one-to-one relationship you can do that too so to do that i'm gonna use context dbset and then include method where we are including all the details that we just looked at and you can use link methods here you know i'm using this fair method to pass the product id for which i would like to get the order details and then finally i'm calling to list easing to get the related data let's run this and see if we can load related data or not for that i'm going to click on that play button again and then i'm going to come back to my browser and here if i try to call this web ap i'm going to pass 14 as the product id the parameter which is for tofu and if i click on execute then it's going to load it's going to load the tofu product and also the related data which is all the details for tofu it's getting loaded in here so this is how you can perform crud operations called a view or load related data from sqlite if you have any questions you can reach out to me on my twitter or facebook account or you can ask us questions in the comment section below thank you so much for watching this video i'll see you next one bye

Channel: CuriousDrive: Solve Coding Challenges & Win Prizes

Views: 11,484

Keywords: .net, vsual studio, programming

Id: xs3JKpePnvs

Length: 27min 50sec (1670 seconds)

Published: Wed Dec 15 2021