Put Your Database on a Diet: SQL Compact Edition

By KENT SHARKEY on 6/9/2008 3:26:58 PM

Just about every application you can imagine needs some way of storing data, thus the importance of tools like Access, Oracle and SQL Server. Lightweight applications can make use of a simple text or XML file, while larger, more complex applications need a full-fledged database. There are some applications that sit in the middle: too much data for a simple file, while you don't want to go through the effort of installing a service just to store some data. Enter the latest edition of SQL Server: Compact Edition.

DatabasesSQL Server Compact Edition (SQLCE) is the version that used to be available only on Windows CE. It is a lightweight database that can be installed by only copying a few DLLs along with your application. It requires less than 2MB of additional disc space. Unlike SQL Express, it does not require the installation of a Windows service. While it does not support all of the features of its larger brethren: for example stored procedures and views are not supported, and the maximum database size is 4GB. However, it provides most of the functionality you need when storing and retrieving data. In addition, it supports the same T-SQL you know and love for querying the database.

You can either create your SQLCE database in code, or by copying from an existing SQL Server database. All of the tables are stored in a single SDF file (just like Microsoft Access), with optional encryption. Visual Studio supports all the tools and APIs you're used to: there is the System.Data.SqlServerCe namespace which includes the usual connection, command and data adapter classes. Retrieving data from the database is the same as with the "regular" editions of SQL Server.

Using conn As New SqlCeConnection(My.Settings.productsConnectionString)
Using cmd As _
New SqlCeCommand("SELECT ProductName, UnitPrice FROM Products", conn)
Using reader As SqlCeDataReader = cmd.ExecuteReader
While reader.Read
Console.WriteLine("{0}: {1:c}", _
reader.GetString(0), _
End While
End Using
End Using
End Using

While SQLCE is useful in its own right, it becomes even more useful when integrated with Synchronization Services. Synchronization Services enables uni-directional or bi-directional synchronization of data between a SQLCE database, and a 'regular' SQL Server database. This enables your application to work disconnected from your main database, syncing updates when the network is available. The database updates may occur in either the server copy, or a client copy. Changes are merged during replication, and any errors that occur due to multiple changes are identified.

Visual Studio 2008 includes the SQLCE tools and Synchronization Services; for Visual Studio 2005, you will need to include them separately. In addition to installing the ability to access and synchronize SQLCE databases, VS2008 includes additional templates to set up uni-direction synchronization (server to client). Adding bi-directional support is a simple matter.

Application Development
Let Apptius save you time and money by adding functionality to your existing programs.
Microsoft Outlook
Apptius offers professional development services for custom Microsoft Outlook solutions
Microsoft Outlook Logo