Is there a Cleaner Dapper way to Update only columns that changed with Dapper?

Question

I'm looking for a way to update only set properties in Dapper. i.e Update an Entity's property only if its not null.

I'm solving the same problem with a rather crude approach as shown below but I believe there should be a cleaner dapper way of doing this.

 public void UpdateCustomer(Customer cust)
    {
        try
        {
            StringBuilder sb = new StringBuilder("UPDATE CUSTOMER_SETUP  SET DATE_MODIFIED = @DATE_MODIFIED ");
           if(cust.BUSINESSNAME != null) sb.Append(",BUSINESSNAME = @BUSINESSNAME ");
            if (cust.BUSINESS_ADDRESS != null) sb.Append(",BUSINESS_ADDRESS = @BUSINESS_ADDRESS ");
            if (cust.CONTACT_NAME != null) sb.Append(",CONTACT_NAME = @CONTACT_NAME ");
            if (cust.CONTACT_TITLE != null) sb.Append(",CONTACT_TITLE = @CONTACT_TITLE ");
            if (cust.CONTACT_PHONE1 != null) sb.Append(",CONTACT_PHONE1 = @CONTACT_PHONE1 ");
            if (cust.CONTACT_PHONE2 != null) sb.Append(",CONTACT_PHONE2 = @CONTACT_PHONE2 ");
            if (cust.CONTACT_EMAIL != null) sb.Append(",CONTACT_EMAIL = @CONTACT_EMAIL ");
            if (cust.CONTACT_URL != null) sb.Append(",CONTACT_URL = @CONTACT_URL ");
            if (cust.DATE_CREATED != null) sb.Append(",DATE_CREATED = @DATE_CREATED ");
            if (cust.CUSTOMER_TYPE != null) sb.Append(",CUSTOMER_TYPE = @CUSTOMER_TYPE ");
            if (cust.SUBSCRIPTION_TYPE != null) sb.Append(",SUBSCRIPTION_TYPE = @SUBSCRIPTION_TYPE ");


            sb.Append("WHERE ID = @ID ");
            sb.Append("; SELECT CAST(SCOPE_IDENTITY() as int ");

            var sql = sb.ToString();


            using (connection = new SqlConnection(connectString))
            {
                connection.Execute(sql, cust);

            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Answer 1

The feature you are looking for is called Change Tracking. This feature is one of the part of a bigger Unit Of Work pattern.

Dapper do not support Change Tracking.

There are few add-ons of Dapper those support this at different level. Refer this this blog post for comparison chart. As mentioned in chart, Dapper.Contrib and Dapper.Rainbow support it in different ways.

As @MarcGravell said in comment, null values for POCO properties is common. It does not always mean "do not update that field". That may also mean "set that DB field to null (or DBNull)". As there is no one guaranteed meaning for the property value being null, most ORMs implement it same way as Dapper does.

Answer 2

We wrap Dapper.Rainbow's Snapshotter to work like a change tracker. You need an instance of the db object for it to work.

It works perfectly for us delivering a dictionary you could quite easily use to generate the SQL you're after.

It might look something like this:

public class Foo{public string Name{get;set;}}

var foo = new Foo();

    var snapshotter = Snapshotter.Start(foo);

    foo.Name = "A new name";

    var dynparams = snapshotter.Diff(); //we basically wrap the snapshotter to give a dict here, but it's basically the same thing

    foreach(var name in dynparams.ParameterNames){
    sb.Append($",{name} = @{dynparams[name]} ");
    }