It's been two days I'm wrestling with PetaPoco to implement a search solution (evidently with some search parameters) which has custom paging in database. I couldn't figure out how to configure PetaPoco paging with ASP.NET DataPager (which is a whole different question). Anyway I want to use custom paging in database.
I have a stored proc called GetUsersPaged like below:
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
and GetUsersCount stored proc like below:
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
Now I have two methods for calling these stored procs like below:
[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>();
}
My question is: everything is fine when I'm executing and testing stored procs in db (inside SQL Management Studio). All results respect to paging parameters. But when I call the same stored procs using PetaPoco, it fetches the whole thing and paging is ignored.
For example when I test stored procs in SQL Management Studio with
@startRowIndex = 0
@maximumRows = 1
and I don't send anything for @name and @email, it returns just one row which is totally correct. But the same things with PetaPoco, it returns 3 rows in the list. Any idea what's the problem?
