SQL Server 2005 Compact Edition released

17 January 2007

I have been playing around with SQLite, which is quite effective and impressive. There is even an ADO .NET Provider that embeds in the same assembly the native SQLite API, so that your single dependency is a 538Ko assembly for a fully functional self-contained database. For all this, you will not get support for distributed transactions though. That seems fair, but no exception is raised when you try to enlist a SQLite connection into such a transaction (unlike the MS Access OLE DB ADO .NET Provider for instance).

Microsoft have just released their own version of an embeddable database. It could have been called SQL Server Everywhere Edition, but eventually we have SQL Server Compact Edition. Obviously, its main benefit is the compatibility with other SQL Server versions (especially the SQL syntax). There are even tools to “easily” synchronize your data with plain server hosted databases.

Beware though: if you install the SDK, you will also have to install the desktop client (or else you are very likely to get an error stating that the sqlceme30.dll file is missing…). And deployment does not come free: a private file-based deployment means to copy 8 files (including the ADO .NET provider), amounting to 1.6Mo.

The ADO .NET Provider documentation itself is rather scarce (mainly a redirection to the standard SQL Server Provider documentation). So the main thing is that distributed transactions are not supported, which is OK. Another thing is that the SqlCeCommandBuilder class does not support named parameters (the protected GetParameterName(string) method throws an exception). That is usually not a problem unless you use this hack. Oh yeah, and the SqlCeConnection.GetSchema methods are not supported (throw an exception) as well…