SQLite | C# | Entity Framework | Part 5 | SQLite CRUD Operations using EF6 in C#

hey everyone welcome to sqlite tutorials in this part we are going to see how to use entity framework with sqlite okay uh having said that let's go ahead and create a new project it's going to be in the same solution but it's going to be a new project i'm going to call this sqlite ef console app too maybe okay because we used one for the first one and let's create this project okay this was our first project and it's right here and this is our second project so let's make this our startup project okay so we've got nothing here and first thing we need to do what i'm going to do i'm going to bring in the nougat package for sequel lights so add nuget packages and we are going to browse for system data sqlite okay let's tap one i'm going to select that that brings the rest with with it i'm going to install and accept to the pop-ups get a minute or so okay i think it's done as you can see it added all this stuff to the project i'm going to close this out okay let's check our projects check out references and all the good stuff is listed here okay the next thing i want to do i want to actually copy the database we worked with our first project i'm going to open the folder go to bin folder debug folder and i'm going to copy this database we're going to use we're going to work with the same database so i'm gonna go into my new project bin folder and debug folder and paste in here okay all right let's go ahead and build this project so that i think it's once appealed that's the most someone project files will populate here and there you go now our project is built okay all right let's see we got our database so um we're going to start with adding a couple of new classes so i'm going to click add select class all right this first one i'm gonna call the sqlite config it's going to be uh the configuration class okay i'm going to make this public and it's going to inherit from db configuration okay i think we're going to need namespace control dot all right it's using system data entity we're going to bring that in and then i'm going to copy some the content of the file i'm not going to get into too much details here this configuration um i'm basically just going to paste copy and paste this constructor that i have here i'm gonna post the code in my uh blog so and drop the link in the description in case yeah you wanna copy and paste it that's fine okay so we got some errors here obviously we need to resolve this errors so ctrl dot control that the next one and control that here let's bring in the namespace and resolve the errors okay no issues font and get rid of this unnecessary namespaces let's keep it clean okay let's go ahead and save this and then next we're going to create another class for db context data contexts add class let's call this data context and it's going to be public so let's go ahead and make this public and we are going to need to inherit from db context it's ctrl dot to bring in the namespace system data entity okay for this guy we're going to do something similar and paste some code in here so i'm gonna um this constructor and override it method here and paste in let's actually get the property too paste right in here okay we'll have a few errors and we are going to resolve the namespaces so we have to bring in system data sqlite and for this guy here we're going to bring in systemdata anti-model configuration conventions okay we have one left this because we don't have this class yes person class see maybe you can get an option okay generate type person let's go ahead and do that in a new file sure okay let's see if our class was created there it is okay it's kind public all right so um let me open the database so we can check our properties i think it's id name but debug folder and this is my database i'm gonna drop in soon as my firefox opens up it's a little on the slow side all right so let's drop our database in our firefox browser with our tool nice nifty tool okay so we have the id property and the name properties okay so we need to properties in our class int id and prop string name okay our class is ready i want to go back here really touch on a couple of things so first data source here and this this is the database we're going to use so i'm going to change this my sqlite db dot db that's our database name okay if it's if it's not in the same folder in the debug folder then you want to supply the full path to this string over here for data source okay um the other thing if you don't have this line of code right here actually it's not it should be removed not add so basically what this line of code does is if you don't have this line of code it's our table name is person right and if you don't have this line of code it's going to look for people table and it's not going to find it because we have person table so that's for this when you first generate this method from scratch um it's not gonna have that line of code so in case you get an error that says okay people table no such table people table then that's your problem so so normally you would do something like this public override void um on well take a name stick here all right avoid i think this we did we have this methods let me comment this out for a second that's why it's not suggested in in the suggestions so there you have it so normally you have only default line of code but again if you don't have this one here this line of code it's going to look for plural name for a table name okay something to keep on back your head if you run into a problem all right so i think we are ready to go with our main methods so let's save this okay and let's go to our main class so here let's create a method first for get people okay let's get some help from visual studio to create the method okay and actually let's go ahead and throw in a console.tradeline okay so here first thing we are going to need to do create a instance of our context data contexts so data context ctx equals new data context okay and then we're going to say for each var person in ctx that people we're going to cancel that right line and we're going to print out person dot id space person.name okay that should be it and then finally we can say ctx dot dispose okay all right let's run this and see what happens let's actually build and then run because my computer is a little on the slow side today it's probably downloading some [Music] updates in the background that's when i notice it's running slow okay let's run this and there you have it so we got our data displayed in our console window of course anytime you're dealing with a uh external file or database you want to try use try and catch blocks just in case you run into an issue so normally we would do to place this inside a try catch block let's try and then catch we can do a console.writeline for the error exception you can display the exception in case of an error you could say type print out the message and then for finally we could have finally block and then close the database with ctx dispose okay now we got an error here it's not okay so probably we should create the instance outside the try catch block okay all right um so that's let's see what's next let's go ahead and let's add a person let's comment this method out and then let's add a new method add person okay let's get some visuals to the help and create the method for us all right so let's first create a instance of a person so person p equals new person and we want this to be id of four i think we have three here so we need the fourth person and say id equals four name equals jill jones okay now likewise we're going to create a or we can say using next let's create an instance of our data context so um this time let's use using statement i mean uh yeah using statement i'm using this interchangeably so you can get you know familiar with both usages so instead of just going playing creating the data context in a separate line i'm using inside a user statement so inside our parents say far ctx equals new data context open close curly braces and in here we are going to say ctx dot people dot add p simple as that and then save changes so ctx dot save changes that's it a few lines of code and you're done okay okay let's go ahead and run this actually you know what i'm going to uncomment this one to this because this method is reading them so let's uncomment that you don't have to comment that out because that's not making any changes to the file so we can read in the files for us so let's go ahead and run this and there you have it now we have number four added to our list okay beautiful all right let's continue with our next method i'm going to comment the add method out and create another one this is going to be delete person okay and i'm going to let visual studio generate meta method for us and here we go i'm gonna get through the this line of code that we're not gonna use okay so we are going to start with again creating an instance of the data context so var ctx equals new data context next we need to find the records or the row we want to delete salvar person equals ctx dot people dot find we are going to use the find method and we'll pass in the id the id of the record we want to delete which is number four the last one is number four okay and then we are going to say ctx that people that remove and will pass in that person that we just found next we are going to save it save the changes and then dispose ctx.dispose okay okay if we did everything right they should delete the person that we are looking for they're searching and then deleting and then save changes and closing the database okay let's run this and see what happens all right so number four is gone we only have three now okay just to verify this we can open our um well i had to drop refresh this again so um let's see i'm going to refresh this and drop the database back in there and we had three records okay all right let's continue with our next method and that's going to be update i'm commenting delete methods and create a new one so update person okay let's generate the method all right so we have three records now uh let's say um i want to change john doe to john smith okay all right let's go ahead and do that so var ctx equals new data context okay uh ctx dot people dot find so the id we're looking for is number three right okay first we are finding it then we are going to say name equals john smith okay and then we'll save the changes oops friends oh come on all right and then just pause it okay let's try this out and run it and there you have it so we updated the last row to john smith okay the beauty of uh entity framework we don't have hard-coded text sql queries it does the job in the background for us it converts to sql query and passes into the database and uh but it's slightly slower than the other one i think it feels like it's i guess a trade-off so uh this is easier than the other one as far as it's just c-sharp uh rather than sql queries but again there is a little speed difference between the two so i would prefer um i think that's it so we did read we added the row we deleted row and we updated the row so i think we are all good unless you guys have any questions please reason drop in the comment section and uh we'll take it from there okay so until the next one take it easy have a good one

Channel: tips'n tricks

Views: 3,312

Keywords: Console Application, C#, WPF, SQLite, Database, CRUD, Operations, Entity Framework

Id: M6Saf2EP_pc

Length: 26min 46sec (1606 seconds)

Published: Sat Nov 13 2021