Silverlight local databases, what do you use?


Completely acknowledging the lack of an integrated local database story with Silverlight, there are many solutions out there. Curious what you use, what you like, and what’s missing from your favorite solution.
Since various solutions cover various options, also curious what scenarios your using your solution for.

Comments (21)

  1. Xavier Fischer says:

    As access to my data are wrapped by WCF services, for local purposes I use serialized objects into isolated storage and use a factory to wrap data calls and redirect them to WCF or IsolatedStorage.

    I'm wondering what are those solutions you're talking about, and what others users are using.

  2. Fahad says:

    Glad this is taken seriously now. Most of the use-cases would be serialize & deserialize user settings in the app, The idea of having a local DB that utilizes the Isolated storage (as Sterling) is cool. APIs should be very simple, may be like the EF 4 POCO model, so we only have to deal with POCO and not with additional attributes and too many naming conventions.

    -Fahad

  3. Kelly Brownsberger says:

    Never used it, but I've heard good things.about Sterling

    http://sterling.codeplex.com/

  4. Steve Strong says:

    I would like to hear a list of solutions,  I use a rool your own solution based on observable collections.

  5. Danijel says:

    The 20 years old proven system: Serialize and zip objects into byte array and store it in Isolated Storage… that's it. Primitive, but works ok in our scenario…

  6. Skully says:

    We use SQL Express and Standard exclusively.  We leverage the SIlverilght Business applicaiton templates for authorization and authentication, the spatial components of SQL Server 2008 to store spatial information, and standard RIA for application configuration.  

  7. Tom says:

    I'm using this CSV reader with WPF now and plan to port it to Silverlight later (if someone else doesn't beat me to it):

    http://www.codeproject.com/…/CsvReader.aspx

    Small, embedded, no dependencies, simple and convenient API, robust, efficient (if used correctly), and easy to prepare and manipulate data in Excel.  If I want to do queries, LINQ to Objects is there and a pleasure to use.

    Of course, I have to handle making and populating classes for my data, and each table is a separate file, but for a simple database design it's fine.  For more complex databases I could see someone wanting ORM tools.  And, this only covers reading data.  If writing is also needed, that could be trivial or painful with this kind of approach depending on what the app needs to do.

  8. Steve.Lasker says:

    Thanks guys, please keep the comments coming.

    Serialized objects are certainly the most common, but typically only works for the simplest scenarios, persisting state of smaller objects, or simple collections, like the list of states or product categories that don't often change. Even persisting the user state of multiple user settings, like window position/size, colors, last search entries (auto complete) type scenarios break down fast if you’re re-hydrating data from serialized objects on disk to memory. Having a technology that can fetch from disk, cache in memory for a given memory limit size, purge memory, and fetch from disk again. Change tracking for local changes, query processor, foreign key/relationships are the core backing technologies I think of for the more mainline scenarios like a list of products (10’s of thousands) which only get few updates. Rather than request the list from the server each time, store the catalog locally, and only require the server to provide the delta of changes. It reduces the workload of the server, and gives (if done right) a quick response to the local user, even for the connected scenarios. Of course, there’s the occasionally connected scenarios where users go into tunnels on trains, into the mountains, basements of buildings, etc.

    Skully: So, I’m curious if you’re referring to using SQL Express on the server, and connecting from the Silverlight client, or do you really go to the extent of deploying SQL Express on each client? Nobody doubts the power of SQL Server (Express), but having to carry that package to the client and maintain it has been a long standing problem. SUV’s are great for their purpose, but just can’t beat zipping around the mountains on a great sport bike 🙂

    Tom: CSV’s is a good solution between serialized objects and SQL Express, and starts to fill a bit more of the gap, but it also doesn’t go very far.

    Kelly: Yup, I’ve recently learned of Sterling and talking with Jeremy about what he’s got going. And it does look good, which is what actually prompted this post as I’m curious what else people are using and why they like the solution.

    Fahad: Always serious about this area 🙂 and now that I’m working on Silverlight, with our continued focus on LOB, hoping we can get a solution here soon.

    Steve

  9. Mark Bosley says:

    So, I am wondering why we can't use SQL Server Compact off of Isolated Storage?

  10. Don Burnett Expression Blend MVP 2010-211 says:

    I wrote an article about adding better tooling support for SQLite. I would like to see Microsoft put in infrastructure support so I can better support this in Regular Silverlight and mobile apps for Windows Phone 7.. If you are going to support databases do it right, let us support better record locking locally etc..

    http://www.uxmagic.com/…/Silverlight-PInvoke-and-Local-Databases-Oh-My!.aspx

    thanks

    Don Burnett

    PS I am no longer a Silverlight insider to post requests there because I refused to participate in discussion post PDC 10  which I considered unproductive at the time..This is why I am posting this here..

  11. Don Burnett says:

    Note: I am advocating for infrastructure support for SQLite and SQL Server compact, not Microsoft rolling their own SQLite implementation..

  12. Rod Mac says:

    It would make a whole lot of sense to use SQL Server Compact for local, disconnected support. I was a bit gutted back along SL didn't support datasets but I guess EF4 would suffice.

  13. Theo Albers says:

    When storing data locally in a relational database we enter the object relational problem again, but now on the client. I mean you do a lot of work to get an object graph on the client and now you have to map that structure to database tables and back. So having an object relational kind of store would be much more convenient on the client. You most likely want to  pull in objects instead of rows on the client.

  14. Rod says:

    We use effiproz for our isolated storage db- can be a bit slow with 1000s of records but works well…

  15. Adrian Hara says:

    I use Perst from McObject. Despite a few shortcomings it has pretty impressive capabilities

  16. Steve.Lasker says:

    Mark, Rod/SQLce off isolated storage: It’s a great question, and wish we had a better answer. SQLce, to get its performance, small footprint and platform support for phone and desktop is a combination of native and managed code. A few years ago, we did prototyping work to bring SQLce to Silverlight, but that effort was cancelled for a number of reasons. While it’s possible you could access SQLce with the P/Invoke features we’ve announced with SL5, it wouldn’t be a very viable solution. SQLce on desktop was focused on the Managed APIs which don’t work in Silverlight as there’s no core ADO.net features within Silverlight. The Native APIs for SQLce don’t meet the historical desktop expectations for OleDB, not to mention they’re just a very old programming paradigm. So, yeah, the most obvious isn’t necessarily the reality.

    Don/SQLite: The same problems we faced with SQLce and the lack of the ADO.net client within Silverlight are the same problems SQLite would have, as mentioned in the link you provided. Sure, you could pull in the ADO.net core libraries, which is what we looked at doing within the SQLce team as well. The P/Invoke model might have more capabilities with SQLite depending on their API, but the HTML 5 angle is more of what we’re considering longer term. That said, it’s the “now” that I’m curious about, as we firm up our future plans, and consider what people can do today.

    At the end of the day, I also agree that once you’ve converted your transactional, table/row/column sql datatype data into objects with CLR types, it’s a bit weird to have to convert those back to SQL types, whether in SQLce or SQLite. Which is what Theo starts to tap into.

    So, why do I seem to keep answering my own question with my own answers?

    I’m trying to get a sense of what people are doing to fill these gaps. The scenarios for local data are constant, but each solution seems to fill a specific scenarios, so I’m curious what you like about each product/solution and what scenarios you think it covers, vs. what it doesn’t.

    Keep the ideas coming as there are a few solutions I wasn’t aware of.

  17. Carlo says:

    We are using Siaqodb on Windows Phone 7 and Android as our local storage.

    A huge plus is the syncprovider for the syncframework 4.

    For more informations see http://www.siaqodb.com

    Kind regards

    Carlo

  18. Steve.Lasker says:

    Hi Carlo,

    Only anonymous comments must be approved.

  19. Hi Steve,

    Good to see you back 🙂

    We are using Perst, a C# object database from McObject. I blogged about it here: mobileworld.appamundi.com/…/perst-a-database-for-windows-phone-7-silverlight.aspx .

    It is very performant and is easy to program. You can use it in 'raw' mode where you have to write your own code to make sure indexes are maintained properly, but it has a Database wrapper class that makes it behave like a relational database.

    We have used it for an LOB app recently, persisting a collection of 24000+ products. With only a little effort and the choice of the right indexes (one that supports random access), you can databind that 24000+ collection of entities to a ListBox for example, and it will only deserialize the visible items (and a few either side) – very performant, and very memory efficient.

    Since the persistent store is a single physical file, it's easy to build a Perst database server-side and then have code on the phone copy down that file – easy way of deploying a database with *loads* of items in it.

    It's easy to work with and I love it!

    I'm currently working on a sync provider for it so it can do two-way data sync with a Sync Framework 4.0 solution.

    Andy Wigley – Windows Phone Development MVP

  20. Rob Tiffany says:

    Andy's on the right track with Perst and Sterling looks pretty good too.  I've just been serializing collections of objects to Isolated Storage.  I create a simple, in-memory object database solution where each deserialized collection acts like a table and you use LINQ to query them.  No rocket science here.

    Hope all is well,

    -Rob

  21. Rod Mac says:

    Re: 'Nobody doubts the power of SQL Server (Express), but having to carry that package to the client and maintain it has been a long standing problem'.

    Apologies for the late post but I had a thought which goes hand in hand with a problem I have on a large ISP's shared SQL Server where the facility to backup a SQL Server DB has been getting harder and harder for non-developers and not least because the DPW seems now to only run as part of Management Studio which is way too difficult for non developers (check out Fasthosts).

    When I started DB development with Access umpteen years ago we ALWAYS created a solution with two Access database files: a front end with the code (and the UI which is not relevant in my suggestion) and a pure data back end. It seems to me the problem is that things like SP's i.e. back end code require a service which hinder portability. This is typified on the shared platform my customers use where backing up schema and data in one hit has become very difficult indeed. How would it be if we had one SQL file for nothing but data (running on all versions of SQL including Compact) and one file for things like SP's (which of course would not run on Compact). You FTP/ x copy the data file wherever you want it and the server versions of SQL Server do all the things we have come to enjoy.