Adding a Database to Xamarin Apps with SQLite-net

today we're going to be adding sqlite into our xamarin a mobile applications to create a database so we can store data offline so our users can access it anytime so tune in [Music] hey everyone i'm james montemagno and i'm back with another xamarin 101 video if you're brand new to the channel i'm james mountain magno i'm a longtime xamarin developer and i've been doing a xamarin 101 series building out everything you need to know from file new to adding databases like we are today to build awesome mobile applications with c-sharp and visual studio and net now if you are new don't forget that there's an entire series of goodness over here so definitely take a look at that um and also down the show notes there's been tons of great episodes and i'm listening to what you all want in the comments below and databases have been a top priority as well now i'm going to go into sqlite today but i'm going to talk about a bunch of different databases so in future episodes we can take a look at those and additionally see how to access a web service pull down data and store it locally so this is the foundation of our mobile application so make sure that you subscribe to the channel and ding that notification bell not only does that help the channel but you'll get notified every single time and put out a new episode so let's get started all right so there's a lot of different choices for data caching and mobile data storage and over on my xamarin plugins github repo this is sort of like a community repo of just kind of awesome stuff that you can have access to that i've sort of listed all sorts of good things like xamarin essentials shiny a bunch of great community plugins that are available and then here i have some things like data caching and databases that you can use in your mobile app so things like acavash cosmos db litedb monkey cache which is the library i created which is just caching not a database realm and sqlite.net and sqlite.net is by my good friend frank kruger which you may know from our podcast together merge conflict he also builds amazing apps like eye circuit 3d and calca and continuous so definitely give him a follow at proclarum i'll put his twitter account down in the show notes but we're going to be taking a look at sqlite dash net now sqlite is sort of the core database technology that is on every single device basically pretty much and sqlite.net is an orm or an object relational mapper orm i'm pretty sure that's what it is leave me comments below if i'm wrong but in orm for c-sharp and net so you can have access to all sorts of niceties to access the sqlite database so your crot operations your create reads updates and deletes of the things that we're doing every single day this has an asynchronous and an asynchronous version to it and we're going to take a look at the asynchronous version because we're already kind of asynchrified anyways but sqlite.net has been around for a very very long time in fact frank and i met at a xamarin meetup talking about sqlite net and different technologies to store data which is funny enough and here we are i don't know eight years later still talking about sqlite.net so this is a dotnet standard library you can use it anywhere in any net application any application even on the web if you want to but it's all there for you there is a single pcl.net standard version here which is called sqlite.net pcl it is actually a done at standard library don't be confused by the name just frank's naming here and as of this recording at least we're going to use the latest stable 1.7335 that's available and this thing's been around for a while with over 5.5 million downloads so we're good to go all right so let's go ahead and get started the first thing we're going to do is make sure that we install that nuget okay now when you search for sqlite net pcl you're going to get a lot of sqliter if you just search for sqlite but you want this one from frank krueger okay so this is the one it's literally by him 5.5 million and we can see over here that this is linked directly to that github page okay so the author is not frank specifically but sqlite dash net so you kind of tie those together there is one dependency which is the sqlite raw and this is what accesses the underlying systems of ios and android so it's kind of cool this kind of runs on everything and you want to install it in every project that's really important because ios and android have different ways of accessing that core underlying system now one thing i like to do is create a service okay for database access also web services things like that so i call this the coffee service over here and i just scaffold it out very simple methods okay nothing too crazy i have an in it all right i have an ad coffee remove coffee and get coffee so that's it so those are the things that are going to go in here and be returned so let's take a look at the documentation here because frank did a great job of documenting it the first thing that we'll notice is that he has some data that he's going to store in his database here he has a stock and evaluation every single item that you need to store in a database has to have a primary key and these usually auto increment because they automatically go up in value and sqlite.net will handle that for you automatically so you can have as many you know things as there are integers so a whole bunch of them you'll also notice here that he has these things called indexed and in the world of databases that are relational this is a way of relating two pieces of data together usually and in this instance here we have an id and then a stock id so you may query valuations and say let me get all my stack id so that's how they're joining together now that index doesn't have anything to do with the joining or any relationship at all what it has to have what it does is it really helps the database index the specific property for speed and querying capabilities so it just creates a different index that it knows to query quickly so if you have too many indexes that can also slow down your database but in general index where appropriate and note that the primary key is already a indexed item so let's start there before we get into the database because we need to go into our coffee that is sitting over here and add a property so let's go ahead and paste that in and this is going to be the same for all of them it's just an id and i'm going to bring in using sqlite.net there we go this is a c sharp attribute which is a special property that frank created is a bunch of different ones like the indexed and primary key and auto increment or that and that compile time that helps his library figure out that we marked it as a primary key and auto increment so that way we don't have to say public auto increment primary key id that's an integer like no it's just an integer and and and this will tell sqlite.net what to do now if we go back to the documentation there's a synchronous api and also an asynchronous api now in this instance we're going to use asynchronous i talked to frank before recording this episode and he said go all in on the async so i said i will i've used the synchronous api in almost all my applications and i've also used the asynchronous api just depending on when i created it but in general one's asynchronous which has async awaits inside of it and one is synchronous if you're doing lots and lots and lots of database access you probably want to do the asynchronous one just in general the thing to note about the synchronous one is that for me at least i put locks around each of my data access so that way if i was accessing it multiple times i wouldn't run into a deadlock if the asynchronous version of it you're good to go so this is nice we're going to literally create a file on disk okay and frank shows us how to use the my documents my you know mydata.db here he's going to create a sqlite async connection to his database and then create tables that's it we'll see that he has things like async update delete all that goodness so here's what we're going to do is we're going to go ahead and just simply copy this code because why not and i'm going to show you what i do in my coffee service in my init we are going to create our database and our tables so we're just going to go ahead and copy and paste that in there and say yeah that's probably okay that's good if you want a little bit more granular control over this instead of using environment variables you can actually use on file helpers which is part of xamarin essentials so if i go ahead and bring in xamarin.essentials.file file system sorry there dot we have an app data directory which is also something i recommend using so if you want a very granular sort of recommended place of storing the app data directory or a cache directory you can specify those and this will map it correctly there so it's up to you but definitely double check where you're putting your data so that's my mydata.db you can have as many databases as you want so again we're going to create this sql async connection i've put this inside of a static class so i just have a single you know version of it that's floating around my app and what that means is i'm going to go ahead and say static sqlite async connection db and we're just going to create it once we only need to create one connection we can leave it open and access it at any time so that means if db oops db does not equal null because you know we already created it don't create it again all right now once we have access to our database we can do all sorts of goodies so here i can say db dot i can do things like backup asynchronously which is pretty awesome actually create indexes create tables i can do deletes i can drop tables i can execute queries i can get things do all sorts of good stuff okay and in this case we're just going to go ahead and say create table async and it's going to be of our new coffee okay and we're of course going to await on that there we go and we're done perfect now you might be wondering like why do i have this in it like what's the purpose well the idea here is that i only want to ever call it and once to create the database once but i don't want to worry about like where do i call it should i call it on the startup of my application i like to lazy load this thing so what i'll do down here is i will simply say await init and then i've awaited it and now every single time someone calls into my you know coffee service i'll make sure that the database is created all right let's do some crud i'm really excited about it so let's go ahead and create some stuff so we're going to create a new coffee here and we'll say new coffee name equals name roaster equals roaster and i do have an image we're going to set it equal to image here because if we go back to our coffee equipment which had the hard-coded data inside of it i can actually grab this image that we were just using locally so we can reuse it let's put it in there boom all right so now that we have our coffee created you sort of guessed it i'm going to await and i'm going to say db.insert async now there is an insert all async so you can insert multiple things or replace it as well so that's another option there but we're just going to insert this once because we're adding new records into our database and we'll await now i also want you to note that this returns a integer because that's that auto-incrementing id so if i want i could actually say var id equals and go ahead and put the id there let's remove a coffee um if you've guessed it over here over here i have remove coffee i have a id that's inside of it but um i'm gonna go ahead and say db dot delete async okay and here i can go ahead and give it a primary key that i want inside here so i'm going to say that this is a coffee i'm going to say id and of course a wait on that so it's going to look for that primary key okay so that's going to go and say give me that primary key that i've specified and it takes in an object but we know it's an integer and i'm going to pass that in to my item here now for my get coffee this is going to return all my coffee so let's go ahead and simply say await db and we can just simply say get async for example that's a good one we could say query async this would go ahead and let us query all of the different items inside of here if we take a look at the documentation this will really show us how to get it so for example we can create a query that says give me onset table where the symbol is is a specific item which is really cool right so we say table stock give me everything that's back here and it creates this query and we can execute on it so what we can do here is we can we could do all sorts of different queries we could say in here we'll say coffee dot to list async okay just say on this table give me everything back done so we're not going to actually query on anything we're going to say give me all the coffee and in this case we'll say var coffee equals and we'll go ahead and return it coffee there we go and here i'm gonna of course need to return high innumerable of coffee there we go perfect so now we have our coffee returning our coffee and we just created a database let me just be honest with you there it's pretty much all we needed to do we needed to go in create our database right up top up here give it a data path and directory to go to create our connection create our table and we can make queries on top of it okay which is super cool we insert our data we don't need to do anything specific for the table because it knows it's a coffee right it knows that hey this is the coffee table go ahead and give me my coffee give it back right same thing with the lid async this is going to go basically look up the coffee that is in the coffee table and then here we're going to go ahead and say give me all of the coffee on this list and we're done now i have created a simple page over here that is a list of coffees so i have a toolbar item that says add that's going to hit this add command over here i have the same exact listview that we've been using i'm going to have a list of coffees or refresh and display some stuff and for good measure down here let's just go ahead and put the id in small just to kind of put it out there so we can kind of see it in there so we see the name roaster and the id that's really it i've also added the context menu to delete said um coffee as well so we can call these methods so this is in our view model and we can see that i have a few things such as an observable range collection on my coffees refresh command add command remove command so let's go ahead and look at the refresh command first okay so what we're going to do here is clear all of our coffees and i'm going to say var coffees equals you guess it coffee service dot bring in our coffee service over here and now i can say add coffee get coffee remove coffee okay and of course we're gonna hit a wait on here and now what we can do is we can say coffee dot add range we'll say coffees there we go and now we have our coffees back so we're good to go clear it out do some stuff we're good to go let's go ahead and remove a coffee you guessed it i'm literally just going to say await coffee service dot remove coffee i'll say coffee dot id pass it in then i'm going to await refresh and there we go refresh that view now we can go ahead and add a coffee now inside of xamarin forms i'm using this display prompt which will prompt me to add a name and a roaster inside of here so let's go ahead and take set data and then you guessed it await coffee service dot add coffee pass up the name pass it the roaster and await refresh and we done that's it i'm just going to call those methods and with everything all said and done our database is ready to go our view model is calling into the service so that way that database logic isn't locked into our view model and now our application should be ready to go so let's go ahead and bring up our emulator here and again since sql lite is included in android and ios and windows all these things it's going to work directly in my emulator which is really really great and of course it's going to bring in all those dependencies that we may have had earlier with our sqlite-net okay cool so here's our application hopefully up and running here and this isn't the database okay this is just my my other pages i had i created a new entry here which is my coffee okay and it's blank there's nothing in here if i go and add one we'll say yes please and we'll just say well that was the that was the that was the coffee huh let's just say tasty coffee let's do yes please yes please okay there you go there it goes added twice hit okay all right now this is cool okay so we've created our coffee item here so we have yes please yes please that's very unfortunate that i did that but it's there and let's go ahead and add another break point down here and just hit f5 and what we're going to see is we have id1 okay that automatically incremented for us and now if i go back over here and zoom out or we're gonna see if i continue on is that i have my coffee look at that it's one it's right there boom right it's good to go if i go ahead and add again let's just say tasty tasty coffee and we'll just say that this is from let's say blue bottle okay let's go ahead and add it in there we're going to go ahead and see that we have our id back as well for the coffee we have our coffees that were returned one two okay one two coming back here i'll go ahead and zoom back out and we see one and two coming in here yes please yes please so that's very very cool now if i go ahead and simply stop debugging this application and launch it again this is where sort of the magic happens is that we remember that we are storing these coffees into a database which means that when i pull to refresh and query the database there's my coffee okay everything is there for me if i go ahead and hold down and hit delete we're now going to see that boom there's our coffee again it's gone if i swipe it away and come back up one more time we now will have only one coffee in our application there we go pull the refresh and bang after our two second weight we have our new coffee that's right there and that's it we have tasty coffee blue bottle and everything you need all right there for me everything working offline with sqlite net it's really really that easy to put data into your mobile application i like to use this little service approach in my application logic so i can really just take that database put it off to the side not worry about where i'm creating it or anything like that anyways i hope you found this video super duper delightful i really enjoyed making it i love using all of frank's libraries um tell me what you want to see you know my next um episode's upcoming we're going to take a look at pulling some data off the internet and putting it into our mobile application we're going to take a look at searching we're going to build out different tabs out of our application navigation all sorts of good things but tell me what you're really interested in on your xamarin adventure and journey i'm going to be back every single week right here on my youtube channel so make sure that you subscribe and hit that notification bell and oh don't forget hit that thumbs up button i keep forgetting to mention that but thumbs up and like it up because if you do that goes into the google algorithm of joy and that shares the video with the world or something like that who knows and don't forget that every single friday or just about every friday i twitch stream at twitch.tv slash james montemagno where i build out real world applications live i just keep going every single week new things currently building on a clone of a fitness application based on peloton so definitely check that out anyways i want to thank you all for your time i really look forward to all of your comments below and thanks for watching [Music] you

Channel: James Montemagno

Views: 25,981

Keywords: database, xamarin forms, xamarin, ios, android, uwp, .net maui, sqlite, litedb, sqlite-net, xamarin.forms

Id: XFP8Np-uRWc

Length: 23min 13sec (1393 seconds)

Published: Thu Mar 04 2021