Musings on the database formerly known as Sql Everywhere

The IM volume I received today about the Rollback to SqlCE issue was enough to border on overwhelming.  Between PMs and some lazy SOB CRM guy trying to get me to do all his work for him, it was hard focusing on work today. (And seriously, if you are a CRM professional, telling me you don't know how to do a two table, one field inner join so i have to do it for you, makes you look like a Complete Moron.  Try 'Bill, I'm really busy, can you do me a favor and write this join for me' next time, at least then my gag reflex won't kick in).  But I digress.

Anyway, I've been getting a lot of email about my presentations on Everywhere.  I guess I got people thinking about a few assertions I've made.  In a nutshell, I made the following comments:

• I love ADO.NET and in no way am I disparaging it, but in some scenarios, the disconnected model just doesn't fit.  This has nothing to do with any deficiency in theory or implementation of ADO.NET.  A Ferrari is a great vehicle but if you need to tow a heavy boat, it's not the right vehicle.
• The one case where I don't think the ADO.NET architectue is well suited to handle is single user scenarios where you are *positive* that you'll only have one user connecting to your database.
• Devices like Smartphones and PDAs proabably aren't going to be targeting multiple users simultaneously and in those cases, where you are resource constrained and pretty much assured of a single user, the database->dataset model is overly consumptive.

In case you don't understand why, let me explain.  What does using a disconnected model bring to the table?  Essentially it brings tremendous scability and at least as far as the server is concerned, the possiblity of improved performance.  It is a Just In Time data access strategy that can greatly minimize locking and bottlenecks.  Like anything, it needs to be used wisely but the fact you don't have to keep a persisent connection to the db is huge.  This benefit isn't free.  In exchange for it you have to pay a price in RAM b/c you're using cached copy of the data.  In most cases, this tradeoff is well worth it.  In some though, it makes very little sense.  Think of a Smartphone.  I have one user.  Only one person needs to be hitting the db at once.  I have the data persisted to disk and I have limited RAM.  What benefit does sticking a dataset in between my UI and the backend db bring?  None.

That's where the beauty of using SqlCE/SqlMobile/SqlEverywhere/SqlCE comes in.  You can use the SqlCeResultset which is scrollable and updateable, and you can cut out the layer in the middle.  Your logic is trimmed down b/c updates/inserts/deletes require no work on your part provided the table you're hitting is keyed.

But that has a problem.  What if the data needs synchronized with a db server?  Well, you can roll your own logic.  Or you can use a familiar, well documented, well tested and peer reviewed mechanism.  Unless you're getting paid by the hour and you have no compunction about robbing from your clients, use the latter.

So in short, on a SmartPhone or PDA, SqlCe/SqlMobile/SqlEverywhere/SqlCe is a seemlingly perfect solution for data access. Now that you can run it on a desktop, the same holds for single user scenarios there.  And using a SqlCeResultset is *not* a replacement for ADO.NET.  It's still ADO.NET in every sense of the word, it's just a evolution of one aspect of it. And a darned good on for single user scenarios.