Many mobile applications require that data is available even if a network is not, and there are many different solutions to this problem. In CF, a simple traditional solution was to use (preferably typed) DataSets. The advantage was that DataSets could easily be transferred via Web Services (preferably compressed), and thereby shared between the server and the client. A serious disadvantage was the performance when using DataSets on a mobile device.

Nowadays, my favorite approach would be to use POCOs all the way from the server to the client utilizing technologies like LINQ, ADO.NET Entity Framework, and ADO.NET Data Services. For online scenarios, this works great in mobile applications, but it's not possible to use the exact same approach offline as all necessary technologies are not (yet) available in CF.

I have investigated different alternatives, like the one provided in Windows Mobile Line of Business Solution Accelerator 2008, but my conclusion is that most approaches are too complex. Of course, you could always try to implement your own LINQ provider, but again, due to missing framework parts, this is a serious and complex task (that could prove too demanding on performance). No, I wanted something simpler, and the rest of this post describe what I came up with. Most of the code can be found in the KISS Architecture (although some naming has changed).

From the user interface (logic) code, I want to do something like this:

Product[] products;
if(SystemState.ConnectionsCount > 0)
{
    KissServiceClient service = new KissServiceClient();
    products = service.GetProducts();
}
else
{
    KissDomain kissDomain = new KissDomain();
    products = kissDomain.GetProducts();
}

Depending on the availability of a network connection, I wanted to get the products from the online WCF service or the local database. On the server side, the implementation of the GetProducts method could look like this:

var q = from p in data.ProductSet
        select p;

But on the client, we instead would do something like this:

return DomainHelper.GetEntities<Product>(string.Format("SELECT * FROM Product"));

The code that does the actual work looks like this:

public static T[] GetEntities<T>(string sql) where T : new()
{
    List<T> entities = new List<T>();
    SqlCeCommand command = Common.Values.DatabaseConnection.CreateCommand();
    command.CommandText = sql;
    SqlCeDataReader reader = command.ExecuteReader();
    Type type = typeof(T);
    while(reader.Read())
    {
        T entity = new T();
        for(int i = 0; i < reader.FieldCount; i++)
        {
            if(reader[i] != DBNull.Value)
            {
                PropertyInfo propertyInfo = type.GetProperty(reader.GetName(i));
                propertyInfo.SetValue(entity, reader[i], null);
            }
        }
        entities.Add(entity);
    }
    return entities.ToArray();
}

The fastest ADO.NET construct to read data from the database, the DataReaader, is used in combination with Reflection to create new entities (defined by the WCF service), and fill its attributes (that must be identical in the entity and table definitions).

In the same way, the following client code can be used to update the database from an entity:

DomainHelper.UpdateEntity<Product>(product, string.Format(
    "SELECT * FROM Product WHERE ProductID='{0}'", product.ID));

Again, the code that does the work, looks like this:

public static void UpdateEntity<T>(T entity, string sql)
{
    SqlCeCommand command = Common.Values.DatabaseConnection.CreateCommand();
    command.CommandText = sql;
    SqlCeResultSet resultSet =
        command.ExecuteResultSet(ResultSetOptions.Updatable);
    resultSet.Read();
    Type type = typeof(T);
    for(int i = 0; i < resultSet.FieldCount; i++)
    {
        PropertyInfo propertyInfo = type.GetProperty(resultSet.GetName(i));
        object value = propertyInfo.GetValue(entity, null);
        if(resultSet.GetValue(i) == DBNull.Value)
        {
            if(value != null)
                resultSet.SetValue(i, value);
        }
        else
        {
            if(!value.Equals(resultSet.GetValue(i)))
                resultSet.SetValue(i, value);
        }
    }
    resultSet.Update();
}

Here, the powerful ADO.NET update construct specific to SQL Server Compact, the ResultSet, is used to update the database with the attributes of the entity.

With only the above code, we can do the fundamental offline database operations in an efficient way while still working with the same entities (POCOs) as the WCF service provide and use when online.