Dapper多参数存储过程查询未从数据库返回任何内容 [英] Dapper multi-parameter stored procedure query returns nothing back from database

问题描述

我一直在使用Dapper作为.NET Core Web Api的ORM。

I have been using Dapper as my ORM for my .NET Core Web Api.

当使用Dapper通过一个参数从数据库查询存储过程时，它将完全按照预期工作。当我添加多个参数时，它不会像往常一样返回任何数据。

When using Dapper to query a stored procedure from my database with one parameter, it works exactly as expected. When I add more than one parameter, it does not return anything back to my datamodel like it should.

我怀疑这与我的语法或我的方式有关正在构造查询。我在下面使用的存储过程在SSMS查询窗口中执行时可以按预期工作。

I suspect this has to do either with my syntax or the way I am structuring the query. The stored procedure I am using below works as expected when executed inside a SSMS query window.

这是我在DAL中包含Dapper查询的方法：

Here is my method containing the Dapper Query in my DAL:

public List<Players> C_GetAllActivePlayersInSport(int orgID, int sportID)
    {
        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            var returnedData = db.Query<Players>("dbo.spPlayers_GetAllActivePlayers_by_Sport @orgID, @sportID", new { orgID = orgID, sportID = sportID }).ToList();

            return returnedData;
        }
    }

传入的值将其传递给方法和查询上面的查询，但是查询执行后，它返回一个计数为0的列表。

The values passed in make it to the method and query above, but after the query executes, it returns a list with a count of 0.

任何帮助将不胜感激！

推荐答案

尝试：

var returnedData = db.Query<Players>(
    "dbo.spPlayers_GetAllActivePlayers_by_Sport",
        new { orgID, sportID }, commandType: CommandType.StoredProcedure).ToList();

（注意： .AsList（）稍为可取）

这篇关于Dapper多参数存储过程查询未从数据库返回任何内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！