 Hello guys, so welcome back in this video. This is part two of SQLite credit operations in the last video We have seen how to create this user interface in this video. We are going to do integration of database so in order to do so we need to add a couple of dependencies just go to pop spec and We need to add two dependency SQL flight This is required to interact with SQLite database and the second one is path provider So this is basically required in order to get the path of file system where we can store our database Let's save it in order to fetch the dependencies and you can close this now So now we need to do what we need to add one Database file in order to handle operations with the database. Let's create a new file. Let's say my Database dot dot so some people might use the naming convention as database handler You can use that as well. Let's open this and Simply create this my database class So first of all we want to create a private constructor here So that nobody can create instance as many as times required So we will be just making it private so that we can get a single term object So let's create private constructor So simply use my database Dot underscore any name. So let's give it private Constructor so that we can Identify easily Okay, so whenever any constructor is called we need to return one instance Let's quickly return One instance. Let's say my Database so what we do is we just create a instance over here. So static Final my database underscore Let's give it database and Is equal to My database Dot private constructor. So this we can return it from here whenever This constructor is going to be called and you need to make it factory method. That's fine We need one database variable as well data this that is static late database and Let's give it DB. I Just change it to my database Underscore and this one should be database now. We need a couple of variables here final string Table name. Let's give it Employee table Final string Column ID is equal to and I did I know string Column name is equal to name Final string Column Desic nation to And the last one is final String column is Meal is equal to Is me Columns are defined Now we need a function to initialize database So let's give it name Nice Data base and we just make it a sink here. First of all, we need to get the path We have to store Database so how do you do so? There is a function get application documents directory and This I'm gonna give you something back So we can cross check it. So this gave us truck tree back so let's create a directory variable and we can import this and Since it is future, so let's use of it from the structure we can create the path So let's create string path is equal to so we need to first of all get the directory parts here three dot But so this gonna give us parts. So we need to compare to a database name along with this so let's simply use interpolation This and simply give it name Table or you can simply give the database name M tb and Done now we need to create database Let's create database and In order to create database. We need to call one function open database So let me just specify the path we already have Then we have version as well. So we can give it to one So this path is required and these are not required, but we need to use this on great Depending upon our requirement, you can use these on operate and on great. So let's use on great and simply use this and This basically give us one database That database we can assign to this Late variable we have defined Let's put it away and Yeah, so now we left with this database creation path So you can see this function for on create is giving us DB and wasn't so with this DB you can execute the query Execute and you can pass your SQL statement. So let's just use this and for reference Put this semicolon here and so once it is executed it's gonna give us something back No So it is basically future. So we need to just specify wait and Since it is features of it. We need to use passing here as well. So let's update this query so create table table name we will be getting from that variable and And then we have this ID that is dollar column ID and you can use integer primary key for the same that's fine and for name We have dollar column name and text is fine Let me save it and Next we have this Column designation dollar column Designation that is again text next we have Dollar column is male, which is Boolean basically and Where is fine? Okay, so this gonna create a database and initialize it to this variable. We have defined here Okay, now what we left with We have done with Major things now. We just left with that credit operations. So let's create a retrieve first get employ let's say list and Passing and let's define this return type future That would be list of We have employ model. Yes, that's fine. So now let's come to the body part So there are basically two ways like one of the ways you can use rock query database top rock query Yep, and you can write your SQL statement here like Select a strike from the table name. So let me just use this from dollar table name And this is gonna give you what this gonna give you a list of map and objects Let's assign it here like this Just need to use a weight. Okay, this is one way Another way is this is basically not employees. We need to convert it. So let's skip it as a result Another way is we can just comment this and use their Database start query method And here you need to specify table name That's all if you want some extra like some You can say group by having limit or something like this or we are something then you can use it So we can simply try with the order by and we will use column name Okay, and this is how you can do so So once you got the result you can simply return it So what we can do we can simply just change this return type to This response type So that we can cast it on the UI side. So let's keep it simple like this So this is for read operation same way I can do what I can just copy this and Paste for let's say insert so Insert employ and we need employ object and Instead of using this query we can simply say insert and this insert gonna take let me just clear this Can better see this this gonna take the table name that we already have this video and the values So this value should be as a map But we have this object Okay, so in order to do so what we need we just need to Go to this employ. So we already have created this for converting to map so just this one is used like this to employ we just go back here and So use employ dot to and So what's wrong? So we cannot assign this Like this. So since the return type is off in time. So you cannot put it into this so the dirt is in and So we can return inch here and this would be Rose inserted and this would be Rose inside so simple we can just remove this You can also use rock where if you want So two operations are done same way. We just quickly complete with the rest and then we will cross check everything let's use update and Update and Rose Up dated use here use here and just change this to update and We need to change anything Yes, we need to add rear argument over here. So we're we need to add the criteria So let's use string dollar column ID is Equal to question mark and that would be handled by We are argument Is just taking one list and the argument would be what? employ dot ID Save this and Same way for delete Let's change this to delete employ and Rose deleted change this change this to delete and We need to add some argument over here So what's wrong with this? Let's quickly check. So it is asking for rear and then we don't need this actually Let's simply specify where we are simply you can use this and Everything is fine. Okay. Fine. Yeah, all done. So if you want, you can additionally add one more here or counting Let's use one more here if you want you can display the count on the heading so let's use count employees and That it is a little tricky. So let me go up and use this Or you can use this rock ready this time that would be easy and just go down here and Paste it here. We can remove this and we can use this rock ready over here We are we will be saying select a streak. So we will say select count and Strict from table name So how do you get count? Now you can say simply in count is equal to Just to remove this and return this count and This would be coming from static method of SQL life. Let's see Girls ask you flight class dot We have first in the value. So from this response, we are getting that First into value. We need to pass this result over here in order to get that So since this is gonna give you optional value, we can cross check for the null. If it is null simply return zero So simple. So I think that's all we have Okay, that's fine. So this portion is complete. Let's go back to UI part and first of all, we just go to add employee and And once the user is clicking on add button We need to inside or what we can do. We just go to the home page first of all and Instead of using this Let's comment to the static values and What we need to do first of all, we need a database. So let's create my database Underscore my database is equal to my database. So this is fine Now in order to keep count, we can just use one in count of Employees, so let's use zero Now once the page is loaded, we need to get the data get data from DB and I just make it a sink and We need to initialize this database. Yes, don't forget that let's go here and use that initialize database So once it is initialized, we can call that Also, this should be Basically a bit and once it is initialized. We can simply use my database dot Get employee list and we can simply assign it to the variable of Let's use this List and since it is Future, let's await for the same and here we are getting a map So let's iterate through that map as I was doing over there And I is equal to zero I is less than this map dot length I plus plus and Here we can add this Into this Employees list. So simply say Employees dot add so it is looking for Let me just go and go secure We have this this to map Yeah, which takes a map and give us employer back. So let's use here employee to employ and Just use this map over here with index I perfect at The same time you can also pull this my database dot employee count Yep We don't need any employer. Let me just update this count and just remove this Save this go back to homepage and remove this does not require and since everything is future Let's use await here as well and this would be sign to count variable and Once everything is done, you can simply set the state and say it's loading to false. I think that's all If I'm not missing here So that's all we need to just call this into This so let's save this and Try to reload this. Let me just stop this and run it again Okay, so We are not getting anything so let's do what we just go to the home and We need to add one more condition over here So we can do what we can check for the count of employees as well. So employees dot length is less than one Then you can simply display one Message that no employee yet. So we can just replace this with his Empty that's all just save this You can see we don't have any employee yet. So that's why it is coming Okay, so let's now move to this add employee so whenever user tap on this floating action button and We are navigating to add employee and here we need to pass that My database object, so let's assign it my database and simply say here Just use comma required this dot database and We just go here and update it quickly Remove this const and say my database my Compage is fine. Let's go to Add employer here So here we got this database and we need to use it So let's go to this Add employee button here Let's create one employee here first of all all these values should be coming from ID controller Dot text and Since it is taking integer. We need to cast inverted int dot Parts and just use this and Let me just press semicolon over here and for employee name. We just need name controller dot text Designation controller dot text and for male So we are using is So we are using is female over here. So what we can do we can just use Negation of this That's fine. So we have data ready Now just use that widget dot my database dot Insert employee and pass this in but you can further add checks like if the ID is not entered Designation is not entered and so on. So I'm just doing it simple Use a weight here and This mark it a sink and once this is done. We can show a scaffold messenger Dot of context dot Show snack bar snack bar and For context simply use a text widget and say controller employee dot Name and We are getting something here. Let's cross check. So Okay, so we should not use this under Synchronous context. So what we can do we can check for the mounting in order to avoid the problem if Mounted variable if it is true, then we can just show this snack bar and We can even navigate to Home screen back we can simply use push and remove until and Just to root is false so that appear previous routes and use here material page route and With a builder we can use this function which is going to homepage and Just use cost here. So all done with this Okay, so let's just restart it And was air coming here. Oh, let it be. So we just go here and Try to add employee with some ID one and Name, let's give it Rio and designation. Let's give it to journal Manager and It's male. That's fine. Add it And we got something wrong. So here we have a problem like a table has No column named ID. So this is very common problem whenever you will do So this is a problem that The names of the columns here should be seen used in this Employee so here. So this should be messed. So make sure this thing Okay, so let me quickly update this. So we are using ID here and I just go to let's close this database part and Go here on the top and here we can simply use ID and Let's go back here use name go back here On the top name name is fine and Designation and is male. So let's go back here Go back on the top Designation and is male so let's stop this and Remove this app and just run it again So you need to make sure all these things Okay, so let's go to add and give it some ID one. Let's give it name Rio and journal Manager and it's male. Just add it and Again, we got a problem here. So here we need to do one more thing We need to check one condition here if it is one Then you can return true Since it is handling Boolean This type is Boolean Otherwise, we just make it false We this is fun. So let's quickly restart this and add a new employee again So I think it is added Yep So that's fine. So let's try to add one more to cross check second. Let's say Eric Let's give it a name manager This is designation and Again, it is male. Just add it. You can see it is added over here So let's quickly add The decoration part in add employees. We have added Snack bar and here Use background color that is color start green Give this Okay, let's try to add one more. Let's give it a ID three. Let's say Pooja and she's exception Officer and female Okay, just add it and you can see green color snack bar Pooja added And this is fun. So we have done with this adding operation. Let's go to the home page and Add the lead operation as well so simply search for the lead and Here simply do what underscore my database start the lead employee and just pass the employee which employee so employee you will be getting from employees and index and Same way, but you can do Just use that Add employee code here Copy this go back here and Just go here. We just use this of it here since everything is a sink and We need to wrap it into if mounted and And Yeah, so here we need to say simply Employee name is deleted. Let's create one variable here copy this remove this once it is string is equal to employee index dot employee name and This is deleted and The color we can make it red and just save it Let's try to delete this Eric And you can see Eric deleted Now we need to update let's go to this files and Simply go to edit employee. Let me quickly just remove this error as well by renaming it to my Underscore database in the lower case okay refactor and Just go to edit employee and Again here once you tap on this we need to pass employee and Is it working? Let's go to this and we can see this information three puja fine So if I want to change anything I Just again need to do the same thing we have done in the ad here So I just copy the data from this Add employee and paste it over here up with this go back here paste it over here and Just convert it into a sink function and My database we are not getting here. So what we can do we just quickly update this This is edit employee and along with the employee we need my Database and simply say Required this dot my database and here we need to do what everything is fine and Here we will say updated and you can change it to let's say orange and This is showing error So import this home page, that's fine. What else? Let's go on the top and see Let's remove this save this go back to home page and we need to pass second argument as well. That is my database my database That's all That's quickly restarted otherwise in Between some of the things are not guessed. So let's try to update this Pooja from Pooja to Pooja Show mother's give it update and We have not added update over there. So let's close this Close this And simply go to edit employee and We miss to update this function that is update imply That's all. Let's quickly restart this. Let's update this Pooja to Pooja Sharma and update and You can see this is update. So we have done with insert Update the lead and retrieve operation and we have added one more function For counting, let's go to home page and display the count on the top in the app bar So let's go to this app bar and here imply and Here we can simply say daughter count and Just remove this cost and save it You can see two employees. Let's add one more. Let's say this is for and simply say Rohan and that is field Manager and Again, just Change it to Ruhi and Just into female and Add it. Yep. So everything is working fine That's all for this video What is wrong here? Let me just remove this thing as well this class Let me just fix it. It should be immutable so let me just simply right here final and Add a const here and just save it So that's all for this video. If you have any doubt, no comment in the comment section. Thank you