Learn Live - Store local data with SQLite in a .NET MAUI app

[Music] thank you [Music] oh my goodness we're back part seven in this series and James you changed a little bit yeah I know I know I cut my hair and stuff changed your name you you gotta you got an accent and everything Frank how are you very well very well happy to be here yeah I'm happy to have you here too so James is off at a conference and my good friend Frank has graciously volunteered to help us out with uh part seven of talking about storing data in a sqlite database on your Maui app so Frank why don't you tell us a little bit about yourself well I'm A Cloud Advocate I know.net but for net Maui you know when when you reach out of my first thought was like oh yeah I should I should play a little bit with.net my way I've been watching your video with you and James and stuff but last time I did some mobile development I was I was bringing the dab so data access data access layer block I forgot the acronyms uh but on compact framework so it's been many many years close to like 15 or something years ago so it's been a while so I think I I'm I need to refresh so you know why not learn it live with everybody here we go yeah exactly yeah so Frank yeah mentioned that.net compact framework which I think was like early 2000s it was like both Frank and I were whippersnappers when we were Young and um so that was yeah a super long time ago and so Frank unfortunately has not been doing.net Maui yet I want to though yes I just want to and we're going to teach them how some.net Maui and some data access today and he's going to learn live with us which is going to be super super duper fun I think so that's the purpose of the show right learning life so yeah I think I will have plenty of questions I I did have a look so you know okay I have some you know some image to preserve but uh I like to learn online so I think people who know me know that yeah yeah yeah so I think this will be a lot of fun as we go through today so um Frank Matt and um of course you can follow along why with these modules at this AKA Ms link learn live Dash 2022-1116 a that's always fun to remember Laurent did a great job setting up those akas so 2022 1116a you got that prank yeah yeah sure yep and say hi to our moderator John in the background John is um he'll send us the questions he'll answer the questions and uh John's taking care of everything in the background so let's say go say hello to John um he's doing a great job in the background and um yeah he was saying that he didn't recognize me oh no [Laughter] so again we got that short link to go see 2022 11116a and uh follow along if you don't want to follow along and it might be better if you didn't lie because Frank and I will probably go off course a couple times just talking about various related topics but um for afterwards when you wanted to go back and recap your learning this is where to go to when you're storing local data with sqlite in a.net Maui app the link to get there and of course that little QR code I'm sure will get you there as well that's at the bottom of your screen and Frank I'm springing this on you I kept it from you yesterday when we talked and this morning when we talked this is going to be brand new to you we have going on what we do we have a continue the conversation and so what happens is after this learn module everybody can jump into a team's conversation and talk to us live so they can actually give us their feedback and really just adds questions of what's going on you know kind of get more in-depth questions and Just Bounce them off to us so just not shooting chat questions like to answer during the session but for afterwards and um everybody jumps in a teen conversation we can say hi we can see each other in a teams chat and the AKA for that is LL CTC learn live continue the conversation and uh today's gonna have to be a little bit of an abbreviated when we're gonna have to cut it short at 15 minutes but yeah jump in say hello after the session here not not right now but um and we'll put this slide back up so you can get that short link again so yeah if you can jump in please do so we love to talk and we can answer we'll try to answer I should say any question at all not just related to data storage in Maui but um any monthly questions or any questions maybe even on the.net compact framework from way back well you know what it was my last experience with mobile but I'm curious to see maybe I'm not the only one like in the chat let us know what was the last experience or your experience the latest experience you have with mobile programming is it is it that compact framework is it like a xamarin is it Maui maybe you're all Maui expert I don't know maybe maybe I'm not alone in that case so let us know curious that's a that's a good question or if anybody's even like making Progressive web apps and let us know that too or you know maybe you're doing Swift UI work or or caught one yeah really interested and see what see what people are building with it or maybe you're just shooting for the mobile web and making your apps responsive love to know what what folks are doing when they're designing for the for the small devices yeah yeah I I heard the math about that uh get together after but I I will be my first to join so that's a lot of the first today so first yeah my way experience first joined to that uh after call that will be cool you had no idea what you were saying yes to you yesterday I'm bringing my questions yep next time you see a little message that pops up for me on teams chat you're gonna be like nope turning this off ah come on oh good there's uh tons of experience Android Studio pay wa examarin no mobile yet c c yep I'm not the only one all right thanks for the answers yeah of course yeah so yeah I think these are a great ways I mean I don't think you need to know mobile at least go through these courses because we'll we'll still take you up even we have all seven of these learn modules but you can Jump Right In into the middle ones and still pick up what what you uh what you need to learn to get going so so let's do that Frank should we let's cover what we're gonna we're gonna show today yeah let's share that yeah so we're gonna be talking about data storage and there's a bunch of different types of data storage that we can talk about well at least as far as like.net Maui apps go there's really three main types that we can do so we're going to compare them and we're gonna talk about them give you the pros and cons of each and then though we're really going to dive in to SQL Lite um super cool and we talked a little bit Frank you and I this morning about what sqlite is and it's um single lights all over the place you can use it on the web you can use it on device and it's super small it's portable and it's just it's an amazing little database that can just really go anywhere it's a little database that could and it does and then because mobile apps need to be asynchronous um you don't want to have your users waiting for essentially file operations and that's what really database operations are they they kind of are like file operations you want to make sure your UI remains responsive so we're going to show you not only how to interact with a SQL Lite database but we are also going to make those interactions asynchronous so your app doesn't look like it froze when it's just doing some database writing or retrieving so that's important make your database or go ahead Frank I'm just saying keeping your user happy yes yep keeping them happy and even more important is that if your app is unresponsive for a certain amount of time iOS or Android or when windows will crash it it'll think that it's it's become you know unavailable so by making those calls asynchronous you're keeping the operating system happy too and then keeps on going so worst thing you can do is make your app crash yeah definitely then a good practice yes so a synchronous so we'll get to that too so of course when you're building mobile apps you're going to want to store data uh locally we can Accord last uh episode Frank we covered on going calling um rest services to get data yeah but we don't want to probably do that all the time because that's you know you have a little latency that's involved going up and calling the internet all the time and bringing data down So eventually though you probably can cache some of that keep it local on device or even like when you distribute your app you might want to have a database or file preferences or something pre-loaded in the bundle when you download it from the store and things just is faster right nobody's ever complained about something big faster nope so and there's a couple different types of storage options that we have there's um preferences there's a file system and then there's the database all right and so the preferences what that is is a really lightweight of storing data like in key value pairs you can almost kind of think of it as a dictionary like a.net dictionary you can kind of think of it as a dictionary stored on device like in the device devices hard drive but so it's just like a key value pair real lightweight just on on the device and it goes with the app so it's a good way to store um like have you agreed to the license terms or do you want to have a light or dark mode stuff like that so okay real lightweight and then we move to the file system and so the file system's a little bit more heavy and it's just like your hard drive essentially on your computer where you can put like images if you want you could put like longer text files if you want on there so it's it's more loose that way it's kind of I don't want to say it's unstructured because it's a file system of course it's going to be structured but it's for larger files and it's for more I'm trying to right now Frank think what I have ever stored on the files system other than like images and stuff like that to bring up so I guess images are are a good images are definitely a good one could you like if you want to kind of serialize your status yeah that would be good right like you have like this this open or like it it's not commit to like the database but it's more like what was the status of the app so you could save that maybe enough yes you know actually that's a good that's a good point so you can have essentially you which you would take let's say we had you know Frank's yeah that's a good like your status your current status that you had in the apps like so you're where you exactly were working on let's say or I'm let's say you're working with Macedon now that everybody's working with and I'm just kind of making this up so what you can do is like kind of store the various timeline of where you're at serialize that into Json put that into a Json file that way when it starts up you can read up that Json file real quick and you can have that timeline there right away if that makes sense so that might be something you could put on the file system right away so a nice way to have bring yourself back up to where you were so that's a very good point Frank there's something that you can do um or even stuff if you would download like if you're going out to a web service and you wanted to download uh essentially everything that comes from a web service is going to be in Json these days take that put it in the Json file pop it into file system and if you need to bring it back out for re re-inflating your status System state there you go so and then the final one and the big one I would say is the database and um we all know and love relational databases um Frank I'm sure that's where your background is on relational databases SQL Server um at least that's where I've come from actually I came from Oracle the Oracle I came from uh yeah I did a lot of Oracle you know what the first uh thing I did as a business was on tandem so the database used and yeah it's a very specific time like that tandem is the OS it's the machine is the database it's used for lottery and Banks and like every stuff wow yeah I never heard it that sounds interesting it's very old that seems to me the theme of today's we're very old but that's why I decided to uh use it and I did a lot of databases but it was relational and uh yeah as soon as I could I jumped on.net and it was fun yeah yeah so my first database was coming out of school was working with this old Erp system called PeopleSoft oh my God and so people saw up as like this you manage employees with it essentially like an HR System like and uh but it ran on Oracle and so I actually became an oracle I was on my way to become an oracle certified database administrator working with that uh what was it forms and uh that was the UI and I forgot the name yeah I did a lot of it yeah what was your first database in the chat I want to know like what was your first experience with database we we could go in all Direction with that like there's many kind of data store database yep yeah and I had a look to uh SQL like it's way more simpler than writing it like I think back then I was using just like flat file just like a single file and just like having some kind of structure in there well I mean we could even say like text files like CSV files could be something of a database but definitely and that's maybe a good way for your file system to come up you know it's super easy just throw a CSV file or a super Json easiest Json file throw it out on your file system and that's can kind of store as a as a super easy database that you if you don't want to have a full full-on relational one yeah it could be just you know if you have just like a few settings you just want to save what was the last whatever maybe you have an incremental I know for like my reading notes I have like just a few settings like what was the last one and like where's that path or like what like have just like five settings I don't want to have a database for that so like having a file Json file that would be good yeah or we can even have list in a CSV file the various databases that we've worked with since we started which actually that might be that might take up all the memory on the device but you know my favorite database so frankly before we keep on going talk about preferences is when I worked in a company that used an axis database as the front end to get at an Oracle database so the company was powered off of Oracle but used an axis yeah so I'll just leave that there so anyways let's talk about preferences yeah so preferences are neat so what they are is just super simple like if you need to say I've already logged in if you need to say that like for booleans or if you want to do light or dark mode or even like strings like your username is Frank you prefer to be called Frank and you want to have that on you know like have the app say hello Frank or hello Matt preferences are a great way to say that so all you're doing is going to say preferences dot set give it a key name and then the value for it and then you can get it out the same way preferences.get give it the key name and then a default value just in case it doesn't exist yet in the preferences essentially a dictionary file and it brings it out now what's cool about these preferences is that back in the xamarin forms days preferences used to be included in the um diamond and such Essentials but now they're built in to essentially you can kind of think of them at in there they're in the Maui Essentials but they're just built in you don't have to add an extra nougat to it you can just get at them so that's kind of neat because each platform iOS Android or Windows or Mac OS even implements their preferences differently underneath the hood but we're abstracted up a level so we just can say preferences that's set and know that underneath that it's going to take care that Maui is going to take care of the operating system specifics for us so it's just set and get pretty cool I think yeah it cannot be easier than that yeah yeah so the API yeah wonderful so um and but the key thing about preferences is simple really you're just going to be looking at um um the primitive data types such as ants booleans don't be putting uh big things in there like huge strings or lists or collections use the file system or database for that but Frank I told you this this morning I do not adhere to this rule so our our viewers should but you know a lot of times what I'll do is if I do have like a collection of you know something that comes and it's not huge but I will serialize that over to a Json string and I'll throw that into uh the preferences probably shouldn't but sometimes I do like I don't know like the type of device or like like the type of the let's say you have a list of color possible color for your app you will put that in there that's what you mean yeah something like that where it's a little bit Overkill to put it into a file or into a database yeah you know so you do have you know like yeah a list of colors or something that were that are possible colors that you want to have for themes okay I'll throw that I'll serialize that in a Json put that into a string and pop that into a preference okay so it works but it's not the best practice so like yeah you didn't do that you shouldn't because if you start throwing a whole list of things in there like you have a really hard or really complex object hierarchy you're going to start getting in uh issues performance issues and such so it's not meant for that but simple things you know a simple list yeah you can probably get away with it I'm sure you're doing it just for you know the purpose testing the platform make sure it still works correctly right I'm doing that for out of pure laziness Frank I was trying to help you but if you yeah I know you were I'm honest if nothing else I am purely transparent and honest and all this but no um you can get away with it when it's small but you should you should probably think about using a database when it comes down to it when it gets you know just a little bit larger so all right so then let's start talking about the file system and and the file system on a mobile app is going to be it's you're it's going to be feel very familiar to whenever you're writing to the file system uh if you're writing a a normal app whether you're writing a console application or if you have happen to be writing a web app and you need to access a file system to for whatever reason the you're going to do it the exact same way as you would it's all.net Frank it's just.net it's wonderful yeah so I mean you're just using system.io and um you're doing write all text or you're doing read all text you have all the file you have all the directory classes available to you and there's really nothing special about reading or writing to the various operating systems it's all just.net so that's cool there's really nothing if you know C sharp and you know how to use system.io you're good to go yeah nothing and my favorite part is on the next slide I think yeah yeah that's wonderful I didn't have that back then that's cool yeah so what's neat is that there's um what happens is that each application on the device is sandboxed and what that helps is is that I can't write Matt's cool app or let's say Matt's bad app and then have that Matt's bad app act right into Frank's good app um sandbox they're separated right so essentially you can think of each app has its own mini file system within it and that's way the way iOS Android made it so like the apps can't overwrite each other's files and such so they sandboxed them and made them completely different or isolated essentially and so you can't it's a way to help from uh malicious actors yeah it's definitely secret more secure this way exactly exactly and so still though you're going to have a full like a URL or a uh the file name or the whole system path name you still have to get at that you might not know what that is when you are saving that right so you just you just can't say Frank's app there's going to be more to it than that and so this that's where this um abstraction comes from the file system app data directory so what that is is just a essentially a way for our apps for our code actually just to say all right you know Android or iOS just tell me just tell me where I what this is where I can put my data and it returns it so it's a way to like stay for us kind of a relative Pat but then use that to make an absolute path because it needs to be an absolute path so but I don't need to remember or do some check or on iOS it's there on Android it's there on like Windows it's there just use this and I know I'm in that sandbox I was about to say bubble but I should use the the good term I think sandbox I think bubbles better but yeah exactly exactly so you still need yeah it just gives you it just and it's great because we we don't need to know anything specific about the underlying operating system this tells us everything that we need to know it yeah it takes care of it for us that's wonderful which is you know we've been talking over and over again through the previous um episodes even is that how Maui does abstract it all up for us where we don't necessarily need to know anything about the underlying operating systems because Maui takes care of that which is really nice yeah and yeah well this is exactly what we were just talking about so it it's an abstraction over a device specific path so whether you're running Windows iOS or Android we can reference the sandbox and not care about where it's running when we're typing we don't care when it compiles and runs let it do it let it do its thing so cool but ah iOS Frank I know you're a big big fan of iOS yeah sure so I I know they do great stuff I just don't hone anything well it's not true I I own one thing the haircuts but uh yeah I know they do they do lovely devices like it's very powerful it's just like yeah I've been I've been giving them my money ever since 2008. and so the problem is the problem the features that they give us with iOS is that they have very specific things so when you're generating files Apple says you know what I want this file to go in a specific folder so if you're generating any data from the app it's saying okay data you generated from the app you have to go into a library folder within so you're in your app sandbox you have a library folder and then so anything should go in there right for that you're storing but if you're storing user generated data that has to go into a documents folder so it's okay so if for example earlier the little Json document that that will be in the specific place or sandbox but let's say I'm saving a markdown that I generate the markdown in my app and I want to save that markdown so that markdown will go there yep that markdown is going to the documents folder because that's you can think of the documents folder like the my documents on the on a Windows um and within windows so it's anything that you would actually create for the user it's the user's files yeah well that makes sense I think like then after that outside the app you could you could see all your documents so yep yeah exactly and then the library folder is like for app specific like lower level files that aren't necessarily for users so um I'm not sure if the preferences get stored in the library or not but preferences might be something that gets put there you know so it's like the user's not generating it but the file itself that the preferences get put into are in the library so here's where the important thing comes into is that when you back up like the iCloud the documents folder is the thing that gets backed up so it's not you're not backing up the entire app you're just backing up the things that need to get restored to that app that makes sense yeah so that's a way that you can actually Store Safe space on your cloud storage so and that's why you have to put them in specific spots so just there's a reason for it it's just that you have to um know about it essentially that's good yep and to get at that special folder the very last line it's environment special folder my documents will return the documents folder for you so just remember that you need to know that we just need to do and tab the tab tab and we're fine yep that's pretty much it so yeah so that's iOS just a little bit something extra to remember um for doing it and yeah here's probably a little bit better um of what we have to do to get that document folder a little bit better View yeah what a little bit of a touch of color with the touch of color yeah yeah I like that probably single space model what's that called Frank the uh the fonts that are all each letter takes up its own that's monospace yeah or mono what yeah good question yep well if you know if any of our viewers know and what's your favorite coding font I like to know that too because I'm always like on the lookout for new coding fonts so all right so let's test our knowledge Frank okay we're gonna see how much you've been paying attention Frank or if I I think I think I will be good for that one yeah I think I've just been rambling too much all right all right okay Frank so let let me put this out to you suppose you've been building a net Maui application for iOS that enables users to create music that's a pretty big application good job Frank all right so you're building a net Maui for iOS users can create music and you want the users to be able to save their work to a file so that they can restart the application and continue working where they left off in the iOS sandbox where would you save this file to music directory the documents directory or the library directory so please go out and vote at akms slash polls-16 and we'll see what it did as well as Frank does well I should yeah I shouldn't should I [Laughter] tested secretly first yeah you know I I see what they did there you know putting music in the choice though yeah tricky yeah tricky but we didn't talk about the music folder so that's just kind of a it's a red herring we're trying to trick you with that yeah but it's in the question yeah I know yep yep ah uh by the way like you had a lot of uh friends with the oracle's database earlier also oh yeah good good I don't feel alone yeah well like I love our records I I did a lot of that too at school and everything yeah Oracle and SQL Server are very similar yeah um well you know it's SQL yeah yep I did MySQL I I touched too many databases so uh maybe I did try sqlite not sure it would be very familiar to you I'm sure um yeah just SQL so so you're building an app that lets users create content so we know so we can just don't worry about that you're doing music so we cross off the music so they're creating the content and it's coming from the users so the library folder is just like if the app is creating some system generated content that's not really supposed to be for the user's um like it's just for the app itself it doesn't really matter to the user so that's where the library stuff goes so really when the users are doing something files go there into the documents because you want that backed up then yeah and that's one if it was a system like if you go to library that won't be backed up right right right yeah so the idea is stuff that goes into the library directory you can recreate ideally without the assistance of the user but something in the documents directory I wouldn't be my app wouldn't be able to recreate any music files that you created Frank because that's your Genius Behind the Music files and my app wouldn't be able to figure that out so it should be called Noise app noise yeah well the White Noise files yeah yeah here shh yeah that's your question in the chat uh Matt uh we talked about those three types and focusing on the SQL Lite but um I have someone asking if we can use light DB for my Maui app light DB that's a good question and I honestly do not know the answer to that question so whether Light TV I know you can use couchdb okay I do not know if you can use light DB and I'll find that out by time we get to the um after party or continue the conversation like is it something you install oh it's a another file database that'd be yeah my yeah so if you can use light DB within a different like if you can access it via Swift UI or if you have a kotlin way to get at it then yes you can use ytdb for within a Maui app but but you would actually have to make sure then that you're going to have the essentially the nougat package the c-sharp wrapper to get at light DB yeah so I mean the the good connection or like yeah to be able to interact with it otherwise you're gonna have to create your own bindings to get to it yeah so that's a good question thank you yeah that's a really good question so I'll take a quick peek um after this session but yeah so that's that's a quick answer if other mobile languages like kotlin or Swift UI can get at it yes Maui can get at it but we'll need a Maui essentially nougat package to get at it so and there might be one out there there might be one out there so all right so you want a small Frank you want to get out a small piece of data so that's accessible between application watches right so which option is the most important I think that that's one I think it's a little bit easier than the previous one okay yeah right so it's super small we're just putting it in somewhere um by super small I want to say it's going to not be a huge object graph it's just going to be like a true or false or an INT so obviously relational database probably going to be too much I I think all of them would work but we're not where work we're looking for the best practices we're a more efficient way yes yeah the most efficient way yeah the most appropriate here yeah and so really uh the file system and the relational database probably are just too heavyweight they would work for sure but um yeah I think preferences was a yeah that was my first like I didn't date much for that point the other one you know I was not sure because they brought music and I was like oh did I miss something previously but that one was kind of like tricky yeah especially when you say iOS and music too like you're saying oh Apple has its own music service maybe there's something yeah yeah tricky so yep but no preferences for the um for for quick launches it's super easy to get at and then when you're building iOS applications for user generated content put it into the documents directory great all right so now we're going to start talking about the fun stuff right we don't review the score of our audience oh we could we could I think it was great I think everybody got the second question okay everybody you did a great job on that one and still uh I think uh 70 found the first answer so that's pretty good so as a teacher you pass [Laughter] I didn't know we were grading me but thank you for putting me of course we are what do you think oh my goodness yeah oh so from here on out I will be giving away the answers while I ask the question oh thanks anyway John is doing it also in the in the chat thanks John I was just curious you know like I think we're you're we spend five minutes on a quiz like I want to know the score so yeah good job everybody yay oh my so all right now that I know I'm on the clock here and being graded we're going to talk about storing data suit with sqlite we're going to keep like a class roster and like how well I did so all right so anyway sqlite Frank this is you know it's our database it's our database on board and um the thing about sqlite is that it's it's a relational database it's actually a full-blown relational database it's not you know I wouldn't say say I'm as powerful as SQL server or Oracle or something like that you know it's not a Enterprise level database but you know what it it's the little database that could and a little bit database that does so and it's cool that you can just have it on device and honestly it's just a file it's just a DOT DB file as well so long story short when you use a database when you have data that's going to have relationships essentially and and you know what you can do super cool things we'll show you like you can use where's where Clauses and stuff like that on it where you can actually go and use more higher performant and like filter things out like you know just just get the people who got the right answer on the quiz you know just bring their name sell it or something like that so yeah and uh well here we go useful when you do have relational data and um it does you store it into a file and but you need to put it in the appropriate spot which is going to be in um your your directories that we just talked about and um dot documents directory for for um iOS because it's going to be user generated content and it's super cool because it is cross-platform and it's lightweight because like I mentioned it's just a file but you could still do so many things with it that's pretty cool yeah yeah so here's the thing and this is where I was kind of talking about before where um with light DB is that like for SQL light it is a whole it is a database engine like so there's a database engine on board iOS and there's a database engine on board for Android that are sqlite engines they're there but we have to be able to interact with them somehow and that's where the the apis the c-sharp nougat packages SQL light net comes into play and so we use sqlite.net axis they get at the database engine so we can access that database file so we can do all the good goodness of relational databases like select top 100 or select where from all that stuff so even though the database file there's just a file there's still an engine on board in your operating system that gets at that and that's you know C and C plus plus writ so so instead of p invoking over there we do have to use uh nougat package and so there you go and so for the light DB question do we have that square in the middle that does the white DB stuff for us that's that's the question um and that's the question I'll try to find out the answer to so there you go just need a nuget package to interact and we should be fine if it exists yep and assuming that light DB provides the on the left hand side the database engine that runs on device so and here's how we would uh how we would connect to that database it's called um if we're using SQL lightnet SQL Lite connection and then you pass it in a file name of the database that you're using so we call our Frank's cool database.db we just pass that in there and ta-da we have a connection to that database so it looks kind of like a lot of like the old um remember doing ado.net yeah kind of like that it reminds me of where you just knew up a connection and away you go but a in Ado you had some credential here you don't have that right it's a file it's a file and it's wide open right well you know like they're it's it's just SQL light so of course it doesn't have all this the bells and whistles right uh yeah okay that's cool yeah so like it's just the connection string it's the path yeah yeah when you think about it though is you're on device you know so Frank you don't have access to my phone so should I be protected from you right so yeah so but that's a really good point yeah there's no no um Security on it but so you'd have to do your security elsewhere if you're gonna put security into it so yeah that brings up a whole other ball of wax of what data you should actually store onto your yeah there's a whole philosophical question there so but anyways they get a connection that's what it looks like just new up a new SQL like connection passing in the file name of where the database would be all right we have a connection to it and it's just a file name yep how do we model the tables though that file in this in the beginning it might not have any it's just a file it's probably going to be empty it probably you know how how do we how what comes first a chicken or the egg the file or the tables you know how does how does this stuff happen Frank and um the answer is we can make it all happen through our code we can just take an empty file no file at all create the file and put all the tables in there and this is how we would do it plain old CLL CLR objects pocos Poco and it's just a user class throw in an ID throw in a username you're just creating properties your model just like properties yeah yeah nothing nothing fancy here actually real simple just doing gits and sets and what's cool here is that for each of those properties you can specify attributes on those so we can overall specify on the class level what the table name should be for that class so if I go back here to the user I can put on on top of the public class user I can put a specify an attribute there saying this class should be known as matte or this table should be known as matte that'd be dumb to do but I could do it and I won't be very friendly but right otherwise that's just going to take the table the class name and make that the table name in the database um otherwise I can say which column is a primary key by putting um a primary key attribute on it specify whether it should Auto increment and then if I want to map the column name to be something different other than what the property name is I can do that say how big I want it to be in the com in the database whether it's like 10 characters or 150 I can use max length and then unique it's very similar to what one would do with um if you're modeling with an ID framework yeah that's sort of a solidar like attributes square brackets on top of the the method and you have that that's pretty cool yeah yeah and is that the name Matt you say like you know obviously like putting name for the class user won't be a good idea but then they if in this some company they say oh we use plurals for the table's name and like that's when you can do those those little uh is it segmentation differences like you could specify okay like I need to go in the database and it needs to be plural right right and so a lot of the times like we see here we're using the class user public class users saying all right table name give us lowercase user and so like if your company does want to have strictly you know table names and primary and column names where ID here is underscore ID and you want that to be your you know it's your what your primary key names are you can do it and so if you wanted your table name to be users there you go that's how you would specify it up up top underneath oh okay at the table so you don't need to but it's there if you if you want to there you have other requirements not technical but you know business Biz yeah exactly and part of that though is it is on device it's not like you're using an Enterprise database but still you want to probably want to make sure that you adhere to whatever your your database administrators come up with because there is a benefit and and having consistency across everything so even if they never actually talk to each other it's still nice to be consistent so and that's how you would make them consistent because sometimes you want your if you're you wouldn't want to have a single class called users when it just models a single user yeah but then like when you stack them in a table it will make sense yeah yeah yeah and any framework actually takes care of that for you a lot when you're when you're doing a migration it'll actually call the table users I think it like it pluralizes things for you automatically SQL light doesn't do that you would have to actually call it out users so don't quote me on that but I think NAD framework does the pluralization for you my feeling is by default it does but it's a I think you can turn it off if you want yeah you probably can so yeah anywho that's that's another talk yep and you can use NAD framework Within within uh within within apps but again we're not going to talk about that okay okay so anyways this is how you would model the table you're just taking your regular uh CLR classes a plain old class and you're you can't put attributes on it you don't have to but here you go all right we're good we have this connection object right and we have our we have our models and that are essentially just modeling a table how do we interact with it how do we actually start pushing things into a database right here that int result line you know what I'm going to use the super cool laser pointer right here yep c-o-n-n dot answered our Conn is our sqlite connection object yeah just say dot insert pass it to user that's going to insert it into the correct table for us seriously how nice is that easy yeah super easy and it's going to turn a number of rows that were inserted and should just be one here so yeah and we don't need to pass the type of like it would know it would know I mean it is a generic type so we could do insert uh um bracket user bracket but it's going to infer it just by passing this B okay by the way it will be more clear maybe as a developer but right make no difference for the code okay that's cool yep and then getting oh and here this is what you were kind of mentioning before Frank so we're going to say table of users so we're saying hey give me that table of user types and just give me a list of them and pop them into the user's um list here so here we're actually saying all right I want the table of user users give me all of them just like yep and so let's just pull them all back popping them into a into a list and returning them wonderful so it's yeah it should feel relatively familiar if you're as if you were working with um Collections and such and when you're retrieving roast in the data from a Taylor you get the power of Link um added so you can do where Clauses or skips or ticks or ordering by you get all that there so you get I mean you don't have to just return all and then do filtering you can actually do table you could do table user dot where dot to list so it kind of it does this filtering uses the database engine to do its filtering first so that's cool so instead of like bringing all the users and then like in the code somewhere like do that you could just like do it straight in into the database engine yeah you can just say hey sqlite do it for me so yeah or if you didn't like doing you could do it this way if you like writing now Frank you and I kind of were wondering and hopefully our our audience can answer it for us so when we write it this way or John can answer it for us I know for sure John Galloway can answer it for us when we write this way from you and connection table that user where select this is this link what's the proper way to refer to that's link right where down here is this fluent where we say first or default what's the proper terminology I always forget anyways long story short is is I could write something like where's back here I could do uh where you use your name equals I could go like that or I could just sip on like this a bit more SQL away or yeah yeah so whatever whatever you're most comfortable with and um to do so both are valid so we're waiting your answer John what what is the official name between each of them that's the last time you will invite me John will say don't invite Frank again he's putting me on the follow spot and um updating very similar to inserting just pass it the new updated user um that you made changes to pop it in obviously you're probably not going to want to change the uh primary key because then it won't know what to update but um yeah and it Returns the updated version of the object I'm assuming so this one just Returns the number of so it's not quite like um it's not a user yeah it's an INT so it's like a a status or something like that yeah okay and then deleting again here you're not actually passing in the whole user but just the primary key for it so it's all it should be intuitive you know once you once you see it once you see it used once it's like okay now I now I now I understand so it's not like there shouldn't be anything super duper tricky coming in through this so let's do it Frank let's actually create something and um we're going to go through is that we're going to actually build something up from scratch where we're going to actually add the packages in we're going to define the entities the Poco objects we're going to connect to the database we're going to insert a row and we're going to retrieve some out of there Wonder like a plan oh yeah in our the visual studio let's do it yes all right so what we're going to do is head out to our learn exercises and then I'm going to jump on down to the exercise itself and then there is a exercise repo that you can go out and you can clone if you wanted to yeah and that's I already have it cloned so we're going to go through step by step and then I already have once you have it downloaded you open up the people solution and then here it is so first thing I like is right here don't try to build the solution just yet it won't compile love it and it doesn't because I just try to do it before we start it and it doesn't compile so I can guarantee you assure you that this right here note is correct all right so what we want to do first is just add a new folder called models and then we'll add a new class in there called person so nothing really super tricky here is calling it models and then add new class add class and we'll call that person go on there we go nothing it's nothing new here but what I'm going to do is use the new way of name spaces and let's make that a public class all right so far so good so far so good all right so that's all this is saying people that models we're all good all right so next thing we want to do is add a property called ID to it and then another one called strength so I'm just going to copy this up so we're not doing really anything super duper and see here we're just kind of modeling you know person ID one it's gonna be frank two is going to be John and three's Matt nothing really okay simple as you probably can get perfect for learning so we don't have complexity is just exactly all right and now this is where things get fun so now we want to actually add the sqlite library in and it's going to be a nuget package and what we want to search for is suko light net PCL and right away Matt I have a question for you because if you search the SQL Lite you will find a nuget package but why do we need the net PCL instead of just that you know the just light version because okay because that's a good question and if you look at all of this I think you need to scroll just a little bit someone mentioned that uh in a video during uh that netconf last week it was like each time you search like it feel like it scrolled just by one and often the what you're looking for it's at the the first yeah yep I noticed that too and I thought because as he was presenting like a you know the screen when you presented sometime like zoomed so I assumed it was because everything was soon but I did try earlier and I was like oh no it does that to me too yeah yeah I don't know yeah well I will open the ticket feel provide feedback yep so yeah the question was why do we want to use uh the PCL yeah right and so PCL stands for portable class library and so the regular like the sqlite.core might have access might be doing things that aren't available on Android or iOS or Mac so the PCL essentially is saying you know what I guarantee that everything that I'm doing is available to uh the various um um os's that we're using so it's kind of like net standard 2.0 yeah so it's it makes sure that I respect some standard so if I'm using it for like a web or like if I'm building a native application for my windows whatever then I I could use like the regular one but then like if I want to make sure it's fully compatible that's why I want to go there with the BCL yeah and so as you can see you want to do sqlite Dash net Dash PCL and mainly because there is a million of them out there and it's easy to get confused because look at all of them and so by doing that still there is a lot of them and like you said Frank look at that it's a tap on so and it's really this even calls out it it's good for net model and xamarin yeah apps so it's it's the lightweight version and we know it's gonna work places all right we'll install it and we agree we have another one also all right you have another another question another nuget package to pick oh another nougat package oh yes also install the it was yeah here we go the blah blah blah provider the Dynamics something something managed the copy paste yes here we go 361 I'm just wondering oh okay I'm just wondering what the difference was oh one is executable and the other one the raw ex and the just rock yeah like like that's in the name but I don't know like in the probably if you read the full description of both yeah yeah all right we are now yeah up and running and so now what we can do is start adding attributes lovely so of course we want to add in our circle light namespace we're in our persons yet so let's just go down here using SQL Lite like so and we have questions but I will wait when you initialize your connection because uh it's related to that okay I just want to make sure uh gotcha the audience is a little bit patient all right I kept it I saw it good good good so what we're going to be doing here is just we're going to be calling the table people and giving it a primary key and such so here we're going to say table is peep oh because people is the plural of person yep and that's just what he wanted to do it we're going to do a primary key and auto increment so that's gonna be a number that goes up and we're going to want to make this unique and give it a max length of let's say 100. so we're just kind of giving our columns attributes essentially to this here so that's all we're that's all we're up to with our columns there but it's nice I mean it gives us a nice way to um two oh what's the word I'm looking for declaratively see what's all going on and what the database is going to use for each column all right so what's next that way you want to do all right so now we want to actually connect to the database and what we want to do is in we have a person repository class here so that is down here at the bottom of the solution and the first thing I want to do is add in the namespace for people that models models and we're going to want to use sqlite2 like so all right so then next up what we're going to want to do did that all right so we're going to I want to add in a private sqlite connection Field named c-o-n-n skills and so the question earlier was related to that declarative because in this slide so the question was um what was it what was it the uh isn't it SQL connection at I disposable and we should be and it should be using a VAR equal Nu uh but because in the slide it was it was declared differently than what you did here okay yeah so the slides might be um right so what we yeah what we should probably do is technically I'm guessing make this static and we only have one copy of it period even though we're we do check to make sure it's opened up and yeah what we should be making it so we uh dispose of it so yeah that's a good call out good call out so what we're going to want to do is just make sure it's what we do here then if not no remember to return it from our init and then finally we're going to use the DB path variable to get at it and then it's just gonna look like this and here's the fun part is that we create at the very end we create the person table that's so simple Yes instead of writing a full SQL script yep yeah super cool all right next thing we want to do is create some users create some users so we have the add new person and um let's go down here let's create them but yeah not the functionality to be able to create yes so we have add new person so the first thing is to do and hit it and then next you know what I let's call this Frank instead of let's do this up here what is it argument or argument no exception dot throw us now [Music] I like that better we can get rid of this yeah how about that six I think pretty cool anyway yeah it's cool I say it's not a descript but it's cool not the script but in the demo it's not what they say to do but yeah yeah some experience here yeah going off track all right and then what we want to do is just insert this line where we actually say insert so we say result equals zero well instead of saying result equals zero we want to say result equals Conn dot insert and then we can say um we should actually new person right ooh person my nickel's name oops so our new person gets inserted into the database just like that and then finally we're going to go without any make sure we get all the people out so we wanna list them list them out so here we go first thing we want to do is in it using a fluent or link yep so here we still don't know yet now nope come on and then we're just gonna return so uh we had one question in the chat by saying uh I know we're moving quick in time with uh what was the link I just want to briefly mention I don't know what's the acronym is for language language integrated query so it's a way to write query without doing it like in SQL or something like that so yeah stay in.net and uh you write queries yeah so you can do something like this where Peter name there's different style of how you could do it but yeah so basically it's link that's what's like cool yeah so this is like the link part right here where I'm passing it like some we're able to manipulate the data just by doing this that's that's the link right so there we go we should be good I think that's it let's check oh we got to integrate the repository into the u i all right so I'm gonna open up the Maui program that's CS all right and then I want to create a string variable named oh can we have a raw sql2 with the sqlite do you know or we need to use oh yeah you could you could pass and Rise no that was a question so good question Mauricio yes you could do that so here what we're doing is that we're getting our local file path so we're using a file access helper which is this class right here so you can see that we're using our app data directory and then we're passing in a file name for that so you see we're using all our file stuff that we learned before even though I don't think we really needed to create a helper class for it but we did and you're saying hey let's create a people.db3 file and get us that path for it all right and then next up what we're going to do is this line right here control V is um we're going to add a Singleton for the person Repository and passing it in the where we're going with it and so by creating the Singleton for it that'll actually take care of our I believe that'll take care of our our what's what am I looking for Frank or disposable to for the uh connection yes so there we go we have it all up and running if we turn Builder that build we're good there and now next up what we want to do then is go into the app and make sure that we have a a repository for it and app Dot zamo like oops not there we want it up above here did you say demo um so this is the app.saml.cs file so this is where it actually starts creating everything for us and oh look at the file name okay file name yep I'm sorry yeah like I was re like I was reading why aren't this example in the screen I don't see that sorry and then it's too tiny for my old eyes can I see it oops all right and so what we're doing here is that when overall app gets stirred up we're going to make sure it gets the repo passed in from where we did all of this stuff by passing in the Singleton so it's going to get the repo in there into the Constructor and then we could just set it overall as an overall property calling it person repo wonderful cool yeah so all right and that's what it's saying it's going to get set by dependency injection now we should be able to run it Let's cross our fingers Let's cross our fingers like copy and paste it correctly did you where you pass that part of the exam [Laughter] in the chat will he fail will it compile yeah deploy succeeded all right I know it compiled good pressure yep come on here we go and here's our app beautiful.net colors yeah I was about to say that the good purple yeah and then we'll do our first name we'll just do Frank oh no unique constraint fail people.name you know what Frank two two you know why because when I was practicing before I already created this app and this app is already on device oh so did you create John 2 yeah there's John on there too there should be Matt Frank John and Frank too yeah so Frank and Frank too yeah Frank and Frank too so what's funny is that the database was already there so we're already showing that you know what this still this already is working and our Matt Frank and John were already they were sitting there and we pulled them back up so that database came along for the ride so that's kind of cool yeah so if I would rename the database like I'm not sure what we call it again we called it what people that db3 so if I would call this persons that db3 would be a total different database and would be empty but as we can see all three of us and Frank doppelganger Frank 2 has uh made it well in this case you redeployed that was one of the that was the another question we had in the chat people were asking when you uninstall the app does it also remove the database I'm guessing yes because it must be in that uh sandbox folder yeah correct and it didn't happen in this case because you just redeploy on top of your previous one yes so I never on this Windows this particular Windows app here I never actually had it uninstalled okay that's why I was already there yeah it was it's already there yep okay cool so let's open answer that question that I took forever to answer I was just waiting for that moment perfect you knew it was coming so that's why we're already here well I didn't know it like you had you you well you had already have uh deployed it but I was waiting for that moment where we are about to close it and what's cool is that we got to see the unique constraint fail so are people that name we saw that our Nina constraint over here in person it's failing so it does maintain that for us which is nice indeed so like just like one little attribute and we can have some kind of uh policies or like yeah constraint yep wonderful okay so what what's next I see time is flying yeah no we I talk way too much all right so let's just let's use it asynchronous I'm having fun like I'm happy with this I think what's coming is the best practice so we should definitely have yes absolutely it's it's uh it's asynchronously because I I mentioned it way when we first started is that sqlite itself is just a file and whenever you're doing any file input or output you want it to be asynchronous because you can start making things unresponsive most of the time it's not going to be unresponsive but just in case you want it to be responsive and by default like I tap when I tap on ADD when I tapped uh wherever we are when I tap add person or when I tap get all people that's coming from the user interface thread right so I'm essentially pausing my app while that happens that app now is unresponsive so what I want to do is I want to say all right go off and do your thing let's make this asynchronous so my app does not pause so it remains responsive so the operating system then says oh I see this app is so responsive you know the UI thread is clear it's cool because as soon as that UI thread would pause for a long time us there's certain cutoff times I believe they're different for each operating system but let's say let's say it was two seconds just as an example if it was more than two seconds that it took to like load up that database or do whatever it needed to do the app would just crash and disappear but if we do everything asynchronously then we don't have to worry about that yeah cool like if you have any little animation even if like we don't feel it as human it will look because it will everything will be a little bit more like yeah yep and there was an old joke way back when when uh um even now it applies that half a mobile programming mobile uh device programming is hiding operations behind animations because everything takes so long you just have to display a nice animation to distract people with what can I we can do here yep the cute little thing is happening you know it's he's dancing on the screen until the data comes back anyways so to do asynchronous stuff super easy you're just going to use a new connection and it's going to be called SQL light async connection and end calling you know create table async you're just adding like the async name behind things yeah so it's like it's the same thing just like you copy pasting the word async at the end of the function or like before the call of emit that yep and you're saying hey wait before it yeah yeah yeah yes you put a weight in front of yeah yep it's so it's your normal um it's your normal what what am I looking for your async a way practices just new names for the functions and make sure you uh go back one cool thing here one thing to know is that when you do do an await like I said we're kind of going off the operator or off the UI thread here so when I want to update anything in a user interface I want to make sure I'm back on the UI thread because otherwise if I'm updating the user interface I have to be on the UI thread in order to get that the user interface updated that makes yeah sense so it's wonderful and uh it's the same as any.net code so it doesn't change because we're in that Maui or yeah okay cool yep and then as you can imagine git insert update all the same async yeah that's cool and right here um we mentioned if you can run raw SQL it's query async to run it and let's do it let's add async everywhere so I'm going to go into here and I'm going to do it see if I can just not even have to look at the code all right so private sequel like async connection all right in it so let's do task oh yeah that's there yeah you need to put the test I forgot that async connection create table a sync a-s-y-n-a-s-y-n-c a s y and C oh wait looks good right sure yeah yeah all right not mine yeah yeah I'm really put myself on here add new person it's too early to start drum rolls but you know yes bad sounds effects maybe I would for weight oh wait insert a s y and C a sink you're not even using the auto completion stuff I know you're a real machine and some a real machine that's because if I do it like kind of just I you know I've been at like autocompletes with like this autocomplete and oh yeah yeah anyways all right a way to sync await connection insert a sync okay and then get all people let's do this async task list person like that oh wait and then oh wait two person list yes Y and C all right our repository is updated cool and now I think it's burning me but we need to or maybe in the uh the app yep so I'm in the the code behind here where we do on New button and on get button yep so I'm just gonna censor event handlers I'm just going to change these to a sinks and another resync void so necessarily the best practice but a way to add new person and then I'll wait here as well I said Frank I think that's it I don't know we'll see what happens let's let's the drum roll microwave is depending on this right now oh my goodness oh my goodness maybe I'll get the afternoon off because I'll either I'll either get an A and be able to take the afternoon off or I'll get an f and I'll be fired and I'll have to take the afternoon I don't think so how many peoples you know have uh you know like coding and coding in front of a camera is two different things it compiles at least the compiles were up so let's just do good all people here we are good and then we'll say John where's the answer or link oh John mentioned that uh he mentioned or is it where was it uh it was query syntax or metad syntax okay well here we go thanks John sorry it took took times though so I'm guessing he had to look he didn't remember there we go well that's wonderful and that was like it took you I don't know the app is very simple but to put it like you're not adding a lot of complexity just to put it async and have like right an app that doesn't crash that keep responsive to the user yeah that's pretty simple yep and honestly that's the way you're going to want to go from the beginning you're not going to ever do synchronous it doesn't why don't just go async right from the get-go yes definitely yep yeah you're not like it's not it was like adding tons of complexity it's it's not no no um yeah that's cool so Matt you passed that that test for sure yes I get the afternoon off nice all right say that I didn't say that that's what I heard all righty so yep we used a single light async we created the connection we inserted the item we got them all back and we tested it and hold on hold on hold on as the all right good here we go all right so for the summary we did it Frank we made it all the way through we took a peek at the different storage options that were available and uh we saw preferences we saw the file system and we spent a lot of time talking about sqlite database and so the preferences are really just for simple you know really you could kind of think if I often think of preferences just as yes no type deals you know like dark mode no dark mode signed in you know agreed to agree to terms within agree to terms sometimes I store more in there where I do serialize like a Json string or an object state but super duper simple super simple and then files us a little bit more um like we saw we'd actually stored the database file is a file but you get at it through a database engine yeah or like like you mentioned frankly maybe you want to serialize a whole object State graph and put that in a file that's another another great example for that or csv's files or whatever that your app might be using and files Go in certain places depending on where they are so Apple's real specific on where you should put things um if if the user generates it you want to put it into the you or into the documents folder and that's for backup purposes and if the app is generating it and can be recreated without user intervention throw it into the libraries well Library folder otherwise use the um there's net abstraction helper helper classes properties that help you get things into the right spot within your app sandbox and then finally relational data into sqlite database so sqlite's a full-on database has a relational database engine we have nougat packages and there's more nuget packages out there for sqlite than what we showed but SQL lightnet I guess is probably the most well-known one and as we saw is pretty easy to work with too yeah and make it async make it async frame oh yeah async For the Win app users yep keep the users happy keep your app from crashing yeah that's cool yeah for sure I mean I really like I mean it's databases can seem like how am I going to do a database on my phone or on my well I shouldn't just say phone now you can do databases you know I'm always everywhere it's client desktop or whatever but when you think about it like within my app and you know within cross-platform app and I was using building a desktop app and works just fine there and it would work just fine if we were doing it on iOS or Android so cool so if you want to see more of the topics 2022 116 a dash summary I'm not sure where the dash summary came from in this link but that's going to bring you something very interesting I'm sure um but go ahead and take the learn module as well and uh let us know how we did in the comments on alarm module we're always looking for things to add to the learning path as well um and the learn module is right here for you to take so scan a QR code we should put the.net bot in that QR code have the little one in the middle I think I've seen that before we can put the little.net bot there oh we could do so many things now yeah just yeah I think that'd be good anyway AKA Ms slash learn live Dash 2022116a take the module let us know what you think yeah cool the link the link will be provided I saw it puppet in the chat okay excellent and then all we have re-watch everything we did it Frank this is number seven out of seven lucky number seven and thank you Frank lucky Frank yes you know what I actually I've been reading a book it's by uh Carl Sagan the astronomer and the reason why lucky number seven is that way back in the day you know before telescopes before anything is that we could see seven celestial bodies like the Earth the moon Mercury Venus Mars Jupiter and Saturn right so seven was like this number because they saw these seven planets that always moved around compared to the background the stars and that's all like seven got started really oh that's that's good yeah yeah isn't that interesting so anyways that's how we came up today so that's how we came up with the seven learn modules no I don't know but anyways rewatched the whole series here so yeah everything we take you from building your first Maui app to styling it to um adding so it's consistent styling to calling out the rest services and then I'm doing local storage as we just saw today so cool all seven modules are all super great check them out at learn live Dash mobile Dash desktop Dash apps dot net Dash Maui and of course Frank and I and John will all be and continue the conversation meeting teams meeting I shouldn't have spoke for John because I'm not sure what his schedule is but we'll all be there um it's gonna be a quick one today unfortunately for only 15 minutes but pop in ask some questions and we'll do our best uh answer them so I'm joining for sure cool so we did it Frank I'm very happy it like you know I was I was going in with like okay it's done that Maui I want to learn but I was expecting I don't know it would be a little bit harder and like it's seamless it's just like it's just like C sharp it's just like the same thing it is yeah I mean it's you it takes a little bit to like I said it's a little bit of a mind Bend yeah yeah is that you shouldn't be intimidated all right let's let's put it that way definitely so all right well Frank we have less than a minute left so well I'm gonna hop off into the team's call and see you all there all right thanks Frank talk to you soon thank you foreign

Channel: Microsoft Developer

Views: 11,212

Keywords: microsoft learn live, dotnet, dotnet-maui, vs, developer, student, beginner

Id: nK0rtoU_D2Q

Length: 90min 54sec (5454 seconds)

Published: Wed Nov 16 2022