CRUD Operations with SQLite for Xamarin.Forms and .NET MAUI

create read update and delete are the basic operations that you want to do on a database and you can also do that with sqlite in your xamarin forms and down at maui applications let's check out how to do that first let's have a quick look at what we're about to learn in this video so here you can see an interface with a bunch of buttons we can add to the database update delete get subscribed get not subscribed you will see what that's all about in a minute but we can add here a new name um it's you it's you and you're subscribed of course so we can add that to the database we can select it we can update it with it's you there we go and i can now update it and it updates here in the list and that goes down to the database but i can also select this one and select the lead or i can just get the subscribed ones get the non-subscribe buttons all these things that we're about to learn in this video let's go check it out and here we are in visual studio 2019 for mac on the left you can see the project that i've been using for another video so that is kind of like the getting started with sqlite in xamarin.forms.net maui so if you haven't watched that yet i would highly recommend that you watch that first but you know if you already have a little bit of prior knowledge and you can probably follow along so what i've implemented here is a very minimalistic ui um i love my simplistic designs where we have this little entry box and a checkbox well it's not actually a box our check round and a button to add it to the database so actually let me add some stuff here so let me add gerald and this check box it doesn't really say so but that tells you if you're subscribed to my channel yes or no so maybe it's a good time to check if that actually is the case so let me just check this one gerald also gerald who is not subscribed let me add some other one dear viewer who is of course subscribed aren't you so here we go and let's add another one not subscribed subscribed harry there we go who is not subscribed so now i have two who are subscribed to who are not subscribed and i have a little entry here right so this is going into the database again check the other video and what i'm going to do now is show you more a little bit about how to do these crud actions so create read update delete actually the create and read are already done as you can see here so let's go over to our database cs and i'm not going to go over to the initialization code for the database i'm just going to go focus on the cut actions so here you can see i can just say database table person and it will sort out like what table that is where are my people stored my persons um and i'm going to return that in a list and i'm just going to get them all i'm going to return that to a list and for the same thing i'm going to save this person so you know i'm going to put a person in here and i'm going to insert async and because you know it it knows kind of like what type you're going to put in here it knows in which table where we have to specify it here it knows in which table to put it here that's why we don't need to specify it for like the insert it will sort that out for you automatically but of course we also need or we need we can do updates and delete if that's something that you want to do so first of all actually let's just implement the database stuff right now so that we you know are building from the top back up so let me actually just add here public task i'm not sure what this is going to return yet so let's put in an end here and let's do the update person async first now with as with everything there's multiple ways to do this so you know i'm just going to show you this one thing but if you have other requirements please feel free to do so and what i'm going to say here is return database dot update so we have update all and we can just put in a collection in here which is pretty cool too if you have like a list of records that you want to well a list of objects technically that you want to put in here that you want to update you can just provide that collection and it will update all of them or i can just update this one and it will update that one object and it indeed it returns an int here if you can see here and that probably returns like the number of rows that are actually updated so you can inspect that to see if the operation actually succeeded yes or no and of course like the object that you're putting in here has to have that primary key specified and probably has to have a value so that it knows which record to update in the database so and what we can do then is just put in here the person and that's it boom we can just you know have that person with the updated stuff you put it in this method and it will update automatically so that was pretty easy now let me just copy this one which doesn't make a lot of sense to be honest but you know i'm just a lazy developer so we're going to name this delete person and then i'm going to search for database dot we probably have a delete so delete again same thing delete all you can put in a collection it will delete all of those or you can have just this one and we can say which object to delete so again i'm just going to provide this with the person and i think this also returns the number of rows that are deleted so again you can implement some logic to see if this is 0 or 1 because we expect it to be 1 in this case right and also this object has to have that primary key with a value so it knows which record to delete right so if we go back to our person here i've done this in the previous video i've added this primary key and auto increment to the id field so that it knows like this is the unique identifier for this object and it auto-increments each time when i do here back in the database when i do an insert it will see like hey the id is not set yet i will take the latest id to a plus one and put that in the database so that is how this works now we have the update and the delete let's leave it at this i will add a little bit to this later in the video to do a little bit more advanced stuff to show you how to do that but for now let's make this work first so let's go to our mainpage.example and i have a collection view now again this is just you know a sample ui there's better ways to do this which are probably a little bit nicer for your user but i'm just going to set this to um actually select whoop select selection mode there we go and i'm going to set it to single so now we can actually this by hard reload by saving this oh it loses my records that's interesting i was hoping to to see them here for some reason it doesn't pick that up in hot reload it should show up whenever we restart the app and now i can select one of those in my collection view and i can also do so whenever a selection changed i can also have this event handler whenever a selection is made in the collection view i can do something with that and if i now go to my main page example cs i will see that event right here and what i'm going to do is here at a person with last selection which is going to save like the last selected person in that list and then i'm going to say last selection is e dot current selection and you will see that this is you'll get a little bit of free collection view tutorial here the current collection is a read-only list and this is a list because the selection mode can also be multiple so you can have multiple but in this case i set it to single so i can just get the one item from here as person so cast it to the right one and then we have that in our last selection so that is the thing that i want to do and now that we have it in this last selection thing here i can add back an example i can add a new button actually let's add two um one two here we go so i'm going to say update database it's not correctly entirely correct update database it's going to be update record but you know what i mean and i'm going to say delete delete there we go delete without typos and let me implement some new handlers here so i'm going to generate these just remove the name and then the intellisense will add some new handlers for me so we should have that as well and probably back in the backing code right here what i also want to do is then say if i want to do the update it might make sense to set the name entry dot text is last selection dot name and subscribed dot is checked is last selection dot oops subscribe not checked there we go so now we have this because i already have this entry and this checkbox right they have names so that's why i can reference them here set the text that the is checked and then i can change the values and i can click that update button and it will update the value so we've added a bunch of code so let me quickly stop and restart this application so all our changes are coming in and the records that i put in there should come back up as well so that i actually have some data to work with so our app is coming back up and here we go our records are showing let me add actually a new one so new record there we go at the database and you can see i can select them now right and it selects the right values here as well so let me select this new record and make this new record one and i'm going to update the database and actually i'm i'm going to subscribe it as well update so there we go you see it doesn't update yet because i forgot to implement something in the ui here so let me actually just add that so i know it for the the rest of the things actually it doesn't do anything oh my gosh this should go on the blooper reel it doesn't do anything because i didn't implement the button yet um okay so let me go back to this example page right here and we have this button update database and this is the button clicked so the names are not really great here so let me add a little comment here update and what i'm going to do here now is first check you know let's it's sample code but let's just do a little sanity check here last selection not is null there we go and we can then say app.database because in my my app here my app example cs i have this static property database so i can always access the database in a easy way database and i'm going to say update person async and here i'm going to say last selection actually probably what i want to do is set first last selection dot name because i'm not using any data binding here so i want to set the right values here dot text and last selection dot subscribed is subscribe dot is checked so we're putting the new values into our person here and then what i want to do is basically copy this little bit here again this is not great it's all sample code this is not probably the way how you want to update your ui you want to you probably use data binding and mvvm that kind of stuff i have some videos on that so go check that out if something is not clear please let me know in the comments i'll provide you with an answer and hopefully maybe even a video um so this just you know refreshes the item source with getting all the new people from the database for our collection view and this is a sinkhole so i need to use a sync here and then it's all good then i can actually also await this one so let's do that now this is then the delete so let's just do that so i don't make any more mistakes here so let me copy this thing right here see what makes sense also add the async here and then i don't really need to refresh the values here because i'm just going to delete it so we don't need this and then i'm going to say delete person async there we go and i'm just going to refresh so we see that it actually is removed and i probably want to borrow some code from here and i want to make this empty so that you know the values are gone as well each checked is false so we go back to that okay so let me stop and restart again and now i actually implemented the code so we should see something happening now the app should be coming back up this goes pretty fast there we go and now we have this new record and let's make this new record one and subscribe and then let's say update and boom you can see that it um it says new record one and is subscribed now and if i click this again and i remove this i do update and you can see it changes back and just you know new update so you can see it it updates all the time so that's great right and also if i would stop and restart this application it's persistent because it's in a local database now also what i can do is here um click this one and i say delete so it's actually deleting and it's resetting this form right here so that is how you can do the delete operations so the create and the update the create and the read we already saw those actually so now you know all the crud operations that you can do let's take it a little bit further and just to have a glance at like the more advanced stuff again if you want to know more about that please let me know in the comments if you have any specific needs i will try to answer it or maybe make a video about that because what you can also do if you go back to our database file right here we can also write our own query so if you have like you know [Music] query let's name this query async here we go if you have this like special need in your queries of course within the limits that are supported for sqlite we can also do query async so here we can just write our own queries and we have to specify which type is coming back here so i want to specify a person here and automatically we'll wrap that in a list because it's expecting to have one or more results so it's always returning this in a list basically so what we also want to do here is probably return a list of person then and here you can then write just your sql code so i can just say select star from person because it will just have the table name of this this object name that's here where subscribed is true so we're going to get all the people that are subscribed right and i don't need this this parameter right here of course you can add the parameters in here that's something that you can do with the queries as well so i can just do this and now i can query subscribe let's name it like that there we go and i can go back to my main page example cs actually let's go to the main page.example first let's get a new button in here in my grid design and let's name this button get subscribed there we go again remove the clicked handler let's add a new one there we go new event handler button clicked to that's the name that we're after so let me add a little comment here get subscribed and what i can do now is basically set this item source right here and but this time i want to get the app database get people no i want to query subscribe to async there we go i need to make this async and so this will get me only the subscribed one so this should get me two i have four so it should get me two results right here but before we do let me add another one for you so of course if you've been working with this longer than you know a little bit um with like.net and that kind of stuff you also know link l i and q which allows you to write queries basically in xi sharp code right so that's pretty cool as well and you can definitely use that with this sql lite local database as well so what we're going to do here is say link link not subscribed and what we can do now is not well actually well we don't do query uh we're gonna just table it's just table person here we go so we're gonna get the whole table and on that we're gonna say you know you can do all these things order by you can skip you can take if you have some paging scenario in there you can do where so where is actually the thing that we're after and then in some lambda expression we're going to put in this p which is actually the person and then we can query through this parameter so we're subscribed is false because we didn't we wanted to have the not subscribed right and we want to still return this list because this is going to return a what is it async table query because it's only going to execute whenever you return this to a list right if you want to know anything about link please check the documentation on that so what i need to do here is to list async there we go and now it's going to you know execute this as a query it will probably rewrite it to something like this but now it's you know an all strongly typed c sharp code which is probably more familiar to you so that's really cool so then we have that and we can add another main page examples yes i will add yet another button here we go get not subscribed let's generate a new event handler right here button click 3 who would have thought and there we go get not subscribed subscribed and we're just gonna get the same thing in here collectionview.itemsource is [Music] link not subscribed there we go and let's make that async so it all works okay so a bunch of new changes let's stop and restart and now we will just see it will just work so you know that's always the boring thing about demos it's much more fun if it's not working right but now we have a bunch of buttons and i can just say get subscribed and it will only show me the subscribed one through this string query that i got here in my database cs and i can get the not subscribed which is going through this link query that i got right here and that is how you implement the crud operations with sqlite xamarin.forms or your.net maui application because this also works for dotnet maui i hope this makes clear to you how to work with the crud operations in sqlite for your xamarinforms and.net maui application as i noticed under my previous video is that there is a lot of questions surrounding these local databases with sql lite so please keep those questions coming down in the comments i will try to answer them and hopefully make a video here and there but there are so many comments i can make videos in the rate that the comments are coming in although i would love to but let me know in the comments what you think and of course as always please like this video if you've actually liked it subscribe to my channel if you haven't already done so and i'll be seeing you for my next video keep coding

Channel: Gerald Versluis

Views: 3,433

Keywords: xamarin forms, xamarin sqlite tutorial, xamarin forms for beginners, xamarin sqlite example, sqlite pcl, using sqlite with xamarin, xamarin forms sqlite, xamarin forms 5, sqlite pcl xamarin, sqlite tutorial for beginners, xamarin sqlite, sqlite xamarin forms tutorial, xamarin local database, sqlite and xamarin, sqlite and xamarin forms, crud xamarin forms, xamarin crud sqlite, xamarin forms crud operations, xamarin crud tutorial, xamarin crud example, C#, .net maui

Id: 8_cqUvriwM8

Length: 18min 52sec (1132 seconds)

Published: Thu Aug 05 2021