I recently had cause to venture out from the cosy comforts of Linq-to-Sql into the scary wilds of raw ADO.Net. But I’m pleased to tell you I found a few tools in my C# 3 backpack that made the experience more tolerable than I feared.
Take a look at this:
public bool Exists(string tableName)
{
return ExecuteScalar<int>(
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName",
new { tableName }) > 0;
}
The details of the query are irrelevant. What’s interesting is the way I’m using an anonymous type to pass in its parameters. I’m sad to say I’m by no means the first to think of the anonymous-type-as-parameter-dictionary idea – ASP.Net MVC was probably the first place I saw it: but this application is particularly neat in the way the anonymous type appears to capture the local variable on behalf of the query.
So how does it work?
Simple. It uses Reflection to grab the names and values of each property on the anonymous type, and then adds parameters to a SqlCommand accordingly.
Hey. Don’t run away: it only uses Reflection the first time round. Then it uses Expression-tree magic to compile delegates for each of the property accesses so that subsequent calls cost barely anything.
Intrigued?
All aboard the Reflection Express
The crux of the matter is this method here:
private static Func<object, object> CreatePropertyGetter(PropertyInfo propertyInfo)
{
var instanceParameter = Expression.Parameter(typeof(object), "instance");
var expression = Expression.Lambda<Func<object, object>>(
Expression.ConvertChecked(
Expression.MakeMemberAccess(
Expression.ConvertChecked(instanceParameter, propertyInfo.DeclaringType),
propertyInfo),
typeof(object)),
instanceParameter);
var compiledExpression = expression.Compile();
return compiledExpression;
}
Given a PropertyInfo representing the property we’re interested in, this method creates a delegate that retrieves the value of the property from an instance of the type. If, for example, MyType has AProperty, then all that Expression malarkey amounts to this:
Func<object, object> compiledExpression = instance => (object)(((MyType)instance).AProperty)
(If you’ve not encountered an Expression tree before, think of it as CodeDOM on a diet. Expression trees are data structures representing a fragment of a program. The LambdaExpression is the most interesting specimen, because, as you see, it allows you to compile the expression into a delegate. And whereas CodeDOM fires up a full-blown compiler and creates an Assembly (with all the overhead that entails), LambdaExpressions are compiled into DynamicMethods using Reflection.Emit. All in all, Expression trees allow you to create little programs on the fly in a very light-weight and performant fashion.)
With that in place, we can now take a Type and create a list of assessors for all its public properties:
var type = instance.GetType();
var propertyDetails = (from propertyInfo in type.GetProperties()
where propertyInfo.CanRead
&& propertyInfo.GetGetMethod().GetParameters().Length == 0
select new PropertyDetails
{
Name = propertyInfo.Name,
GetValue = CreatePropertyGetter(propertyInfo)
})
.ToList();
(Before I put lines 4 and 5 in there I was getting rather funky VerificationExceptions when I tried this on certain objects: .Net slapped my wrist and told me the “Operation could destabilize the runtime”. I apologized and sat humbly on the naughty step for a minute until I remembered that Indexers are properties too, and they take parameters, which the delegates made by CreatePropertyGetter don’t allow for.)
Since Types in .Net don’t grow new properties during the execution of a program, we only need to do this reflection once, and we can then cache the result. That’s exactly what I’ve done in the version of the code shown at the bottom of the post, where I’ve packaged all of this into a handy little extension method, GetPropertyValues.
Finally it’s just a case of running over the object, using each of the getters in turn:
IEnumerable<KeyValuePair<string, object>> EnumeratePropertyValues(object instance, IList<PropertyDetails> propertyDetails)
{
foreach (var property in propertyDetails)
{
yield return new KeyValuePair<string, object>(
property.Name,
property.GetValue(instance));
}
}
Applications
Returning to my introductory Ado.Net example, here’s how you make use of this:
public TResult ExecuteScalar<TResult>(string commandText, object parametersObject)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = new SqlCommand(commandText, connection);
foreach (var propertyValue in parametersObject.GetPropertyValues())
{
command.Parameters.AddWithValue("@" + propertyValue.Key, propertyValue.Value);
}
return (TResult)command.ExecuteScalar();
}
}
Another example. How often do you get in a muddle with string formatting, trying to keep your values matched up with the {0}s, {1}s and {2}s in the format string? Wouldn’t this be simpler:
var name = "Bob Smith";
var age = 37;
var town = "Bognor Regis";
var result = "{name} is {age} years old and lives in {town}. He likes reading {book}."
.FormatWithParameters(new { age, name, book = “Winnie-the-Pooh”, town });
As you see FormatWithParameters doesn’t care what order the parameters arrive in.
With GetPropertyValues to hand, it’s this easy:
public static string FormatWithParameters(this string value, object parametersObject)
{
var formattedString = value;
foreach (var propertyValue in parametersObject.GetPropertyValues())
{
formattedString = formattedString.Replace(
"{" + propertyValue.Key + "}",
propertyValue.Value.ToString());
}
return formattedString;
}
Ready to Copy-and-Paste
Here’s the full code to the GetPropertyValues extension method:
public static class ObjectExtensions
{
private static readonly Dictionary<Type, IList<PropertyDetails>> PropertyDetailsCache = new Dictionary<Type, IList<PropertyDetails>>();
public static IEnumerable<KeyValuePair<string, object>> GetPropertyValues(this object instance)
{
if (instance == null)
{
throw new ArgumentNullException("instance");
}
var propertyDetails = GetPropertyDetails(instance.GetType());
return EnumeratePropertyValues(instance, propertyDetails);
}
private static IEnumerable<KeyValuePair<string, object>> EnumeratePropertyValues(object instance, IList<PropertyDetails> propertyDetails)
{
foreach (var property in propertyDetails)
{
yield return new KeyValuePair<string, object>(property.Name, property.GetValue(instance));
}
}
private static IList<PropertyDetails> GetPropertyDetails(Type type)
{
lock (PropertyDetailsCache)
{
if (!PropertyDetailsCache.ContainsKey(type))
{
var details = CreatePropertyDetailsList(type);
PropertyDetailsCache.Add(type, details);
}
return PropertyDetailsCache[type];
}
}
private static List<PropertyDetails> CreatePropertyDetailsList(Type type)
{
var propertyDetails = (from propertyInfo in type.GetProperties()
where propertyInfo.CanRead
&& propertyInfo.GetGetMethod().GetParameters().Length == 0
select new PropertyDetails
{
Name = propertyInfo.Name,
GetValue = CreatePropertyGetter(propertyInfo)
})
.ToList();
return propertyDetails;
}
private static Func<object, object> CreatePropertyGetter(PropertyInfo propertyInfo)
{
var instanceParameter = Expression.Parameter(typeof(object), "instance");
var expression = Expression.Lambda<Func<object, object>>(
Expression.ConvertChecked(
Expression.MakeMemberAccess(
Expression.ConvertChecked(instanceParameter, propertyInfo.DeclaringType),
propertyInfo),
typeof(object)),
instanceParameter);
var compiledExpression = expression.Compile();
return compiledExpression;
}
private class PropertyDetails
{
public string Name { get; set; }
public Func<object, object> GetValue { get; set; }
}
}
JSON
