SQL Server 2005 Compact Edition

While using SQL Server Compact Edition for mobile applications makes perfect sense, using it for regular desktop applications may not be the best choice. Here is a summary of pros and cons of using the Compact Edition with desktop applications.

Pros:

– Very small footprint (only 7-8 DLLs needed, no services installed, runs as in-process rather than separate service). It takes only 1.8 MB installed.
– Easy deployment. While it can be deployed using the provided Microsoft Installer file, deployment can be as simple as shipping and installing the Compact Edition DLLs to the application directory. However, this type of installation will not provide the automatic updates via Windows Update (it provides automatic updates only if installed via the MSI file).
– Support for T-SQL.
– If installed by deploying the DLLs to the application directory then there is no need for administrative user privileges (only the MSI install requires administrative privileges to register DLLs).
– Support for Remote Data Access (RDA) and ADO.NET Sync Framework. This provides convenience and flexibility to synchronize and transfer data between Compact Edition database and SQL Server databases.
– Support for different file extensions, which can help to secure the database file.
– Allows storing the database file on a network share.
– Higher safety and security because it is code free (no support for T-SQL procedural code).
– Encryption of the database file (as well as password protection) provides data security.

Cons:

– Procedural T-SQL code is not supported (stored procedures, views, triggers).
– The T- SQL language is limited (for example, no TOP, IF, ROW_NUMBER).
– No ODBC driver (although the existing ADO.NET and OLE DB data providers are sufficient from programming stand point).
– Missing role based security.
– No distributed transactions support.
– No native XML and XQuery support (XML is stored as NTEXT).
– Only 256 user connections (although this can hardly be viewed as a drawback because of the intended usage as a desktop database).

After all, the SQL Server Compact Edition is a great addition to the SQL Server family and has many benefits for particular applications. Still, to deliver flexible and rich desktop database applications, something like SQL Server 2005 Express may be a better choice.

Libraries:

The following libraries are installed on the desktop with the SQL Server Compact Edition MSI file:

– SQLCESE30.DLL
– SQLCEQP30.DLL
– SQLCEME30.DLL
– SQLCEOLEDB30.DLL (OLE DB data provider)
– SQLCECA30.DLL
– SQLCECOMPACT30.DLL
– SQLCEER30xx.DLL
– System.Data.SqlServerCe.dll (ADO.NET data provider)

Using the OLE DB provider does not require the .NET Framework, while using the ADO.NET provider requires .NET 2.0 or later (since it is using the SQL Server Native Client).

How-to:

– To connect to SQL Server Compact Edition using SQL Server Management Studio, on the login screen in SSMS you select “SQL Server Mobile” for Server type. Then browse to the database file or create a new database.

– The connection string for using in applications looks like this:
“data source=MyDatabase.sdf; ssce: mode=Exclusive;”

Here is a sample code for connecting:

SqlCeEngine eng = new SqlCeEngine();
eng.LocalConnectionString = "data source=MyDatabase.sdf; ssce: mode=Exclusive;";

Additional resources:

SQL Server 2005 Compact Edition Home:
http://www.microsoft.com/sql/editions/compact/default.mspx

SQL Server 2005 Compact Edition How-to Tutorials:
http://msdn2.microsoft.com/en-us/sql/bb219480.aspx