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
Anonymous
February 01, 2011
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.Anonymous
February 01, 2011
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. -FahadAnonymous
February 01, 2011
Never used it, but I've heard good things.about Sterling http://sterling.codeplex.com/Anonymous
February 01, 2011
I would like to hear a list of solutions, I use a rool your own solution based on observable collections.Anonymous
February 01, 2011
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...Anonymous
February 01, 2011
The comment has been removedAnonymous
February 02, 2011
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): 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.Anonymous
February 02, 2011
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. SteveAnonymous
February 02, 2011
So, I am wondering why we can't use SQL Server Compact off of Isolated Storage?Anonymous
February 02, 2011
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.. 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..Anonymous
February 02, 2011
Note: I am advocating for infrastructure support for SQLite and SQL Server compact, not Microsoft rolling their own SQLite implementation..Anonymous
February 02, 2011
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.Anonymous
February 02, 2011
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.Anonymous
February 02, 2011
We use effiproz for our isolated storage db- can be a bit slow with 1000s of records but works well...Anonymous
February 02, 2011
I use Perst from McObject. Despite a few shortcomings it has pretty impressive capabilitiesAnonymous
February 03, 2011
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.Anonymous
February 03, 2011
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 www.siaqodb.com Kind regards CarloAnonymous
February 05, 2011
Hi Carlo, Only anonymous comments must be approved.Anonymous
February 08, 2011
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 MVPAnonymous
February 10, 2011
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, -RobAnonymous
February 20, 2011
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.