Connect a .NET MAUI App to SQLite Database Step by Step Project Part 1 by Abhay Prince

hey guys so I'm back with another video and in this video we are going to see how can we integrate database in.net Maui app and we are going to use sqlite because this is the kind of default uh database to be used in mobile devices because this is a single lightweight single file based database so this is not going to be just another sequolite with.net my tutorial you see on the internet but this is going to be much more than that so this is the simple one page app we are going to build using sqlite database so we have this screen we have these products container we have this name and price and create product button so we can create a product from here or we have this password Button as well so that we can create product from here as well so let's try to create a product let's call it product one red and it is going to be added to this collection View and let's add one more second product and price let's say 10.56 create product so it is here now we can edit these products so we can save 180 then update this product this product is going to be updated and when we are in edit mode from there we can simply jump to create mode by clicking on this plus button on the top and we can delete the product from here also to delete so all these operations are happening in sqlite database so for this I have created this database context class and this is not kind of tightly coupled with some class which you see on the internet so I try to create it as generic as possible so in this if you simply copy this file in any of your project you can simply you don't need to create multiple database files you don't need to create multiple database wrappers for example uh let's say you have some to-do app or something like in this case also we create another screen also for let's say customers data so I have these models product model I have implemented this beforehand and this customer we are going to implement this and for these two types I am not going to create a separate let's say product database or customer database I'm going to use this only because this is generic we can use whatever type we want so here let's continue but before that let's see how this app looks on Android then we'll start the coding part so this is how it this app looks like on Android so we have this empty collection view we have this bottom form for creating and editing a product and we have this add product button in toolbar so let's create a product let's say again product one 50 create product product is created let's create another one packing product 32.20 create products so we have these products we can edit these when we click on this added details populated into these forms so we can edit this and this button Stacks it change to update product and we can update this let's make it 500 and if we update it so it will be updated in real time in this collection and we can delete the product as well when we delete it again we don't have anything so I'm going to create a new dotnet my project and let's start it from there I have created this Maui project so first thing let's clean these up we don't need this counter and same remove everything from our Main page.zml save it so first thing we need to install sqlite nuget packages so we need two packages first one is sqlite net PCL so you can check this from this icon and then it is from bicycle light hyphen net so let's install this so this got installed and now after this we'll create a folder let's call it data and we'll create another folder let's call it let's say models so in this models we'll create our product model product and this is going to have public class product it will have ID name of the product and then price of the product and this ID this is going to be primary key for a table so we are going to map this product class to a sqlite table and we will add Auto increment to this phone so that this ID gets Auto implemented we don't need to pass this so for this we are good and now in data let's create one more folder for view models so add new folder and we'll call it view models and let's create a products view model products view model and for this one we are going to use Community toolkit so let's install that as well Community toolkit Dot mbvm install so this is installed now let's change this to partial and We'll Inherit it from observable object Pine now okay now before doing anything let's create are wrapper around sqlite so we'll call it database context and we'll set up our cycle light wrapper here so first thing we need to have name of the database so let's have a constant string so let's call it DB name and we can name it whatever we want for this one let's say not product let's have some generic let's have the project name only and for this thing extensions sqlite support multiple extensions we could have dot DB Dot db2db 3 and then we have sqlite sqlite 2 sqlite 3 solve these extensions these extensions actually does not matter but there are some tools by which we can browse this uh the sqlite database so it depends on what GUI or what project we are using to browse the contents of this database so in our case we are going to use db3 let's call it something like my database then next thing is we need to have a path because this is going to be a database file so where this file is going to be stored we need to let the Maui know where we want this file to be saved so for this we will simply have a private static string property let's call it DB pad and this we need Parks dot combine then we'll use file system Dot app data directory so this updated directory this is a kind of not kind of this is actually a API the wrapper API so it gives different device specific structure directory structure so if you if we run this on Android it will give us Android specific file structure the hierarchy complete hierarchy and it if we run this on iOS then it will give us iOS specific uh that file folder looking then the second path this DB name so we are telling that you need to use this DB path to store this database file now next thing we will have a private property here and this is going to be of type sqlite connection sqlite async connection any same connection let's call it connection only and then we'll have a private property here so this private property and let's call it database and this is going to be a get only property and here we will first check if we already have connection then we'll simply return it if we don't have this connection we'll build this connection we'll create this connection so for that we'll use this approach now we'll create new SQL indexing connection and it needs connection string or we can give database Path store date time Sticks this is a flag so that it tells this how the date time Fields should be saved by this database but we'll need this one the database path and sqlite open Flags so first thing in database path we know we have this DB path for this then the second thing these flags so this is an enum with flags so we need to tell that when you are creating this connection to this database how what all uh kind of virtual operations we need so there are couple of you can try this we you can play around with this but the ones we need those are first thing sqlite flags.create so using this we are telling the secularizing connection that the create database if it does not exist so it can create this database then we need another one and that is sqlite open Flags Dot read write because we want to perform both the operations on this we will create product we'll read product we'll delete product we'll update product so we need all these operations then there is one more flag that is shared cache so this is for if we run our app let's say if there are multiple threads involved then this shared cache we can use this so that our database can be accessed by multiple threads at the same time so if your app is completely going to be single threaded you don't need this so this is done so how this is going to work whenever we'll try to get this database it will first check if we have this connection if we have this connection it will simply return that if we don't have this connection then it will create this connection set this connection to this underscore connection object and then it will return this connection object now we have this after this okay before that let's create couple of helper methods so first one is we'll say public async task for now let's simply play around public async task now let's say something like this only so in order to perform operations on this database object what all we need first thing we need to create a table so for that we have this database object this one this sqliteacing connection on this thing we have a method that says create table facing and in this either we can pass type or we can use the generic one we will pass type here so in this case let's say we added this product and if we say this so what it is going to do it is going to create this table if it does not exist already exclusive create table if not exists it will going to create this table and the schema it will going to get it from this product model so it will use this ID as a primary key in Auto increment then these two things name and price after that if you want to perform some operation if we need the table so we have this table method here which is also again a generic method so we can use like this so now we have this IQ variable object we can if we want all the data we can use two list async right same thing we can apply where condition we can apply order by we can apply Skip and take for paging so we can apply all these operations on this one so for this now if you see this is this method if we are going to continue with this approach this is tightly coupled with this product class in our case we are going to the product and let's say we have 5 or 10 different models different tables then we need to create multiple database con context classes or maybe we need to create multiple methods but we could avoid these totally so we are not going to use this let's else we don't need this model so we are going to take a generic route so first thing we need public async task and we'll use innumerable of now we don't know the type so let's have it t table so this is the type of the table and we will say let's say get all acing where T table is a class okay now from this get all async first thing if you remember we need to create this table if it does not exist for that we can use database Dot create table async and we can pass this now if you see it is complaining about that must be a known abstract type with the public parameter less Constructor so T table we tell this is a class but it could be anything so we need to use one more constraint which says this new so it we are telling it that it has a parameter-less public Constructor so it it can apply this new uh it can simply create the instance of this class so we are good with this then we need to get all the records so for that we will simply again use database Dot table and then we'll use this type then we'll say to list async now wait and we will return this so this is fine now let's say we want to have uh maybe filter data right so let me copy this let's go hide this for now let me copy this so let's say get filtered async it will again going to use this and we are going to have for filtering we are going to have this where clause for this so if you check this is accepting a predicate which is of type expression funk T table is the input and Boolean is the output so we'll have the same type of parameter here expression Funk T table Bool and let's call it predicate only and now we'll pass this predicate directly to this and now we are good again so get all a sync get filtered async we are good with these two things but if you check we need to use this line again and again right so we need to this thing create table listing then this getting table part so let's have helper methods for this so we should have this private async task and let's name it as create table if not exist and type of table and we don't need any parameter and the constraint we need this one so food this will move this line here fine now just copy this next thing is we need to get this table this is also again getting repeated for this let's again have a type so it returns us async table query so let's use this task testing table query of T table and we'll call it get table async and again the same constraint in this we are going to return this thing away database table and what is the issue now variable object is oh sorry this is not available [Music] then we have need to have this line here so it does not make sense we have again same two records two lines so what we can try we can simply move this line to this method so here before whenever we are getting the table we'll first confirm if this exists but not like this it should be like this create table if it does not exist then get the table this is what we are going to do so now we don't need all this we need we'll simply get the table so we'll say get table basing with this type and then we'll return our operation that is tool is testing for this one now what is it yeah now wait same thing for this let's copy this the only additional item is we need to apply this wear predicate to this one and we are good okay so this is for getting all records getting filtered items now let's add other crude operations so that means uh creating and updating deleting all those stuff so first thing let's try to implement adding our item so public facing task and let's say add item is sync so we'll have again this type e table and the type of item is going to be item only and the constraint we need the same constraint now put this for getting we need to use this table but for other let's say write operations we need to we can directly apart from those operations on this database objects so in this database we have this insert async method and in this we can add a object so our object is this item so this is fine if you apply a weight this is going to be fine but again we need to First confirm if this table exists in our database for that let's call this create table if not exist this line so first we are confirming that this table should exist in database then we are inserting a new item to this one so for this it returns as task of integer that the number of Records added so we should return boole from here and we'll return if the operation was successful or not so we can confirm it by saying that okay if the number of Records Was greater than 0 that means the item was inserted into the database so the operation was successful will return true forming now next thing is updating an item so let's do this update item async up to date item is sync for this one again we have update async method so we are good with this one and now with for deleting the item so let's say delete item async again everything will look same we'll simply use the delete async now what if we don't have the complete item here we have just ID so we can have one more delete method so delete item by ID or let's say by here ID or you can rename it to get so delete item by key async maybe that should be the key and here we'll have our object ID or maybe let's change it to Key only that would make more sense so let's say primary key and now for this delete async method we can have another version of this one and that is going to be the generic one now we already told this apply this delete async on this T table type and now we can have our primary key that is this one same thing this also returns us integer and we can check this and we can return Boolean if this operation was successful and we missed one I think get all get filtered what if we need to get item by let's say the same ID or we could say primary key from that type so for that let me copy this one and let's add it here so we'll say get item by key async in from this we'll return a single item with object primary key everything is fine and from here we will say get a sink and now it returns treatable so we should be good so everything looks fine now we have implemented I think all the operations we are getting the table getting all records filtered records item by key adding update deletes all operations are there if you think if you see we have this line again and again in this in all these methods all these code methods right this get all and get filtered acing this is fine but after that we have this line in all the methods so this is single line and this makes sense so we should be good with this if you don't want to repeat this line what you can try you can Implement a simple uh let's say simple template design pattern so you'll have something like private icing tasks of let's say T result it will return some result and then you can say something like execute and you'll receive Funk of task of T result for this one and then task 3 result yeah and we need T table as well and in here you can have the first line as this one and then the second line constant and when second line you can simply have like uh do you have this action right so you can have this returned away this action like this and then in order to use this what you need to try uh you need to do you need to Simply return await execute and you will have a simple async version I think with blank and you can have this line directly here like this so you should be good with this one what happened type argument T table T result okay table and T result is again treatable for this same thing if you want to do the same thing for this one you can simply have let's comment this and you can say return away execute and for this the table is T table and the return type is Rule and for this again you can have the same thing as sync and then this line so either way is fine if you are okay with this one you can try this thing so I'll leave this here so you can try this or if you are okay with this you don't need to implement this okay so I think we are good with our database context close clean this up and we should be good now fine everything looks great next thing is let's so uh go to Maui program and in here we need to register this we are going to use dependency injection for this so we are going to register this as Singleton database context and we are good now so we will have a single instance of this class throughout our application so we are good with this now let's go to product view model this one products view model now we'll implement it here so first thing we need to because we are going to perform all the operations on our database context so let's add the database context as a dependency here database context and text and let's have it like this fine now if you remember our application it had a list of products so for that let's have a private observable collection of type product and it will be underscore products and it will be an observable property apart from this we had that one form in which we are creating a product from and we are updating the product so we are simply operating on one product so we'll have that as well so private uh product let's call it operating product and this is also going to be an observable property and we yeah we need one more so to indicate if some operation is happening there discussed okay we missed one thing so lpz and it is again going to be observable property yeah from dispose I remember in database context we can Implement I async disposal disposable method the interface because whenever this application this class is going to be out of memory out of context then we need to dispose this object because this is going to be in memory always right so in this case because this is a Singleton service which is always been required for that but still we can simply remove this so we have sqlite async connection and we have this underscore connection we'll dispose this one so it could be null so let's have this and then we'll say close async we have this method no wait what is saying database will not all uh okay it should be async and we are good now okay now back here so we need one more property which will be of type string so we'll say the busy text will show the different text whenever we are showing that loader will show the different action fine so I think we need these only now the commands so First Command we need private async tag and we'll say load products pressing first thing is we need to load the products from database so for this first thing let's we have this context object right open the first thing let's fetch the products from database products equals a weight context dot get all the sync what all we need we need all products so like this if we have some different model we could directly have this let's say we have customers so we could have said directly customers and we just need to change this we don't need to create complete database file and all those methods and if we had let's say orders if we are creating an e-commerce application then we could simply simply use this thing to like this with the single file we could use any number of let's say these models okay but for now we just need products so we'll check here if we have products so if products is not null and this products collection has at least one product if this is the case we'll check this underscore product so let's say this products if this products is null we need to initialize this and or we could have a simple approach compound assignment fine after this we need to insert these products so let's call it products in products these products we need to insert these products into these are observable collection products dot add this simple product so this is fine now this is ready we need to add this relay comment so Community toolkit mbvm is going to create a command for us for this method now next thing is this is your loading then creating the product but before creating we need to yeah this operating product so now next thing is private tracing task and now we create a new product for that so or maybe let's have same method single method for both insert and update so save product facing and this is also going to be a relay command and saving product because we have this operating product this one which is actually is going to uh keep track of whatever we are editing in that form either it is the new product or existing product we are editing so we need to work on this operating product so if this is null first thing to this save if our operating product is null will not do anything we'll simply return from here if this is not the case then we need to check if this is creation case or updation case so for that we will check if operating product dot ID if base says equals zero that means we are creating the product else we are updating the product okay fine so for creating the product we can use our context Dot add item async and we add the type product and we'll need to pass it so we'll pass this operating product and we are good now creation is fine after creating this product we need to add this product to our observable collection that is our product selection products dot add so we'll add this operating product to our collection now for updating first thing we'll update the item in the database update product async this is going to be product and operating product we have updated this now in order to notify our collection because it already has this item so we have two approaches first thing if we are directly updating this product in that case this product this type should also Implement I notify property changed or we could use committed toolkit but for the sake of Simplicity I am not going to use this I am going to have a different route for that I am going to remove this item from The Collection then I'm going to insert this item back again to that so for this at the same place we need to remove an insert at the same place so I'll get the index of this particular item from our products collection so I'll check what is the index of this operating product so we have this index now next thing I am going to remove this product from our collection so products dot remove add index then I am going to but before that I need a copy of this product product copy and this I need from this operating product so let's Implement a copy or clone method on this this product so let's come here and we'll say let's public and it will return the same product and let's call it clone and we'll use the objects member wise clone as product so now I have copy of this product in this product copy I have index I have removed this from this index now this item is no more in the products collection now we need to insert it back for this we will save products Dot insert and in insert we can say index and products copy and this is fine so now we have all this in place now after creating we need to reset this operating product or after deleting from or after updating we need to we need to basically reset this product this operating product so for that first thing let's have a default value here then same thing will need to set the operating product to new and we need to do this regardless of if we are in a for else part if we are creating or updating we need to reset it so we can do this thing now saving part is also done but whenever we update okay whenever we click on edit we need to set this operating product from our UI so for that let's create one more command so we'll say missing and we know this is not going to be async private void and we can say something like set operating product and in this we'll get a product and it could be in a label so we'll set our operating product to this product and if it is null then we'll default it to the foreign Ty object so set operating product this is also fine let's change this to command relay command so we should be good now and instead of doing directly this we can now do this using command only set operating product command and we can use the execute and then we need to pass the object and in our case the object tool is going to be no empty product fine so saving is also done now only one thing is remaining that is deletion so let's have that as with private testing task delete sync and in here let's have the ID product ID fine so we have that one version which deletes the items using the ID so let's do that so we'll say await context Dot delete item by key async and we are good we'll check if it was true that means the item was deleted successfully if it wasn't the item was not deleted so we can simply show some alert here we can say okay so if this is the case we'll show a weight shell Dot uh after deletion we need to remove this item from our collection as well that is products collection DOT first we need to get this items or we can use first or default on this one let's have it like this where product equals to products.pluster default and then we can use P Dot ID equals equals ID and then we could simply say products dot remove this product and this is fine and now here we could say shell dot current dot basically alert near we can see delete error let's say product was not deleted and okay and delete is also fine now let's add this relay command okay and we had this busy property so we need to set this as well let's try to add this so we'll use this busy equals true and after performing all these operation we'll set it easy to false and for that let's wrap everything in try finally so in try we'll do our operation and then finally we'll set this is busy back to false and at the same time we'll set our busy text to will say deleting product and here will reset it to let's say processing fine we need to do this in all the methods because we are operating on all those methods and all those are connecting to database we need to have this so for this also instead of doing this in all these methods let's use the same template pattern so we could have something like private is saying task and from all these we are not returning anything from any of this command from comment we don't return anything easily so for that let's say let's call it execute only let's call it execute fresync and for this also we'll simply have this funk of task and let's say this is going to be our operation and we will have the busy text so this could be not could be this will be different for all these because for this it will be deleting for saving it will be saving for loading part it will be kind of loading products or fetching products so for that let's have it like this and let's have it in a label maybe we don't provide this and if we don't provide this then we'll simply default it to this processing the defaulting fine now next thing is we'll use try finally here and before this we'll set this is busy to true and BC tags to busy text and if this is null we'll say processing fine and after doing our operation we'll say is busy to false again and we'll reset busy to the default processing is and in this try we'll simply perform our operation rule which should be fine now this is working and same thing for this one we don't need to do the try this try finally and is busy busy text instead of all this we could simply say await execute is sync and in this we'll say cut this paste this so execute this thing and for the second parameter the busy message we can simply pass this and we don't need our location now so it is going to work as before let's do this same thing for all these commands so for this also this is fine now we'll do this here again we are going to do the same thing so for this let's say a weight execute async and facing now we need to cut everything paste this and we are good now but that text we need to change that to creating product and updating products so for that we could have this condition outside so let's say uh busy text and we'll say if this is 0 we'll say creating product if that's not the case we'll say updating product and we'll pass this busy text as a second parameter to this hydrocube so this is also fine now for site operating this is not an async operation so we should be good with this one and now load products so this also let's do the same thing execute anything and we'll have facing and it will be let's say fetching products and let's cut this and paste inside this thing and I guess we are good everything is fine I mean I cleaned this up products view model let's add it to the dependency injection container dependency containers so Builder dot services or let's say maybe a transient or maybe let's have it as Singleton only and for this we are going to use this inside our main page so let's register that as well and main page is also going to be the now what we call it yeah the single time mainly because this player will be only single instance of this one as well so for main page also we are good let's try to build the app rent project is building so this video is already very long but I think it was very fruitful so because we have uh we have added the sqlite packages we have created models we have created the database context we have created our view model so I think from coding from uh back-end coding from C sharp coding we are good everything is in place now next thing is we need to integrate all these into our UI and we need to create that UI you using xaml so for this thing we'll do this in our next part so for this part that's all do let me know in the comments uh what did you like this about this video did you learn something or whatever thoughts you have you could let me know in the comments and like this video share this video subscribe this channels and I'm going to post the next part the second part with UI and integration of this view model and database all these things uh maybe tomorrow day after tomorrow so yeah I'll be back soon that's all for this video bye

Channel: Abhay Prince

Views: 11,265

Keywords:

Id: 7pO3A8VvtRM

Length: 56min 15sec (3375 seconds)

Published: Mon Apr 17 2023