Configure Merge for concurrency handling

[VitaliiOdiichuk]

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]

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]

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]

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

Best Regards