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 ™ Winking smile

8 Comments

  • For less string copying and better performance using StringBuilder would be good option.

  • Cool! Now what about UPDATEs? :-)

  • Yes, good ideas, I'm already dabbing at a few of them :)

    BBL

    /Johan

  • @Mattias I did Insert first, but had to change to support anon class. Not sure I understand what you mean with "even if the value is null"?

    Looking for [Key] atttribute is a good idea, will try that.

    /Johan

  • I totally agree with Mattias Karlsdon. Check please http://osmirnov.net/posts/boosting-stackoverflow-com/

  • I will propose a much better & readable (in my opinion) version of your method :
    public static void Insert(this IDbConnection connection,
    object entityToInsert)
    {
    const string insertTemplate = @"INSERT INTO {0} ({1}) VALUES ({2})";

    var type = entityToInsert.GetType();
    var properties = type.GetProperties().Select(p => p.Name).ToArray();
    var names = string.Join(",", properties);
    var values = string.Join(",", properties.Select(n => "@" + n).ToArray());

    var query = string.Format(insertTemplate, type.Name, names, values);

    connection.Execute(query, queryentityToInsert);
    }

    It will generate the query faster because there's no += for string and string.join is even faster then the StringBuilder version.
    One more thing to note is that you are calling entityToInsert.GetType().GetProperties() multiple times and one is enough.
    Just my 2 cents

  • @scifire yes, thanks. I got rid of the string-cat in newer version (see other blog post), and also got better handling of property reflection in code I will share soon!

    Thanks for feedback!

    /Johan

  • Just a little improvement of scifire version.

    Multiple calls to type.GetProperties() can be solved by introducing a Dictionary<Type, IEnumerable> behind a reader/writer lock to cache the list of properties by type.

    Generating dynamic methods is probably a step too far, as there shouldn't be that many writes in most scenarios (if it's not a problem for SO, it's not a problem for me ;-).

Comments have been disabled for this content.