After several years writing Line-of-Business (LOB) .NET applications, I started to really like disconnected app architecture.
The idea is to load all needed data at once, keep it locally, minimize access time and server communication.
Good examples of such architecture are various mail clients (Outlook, iPhone mail app) as well as feed readers (Twitter and Facebook apps).
After primary data block is received, the app needs a short-lived connection to the server to synchronize changes: download new and updated records, list of deleted records, upload pending changes and, if needed, resolve optimistic locking concurrency issues.
My LOB app needed all these features, so my quest to find a good library for local data storage begun.
First, I made a list of criterias to search for viable solution:
- concurrent write access (in case user starts several instances of my app)
- compatibility with older/newer table structures (new field is introduced, old version of my app should continue to work)
- be really fast (slow app could be a bummer, remember original FB client for iPhone)
- support indexing
- support upsert/merge operation
- be written in pure C# without p/invokes to be portable at least across .Net processor platforms (x86/x64/ARM) and runtimes (.Net, Silverlight, Windows Phone, WinRT). I don’t want to rewrite my app from scratch every time Microsoft introduces new platform.
- support of isolated storage for Silverlight app without elevated privileges
- support POCO without extensive use of Attributes (since they are pain to apply on tool-generated classes)
- have reasonable compact storage
- be easy to use and understand
After evaluating all of these requirements above, I made following conclusions:
- SQLite is quite good, but it’s native, platform dependent dll. What means you have to anchor your code to specific processor architecture. If you publish your Windows 8 app, you have to submit 3 versions (x86/x64/ARM) every time you update your app. Consider also testing 3 times more. And if you want crossplatform Xaml app for Mac & Windows (I mean good old Silverlight), you can totally forget SQLite.
- SQLite C# port. Too complex to understand, trust and thus use in production. Maybe in my previous life.
- Sterling. This one I liked a lot, some nice workarounds to avoid implementation of full-blown Linq provider especially, but performance and storage were unacceptably slow. Knowing that Silverlight does quota-check on every isolated storage file IO, database access was implemented absolutely wrong technically.
- some other commercial solutions. Fall short in different areas (attributes, performance, complexity, concurrent access etc).
So what do brave people do, if something is just not there? Right, gather knowledge, compile and innovate.
So Lex.DB was born…