How To Use an Existing SQLite Database with Xamarin and .NET MAUI

ever since i made a video on how to use sql lite databases in your xamarin forms application i got the question how do i use existing sqlite files put those in my xamarin forms application and use that with a pre-filled database and that's exactly what we're going to learn in this video now before we go look at how to implement this thing let's have a quick peek at the end result which is you know from the outside it might not be very impressive but this is a collection view that shows data with first names last names and the database id and this all comes from a pre-filled sqli database file that is provided together with the app so let's check out how to actually implement that okay so here we are in visual studio for mac 2019 i just created a file new xamarin forms application you can see that on the left open in visual studio with the example of the main page on the right you can see it running on the ios simulator now let's get this out of the way this also works mostly for dotnet maui you need to place your code a little bit differently maybe but it works for don and maui as well it also works with visual studio and windows it also works on android and the other targets that you might have so everything works cross-platform i promise you now i did do two things already one is in our solution you can already see this chinook.db which is a random file i found on the internet i will put the link down in the video description below if you want to try this yourself i added that to my shared project and what i did is set the build action so in visual studio for mac you can right click set the build action to embedded resource if you do this on visual studio for windows you need to right click do properties first and then you'll have a build action in there as well you can set that to embedded resource which basically means we're going to put this file into our project as well um as an embedded resource right but what we want to do is actually copy that file from our embedded resources somewhere on the file system so that the user can work upon that or maybe it's backup to some cloud service or whatever it's just a good idea to get it out of the embedded resources and put it on the file system but we'll see that in a little bit now the other thing i did is right click on the shared project do manage new get packages search for sql lite net pcl so that is the sqlite nuget package that you want to install to work with sql lite i already installed that and i made sure that my xamarin forms and xamarin essentials packages are updated as well so that's everything i did what we want to do now is have a look at this chinook db like i said this is coming from some random webpage on the internet i don't recommend you doing that because you know you know you don't know what might be in there basically um so don't do that just get your database somewhere else maybe there's tools to create your pre-built database right here but let's check out what's inside of it because how else do we know what models and then that kind of stuff we need to create right so let's go here to our view and then say terminal which brings up the integrated terminal right here and then i can say let's see if my chinook is right here now i need to go to existing right here cd another subfolder and here we are and what's really cool on mac os this is built in on windows you might have to install or download a sqlite terminal tool but for mac os it's built in so it's pre-installed on mac os so you can just do sqlite three and you can say hey this is sqlite and i can fire all kinds of queries in here so that's nice i can do dot open and i can say here my chinook chinook.db and it's going to open that file and i can check it out with dot tables and you can see all the tables that are in here so i think there's different types of data in here albums so something about music employees or something about your work so let's just let's just go with the employees basically and i can say dot scheme i think or schema oh here we go so dot schema brings out like the whole database schema for everything and everything right so you can see everything that is used to create these tables but if you want to see it for just one dot schema employees then you can see it for just employees so we have this employee id which is an integer we have the last name first name title all these kinds of things that you can use with employees and this is the information that's very useful to us so for this example i'm just going to use the employee id last name first name but i'm sure you'll be able to translate this into other tables and data and columns as well so let's just keep it at this this is all i need to know for now so let's just basically close this terminal and the first thing we want to do is look at how to copy this database file from our embedded resources to our file system so i'm going to do this in our main page.example.cs you can definitely do it somewhere else depending on your architecture or your needs or whatever in fact i'm not sure if the main page constructor is the best place to do this but you know this is just sample code disclaimer disclaimer i'm going to get a little bit of code here from off screen so don't worry about what i'm pasting in here i will walk you through it the first thing we're going to do is with reflection locate the assembly that we're running from which is the shared dll that we're running from so it's using this introspection extensions which is something that is built in from system.reflection so let's add the using at the top of the file right here so it knows that and we have to specify here type of app which is our examine forms app it can be any other type in the assembly that we want to load from but app you know is usually there so let's just pick that one and then you can see here using stream we do assembly get manifest resource stream so we're getting this gnu.db from our resource files from our embedded resources in this dll and we're going to copy that somewhere now let me import this stream right here using system.io and then it should have most of the things there should be empty quotes right here we'll get to that but what's interesting here is that it says existing sqlite dbsample.genukdb if you're working with embedded resources you want to first prefix it with the name of your application and that is usually also the same as the namespace doesn't necessarily have to be but you know so just take this this namespace or check here your your project name and you will have to input that here and you can also put it in a subfolder so if you want to put it in the databases subfolder you can say now database dot chinook.tb so every folder that you put here in between you have to put the dot folder name dot et cetera et cetera right i just put it in the root so it's going to be my project name.chinook.db and now it can find the file name this can be a little bit of trial and error but i'm sure you can figure that out now we have that stream we're going to copy that to a memory stream and then we're going to copy that to a file somewhere on our file system now we're going to see where we're going to do that that's going to be a little bit down the road so let's put an empty path in here which will cause a runtime exception so be sure to update this but we'll do this for now so another thing you want to do is maybe put a little to do here only do this when app first runs because else it will override the thing all the time which is probably not what you want maybe maybe it is and you can use examine essentials version information with that to see if your application is running for the first time or maybe the application has been updated if you want to know more about that please check out your screen right now it will pop up on your top right corner so go check out that one right after this video of course so now we have everything to copy our database file to our local file system now let's check out how to basically recreate our employee model right so what we're going to do is create new folder and we're going to name that models and inside of that models i'm going to add a new class which is going to be employee which is going to be our employment model now again i'm going to copy a little bit here from off screen so let me just paste in this employee class right here which has like properties for our employee id our last name our first name basically all the columns that we just saw in our database file now if you want to use this with sqlite you probably want to also add here the auto increment and the primary key attribute so that it knows that this is the primary key and it auto increments another interesting thing is that the table was called employees with an s at the end now if we're going to name this class employee which i find better but you know totally up to you then it's not going to find the existing table so you either need to name it employees or what you could do is add a little table attribute here and you can specify a name and other properties as well but we're just after the name so i'm going to name this employees and now it's going to find it as the employees table in the database so now that we have all this set up it's time to access our database actually so let's go into our solution i'm going to again add a new folder i'm going to name this data you can call it whatever you want and i'm going to add a new class which is going to be our employee repository so this is kind of like you know repository usually this is kind of like the repository pattern which is a design pattern to access your database so i'm going to use this well actually i'm just going to implement one method for this so i'm going to again paste in some stuff here you can see here that i have this private field for my sqlite connection which is the connection to our actual database file um and then i have this public static string db path so i've put this in here like this as a static so that i have a single point where i have this db path and i don't have to construct this path over and over again because that will leave room for error right then i will have two different paths and it will be reading and writing it to to do different things that's not what you want so what this does it it will find the path that's combined with the environment get folder path this is just helper methods from the dot-net framework which will get you past to like you know these paths could be different whether you're running on android or windows or ios right so this will determine the same path depending on the platform that you're running on without having to hard code all these paths so it will get the environment special folder local application data you should check out the documentation what it is exactly but i think this is like your local application data folder on each platform that will also be backed up to your client cloud provider if that's what you want so it's all good now we have this little constructor here which will create a sqlite connection to our db path so we only have to point at rdb file and that will be our sql lite connection so let me add the sql lite model models thing here so the employees and of course our list right here and that should be everything to set up for our repository here so um i only have implemented this method for like listing so this will just list everything that is in our employee table of course you can add here like add employee create well add and create employees probably the same thing update delete you can add all your things here right so that's definitely something that you want to do but i just want to pull them out around now for this existing database so this is all that i should be doing here so that's done too now let's go back actually to my main page.example.cs so i can here you know write this database file to the right path so i can say now employee repository which is a hard word i keep typing it wrongly repository dot it's not knowing it so let's add the using existing sqlid db sample.data dot db path so now it's going to end up in the same path so this is all done as well and actually we're going to need a little bit of ui right now so that it also can show our data right so let's go to our mainpage.example i'm going to actually update this title right first so let's name this existing sqlite db sample and i'm going to throw out all the labels right here and what i'm going to do again it's just a boring copy and paste video i'm going to copy this collection view in here so i have this collection view which is great for showing data i'm binding this to an item source of employees so that's going to be a collection of employees we'll implement that in a little bit and i'm going to do this item template where i have a stack layout with three labels the first name the last name and the employee these so that's all the data that we're getting out of the employee right so that's maybe important to note you don't have to get all the fields out of there if you just need these three fields specify those in your model and that's the ones that will get out of there if you need more later on just add those and it will retrieve those from you as well or you can write your own magic string sql queries if that's what you want of course but i wouldn't recommend that so there's all this now go back to our main page.example.cs because i'm only copying the database file here what i still want to be doing here is now add a observable collection um so let's just basically add that here to the top public observable collection of employee let's add all the usings for the models and this observable collection as well this is going to be employees get set has to be a property let's initialize a new one so that it's always there so now i have this one this is like my backing collection for the employees here so my data binding right here it will read all the employees from that and now we actually have to fill this thing right so what we're going to do now is say employee repository is whoops repository let's give it a name is new employee repository and from there what we're going to do for each employee in repository.list because that got all of our employees right we're going to do employees dot at employee and it's going to add them to our observable collection one by one now one thing we should not forget is set the binding context here so binding context is this because this basically says like hey this main page is going to act as the binding context so if you're here reading our employees collection you should do that from our main page object right here so it's going to reach out to this employees and then inside of this collection in this data template is going to look into one of the employees and then is going to look at the properties first name last name employee id i have a playlist full on how to work with data binding xamarin forms it should pop up on your screen right now so go check that out um let me quickly stop and restart the application and actually let me just put a breakpoint in here so you can see a little bit what is going on and what we should see is that we are going to walk through here we're going to load this from our embedded resources in the assembly and our stream should have a a length which is basically like how big our db file is from there we're going to copy that file and whenever we do you can see in the repository.list that our employees are there being read from our database file now and we can show them in this list so let's wait for our application to be deployed so we can actually see if that is what happens so here we are it's coming up and well here's our break point so let's see if it can find the assembly it does that let's see if it can get our stream so in our stream we can see base length and capacity which is i don't know 800 kilobytes 80 something like that so it has actually found something now it's going to put that in a memory stream and it's going to copy that file to our db path so it seems that it's done that successfully now we're going to create that repository we're going to loop through it and it's going inside of it so you can see it actually adds employees right here so if i just click play here if i just click run it will continue and we should see a list of actual employees being loaded in our collection view so that is how you use a existing sqlite database file inside of xamarin forms or done at maui so now you've learned how to take an existing sqli db file and connect that in your exam informs and don and maui application i hope this video was useful to you a quick shout out to my latest member on the senior developer tier thank you so much craig schlamann i'm not sure where you're from it sounds a little bit i don't know german nordic maybe um but thank you so much for becoming a senior developer if you want to know what the memberships on my channel are all about please check out the join button down below if you're new here on this channel thank you so much for watching uh please check if the subscribe button is lit up so you're subscribed to my channel and all the information will come to you did you like this video please click the like button as well and maybe you want to learn more about sqlite well i got a video on how to get started with all the sqlite bits right here maybe you want to know more about that dating binding stuff i we have been talking about then check out this playlist and of course if you want to subscribe to my channel like i just mentioned check out this button right here see you next time [Music]

Channel: Gerald Versluis

Views: 14,344

Keywords: sqlite database, xamarin sqlite example, sqlite and xamarin, using sqlite with xamarin, xamarin local database, xamarin sqlite tutorial, xamarin sqlite, sqlite tutorial for beginners, dotnet maui, .net maui, .net maui sqlite, dotnet maui sqlite, sqlite existing database, sqlite db file download, xamarin forms sqlite, sqlite db file, xamarin sqlite pcl, sqlite and xamarin forms, sqlite xamarin forms tutorial, sqlite pcl xamarin, sqlite-net-pcl, xamarin forms 5.0, sqlite pcl

Id: ftDq-leq5OM

Length: 17min 18sec (1038 seconds)

Published: Tue Mar 08 2022