任何人都可以帮助使用 Dapper ORM 进行批量更新

本文关键字：更新 ORM Dapper 都可以 帮助 任何人 | 更新日期: 2023-09-27 18:32:42

我有一个表employee，我必须将它们的位置更新到新位置，所以我需要批量更新。请帮助我使用Dapper O.R.M.这样做。

我的主键是 Employee-id 。

您可以在下面看到一次在单个记录中更新的示例代码。

// 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();
}

任何人都可以帮助使用 Dapper ORM 进行批量更新

Dapper 支持从列表插入/更新。 

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");
    }
}

Dapper 的问题在于它单独执行每个命令，因此如果您有 MANY，就会出现性能问题。

另一种方法是创建一个临时表，然后使用联接进行更新。为此，您可以执行以下操作：

[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;
    }
}

我在这里使用的库是由一个朋友编写的。它将简单地生成一组插入语句并立即执行它们。

相关文章：