Cepa Mobility - Enabling the Disabled

About a year ago, I had the chance to sit down with a brilliant developer with Muscular Atrophy. I was doing some mentoring that time and was helping them in solving some minor issues regarding a Windows Mobile based solution.

Yesterday, while I was checking out what’s new on MSDN’s Channel 9 website I saw that the guys at Cepa Mobility were interviewed by Channel 9. These guys are doing some really great stuff for enabling people with disabilities.

Here are some very interesting and inspiring videos of the guys and their work:

YouTube - Mobile Communicator for People with Disabilities


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!