Add Databases to Your App with this Xamarin SQLite Tutorial

a lot of apps have something to do with data loading data saving data do all the things with data so it might be good to have a local database on your device let's go see how to get started with sqlite let's first have a quick look at what we're going to see in this video so here you can see a very minimalistic interface uh because you know i like my minimalistic designs where we can have a name this little checkbox is actually it doesn't say it i didn't add the label but this is if you're subscribed to my channel yes or no and then you can add that to the database so if we do our little person here person one and we check this box or actually i've checked them already here so let's keep it unchecked and do i do add database it's going to add this record to the database and after that it's going to retrieve all the records from that database and show it in this list view right here and that's go what we're going to see how to implement that in this video now i'm showing you in this video in visual studio 2019 on mac you can see it on the left this is just a file new xamarin forms template so that you can follow along and not distract it with all kinds of other stuff in this project so you can see that on the left on the right it's running on the ios simulator right now but this also works in visual city for windows you can also do this on android because because it's xamarin forms so it runs cross-platform which is really cool now let's update the title right here and we're going to name this light getting started there we go and whenever we save this it will update automatically on the ios simulator also works on your physical devices so you can iterate on your designs really really fast which is really cool so now we have that out of the way i'm gonna actually have to stop this project at some point um it's not gonna update all automatically that's coming with dot net hot reload as well which is really cool but for now let's just go to the solution explorer and on our shared project so we only need to do this on our shared project i'm going to right click and i'm going to say manage nuget packages and here i'm going to search for sqlite dash net dash pcl so this is kind of confusing um pcl is not something that we want to use anymore for um you know xamarin forms projects we want to use dotnet standard now and with dotnet maui you just want to use dotnet six but this is the name this is the identifier that has been chosen um so the pcl one back in the day when pcl was a thing so this is just still the one that you need to actually use so you know disregard the name just use this one add the package and whenever that is done um we can leverage the power of sqlite and create a local database on our project so that's very cool now first let's get some plumbing in here so what i'm going to do is again go to the solution explorer and open up the shared project for this time and what i'm going to do is right click add and i'm going to add a new class right here and i'm going to name this person so we're just you know we're going to have some sample data this is going to be a public class person and we're going to add some properties to this so let's just add public int id get set what do we have more public string i don't know name get set there we go and maybe public string no let's make it a boolean boolean subscribed because you know maybe maybe you're you have this channel on youtube that you like watching um and you want to subscribe to it maybe you know maybe that's this channel so um this is a little bit of our sample data so this is just a model that we're going to use to persist some data to and get some data from and what we can do here so if you've been working with databases before you probably have then you know that you have to have this kind of like unique identifier right so the id is going to be our unique identifier our primary key is that what it's named in database land and also we want to auto increment it right so we don't have to think about we have to retrieve the latest record and do id plus one we just want to do that automatically so with the auto increment we can just say hey database this is our auto increment field i want you to do that operation to get the latest record do a plus one and then add that to the new record so you can very easily do that by saying primary key on here and also auto increment now it doesn't know this because i didn't do the using here so let's do that manually using sqlite there we go and whenever i do that you can see that it suddenly understands the primary key and the auto increment you could also do this as two different attributes if that's what you you know prefer more but you can you can combine attributes by putting them together like this so now our id is uniquely identifying our person and it also auto increments so you know that's all set now we can basically move on to creating a class for our database and configure some stuff there so again i'm going to go into my solution explorer my shared project add new class there we go i'm just going to name this database now if you were doing this in more of a like real world project you probably want to have maybe some kind of repository layer in there or have this database as a i database with an interface you can dependency inject that into your constructors and your view models and that kind of stuff this is just to show you how to get started with the databases how you're going to fit this inside of your own project with your architecture um that's kind of up to you but you know as always if you want to know more please let me know in the comments and i'll see if i can answer your question or make a little video about that so here we have the database class and what we're going to do here is private read only sql lite actually let let me add this using right here so sqlite so we can have a little bit of intellisense right here sqlite async connection so this that is the the connection to the database basically and um let's add a little constructor as well database and we're going to put in the path here so db path and because you know the database is just a file on your file system so we're going to pass in the file path to where to find that database so you can also even have multiple databases that you can access basically if that's what you want and we're going to say database is new um oops new sqlite async connection with db path there we go so now it knows you know where to find it and we're going to say database dot create table so you know you have to create that little table before we can actually use it of person and there we go so i think you have a couple of flags in here you can say hey do do some kind of implicit primary key to be very honest i don't even know what all these options are so go check out the documentation for that in implicit index all implicit how to increment primary key so you full text search you have full text search that is available here as well so that is pretty amazing for this little database on your local device so that's a couple of things that you can do but you know very easy and i think this create table um so executes a create table if not exists so it only does it whenever it doesn't exist yet um so it's always good to do basically so that your table you know that it will exist and if it exists already it just skips over this call and it does nothing so it's it's a good thing to have that in here now we will probably want to have a couple of methods that you know get an actual list of people so get list person we need to add these using right here for the task and for the list so let's do that and we're going to name this get people async so we know it's an async call personally i don't like that really to add the async to the methods there but you know it's all up to you and we're going to say return database dot table person and we're just going to say to list so we're going to say here is the whole list all the records in the table are right here of course you can also query with this you can write your own queries in text or you can use link to write some queries again if you want to know more a little bit about the examples there let me know in the comments and i'll make something for that as well this is just a little sample to get you started so let's get busy with the next one and um save person async there we go and we're going to say person person so this methods takes a person takes an object and is going to store that in the table and what we're going to say here is return database dot insert async and i'm going to say oops not i'm just going to say this one object and because it knows that what type of object this is this is a person so it knows that it has to put it in the person table and it's going to insert it and why is it returning an int so that's probably like the new primary key for that person so it's going to return that primary key so you can save that somewhere or you can then retrieve the record with the latest data something like that you can do and you can of course inspect it if something went wrong so now we have all this in place and now we're going to actually you know create a property somewhere where we can reach out to the database now again this is kind of like you know something that depends on your architecture i'm going to go and put this in my app object so in the the big app container that is my app right here so that i can always kind of like reach my database from every point in the application and again depending on your architecture that might not be the best thing to do but you know it's one way to make it work so let's go into our ab example cs right here and i'm going to create some properties some fields here to be able to reach our database from right here so first i'm going to add a private static database there we go so we have a little field for our database here and we make it static so that i can always access it basically without instantiating the app right here and i'm going to say public static so i'm going to add a static property database database to actually retrieve it from here and i'm going to say get and i'm going to make this a kind of like singleton thing so if it already has the database object right here i'm not going to create a new one i'm just going to return that one so that you have one connection that is basically going each time because it's a file on the file system so each time you have to set up that connection it's going to you know cost some time it's going to cost some performance so if we have that connection already and we can keep it open then that is a little win right here so i'm just going to say if database is null then i'm going to say database is new database and i'm going to specify the db path in here so this has to be a path to your database file or is going to create it if it isn't there already so this needs a little bit of system i o using system i o there we go path dot combine and what i'm going to do here is environment so we have these special folders that also work cross platform environment.getfolderpad here we go and you can see there's a lot of them so we have the desktop programs not all of them is going to work on each platform so mind you check out the documentation for that um but the one that is working is like your local where is it local application data there we go um so that is kind of like inside of your sandbox of your application um so there it will store the database inside of your application and all the platforms can access it and we're going to name this like people dot what do i call it db3 i think or something some weird extension like that i don't know why that is but you know db3 is i don't think the actual extension matters as long as the file is you know formatted properly so there we have it then we have our database it's going to create our database at like that folder in the people.db3 and i think now we're just going to hear outside of that if going to return database um so you know if it's null then it's going to instantiate it the first time but if it's not known so it already has an instance it's just going to return the database or it's going to create it first and then return the database anyway so now we have everything in place to kind of access our database to write to the database get to the database so now it's about time that we're going to speed up a little bit shift gears i'm going to copy and paste some stuff that we have um for the ui because you know that's not the focus of this video so i'm going to implement some ui and we can see actually how to you know write records to the database and get them from the database as well so let's quickly dive into that i'm going to go to my main page right here so like i said i'm going to implement this ui by copy and pasting some stuff here so you don't have to watch me type all these things actually all of this code comes from the docs.microsoft.com so go check out the link for reference down in the video description so that you can you know review it in more detail i'm going to paste in here a big stack layout with an entry and actually i tweaked the sample a little bit because here it is doing something with the age so it's setting enter age but let's quickly set this to what is it a checkbox [Music] and i'm going to set this to subscribed because that's what i changed it to a placeholder i don't have a placeholder do i have a label or something here well okay let's just have not have a label we know that this checkbox is going to be used for the subscribe right and we have a button at the database and we have this collection view label binding age so this is also labeled subscribed i need to change that then so this is kind of like let me actually save it and it should show up here we go so here we have enter name so this is like the name of our person right it's going to map to that and this checkbox is going to be like is it subscribed yes or no and then down here we have this little collection view that's going to show the records from our database that are in there right now and this button with this button we're actually going to add that bracket to the database so you can see there is a little event here so let's go to our main page example cs and again i will copy some code here off screen just so i don't bore you with all of that so on the appearing of this page it's going to set the item source to the app.database.getpeopleasing so here we can see we are reaching into that database class here and we're getting all the people inside of that database and we're doing so through our little app right here or through our static database that we've set up here so we can reach into that connection we're going to go to people3db and then inside of that database class we're going to return the whole list of all the records that are in there now for our main page the other thing we have here the on button clicked so if null or white space the name then we're going to you know we're doing a little bit of validation here it could definitely be more let me remove this h entry right here because i've removed that one and we're going to await again app.database.save person async and we're going to save a new person and you can see we don't need to specify that id it's going to do that automatically because you know it's the auto increment and the primary key one and here it was an age but here we have the subscribed and i want to say subscribe dot checked what is it is checked there we go so we are going to add that and then our name entry text is h entry text is string empty uh okay so it's kind of resetting the form so let's do this and we're going to say subscribed dot is checked is false so it resets the form and we can add kind of like the next person and we're going to you know collection view item source so we're going to set the reset the item source to our app database get people async again so it refreshes all the records in there now i've made a lot of changes in code so let me quickly stop and restart the application so we get you know the new get we installed the code that we've implemented right here and of course the interface with all the events that we have right now and we should see whenever we enter the name and click that little box that we've actually subscribed so here you can see there's already one in here because i've tried to prepare this sample a little bit so there was already one entry in the database you caught me and here so i'm not even subscribed to my own channel oh my gosh also gerald let's do this and you can see whenever i check this one add to the database it adds this record to the database and you can see with the name and the checkbox and you dear viewer of course you're also going to subscribe with this checkbox add to the database and boom now you as a dear viewer are subscribed to my channel and i hope that you now know how to get started with sqlite a local database on your device i hope this all was clear to you so what we did is we created a little model our person we have created a database class to communicate with our database file on the file system we've set up a couple of methods to you know put something in there get something out there and we've implemented the ui to actually you know do our crud operation so create read update delete actually not all of those this should be enough to get you started with sql lite on your device which is you know pretty amazing now there's lots of other stuff to cover with sql lite and local databases so please let me know in the comments what you want to see maybe i can quickly answer you with a link to more information or i will be making more videos about this as well because like i said there is a lot of stuff that has to do with databases i hope you liked this video if you did please click that like button and if i haven't made it clear yet i would really appreciate if you subscribe to my channel so please check if you've clicked that subscribe button ding that little bell to be notified of new content automatically and i'll be seeing you for my next video keep coding

Channel: Gerald Versluis

Views: 5,757

Keywords: xamarin forms, xamarin tutorial, xamarin sqlite tutorial, xamarin forms for beginners, xamarin sqlite example, sqlite pcl, xamarin sqlite pcl, xamarin for beginners, using sqlite with xamarin, xamarin forms sqlite, xamarin forms 5, sqlite pcl xamarin, xamarin sqlite create table if not exists, sqlite tutorial for beginners, xamarin sqlite, sqlite xamarin forms tutorial, xamarin local database, sqlite and xamarin, xamarin forms 101, sqlite and xamarin forms, learning xamarin

Id: uxqQqyuZ3Qo

Length: 19min 4sec (1144 seconds)

Published: Mon Aug 02 2021