DapperExtensions: Add "insert ... update on duplicate key"

Question

Now I'm using Dapper + Dapper.Extensions. And yes, it's easy and awesome. But I faced with a problem: Dapper.Extensions has only Insert command and not InsertUpdateOnDUplicateKey. I want to add such method but I don't see good way to do it:

1. I want to make this method generic like Insert
2. I can't get cached list of properties for particular type because I don't want to use reflection directly to build raw sql

Possible way here to fork it on github but I want to make it in my project only. Does anybody know how to extend it? I understand this feature ("insert ... update on duplicate key") is supported only in MySQL. But I can't find extension points in DapperExtensions to add this functionality outside.
Update: this is my fork https://github.com/MaximTkachenko/Dapper-Extensions/commits/master

Answer 1

This piece of code has helped me enormously in MySQL -related projects, I definitely owe you one.

I do a lot of database-related development on both MySQL and MS SQL. I also try to share as much code as possible between my projects.

MS SQL has no direct equivalent for "ON DUPLICATE KEY UPDATE", so I was previously unable to use this extension when working with MS SQL.

While migrating a web application (that leans heavily on this Dapper.Extensions tweak) from MySQL to MS SQL, I finally decided to do something about it.

This code uses the "IF EXISTS => UPDATE ELSE INSERT" approach that basically does the same as "ON DUPLICATE KEY UPDATE" on MySQL.

Please note: the snippet assumes that you are taking care of transactions outside this method. Alternatively you could append "BEGIN TRAN" to the beginning and "COMMIT" to the end of the generated sql string.

public static class SqlGeneratorExt
{
    public static string InsertUpdateOnDuplicateKey(this ISqlGenerator generator, IClassMapper classMap, bool hasIdentityKeyWithValue = false)
    {
        var columns = classMap.Properties.Where(p => !(p.Ignored || p.IsReadOnly || (p.KeyType == KeyType.Identity && !hasIdentityKeyWithValue))).ToList();
        var keys = columns.Where(c => c.KeyType != KeyType.NotAKey).Select(p => $"{generator.GetColumnName(classMap, p, false)}=@{p.Name}");
        var nonkeycolumns = classMap.Properties.Where(p => !(p.Ignored || p.IsReadOnly) && p.KeyType == KeyType.NotAKey).ToList();
        if (!columns.Any())
        {
            throw new ArgumentException("No columns were mapped.");
        }
        var tablename = generator.GetTableName(classMap);
        var columnNames = columns.Select(p => generator.GetColumnName(classMap, p, false));
        var parameters = columns.Select(p => generator.Configuration.Dialect.ParameterPrefix + p.Name);
        var valuesSetters = nonkeycolumns.Select(p => $"{generator.GetColumnName(classMap, p, false)}=@{p.Name}").ToList();
        var where = keys.AppendStrings(seperator: " and ");
        var sqlbuilder = new StringBuilder();
        sqlbuilder.AppendLine($"IF EXISTS (select * from {tablename} WITH (UPDLOCK, HOLDLOCK) WHERE ({where})) ");
        sqlbuilder.AppendLine(valuesSetters.Any() ? $"UPDATE {tablename} SET {valuesSetters.AppendStrings()} WHERE ({where}) " : "SELECT 0 ");
        sqlbuilder.AppendLine($"ELSE INSERT INTO {tablename} ({columnNames.AppendStrings()}) VALUES ({parameters.AppendStrings()}) ");
        return sqlbuilder.ToString();
    }
}

Answer 2

Actually I closed my pull request and remove my fork because:

1. I see some open pull requests created in 2014
2. I found a way "inject" my code in Dapper.Extensions.

I remind my problem: I want to create more generic queries for Dapper.Extensions. It means I need to have access to mapping cache for entities, SqlGenerator etc. So here is my way. I want to add ability to make INSERT .. UPDATE ON DUPLICATE KEY for MySQL. I created extension method for ISqlGenerator

   public static class SqlGeneratorExt
    {
        public static string InsertUpdateOnDuplicateKey(this ISqlGenerator generator, IClassMapper classMap)
        {
            var columns = classMap.Properties.Where(p => !(p.Ignored || p.IsReadOnly || p.KeyType == KeyType.Identity));
            if (!columns.Any())
            {
                throw new ArgumentException("No columns were mapped.");
            }

            var columnNames = columns.Select(p => generator.GetColumnName(classMap, p, false));
            var parameters = columns.Select(p => generator.Configuration.Dialect.ParameterPrefix + p.Name);
            var valuesSetters = columns.Select(p => string.Format("{0}=VALUES({1})", generator.GetColumnName(classMap, p, false), p.Name));

            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE {3}",
                                       generator.GetTableName(classMap),
                                       columnNames.AppendStrings(),
                                       parameters.AppendStrings(),
                                       valuesSetters.AppendStrings());

            return sql;
        }
    }

One more extension method for IDapperImplementor

public static class DapperImplementorExt
{
    public static void InsertUpdateOnDuplicateKey<T>(this IDapperImplementor implementor, IDbConnection connection, IEnumerable<T> entities, int? commandTimeout = null) where T : class
    {
        IClassMapper classMap = implementor.SqlGenerator.Configuration.GetMap<T>();
        var properties = classMap.Properties.Where(p => p.KeyType != KeyType.NotAKey);
        string emptyGuidString = Guid.Empty.ToString();

        foreach (var e in entities)
        {
            foreach (var column in properties)
            {
                if (column.KeyType == KeyType.Guid)
                {
                    object value = column.PropertyInfo.GetValue(e, null);
                    string stringValue = value.ToString();
                    if (!string.IsNullOrEmpty(stringValue) && stringValue != emptyGuidString)
                    {
                        continue;
                    }

                    Guid comb = implementor.SqlGenerator.Configuration.GetNextGuid();
                    column.PropertyInfo.SetValue(e, comb, null);
                }
            }
        }

        string sql = implementor.SqlGenerator.InsertUpdateOnDuplicateKey(classMap);

        connection.Execute(sql, entities, null, commandTimeout, CommandType.Text);
    }
}

Now I can create new class derived from Database class to use my own sql

public class Db : Database
{
    private readonly IDapperImplementor _dapperIml;

    public Db(IDbConnection connection, ISqlGenerator sqlGenerator) : base(connection, sqlGenerator)
    {
        _dapperIml = new DapperImplementor(sqlGenerator);
    }

    public void InsertUpdateOnDuplicateKey<T>(IEnumerable<T> entities, int? commandTimeout) where T : class
    {
        _dapperIml.InsertUpdateOnDuplicateKey(Connection, entities, commandTimeout);
    }
}

Yeah, it's required to create another DapperImplementor instance because DapperImplementor instance from base class is private :(. So now I can use my Db class to call my own generic sql queries and native queries from Dapper.Extension. Examples of usage Database class instead of IDbConnection extensions can be found here.