Partial Updates

These scenarios demonstrate how to perform partial updates on a row.

Scenario Prototype

void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage);

void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage);
void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee);

ADO.NET

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    const string sql = @"UPDATE HR.EmployeeClassification
                SET EmployeeClassificationName = @EmployeeClassificationName
                WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@EmployeeClassificationKey", updateMessage.EmployeeClassificationKey);
        cmd.Parameters.AddWithValue("@EmployeeClassificationName", updateMessage.EmployeeClassificationName);
        cmd.ExecuteNonQuery();
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    const string sql = @"UPDATE HR.EmployeeClassification
                SET IsExempt = @IsExempt, IsEmployee = @IsEmployee
                WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@EmployeeClassificationKey", updateMessage.EmployeeClassificationKey);
        cmd.Parameters.AddWithValue("@IsExempt", updateMessage.IsExempt);
        cmd.Parameters.AddWithValue("@IsEmployee", updateMessage.IsEmployee);
        cmd.ExecuteNonQuery();
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    const string sql = @"UPDATE HR.EmployeeClassification
                SET IsExempt = @IsExempt, IsEmployee = @IsEmployee
                WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
        cmd.Parameters.AddWithValue("@IsExempt", isExempt);
        cmd.Parameters.AddWithValue("@IsEmployee", isEmployee);
        cmd.ExecuteNonQuery();
    }
}

Chain

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    m_DataSource.Update(TableName, updateMessage).Execute();
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    m_DataSource.Update(TableName, updateMessage).Execute();
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    m_DataSource.Update(TableName, new { employeeClassificationKey, isExempt, isEmployee }).Execute();
}

Dapper

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    var sql = @"UPDATE HR.EmployeeClassification
                SET EmployeeClassificationName = @EmployeeClassificationName
                WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
        con.Execute(sql, updateMessage);
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    var sql = @"UPDATE HR.EmployeeClassification
                SET IsExempt = @IsExempt, IsEmployee = @IsEmployee
                WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
        con.Execute(sql, updateMessage);
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    var sql = @"UPDATE HR.EmployeeClassification
                SET IsExempt = @IsExempt, IsEmployee = @IsEmployee
                WHERE EmployeeClassificationKey = @EmployeeClassificationKey;";

    using (var con = OpenConnection())
        con.Execute(sql, new { employeeClassificationKey, isExempt, isEmployee });
}

Entity Framework 6

To perform a partial update, first fetch a record and then modify it.

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var context = CreateDbContext())
    {
        //Get a fresh copy of the row from the database
        var temp = context.EmployeeClassification.Find(updateMessage.EmployeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.EmployeeClassificationName = updateMessage.EmployeeClassificationName;
            context.SaveChanges();
        }
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var context = CreateDbContext())
    {
        //Get a fresh copy of the row from the database
        var temp = context.EmployeeClassification.Find(updateMessage.EmployeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.IsExempt = updateMessage.IsExempt;
            temp.IsEmployee = updateMessage.IsEmployee;
            context.SaveChanges();
        }
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var context = CreateDbContext())
    {
        //Get a fresh copy of the row from the database
        var temp = context.EmployeeClassification.Find(employeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.IsExempt = isExempt;
            temp.IsEmployee = isEmployee;
            context.SaveChanges();
        }
    }
}

Entity Framework Core

To perform a partial update, first fetch a record and then modify it.

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var context = CreateDbContext())
    {
        //Get a fresh copy of the row from the database
        var temp = context.EmployeeClassification.Find(updateMessage.EmployeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.EmployeeClassificationName = updateMessage.EmployeeClassificationName;
            context.SaveChanges();
        }
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var context = CreateDbContext())
    {
        //Get a fresh copy of the row from the database
        var temp = context.EmployeeClassification.Find(updateMessage.EmployeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.IsExempt = updateMessage.IsExempt;
            temp.IsEmployee = updateMessage.IsEmployee;
            context.SaveChanges();
        }
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var context = CreateDbContext())
    {
        //Get a fresh copy of the row from the database
        var temp = context.EmployeeClassification.Find(employeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.IsExempt = isExempt;
            temp.IsEmployee = isEmployee;
            context.SaveChanges();
        }
    }
}

LINQ to DB

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var db = new OrmCookbook())
    {
        db.EmployeeClassification
            .Where(ec => ec.EmployeeClassificationKey == updateMessage.EmployeeClassificationKey)
            .Set(ec => ec.EmployeeClassificationName, updateMessage.EmployeeClassificationName)
            .Update();
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var db = new OrmCookbook())
    {
        db.EmployeeClassification
            .Where(ec => ec.EmployeeClassificationKey == updateMessage.EmployeeClassificationKey)
            .Set(ec => ec.IsExempt, updateMessage.IsExempt)
            .Set(ec => ec.IsEmployee, updateMessage.IsEmployee)
            .Update();
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var db = new OrmCookbook())
    {
        db.EmployeeClassification
            .Where(ec => ec.EmployeeClassificationKey == employeeClassificationKey)
            .Set(ec => ec.IsExempt, isExempt)
            .Set(ec => ec.IsEmployee, isEmployee)
            .Update();
    }
}

LLBLGen Pro

To perform a partial update, you can decide to first fetch a record and then modify it, or to update directly without fetching it first.

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var adapter = new DataAccessAdapter())
    {
        // use an entity
        var temp = adapter.FetchFirst(new QueryFactory().EmployeeClassification
                                .Where(EmployeeClassificationFields.EmployeeClassificationKey
                                                                   .Equal(updateMessage.EmployeeClassificationKey)));
        if (temp != null)
        {
            temp.EmployeeClassificationName = updateMessage.EmployeeClassificationName;
            adapter.SaveEntity(temp);
        }
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var adapter = new DataAccessAdapter())
    {
        // for kicks, update the entity directly in the DB, without fetching one first.
        var updater = new EmployeeClassificationEntity();
        updater.IsEmployee = updateMessage.IsEmployee;
        updater.IsExempt = updateMessage.IsExempt;
        adapter.UpdateEntitiesDirectly(updater, 
               new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                                                       .Equal(updateMessage.EmployeeClassificationKey)));
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var adapter = new DataAccessAdapter())
    {
        // let's use Linq for a change...
        var temp = new LinqMetaData(adapter).EmployeeClassification.FirstOrDefault(ec => ec.EmployeeClassificationKey == employeeClassificationKey);
        if (temp != null)
        {
            //Copy the changed fields
            temp.IsExempt = isExempt;
            temp.IsEmployee = isEmployee;
            adapter.SaveEntity(temp);
        }
    }
}

NHibernate

To perform a partial update, first fetch a record and then modify it.

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var session = m_SessionFactory.OpenSession())
    {
        var temp = session.Get<EmployeeClassification>(updateMessage.EmployeeClassificationKey);
        if (temp != null)
        {
            temp.EmployeeClassificationName = updateMessage.EmployeeClassificationName;
            session.Update(temp);
            session.Flush();
        }
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var session = m_SessionFactory.OpenSession())
    {
        var temp = session.Get<EmployeeClassification>(updateMessage.EmployeeClassificationKey);
        if (temp != null)
        {
            temp.IsExempt = updateMessage.IsExempt;
            temp.IsEmployee = updateMessage.IsEmployee;
            session.Update(temp);
            session.Flush();
        }
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var session = m_SessionFactory.OpenSession())
    {
        var temp = session.Get<EmployeeClassification>(employeeClassificationKey);
        if (temp != null)
        {
            temp.IsExempt = isExempt;
            temp.IsEmployee = isEmployee;
            session.Update(temp);
            session.Flush();
        }
    }
}

RepoDb

In RepoDb, you can either do the following.

  • Limit the properties of your entity model for targetted columns.
  • Specify the name of the targe table and pass the object (or dynamic).

The ClassMappedNameCache class will help you extract the target table from the original entity.

Code snippets below resembles item #2 above.

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var connection = CreateConnection(true))
    {
        connection.Update(ClassMappedNameCache.Get<EmployeeClassification>(), updateMessage);
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var connection = CreateConnection(true))
    {
        connection.Update(ClassMappedNameCache.Get<EmployeeClassification>(), updateMessage);
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var connection = CreateConnection(true))
    {
        connection.Update(ClassMappedNameCache.Get<EmployeeClassification>(),
            new { employeeClassificationKey, isExempt, isEmployee });
    }
}

ServiceStack

In ServiceStack, partial updates have to be 'unpacked'. Updated columns are passed in one parameter and filter column(s) (e.g. primary key) in a separate parameter.

public void UpdateWithObject(EmployeeClassificationNameUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        db.Update<EmployeeClassificationPartial>(
            new { updateMessage.EmployeeClassificationName },
            r => r.Id == updateMessage.EmployeeClassificationKey);
    }
}

public void UpdateWithObject(EmployeeClassificationFlagsUpdater updateMessage)
{
    if (updateMessage == null)
        throw new ArgumentNullException(nameof(updateMessage), $"{nameof(updateMessage)} is null.");

    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        db.Update<EmployeeClassificationPartial>(
            new { updateMessage.IsEmployee, updateMessage.IsExempt },
            r => r.Id == updateMessage.EmployeeClassificationKey);
    }
}
public void UpdateWithSeparateParameters(int employeeClassificationKey, bool isExempt, bool isEmployee)
{
    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        db.Update<EmployeeClassificationPartial>(
            new { isExempt, isEmployee },
            r => r.Id == employeeClassificationKey);
    }
}