Adding a Database to Xamarin Apps with SQLite-net

Today, we're going to be adding a sequel light 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.Hi everyone.I'm James Monson Magno.And I'm back with another Xamarin, a one-on-one post.If you're brand new to the channel, I'm James Monza Magno.I'm a long time Xamarin developer, and I've been doing a Xamarin one one-on-one 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 of priority as well.Now I'm going to go into sequel light 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 a 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 I 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, get up repo.This is sort of like a community repo of just kind of awesome stuff that you can have access to, um, that I've sort of listed.All sorts of good things like Xamarin essential, 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 active Ash, cosmos DB, light DB monkey cash, which is a library I created, which is just caching, not a database realm and SQL EIT dash net and SQL I dash net is by my good friend, Frank Krueger, which you may know from our.Podcast together, merge conflict.He also built amazing apps like I circuit 3d and Calca and continuous.So definitely give him a follow up at pro Clara, but his, um, Twitter account down in the show notes, but we're going to be taking a look at sequel light dash net.Now sequel light is sort of the core database technology that is on.Every single device, basically pretty much.And, uh, SQL EIT dash 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 Don net.So you can have access to all sorts of niceties to access the SQL database.So your crowd operations, your create reads updates, and deletes the things that we're doing every single day.This has any synchronous.And an asynchronous, um, version to it.And we're going to take a look at the asynchronous version, cause we're already kind of async defied anyways, but SQL Lite dash.And that's been around for a very, very long time.In fact, Frank and I met, um, at a Xamarin meetup talking about SQL Lite dash net and different, um, Technology to store data, which is funny enough.And here we are, I don't know, eight years later, it's still talking about SQL Lite dash net.So this is a Don at sander library.You can use it anywhere in any Don application, any application, even.Yeah 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 SQL Lite dash net PCL.It is actually a done at standard library.Don't be confused by the name, just a Frank steaming here.And as of this recording, at least we're going to use the latest stable one.seven three, three, five that's available.And this thing has 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 nougat.Okay.And when you search for SQL life dash net PCL, you're gonna get a lot of SQL later if you just search for SQL Lite, but you want this one from Frank Kruger.Okay.So this is the one, um, it's literally by.The 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 about SQL Lite dash NetSuite, that you kind of tie those together.There is one dependency, which is the SQL Lite RA, and this is what.Access as the underlying systems of iOS and Android.So it's kind of cool.It's 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 called this, the coffee service over here and I just scaffolded out very simple methods.Okay.Nothing too crazy.I have an in it.All right.I have an add 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, cause 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 SQL light dash net will handle that for you automatically.So you can have as many, you know, Things as there are injuries.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, um, And 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 the joining together.Now that index doesn't have anything to do with the joining or any relationship at all, what it has that have, um, or what it does is it really helps a database index.The specific property for speed and querying capabilities.So it just creates a different index that it knows to query, um, quickly.So.If you have too many indexes that can also slow down your database, but in general index were appropriate.And note that the primary key is already a index 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 there's going to be the same for all of them.It's just an ID.And I'm going to bring in using SQL EIT dash net.There we go.This is a C-sharp attribute, which is a special property that Frank created as a bunch of different ones, like the indexed and primary key and auto increment of that.And a 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.I know it's just an integer and, and, and this will tell SQL Lite dash net what to do.Now, if we go back to the documentation, there is 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 and almost all my applications.And I've also used the asynchronous API just depending on when I created it.But, uh, in general, one's asynchronous, which has async awaits inside of it.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, this one is that for me, at least I'd 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, my data dot DB.Here.He's going to create a SQL Lite async connection to his database and then create tables.That's it we'll see that he has things like a sank 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, in it, we are going to create our database and our tables.So we're just going to go 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, um, you can actually use, um, file helpers, which is part of Xamarin essentials.So if I go ahead and bring in Xamarin dot essentials doc file.File systems.Sorry.They 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, my data and dot 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 version of it.That's floating around my app.And what that means is I'm going to go ahead and say static, SQL Lite async connection, DB.And we're just going to create at 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 Nall because 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 quarries.I can get things you all sorts of good stuff.Okay.Um, and in this case, we're just going to go ahead and say, create table async and it's going to be a run new coffee.Okay.And we're of course going to, oh, wait 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 wants 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 start-up of my application?I like the lazy load, this thing.So what I'll do down here is I will simply say a weight in it.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.I want to say new coffee name equals name roaster equals roaster.And I do have an image.I'm going to set it equal to image here.Um, because if we go back to our coffee equipment, which had the hard coded data inside of it, Can I 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 that I'm going to await and I'm going to say DB dot insert async.Now there is an insert, uh, 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 one's cause we're adding new records into our database and we'll I'll wait.I also want you to note that this returns a integer because that's an auto incrementing ID.So if I want, I can 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 removed coffee, a ID that's inside of it, but, um, I'm going to go ahead and say DB, uh, um, 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 weight on that.So it's going to look for that primary key.Okay.So that's going to go ahead 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 gonna return all my coffee.So let's go ahead and simply say, uh, await DB.And, um, we can just simply say, um, get async for example.Um, that's a good one.We could say a 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 on, said table where the symbol.Is is a specific item, which is really cool.Right?So when you say table stock, give me everything that's back here and then 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 quarries.Um, we could say in here, we'll say coffee.to, um, 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 going to of course, need to return.Hi, enumerable of coffee.There we go.Perfect.So now we have our coffee.We're returning our coffee and we just created.Uh, 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 quarries on top of it.Okay.Just 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 delete 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 has a list of coffees.So I have a toolbar item that says, ad, that's going to hit this, add command over here.I have the same exact list for you that we've been using, or 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 a 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, and that's really it.Um, I've also added the context menu to delete, um, 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 coffee's refresh command, add command, remove command.So let's go ahead and look at the refresh command first.Okay.So we're going to do here is clear all of our coffees and I'm going to say VAR coffees equals and 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 going to hit a weight on here.And now what we can do is we can say coffee dots, add range.I'll say coffees, the we, and now we have our coffees back.So we're good to go.Clair it out, do some stuff.We're good to go.Let's go ahead and remove a coffee.You guess it I'm literally just going to say, oh wait, um, coffee, service dot, um, uh, remove coffee.I'll say coffee.id.Pass it in.Then I'm going to await refresh.And there we go.Refresh that view.Um, now we can go ahead and add a coffee now, instead 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.Um, take PSAT data and then you guess it, uh, wait, um, coffee, service dot, add coffee, pass it, 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 applications 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 of those dependencies that we may have had earlier, um, with or SQL Lite dash net.Okay, cool.So here's our application, hopefully here.And this isn't the database.Okay.This is just my, my other pages I had, I created a new, um, entry here, which is my coffee.Okay.And it's blank.There's nothing in here.Um, if I go ahead and add one.We'll say yes, please.And we'll just say, uh, oh, that was the, that was the, that was the coffee.Huh?Let's just say tasty coffee.I also do yes, please.Yes, please.Okay.There you go.There he goes.Is it twice hit?Okay.All right.Now this is cool.Okay.So we've created our, um, 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 set a five.And what we're going to see is we have ID one.Okay.That automatically incremented for us.And now if I go back over here and zoom out, or we're going to see if I continue on, is that I have my coffee look at that.And it's one it's right there.Boom, boom, boom.Right.It's good to go.Um, if I go ahead and add again, let's just say.Um, tasty, tasty coffee.And we'll just say that this is from, I'll say blue bottle.Okay.It was good.And add it in there.We're going to go ahead and see that we have our ID back as well.Or 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 the sort of the magic happens is that we remember that we were 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 by swipe it away and come back up one more time.We now have only one coffee in our application.There we go.Pull the refresh and bang as our two seconds.Wait, we have our new coffee.That's right there.And that's it.We have tasty coffee, blue bottle and everything you need right there.For me, everything working offline with SQL Lite dash 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.Now worry about where I'm creating it or anything like that.And it was, I hope you found this post super duper or delightful.I really enjoyed making it.I love using all the Frank's libraries.Uh, tell me what you want to see.You know, my next episodes 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 that have our application navigation, all sorts of good things.But tell me what you're really interested in on your Xamarin and adventure and journey.I'm going to be back every single week, right here on my blog channels to 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.Cause if you do that goes into the Google algorithm of joy and that shares the post with the world.Um, 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/james Monta, Magna, 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 and it was, I want to thank you all for your time.I really look forward to all of your comments below and thanks for watching.