DML Operation in WindowsCE (Compact Edition) Device Application With sdf Database

Earlier I wrote an article on sdf file creation from Windows applications and how to move the sdf fle from a device to the application and vice versa.

I have written methods for connect() and disconnect(), respectively, that I call before and after doing any of that.

The complete class file is as in the following:

public class clsDB
{
   SqlCeConnection m_Con;
   SqlCeTransaction sqlTrans;
   SqlCeCommand com;
   SqlCeEngine engine;
   /// <summary>
   /// Connect to Database
   /// </summary>
   public void Connect()
   {
      try
      {
         // ReadSetting();
         m_Con = new SqlCeConnection();
         string _DBPATH = "\\Application\\FolderName";
         // string _DBPATH = "\\Application";
         string _DBNAME = "\\Data.sdf";
         m_Con.ConnectionString = "Data Source =" + _DBPATH + _DBNAME;
         // m_Con.ConnectionString = "";
         engine = new SqlCeEngine("Data Source =" + _DBPATH + _DBNAME);
         engine.Repair(null, RepairOption.RecoverCorruptedRows);
         m_Con.Open();
         com = new SqlCeCommand();
         com = m_Con.CreateCommand();
      }
      catch (Exception ex)
      {
         throw ex;
      }
   }
   public int ExecuteQuery(string StrSql)
   {

      int result = 0;
      try
      {
         if (m_Con.State == ConnectionState.Open)
         {
            com.CommandText = StrSql;
            result = com.ExecuteNonQuery();

         }
      }
      catch (Exception ex)
      {
         throw ex;
      }
      return result;
   }
   public DataSet GetDataSet(string strSql)
   {
      DataSet ds = new DataSet();
      SqlCeDataAdapter da;
      try
      {
         if (m_Con.State == ConnectionState.Open)
         {
            da = new SqlCeDataAdapter(strSql, m_Con);
            da.Fill(ds);
            da.Dispose();
         }
      }
      catch (Exception ex)
      {
         throw ex;
      }
      return ds;
   }
   public void Disconnect()
   {
      try
      {
         if (m_Con != null && m_Con.State == ConnectionState.Open)
         {
            com.Dispose();
            m_Con.Close();
         }
      }
      catch (Exception ex)
      {
         throw ex;
      }

   }
}

As in the code above, you can see that everything is the same as for SQL Server. Only the class name has changed.

SqlConnection to SqlCeConnection,
SqlCommand to SqlCecommand,
SqlDataAdapter to SqlCeDataAdapter

And so on.

Now suppose if you want to select some data on Barcode_OnRead() as in the following:

private void barcode1_OnRead(object sender, Symbol.Barcode.ReaderData readerData)
{
   clsDB objClsDb = new clsDB(); // make global
   objClsDb.Connect();
   string sQuery = "select * from filedata";
   DataSet dsValidate = objClsDb.GetDataSet(sQuery);
   objClsDb.Disconnect();
}

If you want to update a record:

private void UpdateRecords()
{
   objClsDb.Connect();
   string sUpdateBoxQuery = "update filedata set BoxId ='b1'
   where serialnumber='s1';
   int i = objClsDb.ExecuteQuery(sUpdateBoxQuery);
   objClsDb.Disconnect();
}

With the use of the code above you can easily do DML operations very easily. I want to say one thing, that a WindowsCE device application is not any rocket science. It is very easy to develop and just like your Windows application development. I don't know why Microsoft does not include this option in Visual Studio 2010 and above. If anyone knows then please comment below.

I think I explained all the points. If you have any concern then please comment.