C# MySqlDataReader – “one line” function to return a row as an instance of a class.

MySql in C# is rather painless using the MySQL Connector for .net, but one thing it is missing is a no-frills, no extra requirements, no pre-defined ERD return a row as an instance of a class function. Coming from a PHP background, I love the PDO function fetchObject(), it’s just right there and you get an object you can handle.

I really wanted this functionality in my code to not only make it easier to read, but also make types easier to manage when returned from MySQL – why do I need to call GetString() or GetInt() when I can just say “look, the row that returns will look like this, return me an object with the properties set”.

So I wrote a static extension function:

/// <summary>
/// Grabs an instance of a class directly from the MySqlDataReader instead of manually building from GetString() etc.
/// </summary>
/// <typeparam name="T">Type of object to return</typeparam>
/// <param name="reader">Reader instance</param>
/// <returns>new T</returns>
public static T GetObject<T>(this MySql.Data.MySqlClient.MySqlDataReader reader)
{
    var obj = Activator.CreateInstance(typeof(T));
    var fields = obj.GetType().GetFields();

    foreach (var field in obj.GetType().GetFields())
    {
        var attrs = field.GetCustomAttributes(typeof(System.Runtime.Serialization.DataMemberAttribute), true) as System.Runtime.Serialization.DataMemberAttribute[];

        if (attrs.Length == 0)
        {
            continue;
        }

        var fieldID = attrs[0].Name;
        var fieldType = field.FieldType;

        field.SetValue(obj, reader.GetValue(reader.GetOrdinal(fieldID)));
    }

    return (T)obj;
}

This allows me to do something along the lines of reader.getObject<MyClass>() where MyClass would look something like this:

class MyClass
{
    [DataMember(Name = "id")]
    public int ID;

    [DataMember(Name = "email")]
    public string Email;

    [DataMember(Name = "full-name")]
    public string FullName;
}

et voila, you’ve got an instance of MyClass where the MySQL columns are mapped to friendly properties on your object and the type hint makes it all clear as to what it is.

As you can see, I’ve used the DataMember properties to define the SQL column name the data would come from, allowing the 1-1 mapping of SQL column to C# property.

Now there are optimisations to be add here, I do not contest that, using reflection can slow things down a bit, caching would be a good idea for example. But this works perfectly for me and means rather than 3 lines:
obj.ID = GetInt(“id”);
obj.Email = GetString(“email”);
obj.FullName = GetString(“full-name”);
I now just have one clean line that uses the object I would have already used and populated manually.

Comments

comments