Performing MERGE with Dapper.net

dapper

Solution

Just found out that you can do it like I did in .NET Fiddle code here: https://dotnetfiddle.net/e2G3Ho

Pasting the code below

// @nuget: Dapper -Version 1.60.6

using Dapper;
using System;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;

public class Program
{    
    public class OrderDetail
    {
        public int OrderDetailID { get; set; }
        public int OrderID { get; set; }
        public int ProductID { get; set; }
        public int Quantity { get; set; }
    }

    public static void Main()
    {
        string sql = "SELECT * FROM OrderDetails";
        string sql2 = @"MERGE INTO OrderDetails AS TARGET 
            USING (
            VALUES
                (@OrderDetailID, @OrderID, @ProductID, @Quantity)
            ) AS SOURCE (OrderDetailID, OrderID, ProductID, Quantity)
            ON SOURCE.OrderDetailID = TARGET.OrderDetailID
            WHEN MATCHED THEN
            UPDATE SET Quantity = 666
            WHEN NOT MATCHED THEN
            INSERT (OrderID, ProductID, Quantity)
            VALUES (SOURCE.OrderID, SOURCE.ProductID, SOURCE.Quantity);
            "; 

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
        {   
            List<OrderDetail> details = new List<OrderDetail> {
                {new OrderDetail() {OrderDetailID = 1, OrderID = 1024, ProductID = 42, Quantity = 1000} },
                {new OrderDetail() {OrderDetailID = 9999, OrderID = 10268, ProductID = 42, Quantity = 1000} }
            };

            connection.Execute(sql2, details);
            var orderDetails = connection.Query<OrderDetail>(sql).ToList();

            FiddleHelper.WriteTable(orderDetails);
        }
    }
}

Problem

We are using Dapper.net for the data layer in an ASP.net MVC 5 app. One operation requires the use of the MERGE command (if the TitleID exists, update the record, if it doesn't, insert it) - something like this: ``` MERGE BookInventory bi USING BookOrder bo ON bi.TitleID = bo.TitleID WHEN MATCHED THEN UPDATE SET bi.Quantity = bi.Quantity + bo.Quantity WHEN NOT MATCHED BY TARGET THEN INSERT (TitleID, Title, Quantity) VALUES (bo.TitleID, bo.Title,bo.Quantity); ``` Can Dapper be used to map values onto this MERGE statement? I can't find anything on this and it's unclear what the best approach to using MERGE with Dapper.net is?

Original source