How to Add a Database to Xamarin & .NET MAUI apps with SQLite-net

[@betteaage8845]

For those wondering why the "id" at 20:10 is 1 when it should be 2, it is because it is not the id that is returned - it is the number of rows affected, which is 1.

[@JamesMontemagno]

Yup, I realized that after I recorded :)

[@chrisjlocke]

Oops, just commented on that. Good spot.

[@mojizze]

I can't believe I've been sleeping on this channel. So many good contents here

[@drivetrainerYT]

Can’t stop likes and comments, thanks James, crispy clear and helpful.

[@JamesMontemagno]

Much appreciated

[@matejlaskomulej]

First time trying to store data from app into device and you made it a breeze. Been using some of your packages before, but just found this channel for the first time. Subscribed! Thank you!!

[@hanh.trinh.yeu.thuong]

You are the best teacher on youtube I've ever seen!!!

[@samosimo9279]

Nice video but we need tutorial of sync local database and database "sync sqlite and mysql" with xamarin forms

[@MukelabaiMukelabai]

You made this look so simple--another mountain laid low for me!

[@radoslavatanasov8846]

James so much thanks about your videos you help us a lot! I have a suggestion, can you do a second part of some of your lessons with things for advanced. Here, for example, you can make an advanced video to show how to create a database with a script, how to update the tables with a script and some more complex queries.

[@yasminaamroun8682]

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

[@jennifermckeithen1498]

This is so helpful! Really appreciate that you walk us through the documentation as well. Thanks so much!

[@btigenie]

Design question .... is it better to open your app to mainpage and then check the database if already logged in and then redirect to a login page if not? Or, start off at the login page and check database and then redirect to mainpage if logged in?

[@jalemanyr]

Great tutorial, One question, why need to add a Delay(2000) in the refresh? thanks

[@JamesMontemagno]

Was just an example to show the refresh ui as it is so fast. No needed at all except debugging

[@rushas]

This man is doing god's work

[@rickfuzzy]

Hey James, tip for you in VS: If you press ctrl-c without selecting anything it copies the line, you can then paste the full line with the line break exactly where you want it. This seems to be fairly unknown, but saves a mountain of time for me. For example to duplicate a line just press ctrl-c and ctrl-v without selecting anything Thanks for the video.

[@AustinHale]

Save yourself an additional step by using the command Ctrl+D to duplicate the current line onto the next line.

[@rickfuzzy]

@@AustinHale yeah I saw that too recently. Thanks for the reminder

[@boyingsplace1945]

Many many thanks... very helpful for beginner like me. God bless!

[@JamesMontemagno]

Happy to help

[@AMIRKHAN-dm2fm]

Thank you so much but if we want to make offline and online sync with local sqlite database then how we can achieve that?

[@AMIRKHAN-dm2fm]

@@codigoadictivo Thanks

[@mozilla9112]

Thank you for this. I can;t seem to locate the MyCoffeeStoredPage on your Github though

[@techman2030]

Great video! It follow the docs I have been reading on using SQLite with MAUI. However, can you provide guidenace on howto use an existing databese with the project ratrher than creating a database from scratch from code. I already have a database I want to use. Thanks in advnace.

[@Yevke]

Great Video, but can you maybe answer me here or in private, where can I find the DB file on my PC/Andorid? I was trying to open it up and to see if my users that registered are saved on DB but I just can't find the file, I was using DB Browser (SQLite).

[@blinkskaterkc]

Extremely helpful video, thank you. I have some reworking to do :)

[@kennethguerrero1591]

Great video as always! Please make one using online db - Azure Table Storage and Functions.

[@gregorymoore2877]

I would interested in this too.

[@salvcri]

James, great video!!! Thanks a lot! Could you do a video explaining how to use an external service to push notifications? Thank you again!!!

[@leonardoyanez6430]

Hi, James, very good Video, you have any video with de Maui and Firebase ? thanks very much

[@DifferentTurn]

James, you're the man! Can't describe how much I appreciate absolutely amazing job you've done for Xamarin Community with a such great detailed videos. 🙃 p.s. Am I the only one who can't help himself but always shaking with head & shoulders to the rhytm of JM's intro music?😅

[@EllieMarieP]

I feel you on the head shaking, can't stop myself.😅

[@brimbon_duke]

Thank you for the video. My question is: Suppose I have already an SQLite DB that I want to use as embedded resource, how I open it instead of creating it please?

[@JamesMontemagno]

You would just point it at the file location. I would probably take the resource and then copy it locally into the apps data directory and then just set the file location and boom data

[@brimbon_duke]

@@JamesMontemagno thank you for the answer. Seems simple enough. You are a hero!

[@MehStang]

Awesome video! Can you also show how to now change databases using mvvm and similar services? Maybe discuss more on attributes? I was watching a mongodb c# video and attributes were used everywhere. (Maybe discuss entity framework) I love also how you are maintaining mvvm! I’m jumping into xamarin and your videos along with your other Best friends in the entire world have been illuminating!

[@0blit]

Great to see how easy this is. Would be interested to see a comparison with setup of Realm DB - and pro/con vs SQLite.

[@cs_minasmins4676]

Super interesting how easy it is to create a simple db app. Thx

[@JamesMontemagno]

Glad it was helpful!

[@mikejaylabustro5947]

Hello James, hope you can make more tutorials in SQLite CRUD. How to insert image in bytes and image file path. How to locate and manage the SQLite database using browser. If it is possible to retain your existing local SQLite database once the app has been reinstalled.

[@mathewbowden]

James, you're are a legend mate, I missed the U in your CRUD example, I followed along nicely but not sure how to change on of the values in a record, does SQLite even allow that?

[@JamesMontemagno]

Oh yeah, just call updateasync github.com/praeclarum/sqlite-net

[@catrox1000]

Awesome video like everyone! Could you create one to show us how to sync this sqliteDB with an webdav drive that the app on an other device can use the same data as well.. so like some way how to sync the entries over the different devices in that app.

[@boysen01]

hey James, great video thanks for putting it out there. You did not mention the reason for the Task.Delay(2000) call in the Refresh() method. What is the benefit of having that call in there? Or, what would happen if there was no forced delay ?

[@JamesMontemagno]

Just to show the spinner in the UI since it is instant return. You should not do this in production.

[@boysen01]

@@JamesMontemagno ok thanks good to know

[@carljohnlopez5425]

How do i pass picker to int then to service then lastly storing it to the database (ex. I want to pass picker id 1 type admin or id 2 type reguser but instead getting the type , get the id then pass it to the service and ultimately storing it to the database)

[@GuildOfCalamity]

I was hoping to follow along from the last video (trying to code it from scratch with you)... but it seems you made a bunch of changes to the app in another video? I guess this playlist is not in the correct order? Going to try and piece this together from the github.

[@haroldmacdonald9065]

Great video. I'm curious if you are able to do this with dependency injection? If you are able to, what are the benefits of using a static class vs using DI?

[@JamesMontemagno]

Absolutely! I would register as a singelton

[@MikeKm-hd1ve]

First of all, Thank you for the great video! Btw, Am I the only one getting SQLite connection exception in the Init() function? I copied the code from the documentation. Can anybody tell me what can be the problem?

[@stephanraab3648]

Just the video I was hoping you'd post soon!

[@jeremybray8050]

I'm having a rather strange issue. When I go to do something like CreateTableAsync, my app freezes. Debugging in VS2022 kind of goes crazy in that if I am stepping through the code and hit that line, I can no longer continue or keep stepping or anything. Any suggestions?

[@synergy.2899]

I am also having this issue, did you ever figure it out?

[@jeremybray8050]

@@synergy.2899 I don't think I did. I ended up ditching SQLite for my project and just storing the data that I need in a JSON file since I'm not trying to store so much that SQL makes more sense anyway.

[@synergy.2899]

@@jeremybray8050 I was contemplating on doing the same thing as well. Its a shame because SQLite seems so nice but it doesnt seem like a lot of people had the same issue as us. Were you working with MAUI Blazor?

[@aggies9296]

Thanks James! Awesome video~ Can you show how to use NOSQL database with Xamarin?

[@kratos7ism]

Another great video James !!!! Aren't we supposed to find a MyData.db file in the app folder ??? Because i can't find one

[@maxalmonte14]

Just a question, can I injection the Database manager into the Dependency service? Or is there a good reason not to do so?

[@JamesMontemagno]

Sure! No real reason not to

[@Hunt4iSW]

Hey James great series so far that I've watched! I am having a nasty problem right now however. As i attempt to refresh and repopulate my items with the objects retrieved from the Sqlite database, the query itself will crash the app with a fatal signal 11. Everything in the DatabaseService class is copied from you apart from my own object class being used instead of your Coffee. I've been trying to figure out what's wrong with the database to no avail. What could cause this?

[@94089463]

Simple and thorough. Thanks again, James. Could you please include how to connect your Xamarin App to an API server (JSON) in your upcoming videos? I need to share data from a SQL server used in house with the outside mobile app users.

[@JamesMontemagno]

I have a video on making web request in the 101 series

[@94089463]

I just found it!. Thanks again for your quick reply.

[@ohwmohammed]

Hi how can I deal with sql server by xamarin using Entity Framework Core Or which ORM to use with sql server in xamarin

[@toma1385]

The same way we use sqlite in blazor hybrid?

[@Brutakk]

Any recommendations on how to deploy a Xamarin app with a prepopulated SQLite database?

[@JamesMontemagno]

you could drop in the sqlite database and read it locally using Xamarin.Essentials from the assets folder

[@chaseroth9266]

Awesome Video! Interesting to see you using a lazy loading technique for initializing your services. In my next application I will give that approach a try. :)

[@VirtualGrime]

Followed your logic most of the way James, just got stuck in two places: 1) Consider your Coffee as my Operators. Where do I declare my GetOperators? public static async TaskGetOperators() 2) Secondly, var operators = new Operators { OpName = opName, OpPhone = opPhone, OpEmail = opEmail }; opName, opPhone and opEmail identify as errors. How can i fix that?

[@cristisandu678]

Wow, you can make a integration with a Azure DB ? I have 150$ on Azure monthly and I try to use it in a application but documentation is a little bit too complicated. Thank you very much! You are a legend 🎉

[@Catsmeat2]

Second this!

[@GAMacky]

Thanks for yet another great video. You have shown at 21:05, when closed and relaunch the app, the coffee db still persists. I think that's because the app was still persist in the memory? because every time the app is launched, shouldn't the variable db be null? If it's null, then the db is recreated with the db.CreateTableAsync statements. Shouldn't that then wipe out what was in the db file?

[@JamesMontemagno]

Create table will only create it if it hasn’t already. The file persists on disk.

[@davidjackson148]

Can you do a MAUI specific example please :)

[@davidjackson148]

Does your CoffeeService have to be s "static" class? I have a dynamic class (that gets injected) with a static DB instance (I couldn't figure out how to DI it, I kept getting errors). But now to a Windows target, it works fine but to an Android target I get SQLiteException, have you seen this behaviour before? Its a Xamarn App that was working fine, that I'm trying to move to MAUI.

[@john-blair]

Why not use a static constructor for initialising the db instead of calling Init() everywhere?

[@JamesMontemagno]

Mostly to lazy load it and also you need to initiatlize the tables and those are async methods.

[@starvr]

James, when I install the SQLite-net it downgrades the essentials and forms, is this going to be a problem?

[@moussababaousmail805]

Excellent work. Thank you!

[@aleemosi]

Hello, your videos are great. Can you do an example on how to get the sum of a total from sqlite and show it in a label?

[@gideonmensah-b5x]

Thanks, this is so helpful. Please can you host the same database online instead of offline. Please can you also help me with that. Thank you

[@JamesMontemagno]

Take a look at my web requests video as that walk through calling web apis which would talk to your data base that is hosted online

[@danmanning6759]

Thanks for creating this video, great walkthough! Question though, I've always thought that it was bad practice to create 1 db connection and leave it open, especially in Xamarin where the app can be backgrounded etc. Is that still true, or am I mixing up sqlite-net and entity framework?

[@mikegrainger8082]

As normal, great video. Many thanks for your efforts.

[@garuda4229]

Love the tutorial. I'm having one issue, when I run the refresh command I get the error System.InvalidCastException: 'Specified cast is not valid.' I can't find anywhere where a variable type or anything wouldn't match so would appreciate any ideas as to what could be causing it. Thanks!

[@JamesMontemagno]

Hmmm if you are able to put a sample somewhere on GitHub and then maybe open a ticket on my coffee app repo

[@DamagedDingo]

How to store this in cloud so that it data is recovered on logon?

[@pjh5757]

I am using this tutorial but I have noticed that when the application is launched with data in the SQLite DB you have to refresh. I have been tinkering with this and cannot seem to find what looks like the "proper" way you would have this DB data shown without the refresh on launch.

[@oroke24]

Error: Found conflicts between versions of “System.Numerics.Vectors”.. I have no idea 😑

[@btigenie]

How could you use an sqlite tool to view the contents of the database either directly on the emulator or by somehow copying it to the development computer?

[@JamesMontemagno]

There is a nice sqlite explorer for VS and VS Code that I have used for web stuff, but you would need to see where things are stored or export the database :)

[@Artur-bd3xd]

Thanks for the video, James! I have been following your tutorial and at the point were I add a coffee to the data base it doesn’t appear after the refresh, the main page is just blank, any suggestions? Thanks! (timing of the problem 19:44)

[@JamesMontemagno]

When you come back to the page call refresh on the view model again in on appearing

[@bjrnhellesylt3922]

Thanks a lot for a great video. How about making a video where you connect to an MS SQL server and authenticate with IdentityUser for Maui. I have not been able to find such a video yet. After all, users are usually authenticated against an online database. Thanks in advance.

[@robertbrown7478]

Great video James! Can't wait to see next weeks episode on pulling data from the Internet. Are you using the default HttpClient or are you using something like Refit? Also it would be cool to see how you handle authentication when connecting to an API.

[@JamesMontemagno]

For start I think I’ll do just HttpClient and talk about how I do some simple helper methods and such and maybe mention refit and others. I just did a Xamarin show recently talking about refit

[@pratyushmishra6941]

HttpClient / HttpWebRequest(depricated) can be used for web services handling. i found HttpWebRequest to be working 10/10 times but HttpClient is a 7/10 ish.

[@itaniralephata4430]

Hi James. I need to develop a employee directory but I'm having issues adding the existing database to display the employees. Can you please assist. Many thanks

[@JamesMontemagno]

Well, not sure what format your database is in, but you could write a small desktop app to convert it into the sqlite-net format that is needed and then drop it into your mobile app and read it with docs.microsoft.com/en-us/xamarin/essentials/file-system-helpers

[@itaniralephata4430]

@@JamesMontemagno thank you for your response. So basically I want to attach an existing table to my Xamarin app and have a search functionality. So I just needed help on adding an existing table to my Xamarin application. Thanks in advance

[@rajnikantpatel4963]

Great video as always Want to see how to do migrations in SQLite.

[@juliandormon7536]

If anyone is getting stuck adding the SQLite file. Using the following solution (also by James) worked for me. var databasePath = Path.Combine(GetLocalFileDirectory(), "MyData.db"); db = new SQLiteAsyncConnection(databasePath); public string GetLocalFileDirectory() { var docFolder = FileSystem.AppDataDirectory var libFolder = Path.Combine(docFolder, "Databases"); if (!Directory.Exists(libFolder)) { Directory.CreateDirectory(libFolder); //

[@matt-i3r6w]

Could you please make a tutorial about Firestore ?

[@abdoaly1485]

Dear James , I want to know if it possible to exchange data between xamarin form and desktop app. using ms-access db ... thanks in advance

[@JamesMontemagno]

I would assume possible if there is an api to call. Never done it. I would probably create a backend service that interfaces with your database. That is best practice

[@flboy77]

Great video. Have to ask as I’m coming from another dev env: wouldn’t GUID be safer than an int id when many devices are sync’ing with one centralized server?

[@PeteMidg]

Excuse my interjection but this case is internal to the device; once you have sync with azure (say) that’s when guids are most useful, but if you start with the intention to sync you should probably start with guids:-)

[@lardosian]

So generally we would create a separate api and connect to that to do the CRUD part?

[@JamesMontemagno]

I usually create a Web API backend with ASP.NET Core.

[@lardosian]

@@JamesMontemagno Thanks James.

[@hidekimatsuura9061]

How would you do full text search with fts5?

[@davidjackson148]

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

[@divangerber7897]

Ok, can you do a video on how to make use of an web API to connect a cross platform app to a SQL Server(EXPRESS) Database. Where a couple of users with their own devices can CRUD to the database from there mobile devices??

[@JamesMontemagno]

I have a full video on rest apis kzbin.info/www/bejne/l2SaonWDqZWJb9k

[@parthathatoi8806]

I'm new in XAMARIN and follow your videos and really you are awesome. I just need - is it possible to joins tables or write strored procedure or complex inline query in SQLite xamarin. I did not find such example anywhere.

[@JamesMontemagno]

Probably something like stackoverflow.com/questions/27260905/join-tables-in-sqlite-net-with-linq-on-xamarin-android-is-not-supported I don't think it has store procedures, it isn't a full like SQL database.

[@parthathatoi8806]

@@JamesMontemagno Thanks James hope healthy and safe. I'll definitely try and let you know.

[@jhdabomb]

Nice tutorial! How can you automatically display the data upon going into the MyCofffee page (@21:10) from the database without pulling to refresh the page?

[@JamesMontemagno]

Just call the method from onappearing in the page

[@jhdabomb]

@@JamesMontemagno Thanks again for the tip! Much appreciate! Is this what you're referring to? I did it like so in the page code behind. private readonly MyCoffeeViewModel _model; public MyStoredCoffeePage() { InitializeComponent(); _model = BindingContext as MyCoffeeViewModel; } protected override async void OnAppearing() { base.OnAppearing(); await _model.Refresh(); }

[@JamesMontemagno]

@@jhdabomb Yup, that should work

[@AS-kw1ob]

How to fill a database with data that are required in the app, and not coming from the user by using the app?

[@yaserpy]

how to build xamarin sqlite crud app ( with images - exp : like photo app gallery )

[@drossoftapps8208]

Great video as usual! Regarding performance, how does SQLite-net compare to EF Core? I use EF Core in my Xamarin apps and have some performance issues (mostly on Android).

[@JamesMontemagno]

EF Core is not optimized for mobile solutions due to reflection so I would probably not go that route unless you are building an internal enterprise app. That said I know the team is trying to improve this scenario in and around .NET 6

[@drossoftapps8208]

@@JamesMontemagno Thanks for the advice!

[@realcolormusic]

Great tutorials and very helpful for beginners. Thank you very much James.

[@ezzaldeen]

What about ef core sqlite ?

[@larslarsson2843]

What im i doing wrong? i get System.TypeInitializationException: 'The type initializer for 'SQLite.SQLiteConnection' threw an exception.' ?

[@lm9006]

Tnx man, you are the best! :)

[@jcvandermerwe5311]

Thank you so much for all this brilliant content! Is there any chance you can do a tutorial for SQLite relational databases? I'm trying to add a ForeignKey attribute to a prop but can't find anything solid via Google.

[@JamesMontemagno]

Frank has an example on his GitHub repo

[@JamesMontemagno]

github.com/praeclarum/sqlite-net

[@jcvandermerwe5311]

@@JamesMontemagno Thanks a million for this! You're a legend.

[@roberthudson8908]

Another great video James! Thanks. A few questions... are there any significant advantages of dealing with SQLite on such a low level? I have a production app with a fairly complex data model. To store the data offline I de/serialize the entire thing using Akavache (which uses SQLite under the hood). Everything works great, no issues at all. Akavache encapsulates all the wiring of tables internally. Same with your MonkeyCache, it's also awesome too I'm starting to use it as well. Question #2 - I'm curious... why do you say MonkeyCache/Akavache et. al. are "caches" only? Why can't they be persistent data stores?

[@JamesMontemagno]

I don't think any significant advantages or not. I just have always saved local data in SQLite over the years so I am used to it. MonkeyCache for sure is a cache layer and isn't intended to bused as a database. You can re-direct the file save location, but I intentionally put it in cache folder by default. I mostly use it for caching web requests and small bits of data. Akavache for sure is more like database as it is a key value store, but is persistent.

[@roberthudson8908]

@@JamesMontemagno Thank you James. That makes perfect sense. BTW, I've stuck with Xamarin Forms since 2014. I wouldn't have done so without you and your awesome contributions to this platform. I appreciate it very much,

[@CraxGaming]

Can you do a navigation video?

[@JamesMontemagno]

It is on my list.

[@CraxGaming]

@@JamesMontemagno thanks. 🙂

[@xixixiaojie]

Hi James! Thanks a lot for this video. I have a problem with my project and I'll like to ask for help on here. I am new to Xamarin and I have a project I'm currently working on where I have to connect my Xamarin app to a serverless backend. I have a signup page where users can register and when they hit a button, it automatically sends their information to my backend. The problem I have now is : How do I collect user inputs? I'm writing the API call to collect the data but I can't seem to get how to tell my program to take whatever is in this entry field (or entry fields), and send it over here. What I did so far was to give the entry fields a name so I can access it in the code behind as "entryField.Text". I tried to declare a variable and make it "entryField.Text" but I can't access the variable in my ViewModel and it also says an "unnecessary assignment". Please what am I doing wrong and how can I collect user inputs to use in my ViewModel and API call? I hope I can get someone to give suggestions on what to do.

[@cissemy]

Great Can you add Update (missing part of CRUD) ?

[@JamesMontemagno]

You just call "UpdateAsync", in a future episode i will add some more screens to the app.

[@cissemy]

@@JamesMontemagno Thanks Also can you make a video about Firebase Cloud Messaging ?

[@mikejaylabustro5947]

Where is "MyData.db" located in solution folder using file explorer? Path.Combine(Xamarin.Essentials.FileSystem.AppDataDirectory, "MyData.db") If it's not possible, where to find it once the app is installed in actual android phone device? Thank you James!

[@JamesMontemagno]

You can see where it this maps to here -> github.com/xamarin/Essentials/blob/main/Xamarin.Essentials/FileSystem/FileSystem.android.cs which is inside of the app specific folder developer.android.com/training/data-storage/app-specific#internal-access-files

[@matt-i3r6w]

Is it better to use sqlite or firebase ?

[@JamesMontemagno]

Depends on your app and if you need the features of firebase and where you want your data to live. If you have all your data in firebase then that would be fine, but if the data is anywhere else then probably not

[@chrisjlocke]

Firebase is a big meaty DBMS isn't it? SQLite is a small library.

[@elkhayyat85]

Thank you Sir for your Tutorials are helpful and easy explanation way.. Really you are a good Teacher. I have a request, can you please make a tutorial About WPF with MVVM Design to build a App like in real life when creating a project?? And sure we would like you add your touch what best ways, and framework and deal with Database.

[@JamesMontemagno]

I don't really do much WPF work, but it is all the same. I would look at the documentation. For database stuff look at Entity Framework Core for you desktop apps.

[@LifeOfLeeofficial]

Awesome!

[@PuineaGip]

Hello James, I copied your code almost one to one and my app just crashes whenever I refresh can you help me?

[@JamesMontemagno]

If you create some gists of code samples then let me know and shoot me a dm on twitter

[@PuineaGip]

@@JamesMontemagno Ok I will! I think I am past that point now but I still have a similar issue and the app still crashes. Will dm you in a bit.

[@jakethompson5129]

Why doesn't it have an audio file option?

[@oktjona]

How to add sql dB yo netmaui

[@hkg511]

can you also save an image ?

[@JamesMontemagno]

You could just use System.IO to save the file locally. If you needed to store the image in a database then you could encode it to base64 probably.

[@maxmannstein]

how to autorefresh?