How to Create Database in Windows CE (Compact Edition) OS Operated Device

In this article I will share the code of a Windows-based application that helps to upload files into a Windows CE device as a .sdf file. First I will create the sdf file in the Application startup (Application bin folder) then transfer this sdf into a device using something.

Are you confused about .sdf files?

SQL Server saves a database in a .mdf file, correpondingly a Windows CE device saves a database in a .sdf file. But a .sdf database has some limitations. I will explain the limitations later.

We use a SqlCeEngine class to create a database.

Code to create a database

public static void CreateDataBase()
{
   public static string _DBPATH = Application.StartupPath;
   public static string _DBNAME = "Data.sdf";

   SqlCeEngine SqlEngine;
   strDBURL = _DBPATH + "\\" + _DBNAME;

   SqlEngine = new SqlCeEngine("data source=" + strDBURL + ";Max Buffer Size = 512; Max Database Size = 500");
   SqlEngine.CreateDatabase();

}
catch (Exception ex)
{
   throw ex;
}

I can set the maximum buffer size and maximum database size in the sdf file.

Code to create Table in Data.Sdf

public static void CreateTable()
{
   strConStr = "data source=" + strDBURL + ";Max Buffer Size = 512; Max Database Size = 500";
   SqlCeConnection cn = new SqlCeConnection();
   cn.ConnectionString = strConStr;
   cn.Open();
   SqlCeCommand objSqlCommand = new SqlCeCommand();
   objSqlCommand.Connection = cn;
   objSqlCommand.CommandType = CommandType.Text;
   try
   {
      strSQL = "CREATE TABLE FileData(";
      strSQL = strSQL + "UniqueID nvarchar(200),";
      strSQL = strSQL + "Serialnumber nvarchar(300) NULL,";
      strSQL = strSQL + "Customer nvarchar(500) NULL, ";
      strSQL = strSQL + "Rack nvarchar(500) NULL, ";
      strSQL = strSQL + "HHTId nvarchar(100) NULL, ";
      strSQL = strSQL + "BoxId nvarchar(300) NULL, ";
      strSQL = strSQL + "ScanDateTime nvarchar(200) NULL) ";
      objSqlCommand.CommandText = strSQL;
      objSqlCommand.ExecuteNonQuery();
   }
   catch (SqlCeException ex)
   {
      throw ex;
   }
   finally
   {
      cn.Close();
      cn = null;
   }
}

Now my database Data.sdf is ready with the table named FileData.

Then I need to write code to transfer the sdf file to the device. Before that I need to check whether or not the device is connected. With the RAPI class it is very easy . It has some builtin Boolean methods that are eiter true or false, like:

OpenNETCF.Desktop.Communication.RAPI op = new OpenNETCF.Desktop.Communication.RAPI();

• op.DevicePresent: This method tells us whether or not the device is connected with you system.
• op.DeviceFileExists("/Application/FolderName"): This method checks the desired file over the desired path in the device.
• op.CreateDeviceDirectory("/Application/ FolderName "): This method will create file directory in device.
• op.CopyFileToDevice(): This method will copy a file from the Windows application to the device. It takes the 3 arguments, string localFileName, string remoteFileName and bool overwrite.
• op.CopyFileFromDevice(): This method will copy a file from a Windows application to a device. It takes the 3 arguments, string localFileName, string remoteFileName and bool overwrite.
• op.Connect(): This method will connect your device with your application if the device is present.
• op.Connected: This method whether or not the device is connected using op.Connect().
• op.Disconnect(): This method will disconnect the device from the application.

Don't confuse DevicePresent with Connect.

Note: the RAPI dll works if Microsoft Activesync is present in your system.

Conclusion

I have explained ever step that helps to create a sdf file database at the device end and the use of the RAPI dll to transfer sdf from a device to a Windows application and vice versa.

I will explain how to do DML operations in a sdf file from a windowsCE device application in my next article. Stay tuned.

Happy coding.