c# - 使用合并语句获取插入和更新的单个计数 - C# 和 SQL Server

Question

我正在使用 MERGE命令来更新行(使用 dapper ORM)，我想分别获取单个插入和更新的计数。我已将合并操作转储到临时表以供稍后查询。当我检查数据库时，行被正确插入，但是我无法获得每个操作的计数
在这里，我通过 C# 添加了一个包含 3 个项目的列表，并且合并语句正确添加了它们。

在检查存储操作(插入或更新)的结果时，我看到计数为 1 而不是 3

在 SSMS 中使用类似的源表和目标表执行时，合并语句中使用的临时表返回具有正确计数的表:

如果我做错了什么，请告诉我。谢谢你。
我将粘贴 C# 代码以供引用:

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int Rate { get; set; }
}

    public async Task<IEnumerable<Tuple<string, int>>> UpsertProductCostAsync()
    {
        var products = new List<Product> 
                    {
                        new Product {ProductID = 1, ProductName = "Tea", Rate = 10},
                        new Product {ProductID = 2, ProductName = "Coffee", Rate = 20},
                        new Product {ProductID = 3, ProductName = "Muffin", Rate = 30}
                    };

        var mergeStatement = @"DROP TABLE IF EXISTS ##MergeActions
                               CREATE TABLE ##MergeActions ([MergeAction] VARCHAR(10))
                               INSERT INTO ##MergeActions ([MergeAction])
                                   SELECT [MergeAction]
                                   FROM
                                       (MERGE [dbo].[Products] AS target 
                                        USING (VALUES (@ProductID, @ProductName, @Rate)) AS source (ProductID, ProductName, Rate) 
                                              ON (target.ProductID = source.ProductID)
                                        WHEN MATCHED THEN
                                            UPDATE
                                                SET target.ProductName = source.ProductName,
                                                    target.Rate = source.Rate
                                        WHEN NOT MATCHED THEN
                                            INSERT ([ProductID],[ProductName], [Rate]) 
                                            VALUES (source.ProductID, source.ProductName, source.Rate)
                                        OUTPUT $action AS MergeAction) MergeOutput;
                                        ";

        _con.Open();
        var rowsAffected = await _con.ExecuteAsync(mergeStatement, products);
        var operationsWithCount = await _con.QueryAsync(@"SELECT [MergeAction], COUNT(*) as [Count]
FROM ##MergeActions  
GROUP BY MergeAction;");

        var result = new List<Tuple<string, int>>();

        foreach (var o in operationsWithCount)
        {
            result.Add(new Tuple<string, int>(o.MergeAction, o.Count));
        }

        return result;
    }

SQL Server 表定义:

CREATE TABLE Products
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate int
)

Answer 1

我建议从 SQL Server 中的表值参数开始。这将允许您传入产品列表并在相同的 SQL 命令中获得结果。

CREATE TYPE TVP_Product AS TABLE
(
    ProductID integer not null,
    ProductName varchar(100) not null,
    Rate int not null
);

你的 C# 可能是:

public async Task<IEnumerable<Tuple<string, int>>> UpsertProductCostAsync()
{
    var products = new List<Product>
            {
                new Product {ProductID = 1, ProductName = "Tea", Rate = 10},
                new Product {ProductID = 2, ProductName = "Coffee", Rate = 20},
                new Product {ProductID = 3, ProductName = "Muffin", Rate = 30}
            };

    // one approach to transforming a collection to a datatable.
    // you could find / create an extension method to do this more succintly
    var productsDt = new DataTable();
    productsDt.Columns.Add("ProductID", typeof(int));
    productsDt.Columns.Add("ProductName", typeof(string));
    productsDt.Columns.Add("Rate", typeof(int));

    foreach (var product in products)
        productsDt.Rows.Add(product.ProductID, product.ProductName, product.Rate);

    var mergeStatement = @"
            
MERGE INTO [dbo].[Products] AS target 
USING (select ProductId, ProductName, Rate from @products) as source 
ON (target.ProductID = source.ProductID)
WHEN MATCHED THEN
UPDATE
SET target.ProductName = source.ProductName,
    target.Rate = source.Rate
WHEN NOT MATCHED THEN
INSERT ([ProductID],[ProductName], [Rate]) 
VALUES (source.ProductID, source.ProductName, source.Rate)
OUTPUT $action AS MergeAction;

";

    _con.Open();

    var actions = await _con.QueryAsync<string>(mergeStatement, 
        new { 
            products = productsDt.AsTableValuedParameter("TVP_Product") 
        }
    );

    var results = actions
        .GroupBy(x => x, (y, z) =>
            new Tuple<string, int>(y, z.Count()))
        .ToList();

    return results;
}