none
Store local data

    Question

  • Is there already some support for storing data in a local SQL-like database?

    I somehow missed any mention of EntityFramework for WinRT. There seems to be no local database (SQL compact) preinstalled.

    Do we need to implement something like our own serialization solution (like we had on Windows Phone pre Mango)?

    Any recomendations?

    I need to have a local database (some 1000 entries). Syncing records to the cloud when needed is an option I like to implement too. Looking forward doing it with the sync framework (if thats an option with Metro anytime soon).

     

    Friday, September 23, 2011 11:14 AM

Answers

All replies

  • @Tamlocar - the local storage solution in the developer preview is using the Windows.Storage namespace options.  Yes, this is very similar to Windows Phone with the use of IsolatedStorage mechanisms. 

    You mentioned you have 1000 entries desired in the local storage...what is the scenario with so much data locally?  Also, do you need a relational DB solution or just something that you could run LINQ queries off of.


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer
    Friday, September 23, 2011 2:50 PM
  • Hi Tim,

    I am currently using the SQL DB on the WP7 (Mango). Perfect in holding data. Especially if you have to keep the memory profile small...

    No plan on using the cloud yet. At least not in the first implementation steps - makes it much much more complex as you need to do the sever stuff additionally. Sure - that planned.

    1000 entries ist not so much (its could get easily >15000 depending on the datasource). Its a collection of food nutrition facts. The user rarely changing this data - but the user could add/delete stuff there. And there are other tables related to that (consumed food etc.) which are changing very often.

    Using LinQ is essential (and very convinient) - a Collection stored in memory would probably be ok on a device with enough RAM. But it could make the storing/updating the data complex if you use a full serialisation of all data into one file.

    The Sterling DB (Codeplex) is nice as its serializing binary and using per record files. But thats rather slow if you have a full-table scan (where you have not created an index table).

    I hope you guy plan on suplying a SQL like (or NoSQL) DB on release. Otherwise there will be an outcry - all the WP7 devs already gone through that mess once...

    I know of no DB which I could just throw into my package and which supports a fast way via LinQ. Entity framework would be great to have.

     

    Friday, September 23, 2011 6:03 PM
  • @Tamlocar this is good feedback thank you for clarifying your scenarios.
    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer
    Friday, September 23, 2011 6:26 PM
  • I would like to add that a real SQL database (SQLite or SQL CE) is really required. I have a logbook database application for scuba divers (divinglog.de) and 1000 records are really not that much. Please do not add a solution like in WP7 Mango, please add a real database (I'm still using SQLite on WP, because the Mango solution does not work for me). I have to be able to sync the DB file across different devices (PCs < Win 8, iOS, WP, Android), so SQLite is perfect for me.

    I'm surprised that a local database seems to be so strange for you. A lot of apps need that (RSS, Twitter, Email...).

    Saturday, September 24, 2011 1:06 AM
  • Tim.  I'm thinking about writing an OSS Metro app to demonstrate WinRT capabilities.  I was hoping to gather some timing data in a small relational store (like SQL CE 4).  I wanted to start by timing async IO operations for SSD vs traditional disk vs NAS.  The app was going to experiment with ways of notifying user (Power Users, Devs) where latency is occurring, or async failures are occurring.  I think this will get interesting for some Metro apps where multiple 'user actions' are in flight at the same time (moving an HD video, OData call to cloud services, saving settings to SkyDrive, uploading photos to FB).  

    If I had a big pile of async timings from myself and other devs that built from source, then we could do some quant analysis to see what kinds of visual clues we can give the users, and what kinds of sane timeouts we can use for different types of async operations (all async calls should have timeouts, right...).

    There's no relational data store available in Metro?  If so I'll need to look into ETW to see if that could create a stream of custom events at the rate and fidelity I'd like, and record it outside the app.  <groan>

    Saturday, September 24, 2011 8:48 AM
  • http://sqlwinrt.codeplex.com/
    Friday, October 14, 2011 12:12 PM
  • I'd recommend this wrapper for sqlite: http://sqlwinrt.codeplex.com/
    Friday, October 14, 2011 12:13 PM
  • @Atamiri - please note that while awesome, the SQLWinRT project on codeplex is a wrapper to communicate with the classic SQLite engine...which uses APIs that would not pass store validation currently.
    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)
    Friday, October 14, 2011 3:51 PM
  • What exactly is the problem? I know there are some API calls in SQLite for dynamic loading of libraries but the code can be ignored using a preprocessor macro (I'm using version 3.5.8 and they have such a macro there).

    Friday, October 14, 2011 3:57 PM
  • @Atamiri - if you run the app verification tool on an app that uses that library you will see the issues.
    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)
    Friday, October 14, 2011 4:07 PM
  • The SDK for Mero style apps also supports a subset of the ESENT APIs which is a transactional database shipped in-box

    (here's an intro

    http://blogs.msdn.com/b/windowssdk/archive/2008/10/23/esent-extensible-storage-engine-api-in-the-windows-sdk.aspx

    and here are the supported APIs.

    http://msdn.microsoft.com/en-us/library/windows/apps/br205753(v=VS.85).aspx

     

    -Gerald

    Tuesday, October 18, 2011 3:34 AM
  • Besides sqlwinrt.codeplex.com not passing certification, it targets an x86 build of SQLite, and I wonder what the story is for publishing an app for x86 and ARM simultaneously. I wonder if platform-specific code will even be permitted. A good client-side database is essential for an advanced book reader app. But we need something like SQLite or Perst. SQL CE doesn't support full-text search.
    Friday, October 21, 2011 1:10 PM
  • MS has got to be kidding. I wish they would come out and say it is on the way. They tried this with WP7, all the developers complained. WP7 now supports LocalDB. Metro apps should be portable with WP7. It is C# and XAML. I have an app released for WP7 using LocalDB. I am trying to port it to Metro.  Just for starters: System.Data,System.Data.Linq,System.Data.Linq.Mapping all missing. DataContext, INotifyPropertyChanging all missing.

    This should be a no-brainer for code sharing. Metro and WP7 code should be interchangeable. I will ask the question now: Is LocalDB going to be supported in Metro ?

    There is a pure C# port of SQLLite at http://code.google.com/p/csharp-sqlite/downloads/list. I have no idea if this will pass the store or if it can even be compiled in Metro. In theory it should work. I know it works for MonoDroid and Mono-iPhone. I guess I should have started there and not used the LocalDB in WP7.

    Saturday, May 05, 2012 2:13 AM
  • Have a look at SQLite3-WinRT, it allows you to use SQLite in a Metro style app. It passes the Windows App Cert Kit verification since it uses a version of SQLite that calls only supported Windows API functions.
    Thursday, May 10, 2012 3:48 PM
  • @gdoo - cool...however I highly recommend waiting for supported sqlite3.c for WinRT from sqlite team specifically

    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Thursday, May 10, 2012 4:09 PM
  • @Tim: The SQLite source for SQLite3-WinRT is actually taken directly from SQLite's winrt branch. It's this version to be precise, and we'll update it soon to incorporate their latest changes.

    Thursday, May 10, 2012 4:24 PM
  • @gdoo :-)


    Tim Heuer | Program Manager, XAML | http://timheuer.com/blog | @timheuer

    (if my post has answered your question, please consider using the 'mark as answer' feature in the forums to help others)

    Thursday, May 10, 2012 4:26 PM
  • (disclosure: I'm involved in project)

    you can use now Siaqodb as object database for Metro style apps: http://siaqodb.com/?p=818 fully async APi("async LINQ" queries), pass store validation,etc

    We wait for your feedback...

    Monday, May 21, 2012 11:41 AM