Add Databases to Your App with this Xamarin SQLite Tutorial

[@jfversluis]

Now you know how to setup your database, let's learn how to actually use it with CRUD actions here: kzbin.info/www/bejne/bpDGooisp86qg5o Or, maybe you want to encrypt your database? Check out this video: kzbin.info/www/bejne/hWK4gpmlnZlqZ5I

[@davidjackson148]

Had to create the whole thing as static. Can't even have it static from an injected service

[@christopherlikins6321]

I hit a roadblock with another method and needed to scrap and start over. This worked and got me the connection that I needed. From here I can experiment and progress. Thank you for this.

[@jfversluis]

That’s wonderful to hear Christopher! Glad I was able to contribute to your success!

[@KenRossPhotography]

Another wonderfully informative video Gerald! One of the challenges for databases on a mobile app is what happens when you release v2 of the app and the database schema changes? I'd love to see you cover a recommended strategy for handling the schema changes (new tables and/or fields) once the app is "in the wild".

[@jfversluis]

Sorry I missed this one Ken, definitely important, I'll look into it, thanks!

[@DifferentTurn]

As always great content, Gerald! Appreciate the work you've put in the videos. Keep it up!

[@jfversluis]

Thank you Alexey! Appreciate the support!

[@0brooo]

You sir are the reason I’m passing my class 😂😂😂

[@jfversluis]

Haha that is great! Good luck with your studies!

[@ValterJuniordalpogetto]

Great video. Clear code and straigth to the point.

[@jfversluis]

Thank you!

[@jimnorth9902]

Thanks for the quick SQLite intro. A video where you extend the sample app to incorporate basic MVVM would be of interest to me. I'm new here and haven't gone through all of your videos yet, so pardon me if you already have done a database + MVVM tutorial and I missed it.

[@jfversluis]

Don't think I have one with that scenario specifically. I think you should be able to make it work with some of my other videos about CollectionView and getting data from a REST API. Did you figure it out by now?

[@hassibamouncet8601]

Awesome video I have a question, how can we link more than one table? Is there something like foreign keys? and how can we use existing database? thanks

[@luisjjjj]

Great video, you are amazing. One short question: what option is more recommended for saving lists recovered from an API to used them when there is not internet?, SQL or Monkey Cache?

[@jfversluis]

Thank you Luis, appreciate it! Monkey Cache is a layer on top of, potentially, SQL. So they are not really alternatives. The best option for you is the one that fits your needs and you enjoy working with most. Both can do the same thing in the end :)

[@mikes3122]

Nice tutorial ! Question but, is there a way to implement a table with a 1 to many relationship with another class? Tried this and apparently a list is an unacceptable type for sql lite when making a table.

[@randyblock8747]

Same problem here, a little lost as most examples are quite simplistic with a single table or two with no one to many

[@patricklopes5463]

Great video Gerald it will be awesome with you combine this video with the barcodes reading!!

[@jfversluis]

I leave it to you to combine all the bits and pieces as only you know how they should fit together for your scenario 😉

[@fatehnezami]

Great video 👍

[@ChanoTSG]

i loved your video, just learned first steps sqlite db in xamarin, do you have more videos about sqlite using mvvm? i keep watch your videos to practice xamarin forms

[@DavidStania]

Great Gerald! Could you make a video with explanation how to use migrations with SQLite and MAUI? Using a database in Apps is a commone approach, but sometime you need to extend your database and update your app. How to handle this without reinstalling the app or deleting the database?

[@anassportal]

i would like to know more about sqlite query As you mentioned in (8:42)

[@randyblock8747]

Trying hard to find an example of relationships with SQLite and the link you’ve shared doesn’t show any. Can I create relationships in a code first approach similar EF? I did see a Pragma for turning ForeignKey On.. is this the approach to use? Maybe Sqlite is not the one to use for local dB if there’s relationships involved?

[@billymartin6497]

Hi, Gerald. Thanks for the video. In Frank's sample, he used MyDocuments, but you're using LocalApplicationData. Essentials doesn't even seem to mention MyDocuments... but it seems to work. Can you please explain this to us? Thanks.

[@crazytv6887]

11:20 i have error in that if - Cannot implicitly convert type 'SQLITE.Database' to 'bool' what to do?

[@jfversluis]

Sound like you forget the == null part

[@crazytv6887]

@@jfversluis omg i used = instead of == Thank you

[@RaabStephan]

Will you go over encryption with SQLite?

[@asturnebur]

how you get relationships between tables (inner joins for example)?

[@jfversluis]

Great suggestion, thanks!

[@apurv_singhal]

Hi Gerald, You are great and your videos are very informative. Is it possible to make a video on Offline First Mechanism where the local data will replicate the Database Server so that the Mobile App can have an offline mode also, if the mobile device is has no network, then data will be stored on Sqlite(Local database) and once the device is online the data will automatically sync the data to the server.

[@larslarsson2843]

System.TypeInitializationException: 'The type initializer for 'SQLite.SQLiteConnection' threw an exception.'........... im going crazy.... why please help me ?

[@knad9752]

Doesn't work. I've follow 100% and copy and paste. Error at GetPeopleAsync and SavePersonAsync 9:42

[@jfversluis]

What error might that be?

[@snooki9]

Dear Gerald, thanks for this video ! I have question a difficulty to understand something with SQLiteAsyncConnection. (try to work on MAUI) Does SQLiesasyncConnection create the database file when it does not exist ? But more over, and here is where i am stuck i think : When the dbfile already exist, does sqliteasyncconnection recreate the file or nicely avoid to do that ? I wish to create a app with one database and lot of table. The thing is I don't now where to put this database creation "order", and where to put the "createtableasync" order (which if i understand well create the table if not exist and don't if if it's existe) : Can I put the sqliteasyncconnection once in the app.cs (or appshell if MAUI i don't know) and put the createtableasync in the service code of each of my model ? Or should I put the sqliteasyncconnection once in every service code of every model ? Thanks in advance !

[@jfversluis]

You might want to watch also this one, I think this makes it more clear: kzbin.info/www/bejne/nKWnomCimtZoha8

[@dobrygraczus3344]

I have got this Error CS0053 Performance inconsistency: property type 'Database' is less available than effect 'App.Database'. What i doing wrong

[@philippedoumet6073]

Great tutorial!!👌👌

[@jfversluis]

Thanks! 🙂

[@gulamzain3015]

how do i get db file in mobile storage...Iam not able to find or is there any way to get?

[@emmanueladebiyi2109]

Awesome video!

[@jfversluis]

Thanks!

[@vishalreddy3756]

Hello Gerald ! Thanks to you for making my life with Xamarin easier ! Would appreciate it if you could teach me how to check if the user login details are correct in the local database.

[@vishalreddy3756]

And only allow the user to enter the app if his/her details are correct. If not, i'd like to display a message saying "Register with US"

[@kratos7ism]

great tutorial Gerald . One question i have is where is the databases and its tables stored when the project is compiled and how can i access them and view the stored data ???

[@jfversluis]

Thanks! It's all saved in the DB file that is mentioned. Depending on what platform you're running on you can find it on your local disk. This works for iOS, Windows and macOS, for Android it is actually stored in the emulator and a bit harder to get out.

[@kratos7ism]

@@jfversluis So based on your example there should be a people.db3 file created after compiling the code if i got that right ???

[@charlesemge3824]

Question: Using "InsertAsync" seems to miss database inserts when done on a large scale (looping quickly adding lots of entries). I switched to non-Async to fix the issue. What is the best way to test if the Async Connection is busy, or to prevent this - Thanks!!!! Love your content.

[@jfversluis]

Hm I think it should throw an exception in that case? Maybe that is being swallowed because of something async in there? Not sure tbh... Could you do it all at once in a transaction? I guess that would be beneficial for perf as well. And thank you! :)

[@charlesemge3824]

@@jfversluis I agree (it should). Ill take a look at all at once - 862 entries in an insert statement would be a big string. Ill keep playing with it. Thanks

[@charlesemge3824]

Awesome! And thanks.

[@jfversluis]

My pleasure Charles!

[@ilyas_tatarin]

Hi, I have question about SQLIte. While working on project we faced a problem. Our class have a List, which cannot used for SQLite. Am I right that I have to use ManyToMany. BTW class have the list of other class. Could you help with this issue. How to use these methods. Thanks

[@jfversluis]

I think you have an extensions library to be able to use many to many relationships, but you might want to consider structuring your data bit differently at least on the client.

[@ilyas_tatarin]

@@jfversluis Thanks a lot!!

[@chucky83331]

Thank you for great video, could you help me on how to sum a SQLite column and store result in a variable. I've played with this for some time without luck so any help would be appreciated very much. I'm retired and have just taken up C# programming to keep me busy in old age and programs like yours are a very big help. Thank You

[@jfversluis]

Hey Charles, that is amazing! I hope you're getting along well. I don't know what you're trying to do exactly without seeing your code, so it's hard to give solid advice. If it's not too many records you could do this: github.com/jfversluis/XFSQLiteGettingStartedSample/blob/main/XFSQLiteSample/Database.cs#L19 and get all the records and loop through that in your own code with a for or foreach loop and increment a variable with the field on the Person (in this case) so you'll have the sum. Or you can write a SQL query yourself which is more efficient but a bit more advanced. That will get the sum of that column in one statement basically. Hope this helps a little. Good luck!

[@moomenmohamed810]

Good afternoon, i liked a lot this tutorial. Just have a question, how can I insert a photo into the database ??

[@jfversluis]

You typically don't want to do that. Databases aren't great for storing binary data. Save the picture somewhere else, maybe even online and save the url or the path in the database

[@gregorymoore2877]

@@jfversluis Correct. Sqlite will happily store anything you want in the database. Images would typically be stored as a BLOB. But this is a bad idea because over time your database file will grow large and queries will get slow. Better to store the images as a file and then just store the path or url as a string in the database.

[@aleemosi]

Hello. I have made an app calculating pay but I what I would like to solve, if you don’t mind making a video on how to get the sum of the weeks pay by doing a search by date for two weeks pay and show in a text field. Can you please make a video for that.

[@aleemosi]

By the way, I use SQLite

[@jfversluis]

That sounds very much like specific business logic you’re trying to write for your app :) I’m trying to show concepts that you can use in your app. Unfortunately, I can’t write your app for you

[@aleemosi]

@@jfversluis The app is not for any business use nor for profit. Is only for my practices and learning. I have been trying to solve this issue for months and never can't figure out why my code is not working for me. But thank you.

[@andrewvasiliou2525]

Hi Gerald, great videos. can you create a video on how to store data from a second page of your app to a database.?

[@jfversluis]

Not sure why a second page would make any difference?

[@glennthomson8808]

Hello Gerald, thanks for you hard work on videos, can you do a fairly simply video on CommunityToolKit CameraView, I could not get the sample to work (outside of the sample), to many files and references for me to understand. Sometimes I think the programmers are a little crazy with complexity.

[@jfversluis]

Hi Glenn, I'll see what I can do. I don't really understand what you mean with the too many files and references though. You should be able to install the Toolkit, add the namespace and add and that should get you going :)

[@avnermosh]

Is there a way to start with pre-existing sqlite database (e.g. for demo purposes) and just load it, without having to create it?

[@jfversluis]

Great question! I'll see if I can make something about that... Technically you're providing the path like I do in the video, so if you add a existing file and put that in the right place, that should just work :)

[@benjamindlion7544]

I'd really appreciate this too as it's driving me up the wall! Someone somewhere says you can drop a .db3 file into Resources/Raw and specify it as a MauiAsset, but don't actually give an example. I really want this to be true, as my app is based around a pre-loaded database which I need to maintain. I achieved it in Xamarin by creating a db as you have done, then searching for it in my disk files and replacing it with my own. But this method is different for all operating systems and very messy!

[@denisgudiel5574]

Amazing, you could create content on how to create a crud with SQL Lite. Thanks for sharing your knowledge

[@jfversluis]

Thanks! What exactly are you looking for? This video already has create and read :)

[@denisgudiel5574]

@@jfversluis sorry I read wrong, excellent video

[@wingkinbrianlam5053]

is it alright if i just create another CS and put it in return method instead of adding database in App_xaml_cs

[@jfversluis]

Whatever you need to make it work on your project :)

[@nandukathmandi1523]

Great Video, you are always coming with great content everything is helpful for me... I am running your same code in my machine I got one error like, "no such table: Person" can you please help me to resolve this issue.

[@jfversluis]

You are using the exact same code? Are you sure you have that CreateTable() line in there?

[@nandukathmandi1523]

@@jfversluis yes and I tried with GitHub code also but same issue

[@usamaelhamed4324]

Thanks Gerald, for this video, if it possible to explain how to work with GetWithChildren and all the CRUD operations with that, Like DeleteWithChildre and UpdateWithChildren, your the best my Friend.

[@jfversluis]

Thanks! I'll add it to the list :)

[@sharafudeen4734]

This is great! It would be better, if synchronisation features added.. 👍

[@jfversluis]

Will look into it

[@salehbinjaufan7096]

Thank's so much

[@jfversluis]

You’re welcome!

[@zekadnoureddine6018]

Great video .. think you.. In this sample ... Your path database...not working in device UWP !!!!

[@jfversluis]

Thanks for letting me know !!!!!!

[@rvranny]

Hey Gerald, isn't it good time to move every new video to MaUI?

[@jfversluis]

Haha a lot of the videos would actually work without any changes on .NET MAUI! I'm starting to make more videos on .NET MAUI so stay tuned :)

So far so good creating db is not a problem. But how to access a db3 file that filled before?

[@jfversluis]

Get that file on the device, get the path to it and connecting should work in the same way :)

[@yasminaamroun8682]

can you give us an example of a database with several tables?

[@jfversluis]

What's the difference?

[@AMIRKHAN-dm2fm]

Please make offline syncing demo

[@jfversluis]

You mean download data and use that offline?

[@AMIRKHAN-dm2fm]

@@jfversluis yes and also upload data to server from local db when Internet available

[@kristofszabo666]

Yeah, these would be great to know! If you put your async HTTPS call to the OnAppearing method, would it download the data while the app is loading in? Because currently with my app, it need 1-2 extra seconds, because I couldn't use async/await for that method.

[@AMIRKHAN-dm2fm]

@@kristofszabo666 great and what about sending local data to server.?

[@mdottech6502]

Sir , I am working with .NET MAUI and my app is being crashed Can You please help me

[@jfversluis]

I wish I could, but I'm not a mind reader so it's impossible to know what is going on, sorry!

[@paiin72]

Is it possible with online database to access via ip adress?

[@jfversluis]

You could, but directly connecting to a database like that through a public IP is probably not the best idea from a security standpoint

[@paiin72]

@@jfversluis would it be hard to change?

[@paul790905]

Gracias, thankyou

[@jfversluis]

You're welcome!

[@GunzzYT]

Great tutorial explained it better then my paid teacher XD

[@jfversluis]

Haha thank you so much! Point them to my channel and let them know to just play these in class 😏

[@GunzzYT]

@@jfversluis i will XD. i did run in to a problem, i tried to create the static database in the App.xaml.cs but i kept getting and error "namespace not found.." i tried implementing the using Microsoft.EntityFrameworkCore.Storage; or using static Microsoft.EntityFrameworkCore.DbLoggerCategory; but this didn't solve the issue any tips? (im using Visual Studio 2022 17.3.5)

[@a.anshal]

I cannot get passed the following starting code without getting the subsequent errors: _database = new SQLiteAsyncConnection(dbPath);

[@a.anshal]

The type initializer for 'SQLite.SQLiteConnection' threw an exception.

[@jfversluis]

There have to be details in that exception that tell you more :)

[@haideralifaizi7568]

@@jfversluis i also get this error. I tried many time but getting error again and again. Please, make a video and tell here how can i resolve it

[@黃永昌-c9r]

_database = new SQLiteAsyncConnection(dbPath); _database.CreateTableAsync().Wait(); // add ".Wait()" solve SQLite.SQLiteException: 'no such table:Person

[@jfversluis]

Yep you’re absolutely right :) fixed that for this video; kzbin.info/www/bejne/hWK4gpmlnZlqZ5I

[@davidjackson148]

That sqlite library doesn't work on android but works on windows

[@jfversluis]

I’m pretty sure it also works on Android!

[@davidjackson148]

@@jfversluisI'm moving an app from Xamarin to MAUI and using DI. The app (tho incomplete) worked fine in Xamarin. I'm just trying to get the basics in place and get it to the same place. On a Windows target, I get no issues, switch to an Android target and I get the exception, and it's one of those annoying ones that you can't breakpoint on.

[@davidjackson148]

My code is pretty much identical to yours the only difference is I hide my database inside a service (singleton) that gets injected

[@AhmadRaza-ct3yi]

Can you develop software for me?

[@jfversluis]

Unfortunately I can’t, sorry!

[@memsom]

This is not SQLite, this is SQLite-net. SQLite is quite similar, but all the magic scaffolding and object persistence is not standard SQLite.

[@jfversluis]

If it has everything SQLite has and even more, it's still SQLite 😉

[@gregorymoore2877]

@@jfversluis Sqlite-net is a C# wrapper around Sqlite. So , yes, under the hood you actually are still using Sqlite.

[@jfversluis]

@@gregorymoore2877 exactly!

[@gwisekor]

ef6?

[@jfversluis]

Should be possible: docs.microsoft.com/ef/core/get-started/xamarin I'll see if I can make a video :)

[@memsom]

Ef core. It’s not great with SQLite. A lot of the migration stuff is a bit broken

[@williamprogramer4168]

⭐⭐⭐⭐⭐

[@jfversluis]

It is 5 out of 5 right? 🙀

[@sergeysemkin6570]

Молодец! Мужик! Я бы выпил с тобой водки!

[@jfversluis]

большое спасибо! ваше здоровье!

[@JesseIngles]

JESUS LOVE YOU.

[@jfversluis]

Haha thank you!

[@amigoSeiya]

Hi Gerald! I downloaded the sample code and for some reason, the first time the app runs, both in iOS and Android, the app crashes on the Mainpage, on line collectionView.ItemsSource = await App.Database.GetPeopleAsync(); If I run the app for a second time, it works. What could it be? I tried to implement your code on my app and I get the same error, Can you please check it out? Thanks!

[@amigoSeiya]

I just watched your second video, and the first sample is missing var database = Database; on the App.cs, after the init. That solved the problem. :D

[@jfversluis]

Yeah I noticed after posting it but fixed it for that second video 😅 glad you found it!

[@amigoSeiya]

@@jfversluis This helped me a lot, thanks for your awesome videos :D

[@gideonmensah-b5x]

This very good. Thank you. Will love to have your email account to get in contact with you.

[@jfversluis]

Thanks Gideon! My email address is no secret, however as you might imagine there are a lot of people that would like to ask me or get help so I’m trying to minimize one-on-one messaging as much as possible :) You’re free to join the discord server where there are more like-minded people that can’t help out.

[@gideonmensah-b5x]

@@jfversluis Thank you.. a link to the discord server

[@jfversluis]

@@gideonmensah-b5x Should be under every video :) discord.com/invite/2cAyTGNHBD

[@gideonmensah-b5x]

Thank you... please can you show me how to select the data inot datagridview instead of collectionview