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;
}
}
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 blog post for comparison chart. As mentioned in chart, Dapper.Contrib and Dapper.Rainbow support it in different ways.
Edit: To answer your comment
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.