Jan 11

SQL Server CE – Beyond the Mobile Device

We”ve reached the Beta stage for a project and I thought I”d share something we did that I think is cool that not everyone may have considered.  One of the applications we built as part of the project is a WPF desktop app for mobile information workers.  The application runs on a laptop and needs a local database since connectivity is not always available and also because there is a lot of data to crunch (think product catalog and pricing, customer information, order history).  In a previous release of the application SQL Server Express was used as the data store.  In the new version we chose to use SQL Server Compact Edition (specifically SQL CE 3.5).  Normally SQL CE makes developers think of mobile devices running Windows Mobile 5/6.  But Microsoft guidance also points at SQL CE as a good fit for desktop database in an occasionally connected scenario like this.  I think this approach worked out well for us and thought I’d share why. 

Deployment is a big one in my opinion.  Deployment is simplified since you only need to deploy the CE dlls, not install or configure SQL Express or sql users and permissions.  This was easy using a Visual Studio setup project (MSI). You can”t use stored procs with SQL CE so the inline SQL is in the application code, so we just rely on our application’s auto update mechanism for adding or changing queries instead of managing SQL scripts to update procs.  For schema changes, we have a the ability to reinitialize the database by recreating the the database file (.sdf) on the server and downloading it as part of an update.  There are other approaches like SQL script management, but this works for us.  It all depends on your use case.

From a .NET perspective, using SQL CE works just like any other data access.  We chose to use ADO and DataSets to be consistent with some other applications using the same pattern, but nHibernate or linq/EF can be used (I think the designer story isn’t fully baked with 3.5, though) .  We used some customized WCF services and the MS Sync Framework/Sync Services for ADO.NET for synchronizing data (beyond the scope of this post) but again it depends on your use case for what is best.

Some developers might say “No stored procs?  Performance must be bad so I’m going to use SQL Express instead.”  But remember that SQL CE is an in memory database dedicated to your app and doesn”t have the overhead of a service managing multiple connections from multiple users.  We have one table with over 120,000 records and several others with thousands of records.  We had a lot of calculations going on and on the whole it performs well. 

In addition, you can use SQL Server Management Studio to view query plans and add indexes to tune perf. The ability to use SSMS just like you would with a regular SQL Server is another plus.  You can connect and run queries,load data,do what you would normally do.  We also found that troubleshooting can be as simple as getting the database file from a tester and dropping it in to our local development environment.  There are some nuances to the SQL syntax supported, but we didn’t find anything we couldn’t do.

With the today’s release of SQL Server CE 4.0 which has a focus on use for smaller sized web apps and improved integrated tooling in VS 2010, I thought I’d share our desktop experience so people might think about the use of SQL Server CE beyond mobile devices.

Comments Off , permalink

Comments are closed.