时间戳

首页 > 解决方案 > 是否可以同时通过 dapper 运行 Insert 和 Get？

dapper - 是否可以同时通过 dapper 运行 Insert 和 Get？

问题描述

使用 ASP.NET Core3.1 和 Dapper，我想在我的数据库中插入一条记录并获得整个表作为结果！目前我有两个查询repository如下：第一：向我的数据库插入一条记录：

   //some codes here
  string sql = "UPDATE likes AS L SET L.isLike = @isLikeSQL WHERE L.ItemId = @ItemIdSQL  AND L.Ip = @IpSQL ";
  await dbConnection.ExecuteAsync(sql, new { ItemIdSQL = ItemId, senderpSQL = Ip, isLikeSQL = isLike });

第二：插入顶部记录后获取整个表：

//some codes here
string sql = " SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @itemIdSQL  AND I.isLike=1; " +
            " SELECT COUNT(*) FROM map118.likes AS I WHERE I.ItemId = @itemIdSQL  AND I.isLike=0; ";
using (var data = await dbConnection.QueryMultipleAsync(sql, new { itemIdSQL = ItemId }))
    {
      numberOfLikes = data.Read<int>().FirstOrDefault();
      numberOfDislikes = data.Read<int>().FirstOrDefault();
    }

我需要知道我是否可以通过 dapper 一次查询获得以上所有内容！事实上，我们QueryMultipleAsync在 dapper 中可以管理多个查询（正如我自己所做的那样），但我不知道我们是否可以合并一个ExecuteAsyncandQueryAsync并将它们全部放在一个查询中以获得更好的性能。我需要这样的东西：

string sql =
 "UPDATE likes AS L SET L.isLike = @isLikeSQL WHERE L.ItemId = @ItemIdSQL  AND  L.Ip = @IpSQL " +
 " SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @itemIdSQL  AND  L.isLike=1; "

using (var data = await dbConnection.QueryOrExecute???(sql, new { itemIdSQL = ItemId }))
    {
      numberOfLikes = data.Read<int>().FirstOrDefault();
      numberOfDislikes = data.Read<int>().FirstOrDefault();
    }

标签： dapper

解决方案

UPDATE不返回网格（除非您使用该OUTPUT子句。您可以在SQL 操作中执行任意数量的操作 - Dapper 关心的只是结果网格，所以是的，您可以执行一个update 和（单独）一个select. Executeover的唯一意义Query是Execute期望零网格。如果您要返回单个网格，那么Query; 如果您要返回多个网格：QueryMultiple. Dapper 对查询内部发生的事情不感兴趣-它完全不透明-全部看到的是结果网格。

不过，我强烈建议使用正式的操作分隔符 ( ;)。例如：

const string sql = @"
UPDATE likes AS L
SET L.isLike = @isLike
WHERE L.ItemId = @ItemId AND L.Ip = @Ip;

SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=1;
SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=0;
"
using (var data = await dbConnection.QueryMultipleAsync(sql,
    new { ItemId, isLike, Ip }))
{
    numberOfLikes = data.ReadSingle<int>();
    numberOfDislikes = data.ReadSingle<int>();
}

虽然说实话，我可能很想select用 Dapper 对值元组的位置处理来做一个 final ：

const string sql = @"
UPDATE likes AS L
SET L.isLike = @isLike
WHERE L.ItemId = @ItemId AND L.Ip = @Ip;

SELECT 
    (SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=1),
    (SELECT COUNT(*) FROM likes AS I WHERE I.ItemId = @ItemId AND L.isLike=0);
"
(var numberOfLikes, var numberOfDislikes) =
    await dbConnection.QuerySingleAsync<(int,int)>(sql,
    new { ItemId, isLike, Ip });

或涉及的东西group by L.isLike

推荐阅读