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

  1. public static void CreateDataBase()  
  2. {  
  3.    public static string _DBPATH = Application.StartupPath;  
  4.    public static string _DBNAME = "Data.sdf";  
  5.   
  6.    SqlCeEngine SqlEngine;  
  7.    strDBURL = _DBPATH + "\\" + _DBNAME;  
  8.   
  9.    SqlEngine = new SqlCeEngine("data source=" + strDBURL + ";Max Buffer Size = 512; Max Database Size = 500");  
  10.    SqlEngine.CreateDatabase();  
  11.   
  12. }  
  13. catch (Exception ex)  
  14. {  
  15.    throw ex;  
  16. }  

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

Code to create Table in Data.Sdf

  1. public static void CreateTable()  
  2. {  
  3.    strConStr = "data source=" + strDBURL + ";Max Buffer Size = 512; Max Database Size = 500";  
  4.    SqlCeConnection cn = new SqlCeConnection();  
  5.    cn.ConnectionString = strConStr;  
  6.    cn.Open();  
  7.    SqlCeCommand objSqlCommand = new SqlCeCommand();  
  8.    objSqlCommand.Connection = cn;  
  9.    objSqlCommand.CommandType = CommandType.Text;  
  10.    try  
  11.    {  
  12.       strSQL = "CREATE TABLE FileData(";  
  13.       strSQL = strSQL + "UniqueID nvarchar(200),";  
  14.       strSQL = strSQL + "Serialnumber nvarchar(300) NULL,";  
  15.       strSQL = strSQL + "Customer nvarchar(500) NULL, ";  
  16.       strSQL = strSQL + "Rack nvarchar(500) NULL, ";  
  17.       strSQL = strSQL + "HHTId nvarchar(100) NULL, ";  
  18.       strSQL = strSQL + "BoxId nvarchar(300) NULL, ";  
  19.       strSQL = strSQL + "ScanDateTime nvarchar(200) NULL) ";  
  20.       objSqlCommand.CommandText = strSQL;  
  21.       objSqlCommand.ExecuteNonQuery();  
  22.    }  
  23.    catch (SqlCeException ex)  
  24.    {  
  25.       throw ex;  
  26.    }  
  27.    finally  
  28.    {  
  29.       cn.Close();  
  30.       cn = null;  
  31.    }  

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.


Similar Articles