Simple Insert Extension for Dapper

Last week I started using Dapper (dapper-dot-net on Google Code) for a project I’m working on. Dapper is a micro ORM, which extends IDbConnection. It’s very fast and works very well for queries where you want to return both typed and dynamic lists of objects. Have a quick look at the webpage and you’ll see what I mean.

Today I needed to do some inserting, which you can do with the Execute() extension, but you’d have to type out all the “insert into xxx (col1,col2) values (@val1, @val2)” stuff. Some guys suggested to create a dapper.contrib and adding extensions of insert, update, delete and so on but it’s not there yet (at time of writing).

Anyway, the extensions in dapper are already so well written I thought it should be quite easy to just add a dead simple (or stupid if you prefer) Insert() extensions on top of the existing Execute(), and I ended up with this raw unrefactored code. All it does really is building up the SQL for the insert using some property reflection, dapper itself does the heavy lifting:

public static class DapperExtensions
{
    public static void Insert(this IDbConnection connection, 
                              object entityToInsert, string sql = "insert into " )
    {
        var name = entityToInsert.GetType().Name;

        if (sql == "insert into ")
        {
            sql = "insert into " + name + " ";
        }
        sql += " (";
        for (var i = 0; i < entityToInsert.GetType().GetProperties().Length; i++)
        {
            var propertyInfo = entityToInsert.GetType().GetProperties()[i];
            sql += propertyInfo.Name;
            if (i < entityToInsert.GetType().GetProperties().Length - 1)
                sql += ",";
        }
        sql += ") values (";
        for (var i = 0; i < entityToInsert.GetType().GetProperties().Length; i++)
        {
            var propertyInfo = entityToInsert.GetType().GetProperties()[i];
            sql += "@" + propertyInfo.Name;
            if (i < entityToInsert.GetType().GetProperties().Length - 1)
                sql += ",";
        }
        sql += ")";
        connection.Execute(sql, entityToInsert);
    }
}

I’m using it like this with a type:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var entity = new Test() { Name = "Johan", Age = 43 };
    connection.Insert(entity);
}

public class Test
{
    public string Name { get; set; }
    public int Age { get; set; }
}

…or like this with an anonymous type:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    connection.Insert(new { Name = "Johan", Age = 43 }, "insert into test");
}

Works for me ™