It’s well known that Microsoft made a very clear choice regarding the use of a DBMS within embedded products, from smartphones (Windows Phone) to tablets (Surface WinRT) via the devices based on Windows Embedded Compact 2013; this choice has a precise name and it is SQLite !

From WEC7 to WEC2013 : SQL Server Compact removed !

As far as Windows Embedded Compact, the change was made from version 7 to 2013, even removing the "SQL Server Compact" feature within Platform Builder Catalog Items (in the "Core" features related to the SYSGEN_SQLCOMPACT variable) through which it was easy to add support for this DBMS into the operating system image.

01

Also, in the official MSDN documentation of .Net Compact Framework 3.9 it is described the removal of SQL Server Compact support within the framework itself and the use of SQLite is reported as a possible "workaround".

02

At this point, we just have to accept the choice made ​​by Microsoft and explore how you can use SQLite in our embedded systems.

SQLite : native support and managed support (with ADO.NET)

SQLite is a standalone DBMS like SQL Server Compact and it consists of a single DLL through which you can manage your databases. On the official website the source code is available and it is mandatory to recompile them for Windows Embedded Compact 2013. Fortunately, there is a very useful project on CodePlex, SQLite for Windows Embedded Compact 2013, by David Jones (along with me one of the members of the "board of directors" of the Embedded101 community) through which it is possible to have the binary DLL of the native code of SQLite library so you can also include it in the operating system image through the Platform Builder Catalog Items with a simple click.

03

Regarding managed code support, it is available for a short time an ADO.NET wrapper on the official website in the section "Precompiled Binaries for Windows CE (. NET Compact Framework)" for the. Net Compact Framework 3.9 (for both ARM and x86 architecture) . It is in addition to those already available for the. Net Compact Framework 3.5.

04

In this way, we have the full support of SQLite on Windows Embedded Compact 7 and 2013, both native and managed code.

SQLite ADO.NET : a big view

Once you have downloaded the zip file related to your interested architecture (ARM or x86)  for .NET Compact Framework 3.9, we find the precompiled native library ( SQLite.Interop.090.dll ) and the wrapper ADO.NET managed assembly ( System.Data.SQLite.dll ) inside it, both files must be distributed to our target : the native library may be included directly in the operating system image through a subproject and a corresponding BIB file while the ADO.NET assembly may  be included in the application folder itself.

For those who already know the data access through ADO.NET ( SQL Server Compact or not) the use of the namespace System.Data.SQLite with all its classes will be absolutely familiar.

The connection to a database is created through the SQLiteConnection class to which it is necessary provide the connection string , which must contain the absolute path of the database , after which the opening is made through the Open() method that provides to create the database if it does not exist .

   1: string databasefile = "test.db";
   2: SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;",
   3:     Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase), databasefile)));
   4: conn.Open();

Once the connection is established, you can perform all the CRUD operations on the database.

Each command to interact with the database is represented by SQLiteCommand class that can be instantiated using the constructor or the CreateCommand() method of the SQLiteConnection class. The execution of a command is performed through the SQLiteDataReader class using the ExecuteReader(), ExecuteScalar() and ExecuteNonQuery() methods.

   1: string cmdText = "SELECT * FROM MyTable";
   2: SQLiteCommand cmd = new SQLiteCommand(cmdText);
   3: cmd.Connection = conn;
   4: SQLiteDataReader reader = cmd.ExecuteReader();
   5:  
   6: while (reader.Read())
   7: {
   8:     Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2));
   9: }
  10: reader.Close();
  11:  
  12: //...
  13:  
  14: SQLiteCommand cmd = conn.CreateCommand();
  15: cmd.CommandText = "SELECT * FROM MyTable";
  16: SQLiteDataReader reader = cmd.ExecuteReader();
  17:  
  18: while (reader.Read())
  19: {
  20:     Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2));
  21: }
  22: reader.Close();

In addition, transactions support is available using the SQLiteTransaction class that is instantiated by the BeginTransaction() method of the SQLiteConnection class and it can be canceled or committed through the Commit() and Rollback() methods.

   1: using (SQLiteTransaction trans = conn.BeginTransaction())
   2: {
   3:     try
   4:     {
   5:         using (SQLiteCommand cmd = new SQLiteCommand())
   6:         {
   7:             //...
   8:         }
   9:         trans.Commit();
  10:     }
  11:     catch
  12:     {
  13:         trans.Rollback();
  14:     }
  15: }

The last mode of interaction is through the DataSet with a disconnected data access, using the SQLiteDataAdapter class and its Fill() method to load the data and the Update() method to update the database.

   1: DataSet ds = new DataSet("MyDataSet");
   2:  
   3: SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
   4: adapter.Fill(ds, "MyTable");

Conclusions

We can agree or not with the Microsoft choice to adopt a new database for embedded applications and not to bet on a homemade product. The transition from SQLite to SQL Server Compact can be considered painless thanks to the use of the ADO.NET interface. Moreover, the wide use of SQLite on other operating systems and in the mobile world and the fact that it is completely open source can be an advantage. Surely there is more to be expected in the future like a greater integration between the development tools from Microsoft and this DBMS that became the point of reference within the devices world.