SQLCE – Embedded database

How to use SQLCE

When we develop an application that requires small and handy data store, there are many options such as text file, ISAM, mdb, XML, etc. But I guess one of good candidates is ‘embedded database’ which is a tiny DBMS, tightly integrated with the application.

SQL CE (SQL Server Compact Edition, SSCE) is one of embedded database software, free from Microsoft, which can be used for desktop and mobile devices. SQL CE 3.5 (latest at this point) comes with Visual Studio 2008 but can be installed separately from the web. (Note: VS 2010 / .NET 4.0 doesn’t have new SQL CE 4.0, so we should use SQL CE 3.5)
*Note: SQL CE 4.0 was released on March 2011. Please refer to this link : http://www.microsoft.com/download/en/details.aspx?id=17876.

In order to use SQL CE in VS project, first we need to add reference to System.Data.SqlServerCe.dll.

Once its reference is added, we can use all SqlCe* classes, pretty similar to other ADO.NET classes. Let’s take a quick look.

(1) SQL CE Namespace

All SQL CE classes are under System.Data.SqlServerCe namespace. In C#,

using System.Data.SqlServerCe;

(2) CE Connection String

CE can specify optional password for security purpose. The followings show without or with password connection string.

string connStr = "Data Source=Test.sdf";

string connStrWithPwd = "Data Source=Test.sdf;Password='1234'"; 
 

(3) Create a database file (.sdf)

CE database is stored in one file whose file extention is .sdf. There are several ways to create this .sdf database file. Programmatically, we can use CreateDatabase method in SqlCeEngine class as below:

SqlCeEngine eng = new SqlCeEngine(connStr);
            eng.CreateDatabase();

Using SSMS

If you have SQL Server Management Studio (SSMS), you can choose [SQL Server Compact] Server Type in Connection Dialog, and select [New Database] in Database file combo box. Then below dialog will pop up, you specific db file name, password and some others.

Using VS

If you have VS 2008 or VS 2010, goto Server Explorer (View->Server Explorer) and right click from [Data Connections]. Select [Add Connection], and choose [Microsoft SQL Server Compact 3.5] in [Choose Data Source] dialog. The same (similar) dialog as above will show if you choose [Create] database.

(4) DDL/DML/Query - Create/Insert/Update/Delete/Select

All T-SQL statement can be executed in the same way of other ADO.NET. For example, the following example shows (1) create a table, (2) insert a row, (3) select a table.

using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
    conn.Open();

   // Create a table
   string sql = "CREATE TABLE Table1 (Name nvarchar(10))";
   SqlCeCommand cmd = new SqlCeCommand(sql, conn);
   cmd.ExecuteNonQuery();

   // Insert a row
   sql = string.Format("INSERT Table1 VALUES (N'{0}')", "John");
   SqlCeCommand cmdInsert = new SqlCeCommand(sql, conn);
   cmdInsert.ExecuteNonQuery();

   // Select query
   sql = "SELECT * FROM Table1";
   SqlCeCommand cmdSelect = new SqlCeCommand(sql, conn);
   SqlCeDataReader rs = cmdSelect.ExecuteReader();
   while (rs.Read())
   {
      Console.WriteLine(rs[0].ToString());
   }
}
 

Note: In real application, it is not recommended to use TSQL concatenation or string manipulation as seen INSERT statement above because it can lead to SQL injection bug.

Many developers sometime prefer a help class (or wrapper) for underlying data processing. I added a little sample here.

using System.Diagnostics;
using System.IO;
using System.Data.SqlServerCe;

namespace NS
{
   internal class CeProcessor
   {
        private string sdfPath;
        private string pwd;

        public CeProcessor(string dbFilePath, string pwd)
        {
           this.sdfPath = dbFilePath;
           this.pwd = pwd;
        }

        public bool DatabaseExists
        {  get { return File.Exists(this.sdfPath); } }

        public void CreateDatabase()
        {
           Debug.Assert(!DatabaseExists);
           SqlCeEngine eng = new SqlCeEngine(ConnectionString);
           eng.CreateDatabase();
        }

        public void ExecuteSql(string sql)
        {
           using (SqlCeConnection conn = new SqlCeConnection(ConnectionString)) {
              conn.Open();
              SqlCeCommand cmd = new SqlCeCommand(sql, conn);
              cmd.ExecuteNonQuery();
           }
        }

        public object GetScalar(string sql)
        {
            object result = null;

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString)) {
               conn.Open();
               SqlCeCommand cmd = new SqlCeCommand(sql, conn);
               result = cmd.ExecuteScalar();
            }
            return result;
        }

        public SqlCeDataReader GetDataReader(string sql)
        {
            SqlCeDataReader rs = null;
            SqlCeConnection conn = new SqlCeConnection(ConnectionString);
            conn.Open();
            SqlCeCommand cmd = new SqlCeCommand(sql, conn);
            rs = cmd.ExecuteReader();
            return rs;
         }

         internal string ConnectionString
         {
            get
            {
                if (string.IsNullOrEmpty(pwd))
                {
                    return string.Format("Data Source=\"{0}\";", sdfPath);
                }
                else
                {
                    return string.Format("Data Source=\"{0}\";Password=\'{1}\'", sdfPath, pwd);
                }
          }
        }
   }
}