Strongly-typed data access with WebMatrix.Data

One of the features of the new Microsoft Web Stack is WebMatrix.Data. This is a small data-access library that makes heavy use of dynamic objects to access database tables:

using(var db = Database.Open("MyDatabase")) {
 
   var users = db.Query("select Id, Surname, Forename from Users");
 
   foreach(var user in users) {
     Console.WriteLine(user.Surname + ", " + user.Forename);
   }
}

In this example, the Query method returns an IEnumerable<dynamic> and we access the Id and Name properties of on each user. These properties haven’t been defined anywhere – they’re inferred from the column names in the database.

While this approach works well, it can become painful if you want to do anything even slightly more complex such as adding additional behaviour to the data objects. For example, If we wanted to have a DisplayName property that concatenates the Forename and the Surname then we can’t define a new property on the object.

To get around this I quickly put together a strongly-typed wrapper that allows you to project WebMatrix.Data queries into strongly typed objects. So, if we define an object like this:

public class User {
  public int Id { get; set; }
  public string Surname { get; set; }
  public string Forename { get; set; }
 
  public string FullName {
    get { return Surname + ", " + Forename; }
  }
 
}

…we can use it like this by using a strongly-typed extension method for Query:

using(var db = Database.Open("MyDatabase")) {
 
   var users = db.Query<User>("select Id, Surname, Forename from Users");
 
   foreach(var user in users) {
     Console.WriteLine(user.FullName);
   }
}

The Query<T> method is a simple extension method that delegates the work to a mapper:

public static class DataExtensions {
	public static IEnumerable<T> Query<T>(this Database db, string commandText, params object[] args) {
		var queryResults = db.Query(commandText, args);
		var mapper = Mapper<T>.Create();
		return (from DynamicRecord record in queryResults select mapper.Map(record)).ToList();
	}
}

Most of the work is done by the Mapper. This class constructs and caches delegates for the specified type’s property setters:

public class Mapper<T> {
	private Func<T> factory;
	private Dictionary<string, Action<T, object>> setters = new Dictionary<string, Action<T, object>>();
	private static Lazy<Mapper<T>> _instance = new Lazy<Mapper<T>>(() => new Mapper<T>());
 
	public static Mapper<T> Create() {
		return _instance.Value;
	}
 
	private Mapper() {
		factory = CreateActivatorDelegate();
 
		foreach (var property in typeof(T).GetProperties()) {
			if (property.CanWrite) {
				setters[property.Name] = BuildSetterDelegate(property);
			}
		}
	}
 
	public T Map(DynamicRecord record) {
		var instance = factory();
 
		foreach(var column in record.Columns) {
			Action<T, object> setter;
 
			if(setters.TryGetValue(column, out setter)) {
				setter(instance, record[column]);
			}
		}
 
		return instance;
	}
 
 
	private static Func<T> CreateActivatorDelegate() {
		return CreateActivatorDelegate(typeof(T).GetConstructor(Type.EmptyTypes));
	}
 
	private static Func<T> CreateActivatorDelegate(ConstructorInfo constructor) {
		return Expression.Lambda<Func<T>>(Expression.New(constructor)).Compile();
	}
 
	private static Action<T, object> BuildSetterDelegate(PropertyInfo prop) {
 
		var instance = Expression.Parameter(typeof(T), "x");
		var argument = Expression.Parameter(typeof(object), "v");
 
		var setterCall = Expression.Call(
			instance,
			prop.GetSetMethod(true),
			Expression.Convert(argument, prop.PropertyType));
 
 
		return (Action<T, object>)Expression.Lambda(setterCall, instance, argument).Compile();
	}
}

Note that this is just a prototype – there isn’t any error handling in the mapping code.

While WebMatrix.Data isn’t a substitute for a full O/RM tool, it’s certainly useful for simple scenarios where you just need to quickly access data, and hopefully strongly-typed extensions like these can make it a little more useful in some scenarios.

Written on January 24, 2011