SqlCeEngineEx - Extending the SqlCeEngine class

I use System.Data.SqlServer.SqlCeEngine() quite a lot in all my projects. I normally create the database on the fly when the application is launched for the first time and then I populate the initial data via a web service.

I often check if database objects exist before I create them. You can do this by querying the INFORMATION_SCHEMA views. I created a helper class called SqlCeEngineEx that contains the following methods for querying the INFORMATION_SCHEMA:

1) bool DoesTableExist(string table) - Checks if a table exists in the database
2) string[] GetTables() - Returns a string array of all the tables in the database
3) string[] GetTableConstraints(string table) - Returns a string array of all the constraints for a table
4) string[] GetTableConstraints() - Returns a string array of all the constraints in the database

And here is the full code:

public class SqlCeEngineEx : IDisposable
{
  private SqlCeEngine engine;

  public SqlCeEngineEx()
  {
   engine = new SqlCeEngine();
  }

  public SqlCeEngineEx(string connectionString)
  {
   engine = new SqlCeEngine(connectionString);
  }

  public bool DoesTableExist(string tablename)
  {
   bool result = false;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText =
      @"SELECT COUNT(TABLE_NAME)
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME=@Name"
;
     cmd.Parameters.AddWithValue("@Name", tablename);
     result = Convert.ToBoolean(cmd.ExecuteScalar());
    }
   }

   return result;
  }

  private string[] PopulateStringList(SqlCeCommand cmd)
  {
   List<string> list = new List<string>();

   using (SqlCeDataReader reader = cmd.ExecuteReader()) {
    while (reader.Read()) {
     list.Add(reader.GetString(0));
    }
   }

   return list.ToArray();
  }

  public string[] GetTables()
  {
   string[] tables;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
     tables = PopulateStringList(cmd);
    }
   }

   return tables;
  }

  public string[] GetTableConstraints()
  {
   string[] constraints;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText = "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS";
     constraints = PopulateStringList(cmd);
    }
   }

   return constraints;
  }

  public string[] GetTableConstraints(string tablename)
  {
   string[] constraints;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText =
      @"SELECT CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
       WHERE TABLE_NAME=@Name"
;
     cmd.Parameters.AddWithValue("@Name", tablename);
     constraints = PopulateStringList(cmd);
    }
   }

   return constraints;
  }

  public string LocalConnectionString
  {
   get { return engine.LocalConnectionString; }
   set { engine.LocalConnectionString = value; }
  }

  public void Compact()
  {
   engine.Compact(null);
  }

  public void Compact(string connectionString)
  {
   engine.Compact(connectionString);
  }

  public void CreateDatabase()
  {
   engine.CreateDatabase();
  }

  public void Repair(string connectionString, RepairOption options)
  {
   engine.Repair(connectionString, options);
  }

  public void Shrink()
  {
   engine.Shrink();
  }

  public bool Verify()
  {
   return engine.Verify();
  }

  public void Dispose()
  {
   engine.Dispose();
   engine = null;
  }
}



Microsoft Development Center Copenhagen - TechFest 2007

I was at the Microsoft TechFest yesterday where we had an exhibition and a half hour presentation. I presented 2 solutions: TimeTracker which I mentioned in my previous blogs; And a successful customer project called the Mobile Inpsection Log which is designed for quality assurance and control for field workers.

The Mobile Inspection Log was fully tailored to the customers requirements. I did not design it for one handed navigation, I instead designed the mobile client to look similar to its existing desktop application counter part. I'll post some screen shots if I get permission from the customer and management.

After the presentation I was interviewed by MSDN Channel 9 where I gave an overview of what we do and what is next for us. I'll post a link to the Channel 9 interview once they publish it on their site.

During lunch break, we had the chance we watch a presentation from the Imagine Cup 2007 1st place winner in Software Design. These guys did an excellent job for a project called the LiveBook. Here's a link to it Team 3KC Returns/Project LiveBook!



Visual Studio 2008 Released!

Finally it's out!

http://msdn2.microsoft.com/en-us/vstudio/default.aspx


Now I can finally upgrade all my production code to the latest IDE!



Microsoft Dynamics Convergence 2007 - Copenhagen

It's been pretty interesting these past days where I've been hanging out at the Microsoft Dynamics Convergence 2007 Conference in Copenhagen. I had a chance to speak as a guest speaker for a session called "MOB102: Extending Your Microsoft Dynamics Solution Through Mobile Applications".

I also had the chance to show a demo of a new product that I have been working on lately. A Time, Material, and Mileage registration application called the TimeTracker. One of the key features that the crowd really loved was the one handed navigation or thumb navigation design. Of course the graphics, performance, and the integration with Microsoft Dynamics NAV was also a huge hit.

The last day of the conference is tomorrow and I'm looking forward to what tomorrow has to offer



Official Windows Mobile 6.0 Upgrade for HTC P3300

Finally, the Windows Mobile 6.0 Upgrade is released for the HTC P3300 device. You will have to sign up for the HTC e-Club though (which is free)

http://member.htc.com/member/login.aspx