Helper method to only update some field values

[peter3]

I've written an extension that allows me to update only specific fields. If you want to use it, be my guest.

    /// <summary>
    /// Updates table T with the values in param.
    /// The table must have a key named "Id" and the value of id must be included in the "param" anon object. The Id value is used as the "where" clause in the generated SQL
    /// </summary>
    /// <typeparam name="T">Type to update. Translates to table name</typeparam>
    /// <param name="connection"></param>
    /// <param name="param">An anonymous object with key=value types</param>
    /// <returns>The Id of the updated row. If no row was updated or id was not part of fields, returns null</returns>
    public static object UpdateFields<T>(this IDbConnection connection, object param, IDbTransaction transaction = null, int? commandTimeOut = null, CommandType? commandType = null)
    {
        var names = new List<string>();
        object id = null;

        foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(param))
        {
            if (!"Id".Equals(property.Name, StringComparison.InvariantCultureIgnoreCase))
                names.Add(property.Name);
            else
                id = property.GetValue(param);
        }

        if (id != null && names.Count > 0)
        {
            var sql = string.Format("UPDATE {1} SET {0} WHERE Id=@Id", string.Join(",", names.Select(t => { t = t + "=@" + t; return t; })), typeof(T).Name);
            if (Debugger.IsAttached)
                Trace.WriteLine(string.Format("UpdateFields: {0}", sql));
            return connection.Execute(sql, param, transaction, commandTimeOut, commandType) > 0 ? id : null;
        }
        return null;
    }

    public static object UpdateFields<T>(this IDbConnection connection, object fields, CommandDefinition commandDefinition)
    {
        return UpdateFields<T>(connection, fields, commandDefinition.Transaction, commandDefinition.CommandTimeout, commandDefinition.CommandType);
    }

[clabnet]

I don't tried it, for my opinion the idea is very useful.
It is a function already present in OrmLite, for example.

[ericdc1]

I suggest using either of these in your POCO model on the properties you want to exclude.
[Editable(false)]
[ReadOnly(true)]

Documentation on this below:
By default the insert statement would include all properties in the class - The Editable(false) and ReadOnly(true) attributes remove items from the insert statement
Properties decorated with ReadOnly(true) are only used for selects
Complex types are not included in the insert statement - This keeps the List out of the insert even without the Editable attribute. You can include complex types if you decorate them with Editable(true). This is useful for enumerators.

[peter3]

Using the attributes is only useful if you always want to exclude the same fields. If you have a more dynamic environment where the fields to exclude/include can vary, this helper handles that (for example, you have a file upload table and you want to be able to change the filename without also having to fetch and re-post the possibly large file data, but you also want to be able to upload new data in a separate action).

[scgough]

Hi @peter3 - great method. 👍 It's been very handy. I've got a small bug with it though.

In SQL Server, if the table name being updated is named using a reserved word (e.g. User) then the method doesn't nicely wrap up the name (like the main insert/update methods do) and gives me an error.
In this example, it should write the SQL as UPDATE [User] set ... but it is writing UPDATE User set...

EDIT: I've tested and the following line should be changed to:

var sql = string.Format("UPDATE {1} SET {0} WHERE Id=@Id", string.Join(",", names.Select(t => { t = t + "=@" + t; return t; })), GetTableName(typeof(T)));

[kiquenet]

Useful method for only update some field values

Usage sample?

Any more alternatives? do you use yet in production?

[oghenez]

I don't tried it, for my opinion the idea is very useful. It is a function already present in OrmLite, for example.

i would have used Ormlite, but it isnt free. and is it also supports only .net 4.5 upwards. the project i am working on is currently .net 4.0 only