Can anybody help to do bulk update using Dapper ORM?

Question

I have a table employee, and I have to update their location to new location, so I need a bulk update. Please help me to do so using Dapper O.R.M.

My primary key is Employee-id.

Below you can see the sample code which updates in single record at a time.

// Employees is list of class class Employee
SqlConnection connection = new SqlConnection(connectionstring);
connection.open();

foreach (Employee employee in Employees)
{
    string query = @"UPDATE [dbo].[Employee]    
                     SET Name = @Name, Age = @Age, Sex = @Sex, 
                         Location = @Location  
                     WHERE Id = @Id";

    connection.QueryAsync<bool>(query, new { @Id = employee.Id, @Name = employee.Name, 
                                             @Sex = employee.sex, @Age = employee.age, 
                                             @Location = employee.location})).SingleOrDefault();
}   

Answer 1

Dapper supports inserting/updating from a List.

internal class Employee
{
    public int Id { get; set; }
    public int Age { get; set; }
    public string Name { get; set; }
}

[TestFixture]
public class DapperTests
{
    private IDbConnection _connection;

    [SetUp]
    public void SetUp()
    {
        _connection = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=mydb");
        _connection.Open();

        _connection.Execute("create table employees(Id int, Name varchar(100), Age int)");
        _connection.Execute("insert into employees(Id, Name) values(1, 'xxx'), (2, 'yyy')");
    }

    [TearDown]
    public void TearDown()
    {
        _connection.Execute("drop table employees");
        _connection.Close();
    }

    [Test]
    public void BulkUpdateFromAListTest()
    {
        _connection.Execute(@"update employees set Name = @Name where Id = @Id",
            new List<Employee> 
            {
                new Employee{Age = 1, Name = "foo", Id = 1},
                new Employee{Age = 2, Name = "bar", Id = 2}
            });

        var result = _connection.Query<Employee>("select * from employees").ToList();

        Assert.That(result.Count, Is.EqualTo(2));
        Assert.That(result.FirstOrDefault(x => x.Id == 1).Name == "foo");
        Assert.That(result.FirstOrDefault(x => x.Id == 2).Name == "bar");

    }
}

The issue with Dapper, is that it executes each command separately, so if you have MANY, there will be performance issues.

Another approach, is to create a temp table and then update with a join. For this, you can do something like this:

[TestFixture]
public class BatchRunnerTests
{
    private readonly IDbConnection _dbConnection;

    public BatchRunnerTests()
    {
        _dbConnection = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=Bktb4_CaseMgr_Db"); ;
        _dbConnection.Open();
    }

    [Test]
    public void TestBatchRunner()
    {
        var records = new List<Employee>
        {
            new Employee {Age = 1, Name = "foo", Id = 1},
            new Employee {Age = 2, Name = "bar", Id = 2}
        };

        var tablwToUpdateFrom = BuildTable(records);

        _dbConnection.Execute("update a set Name = b.Name from employees a join " + tablwToUpdateFrom + " b on a.Id = b.Id");
    }

    public string BuildTable(List<Employee> data)
    {
        var tableName = "#" + Guid.NewGuid();

        _dbConnection.Execute("create table [" + tableName + "] ( Id int null, Name varchar(50) null)");

        var batchRunner = new SqlBatchRunner(_dbConnection);

        data.ToList().ForEach(x =>
            batchRunner.RecordingConnection.Execute(@"insert into [" + tableName + "] values(@Id, @Name)", x));

        batchRunner.Run();
        return tableName;
    }
}

The library that I'm using here is written by a friend. It will simply generate a group of insert statements and execute them at once.