sql - 使用 PetaPoco 执行参数化存储过程

Question

我花了两天时间与 PetaPoco 努力实现一个搜索解决方案(显然带有一些搜索参数)，该解决方案在数据库中具有自定义分页。我不知道如何使用 ASP.NET DataPager 配置 PetaPoco 分页(这是一个完全不同的问题)。无论如何，我想在数据库中使用自定义分页。

我有一个名为 GetUsersPaged 的存储过程，如下所示:

ALTER PROCEDURE [dbo].[GetUsersPaged] 
@startRowIndex int,
@maximumRows int,
@name nvarchar(300) = NULL,
@email nvarchar(100) = NULL
AS
BEGIN
    SELECT *
    FROM 
    (
    SELECT *,
            ROW_NUMBER() OVER(ORDER BY Id) AS RowRank
            FROM UserInfo
            WHERE 
        (Nickname LIKE '%'+@name+'%' 
         OR FirstName LIKE '%'+@name+'%' 
         OR LastName LIKE '%'+@name+'%' 
         OR @name IS NULL) 
            AND 
        (Email = @email OR @email IS NULL)

    ) AS UsersPagedList
    WHERE RowRank BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) 
END

和GetUsersCount存储过程如下:

    ALTER PROCEDURE [dbo].[GetUsersCount] 
    @name nvarchar(300) = NULL,
    @email nvarchar(100) = NULL
    AS
    BEGIN
   SELECT COUNT(*)
       FROM UserInfo
       WHERE 
    (Nickname LIKE '%'+@name+'%' 
     OR FirstName LIKE '%'+@name+'%' 
     OR LastName LIKE '%'+@name+'%' 
     OR @name IS NULL) 
        AND 
    (Email = @email OR @email IS NULL)
   END

现在我有两种方法来调用这些存储过程，如下所示:

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public List<DAL.UserInfo> GetPagedUserSearchResults(int startRowIndex, int pageSize, string name, string email)
    {
        DBService dbService = new DBService();
        var db = dbService.GetDatabase();
        var list = new List<DAL.UserInfo>();

        if(name != string.Empty && email != string.Empty)
            list = db.Fetch<DAL.UserInfo>(@"EXEC GetUsersPaged @@startRowIndex = @0, @@maximumRows = @1, 
                                            @@name = @2, @@email = @3", startRowIndex, pageSize, name, email);

        else if(name == string.Empty && email != string.Empty)
            list = db.Fetch<DAL.UserInfo>(@"EXEC GetUsersPaged @@startRowIndex = @0, @@maximumRows = @1, 
                                            @@email = @2", startRowIndex, pageSize, email);

        else if(name != string.Empty && email == string.Empty)
            list = db.Fetch<DAL.UserInfo>(@"EXEC GetUsersPaged @@startRowIndex = @0, @@maximumRows = @1, 
                                            @@name = @2", startRowIndex, pageSize, name);

        else if(name == string.Empty && email == string.Empty)
            list = db.Fetch<DAL.UserInfo>(@"EXEC GetUsersPaged @@startRowIndex = @0, @@maximumRows = @1"
                                            ,startRowIndex, pageSize); 
        return list;
    }

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public int GetPagedUserSearchResultsCount(string name, string email)
    {
        DBService dbService = new DBService();
        var db = dbService.GetDatabase();
        IEnumerable<DAL.UserInfo> count = null;

        if (name != string.Empty && email != string.Empty)
            count = db.Query<DAL.UserInfo>("EXEC GetUsersCount @@name = @0, @@email = @1", name, email);

        else if (name == string.Empty && email != string.Empty)
            count = db.Query<DAL.UserInfo>("EXEC GetUsersCount @@email = @0", email);

        else if(name != string.Empty && email == string.Empty)
            count = db.Query<DAL.UserInfo>("EXEC GetUsersCount @@name = @0", name);

        else if (name == string.Empty && email == string.Empty)
            count = db.Query<DAL.UserInfo>("EXEC GetUsersCount");

        return count.Count<DAL.UserInfo>();
    }

我的问题是:当我在数据库(SQL Management Studio 内部)中执行和测试存储过程时，一切都很好。所有结果均与寻呼参数有关。但是当我使用 PetaPoco 调用相同的存储过程时，它会获取整个内容并且分页被忽略。

例如，当我使用

在 SQL Management Studio 中测试存储过程时

@startRowIndex = 0
@maximumRows = 1

我没有为@name和@email发送任何内容，它只返回一行，这是完全正确的。但与 PetaPoco 相同，它在列表中返回 3 行。知道问题出在哪里吗？

Answer 1

您是否尝试过设置 EnableAutoSelect = false？当我将其保留为默认设置时，调用返回所有记录。