Update records with Dapper in C#

Updating a Single Record

using Dapper;

// C# Update Records with Dapper
void UpdateEmployeeDepartment(Employee employee)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("UPDATE Employees SET Department=@department WHERE Id=@id",
            param: new { id = employee.Id, department = employee.Department });
    }
}

In this example, we update the Department of an employee based on their Id. The parameters are passed using an anonymous object, where department is mapped to the query parameter @department, and id is mapped to @id.

You can also use the object itself as the parameter, like this:

con.Execute("UPDATE Employees SET Salary=@Salary, Position=@Position WHERE Id=@Id",
            param: employee);

In this case, the employee object’s properties will be directly mapped to the corresponding query parameters.

It’s best practice to keep your UPDATE statements concise by including only the columns that need to be updated. If a column doesn’t need to be updated, don’t include it in the SET clause or WHERE clause.

Updating Multiple Records

When updating multiple records, you have two main scenarios to handle:

1. Updating Multiple Records with Different Values

If you need to update multiple records with different values, you’ll need to execute multiple UPDATE statements.

For example, if you want to update the Salary for two employees, one to 60000 and the other to 75000, you can pass in a list of employee objects like this:

public void UpdateMultipleSalaries(List<Employee> employees)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("UPDATE Employees SET Salary = @Salary WHERE Id = @Id",
            param: employees);
    }
}

In this example, Dapper will execute the UPDATE statement once for each item in the employees list. If you pass in 5 employees, Dapper will run 5 individual UPDATE statements.

2. Updating Multiple Records with the Same Value

If you want to update multiple records with the same value, you can use a single UPDATE statement with a WHERE IN clause.

For example, if you want to set the Department to "IT" for a list of employees you can write like this:

using System.Linq;

public void UpdateDepartmentForMultipleEmployees(string department, List<Employee> employees)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("UPDATE Employees SET Department = @department WHERE Id IN @ids",
            param: new { department, ids = employees.Select(e => e.Id) });
    }
}

In this case, the IN clause allows you to update all the employees’ departments at once, which is more efficient than executing multiple individual UPDATE statements.