How to store data / use databases with Windows Universal Apps (C#, XAML)
I'm looking into developing Universal Windows Applications with C# and XAML. If I were to design an application to log user's meals (nutrition log app) I'd need a database of foods (nutrition data - protein, fat etc.), user's data (age, weight, height, goals, calorie needs etc.) and meals (time, foods consumed, quantity of each etc.).
Some of these would be local, some not.
QUESTION: What's the best approach to take here, what kind of databases should I be looking into, what form to store data in, what's the best way to store a lot of data and have it readily available, what do others use with Universal Windows Applications?
Side note: I've written a simple C# Console Application that takes my data, calculates my needs, allows me to input food's nutrition data and then use that data to log meals. This data is written in separate XML files (user.xml, meal.xml and food.xml). I can do all kinds of queries using LINQ - what I've eaten in the last X days, how much do I still need to eat today, how much protein does food X contain... But XML can't be the best way to store and handle lots of data.
You can use SQLite. It is a full-blown SQL relational database which stores its data in a single file. It has ports for PCL and Universal apps, and has a .NET connector which allows it to work with ADO and LINQ. See download page.
We have been using SQLIte and its working fine for us. You can use either SQLIte PCL if you wish to work with raw SQL queries or SQLite NET which gives you object instances to play with.
Only catch is that SQLite is a native component which forces you to build against three CPU archs x86, x64 and ARM. So you need to have three different build output. Some details here http://codifyit.blogspot.in/
For your data which you does not get updated too often, you can use some kind of database caching controlled from your code.
We are using Azure Mobile Apps and leveraging;
using Microsoft.WindowsAzure.MobileServices.Sync; using Microsoft.WindowsAzure.MobileServices.SQLiteStore;
With these you define a local data store;
MobileServiceSQLiteStore store = new MobileServiceSQLiteStore(ApplicationData.Current.RoamingFolder.Path + "\\MyLocalSQLite.db"); Debug.WriteLine("Database Location: " + ApplicationData.Current.RoamingFolder.Path + "\\MyLocalSQLite.db"); store.DefineTable<Account>(); await App.MobileService.SyncContext.InitializeAsync(store);
This allows you to store data in the cloud as well as locally using
private IMobileServiceSyncTable<Account> accountsTable = App.MobileService.GetSyncTable<Account>();
You will need to read up on using them a bit, but its fairly strait forward. https://blogs.msdn.microsoft.com/azuremobile/2014/04/07/deep-dive-on-the-offline-support-in-the-managed-client-sdk/ got me started.
These tables can convert to collections;
Accounts = await accountsTable.ToCollectionAsync();
which you can then bind to with your UI.
All in all its a pretty slick system once you make it up the learning curve, some of it is still pretty new though, and you will run into a few gotchas. Happy Coding!