Flutter SQFLITE CRUD Operations

an assalamualaikum in this video we'll see how to use SQL flight to store our data locally and actually we would do a crowd operation where you'd be able to create new data and store them in the database in SQL flight and after that we would be able to edit them so let's go ahead and edit them and we see that our change persists and as well as would be able to delete them if we want this video is sponsored by Zillow cloud and with zobocloud you can use powerful communication app using their voice video and chat API and they have Services over 212 countries a lot of the big Enterprises work with them and with this over Cloud we don't need to write a lot of code just with a few lines of code your app is alive and up and running it's built for developers and businesses as well so you have all the solutions you need over there most importantly it provides our popular framework like flutter yes of course it does provide Android iOS and web and which I found very interesting is that they offer solutions for flutter flutter is relatively new but yet they provide an excellent solution and they have great video now once you're over here you can just go ahead and start building your demo app as you try to build a demo app it'll take you to their dashboard and they have excellent support for all the services in your dashboard so you can create your own app and after that you would come over here and you'd be seeing that a lot of options that where you can customize your app like for example you'd click over here and then you choose whether it's for web Android flutter and things like that not only that they have excellent documentation for all kind of apis for example if you come over here you can get started or even look at the sample codes over here you can download the sample codes and you are good to go as well as you'd be able to download their SDK latest version and it will show you how to integrate the SDK as well so zovocloud has made communication apis much better easier so if you're a flutter developer Zelda cloud is one of the best solution for your video call voice call and chatting API and they also have excellent after sale service so if you have any questions problems you can just leave a message and they'll reply very quickly to get started make sure that on the terminal you have this command and then after that you can hit enter and it will download the latest SQL flight to your photo app all because I've already downloaded it so let's go ahead so after that go ahead and make sure that you create a file which is called SQL helper.dart and then we'll import this two packages one is foundation and the other one is SQL as SQL and we'll create a class the class name would be SQL helper and inside this will create our first method the method name is create tables and as you can see that it will take the SQL database object and inside this we'll run this command or actually function which is called database.execute and inside this we are going to create a table the table name is items and here we'll have ID as integer then title as a text and description as a text as well as you can see and we'll also have this created ad which is for saving the date now this section is pretty much the same as mySQL database so anyway so this is the method that should get called uh in a lot of operations that we are going to do so what all we are doing over here is creating a database table the table name is items we are done with that after that we'll create another method and the method name is DB so well this is the method actually that's pulling create tables as you can see so this is the method the one that we created but it's coming from this DB method but you can call it anything you want and once it creates a table actually it would also attach it to a database name so in our case you can name it anything you want so while I have already created one which is called dbtag.db and this time I'll just create the best tag dot DB so that would be our database and inside the database we'll have this table which is called items so what will happen each time we create an item from our UI or update an item so this DB method would get hold eventually create tables will get called so what will happen over here first it will check if this database exists or not if it exists if it not exists it'll create one for first time and then it will also check if we have a table well now if the table already exists it won't create so when you call this method a second time eventually it'll just return as a database object and that database object also would help us to connect with this table over here and after that we'll create another method the method name is create item now this create item would be called from a button click now this would be our button and from this button would call this method on pressed event now as we call it we'll pass title and the description and these are the items that we want to save in the database so this would be coming from our front end code where we have the text controller and as you can see as we call this method first it will call SQL helper dot DB method which is this one and this one eventually would call this one okay and before we go ahead and insert item while if we click on this we'll have text controller would pop up or a dialog box would pop up and we'll type in our title and description then we'll grab our title and the description and after that we'll open our database connection and this would be the object and at the same time we'll put our data in a map format and once we do that then we'll call this method which is called DB dot insert now because of this one because of this method which is called DV dot insert would be able to insert items in this table and this is the data so remember you have to insert the data in map format so you can get them over here in the function but as you insert it has to be a map format so each time you insert you have to mention your table name and the data as well as this is another property we should set now this would help you to prevent from duplicate entry and it's a good practice and after that actually we are returning an ID so DB dot insert if it can insert data successfully it returns an ID of that Row in the database and after that we'll create another method now this method would get called the first time when we launch our app or reload our app so what it will do it will get all the items from our database so once again first we get our database connection and then we do a query now you need to perform db.query when you want to get an item and once again you have to mention your table name and how you want to get the data so we want to get the databased on ID so it would use ID to to organize our data now divi.insert we use for inserting divi.curry for getting one data or all the datas or certain data from database so as you can see after that we return a future type but inside this we return a list of map so as I said earlier that once you insert data that has to be inserted as a map once you retrieve the data whatever the data you get in return that also has to be in this map format so we'll have a lot of data all this data would be saved in a list like that and after that we'll create another method now this method would be responsible for just getting one item from our database a school database now this would be of course certainly based on certain ID from our sqlite database and once again we get the database connection now over here we are passing extra properties and parameters because we want to get a certain data based on this ID and we only want to get one and you have to remember so you also you have to use the ID as in the condition and the database name now you may get confused when do you use that now you use this if you want to get one data or update certain data if you want to update certain Row in the database you have to use certain ID over here we created another method this is called update item so for update item you may want to update the title and the description as well so here's the typo let's fix that and here this one as well anyway so as we want to edit or update an item we'll have access to this ID remember before we really update we'll call this method and this method would help us to get a certain ID and we'll say it very soon how we get that so once again we also have over here this data format in a map so if you don't know what is map check out my other tutorials and then over here so this map contains all the updated data like title and description and we have to make sure that we also update the 10 in the time in our database table so after that just like just like db.insert we also have db.update so we have here items our data and because we are updating certain element in the database so we also have to get that ID so that ID actually is mentioned over here which one will pass from here okay the last method we'll create that is called delete item so this is our delete item over here and once again if you want to delete a certain element so you should use ID and that's what we did and once again we are mentioning which ID and it falls to this condition where so if you want to send ID as a parameter with a certain value which would be changing you have to use both where and where args like as you can see we did early so with this we have this basic crud methods there already now this is the time we go ahead and work with our UI so this is our main.dart file and right after this actually we are going to declare one list item and a method so as you can see here first we have created a list of maps list of maps and as I said earlier over here if we want to retrieve all the data from the database it returns us a list of maps so that's why first here we created one empty list of maps of course we have a Boolean for loading data then as you see over here we have this method which is called refresh journals now with base actually how to retrieve all the items from our database and we'll save this in a variable which is called Data now data will in a set set State method will assign it to journals the one that we created so once again over here we are calling get items it will return us a list of maps as you can see one more time get items it returns a list of maps and all we need to do right now we need to go ahead and call this to be able to call this we'll just uh copy this one from here then we'll put it over here and as we did that actually we could do one thing over here we could do a print method and go now this is the time that we go ahead and reload our app and if we do that hopefully in the terminal over here I will see this message number of items so let's go ahead and do that so after restarting our app we see the number of items is zero because we don't have any items so it means that we're ex actually successfully able to create this as well as the table now we could print and check out on this so here we do over here we would say creating a table and it would print a law over here and we'll also well now let's restart it and we'll see how it goes okay so number of iTunes is zero now why this is not printing because the table which is items has already been created so if it's created it checks for you automatically this method open database so it does it automatically for you so that's why it's not going inside this double it makes sense anyway so now we'll come to our main.dart over here now inside this actually right after this app bar we want to create a Floating Action button and this is our Floating Action button and inside this button actually we want to create a show form method we don't have the method but we could just sort of personnel to it and here we do see that we have this one so as we click on this actually we want to be able to show a form over here and then submit the data so this is our method show form and this is the method that actually we want to call from here so we'll just simply call it show form and inside this for now we'll pass null now as you can see over there and inside this actually we are going to use text controller we don't have them yet so we are going to declare two text controller object at the top over here and with this our error is gone now inside this actually first we are checking we have an ID or not now this would be used to update our items and submit in the database so we'll look at that later for now we're going to ignore that and then over here we see we see that we have show model bottom shared so this is what that we want to be able to click and show so we are passing context and things like that and inside the build method we have this basic styling where we have this padding and because we want to show both the title and the description so that's why we are having a column over here and inside the column we have this children text field and we have a bit of height with the sized box and add the text field for description and then sized box again and well that's all about this show model bottom and show form method which is being called from here now we're gonna save it and then we want to be able to click on this and see that we would be able to view a form so while this is our form so over here we have title and the description so well of course definitely we can type over here this is a title and this is our description well now of course we don't have any buttons over here so we need to create buttons now as we saw earlier that actually the show form would be used both for creating new items as well as updating new item updating the items that already will see in future over here now to be able to do that right down over here actually we need to create elevated buttons with conditions here is our elevated button and inside this actually we have two methods and we are checking with ID first the ID that actually we referred at the top over here now this ID would be passed down to this method and we'll see how to do that but anyway so first over here inside this elevated method we check if the ID is null then we go ahead and do add item which should help us to create new items if ID is not null then we'll go ahead and update an item and after after that we just cleared the text field and also returned to our earlier UI and based on the ID we want to show if we have a new item that we are going to create or whether we are going to update an item now this time actually we need to go ahead and add the method before we do that actually I'm going to uncomment comment out of this so for now inside this elevated button inside this unpressed event all we need to do go ahead and create a method for this while the method already we mentioned we will go ahead and create one now this method actually would be creating at the top before the show for method so we're gonna create it here its name is add item so this is being called now is it being called actually inside this we are going to pass our text controller and the title as all as the description controller so this is the method create item that we created early over here as you can see it takes title and the description and title and description we put them in a map and after that we insert into database so this is the method at item should get called from this on pressed event inside this elevated button now we'll refresh our app one more time over here and then for now our items still zero and we'll click on this one and we'll create some values for this in the text controller here we'll say title and here we'll say new description so here we'll click on this uh create new now you have to know one thing though that I'm sorry so as we clicked on this unpressed Floating Action button actually we didn't pass any ID to this show form method which is this one so it doesn't fall in this condition so because ID is null so when ID is null as you can see over here that we show that we are going to create a new item over here anyway so let's go ahead and create well after submitting the button to be able to see that how many items we have actually we can click over here and actually put this line which was early in any state but in its state only gets called first time when the app loads are we refresh our UI on purpose but actually we can read the message immediately right after this add method once we load call this method remember refresh journals what it does over here it loads all the items from the database and that's it so now over here yes it does look like zero because it's coming from init state right but in its state as we know that it gets first time called or if you refresh your app now while just now we updated but we don't see anything but we would uh submit another one and we'll see that we'll have more information over here so let's go ahead and do this one like title two and here we'd say description two now let's go ahead and create a new one and here we see that our number of items is two and we create more items so here we'll say title three and the description three so we'll see that it updates automatically so let's submit it and we see the number of iTunes three okay so success we are able to put the data in our sqlite database successfully now we also want to show them in the UI so to be able to show them in the UI we need to come over here and create a body section so here is our body section and inside this we have listview Builder and we get all the length then we have this item Builder and we are showing everything inside this card and as you know that we are saving all the data inside the journals list so we can access them using this index and we get each of the properties like title and description so we can go ahead and restart our app and as you can see that this is what we have now so this app is already working now we're gonna create a new one over here and we'll see that we'll see it immediately so here it's a title for and a description for so let's create it and we see it immediately awesome so it means that our submitting data in the database is working so this is the time we go ahead and be able to edit them now to be able to edit them actually inside there so you can work with trailing now I can see that now you can see that I have added a new property which is called a trading and inside this we have this sized box we have this special with and then we want to show everything in a row like the edit icon and delete icon so let's save it and we'll see that edit and delete now inside this edit section inside this icon button for editing as you see on the unpressed event we are calling show form again the one that we called on this button price and inside this actually we are passing this ID because each of the journals we can access the certain one using index and for that one we can also access the ID so now if you go and look at this one so you'll see that so we are passing ID so ID is not null if ID is not null for that ID whatever we got earlier so we searched that element if the ID is equal to the ID of one of those elements then we return to it and for that one we return our title and description remember this journals this gets cold over here like inside this method and then it gets updated so it reads everything from the database and then update this journal list so if we were looking for some elements for updating or deleting actually we can find inside this and based on that we can do a lot of interesting stuff so let's go ahead and restart our app one more time and then we'll see that we'll be able to uh change our title or description or data so let's go ahead and click on this so over here we have this information so here we would say title zero and over here we do description zero well we update we try to update but it didn't work okay now the reason why it didn't work let's come down over here because we have to check this update item method but we don't have this method yet so we need to go ahead and create this method so once you change anything over here eventually this elevated button that one gets cold and it checks if the ID is null or not if that is not it means we are creating a new one if that is not null then this section should get cold but we don't have any method so we have to create one and then it would work so we'll go to the top over here and right after this add item we're gonna create a new method which is called update item and we are passing the ID and as we pass the ID to it over here we also pass ID to this update item method the text section from the text controller and the description section so now we need to come down over here and uncomment this and we should be good to go let's restart our app and then we're gonna click on this and then we're going to change this one to title 0 and over here we're gonna change it to description zero and then here we'll click on this update and as you can see it works well it's pretty much working so update method is working we can also change over here you can change it to test and here we do the same thing test and we do see that it works immediately well then next we need to work on this delete button which we have over here so for delete button we do see that over here we already have an icon button all we need to do create a new method and call it from here so we'll go at the top and we know that right after uh right before this show form actually we can create a new method the method name is delete item and we have to pass the ID for that one well the ID as we can say we saw that we can get it from list view Builder anyway so after deleting we also show a scaffold messenger with a message and then we reload everything remember each time after deleting updating and creating we always reload our items from the database all of them because that's the latest and updated one anyway so now we'll copy this one and come down to our UI section over here and we'll just simply pass this one and inside this we're gonna pass the ID so now the ID once again is coming from here so grab it and put it over here and we are good to go now let's restart it and we see that they're here so I'm going to click on this and we'll see that it goes uh or just going immediately so as you can see we can also remove this we can remove this we can remove this and let's go ahead and create a new one over here it's a new title and new description okay so let's go ahead and create one and we see that we can edit that so here we say title four new description four let's update great delete the earlier one yeah so that's how it works so if you learned something don't forget to subscribe and smash that like button thank you

Channel: dbestech

Views: 60,927

Keywords: flutter sqlite, flutter sqlite login, flutter sqflite, flutter sqflite crud, flutter sql crud

Id: MQhjx1HS_pI

Length: 30min 34sec (1834 seconds)

Published: Fri Nov 25 2022