Refactored Dapper Extensions

Making extensions for Dapper-dot-net was so fun and I needed a few more simple ones so I refactored a bit and made a few Update() and Delete() extensions as well. I’m also looking for Id-properties with the [Key] attribute on them. The class grew a bit, but it now supports the functions below. Note that this will only work for very simple objects and tables, but it might be helpful for some of you

I’m trying to follow the overall Dapper syntax as much as I can. If you done specify the table name with the input parameter, I’m using the name of the class/entity as table name, except where anonymous types are used.

The object I’m using for testing

    public class Test
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

Insert typed object

                var entity = new Test() { Name = "Johan", Age = 44 };
                connection.Insert(entity);

Insert anonymous object

                connection.Insert("insert into test", new { Name = "Johan", Age = 20 });

Update typed object (where-clause built up from Id-properties)

                var entity = new Test() { Id = 16, Name = "Johan", Age = 50 };
                connection.Update(entity);

Delete typed object (where-clause built up from Id-properties)

                var entity = new Test() { Id = 4, Name = "Johan", Age = 44 };
                var deletedCount = connection.Delete(entity);

Delete anonymous object (where-clause built up from anonymous object)

                var deletedCount = connection.Delete("delete from test", new { Name = "Johan" });

The Code:

public static class DapperExtensions
{
    public static void Insert(this IDbConnection connection, object entityToInsert)
    {
        Insert(connection, null, entityToInsert);
    }

    public static void Insert(this IDbConnection connection, string sql, object entityToInsert)
    {
        var name = entityToInsert.GetType().Name;
        var sb = new StringBuilder(sql);
        if (sql == null)
            sb.AppendFormat("insert into {0}", name);
        sb.Append(" (");
        BuildInsertParameters(entityToInsert, sb);
        sb.Append(") values (");
        BuildInsertValues(entityToInsert, sb);
        sb.Append(")");
        connection.Execute(sb.ToString(), entityToInsert);
    }

    public static int Update(this IDbConnection connection, object entityToUpdate)
    {
        return Update(connection, null, entityToUpdate);
    }

    public static int Update(this IDbConnection connection, string sql, object entityToUpdate)
    {
        var idProps = GetIdProperties(entityToUpdate);
        if (idProps.Count() == 0)
            throw new ArgumentException("Entity must have at least one [Key] property");

        var name = entityToUpdate.GetType().Name;

        var sb = new StringBuilder(sql);
        if (sql == null)
            sb.AppendFormat("update {0}", name);

        sb.AppendFormat(" set ");
        BuildUpdateSet(entityToUpdate, sb);
        sb.Append(" where ");
        BuildWhere(sb, idProps.ToArray());

        return connection.Execute(sb.ToString(), entityToUpdate);
    }

    public static int Delete<T>(this IDbConnection connection, T entityToDelete)
    {
        return Delete(connection, null, entityToDelete);
    }

    public static int Delete<T>(this IDbConnection connection, string sql, T entityToDelete)
    {
        var idProps = typeof (T).IsAnonymousType() ? 
            GetAllProperties(entityToDelete) : 
            GetIdProperties(entityToDelete);

        if (idProps.Count() == 0)
            throw new ArgumentException("Entity must have at least one [Key] property");

        var name = entityToDelete.GetType().Name;

        var sb = new StringBuilder(sql);
        if (sql == null)
            sb.AppendFormat("delete from {0}", name);

        sb.Append(" where ");
        BuildWhere(sb, idProps);

        return connection.Execute(sb.ToString(), entityToDelete);
    }

    private static void BuildUpdateSet(object entityToUpdate, StringBuilder sb)
    {
        var nonIdProps = GetNonIdProperties(entityToUpdate).ToArray();

        for (var i = 0; i < nonIdProps.Length; i++)
        {
            var property = nonIdProps[i];

            sb.AppendFormat("{0} = @{1}", property.Name, property.Name);
            if (i < nonIdProps.Length - 1)
                sb.AppendFormat(", ");
        }
    }

    private static void BuildWhere(StringBuilder sb, IEnumerable<PropertyInfo> idProps)
    {
        for (var i = 0; i < idProps.Count(); i++)
        {
            sb.AppendFormat("{0} = @{1}", idProps.ElementAt(i).Name, idProps.ElementAt(i).Name);
            if (i < idProps.Count() - 1)
                sb.AppendFormat(" and ");
        }
    }

    private static void BuildInsertValues(object entityToInsert, StringBuilder sb)
    {
        var props = GetAllProperties(entityToInsert);

        for (var i = 0; i < props.Count(); i++)
        {
            var property = props.ElementAt(i);
            if (property.GetCustomAttributes(true).Where(a => a is KeyAttribute).Any()) continue;
            sb.AppendFormat("@{0}", property.Name);
            if (i < props.Count() - 1)
                sb.Append(", ");
        }
    }

    private static void BuildInsertParameters(object entityToInsert, StringBuilder sb)
    {
        var props = GetAllProperties(entityToInsert);

        for (var i = 0; i < props.Count(); i++)
        {
            var property = props.ElementAt(i);
            if (property.GetCustomAttributes(true).Where(a => a is KeyAttribute).Any()) continue;
            sb.Append(property.Name);
            if (i < props.Count() - 1)
                sb.Append(", ");
        }
    }

    private static IEnumerable<PropertyInfo> GetAllProperties(object entity)
    {
        return entity.GetType().GetProperties();
    }

    private static IEnumerable<PropertyInfo> GetNonIdProperties(object entity)
    {
        return GetAllProperties(entity).Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute) == false);
    }

    private static IEnumerable<PropertyInfo> GetIdProperties(object entity)
    {
        return GetAllProperties(entity).Where( p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute));
    }
}

public static class TypeExtension
{
    public static Boolean IsAnonymousType(this Type type)
    {
        if (type == null) return false;

        var hasCompilerGeneratedAttribute = type.GetCustomAttributes(typeof(CompilerGeneratedAttribute), false).Count() > 0;
        var nameContainsAnonymousType = type.FullName.Contains("AnonymousType");
        var isAnonymousType = hasCompilerGeneratedAttribute && nameContainsAnonymousType;

        return isAnonymousType;
    }
}

Feel free to fork the code as much as you can, I’m too tired to stay up longer. Dapper is getting really useful