SQL Server 2005 Compact Edition

Here is my problem… I need to develop a PIM (Personal Information Management) application. It will be a small client application that users can take along with them on their laptops or USB drives or even PDAs. What are my storage options in this scenario?

Having an XML or text file to store complete data for this application is not even worth of thinking when you know that your data is growing and there will be sensitive information that needs to be stored securely and searched quickly.

Another option is MS Access mdb file. It has its own issues and the debate of whether it is a better choice is still going on. Now that I heard about SQL Server 2005 Compact Edition (CE) (previously SQL Mobile or SQL Everywhere), I would certainly choose it just because I can leverage on my SQL skills and be consitent all across.

Microsoft SQL Server 2005 Compact Edition is the next version of SQL Server Mobile adding the desktop platform. SQL Server Compact extends the SQL Server Mobile technology by offering a low maintenance, compact embedded database for single-user client applications for all Windows platforms including tablet PCs, pocket PCs, smart phones and desktops. Just as with SQL Server Mobile, SQL Server Compact is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005 for developing desktop and mobile applications.

Unlike SQL Server 2005 Express Edition, CE is much smaller (less than 2 MB) and can have a database within a password protected single file (with sdf extension). I completed its installation within a minute. I used Visual Studio 2005’s Server Explorer Data Connection to create a new database and to connect to it. Same interface can be created to design and populate tables. You can also use SQL Management Studio to mange the database.

To use it in an application, add a reference to System.Data.SqlServerCe. Then connecting to this database is a breeze.

Imports System.Data.SqlServerCe

Dim objConn As SqlCeConnection
Dim strCon As String

strCon = "Data Source=FirstDB.sdf;Encrypt Database=True;Password=Welcome123;File Mode=shared read;Persist Security Info=False;"
objConn = New SqlCeConnection(strCon)
objConn.Open()

Inserting data is also very simple:

Dim objComm As SqlCeCommand
Dim strSQL As String

strSQL = "INSERT INTO TestTable (ColName) VALUES ('" + txtName.Text.Trim() + "')"
objComm = New SqlCeCommand(strSQL, objConn)
objComm.ExecuteNonQuery()

Getting the data back:

Dim objAdapter As SqlCeDataAdapter
Dim objDataset As DataSet
Dim strSQL As String

strSQL = "SELECT ColID as [ID], ColName AS [Name] FROM TestTable"

objAdapter = New SqlCeDataAdapter(strSQL, objConn)
objDataset = New DataSet()
objAdapter.Fill(objDataset, "Products")

dgResult.DataSource = objDataset.Tables("Products")

I created a small windows .NET 2.0 application that insert data into a local CE database and populate it on a grid as shown in the above code. I could make it work in other machines where CE is not installed by copying all the CE dlls along with my executable. It is that simple and easy.
The advantages are many. It is in-proc and supports transactions. When the application grows, it is easy to migrate to SQL Server. The synchronizing features helps to get the local data synchronized with a central server using RDA (Remote Data Access) or Merge Replication.

The issues? It does not support views, triggers, stored procedures or user functions. Though you can have SQL scripts embedded in your project separated with GO statements and get them executed together. The key minds from Microsoft behind this technogy, Steve Asker and Anil Nori explined in their web cast that they are not planning to provide support for stored procs, etc, not just because that the size of this product will grow, but mainly because they dont want the business logic to be residing in the database.