zzzprojects/Dapper-Plus

Configure Merge for concurrency handling

VitaliiOdiichuk opened this issue · 3 comments

VitaliiOdiichuk commented

Hello,

I'm using Dapper Plus to post data to an MS SQL database, specifically for performing a bulk merge operation. Currently, I have a class called TestDO that represents the data I'm working with.

public class TestDO
{
    public string AUID { get; set; }
    public decimal? AEmployeeHours { get; set; }
    public byte[]? ARecordRowVersion { get; set; }
}

I would like to handle concurrency during the update process by utilizing the SQL merge statement. Below is an example of the script I need for concurrency handling:

MERGE [TestTable] AS t
USING (VALUES (@AUID, @AEmployeeHours, @ARecordRowVersion)) AS s([AUID], [AEmployeeHours], [ARecordRowVersion])
    ON s.[AUID] = t.[AUID]
    WHEN NOT MATCHED BY Target THEN
        INSERT ([AUID], [AEmployeeHours]) 
        VALUES (s.[AUID], s.[AEmployeeHours])
    WHEN MATCHED AND t.[ARecordRowVersion] = s.[ARecordRowVersion]
        THEN UPDATE SET t.[AEmployeeHours] = s.[AEmployeeHours]
    OUTPUT s.AUID

I would like to configure Dapper Plus merging to generate a similar script. Specifically, I am interested in the following parts: "AND t.[ARecordRowVersion] = s.[ARecordRowVersion]" and "OUTPUT s.AUID".

Here are the additional technical details:

Dapper version: 2.0.123
Dapper Plus version: 4.0.37
Database Provider: MS SQL 16

Note: The versions of Dapper and Dapper Plus can be updated to the latest if required.

Thanks

JonathanMagnan commented

Hello @VitaliiOdiichuk ,

Thank you for your question, I will ask my developer to create you an online example with your example

Best Regards,

Jon

JonathanMagnan commented

Hello @VitaliiOdiichuk ,

Here is a way that you can achieve this: https://dotnetfiddle.net/XWLoeg

We added two examples. With this, you can know the number of rows that has been insert / updated versus the number of entities.

Let me know if that's what you were looking for or you need something more for your scenario

Best Regards,

Jon

VitaliiOdiichuk commented

Hello, Thanks for quick response. Example was really helpful.

Best Regards

  • 😄1