There were two days when I struggled with PetaPoco to implement a search solution (obviously with some search parameters) that has user paging in the database. I could not figure out how to configure PetaPoco paging using ASP.NET DataPager (which is a completely different issue). In any case, I want to use custom paging in the database.
I have a stored process called GetUsersPaged , as shown 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 procedures as shown 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 procedures, as shown 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 execute and test stored procs in db (inside SQL Management Studio). All results refer to paging parameters. But when I call the same stored procs using PetaPoco, it retrieves all this and the paging is ignored.
For example, when I test stored procs in SQL Management Studio with
@startRowIndex = 0 @maximumRows = 1
and I do not send anything for @name and @email , it returns only one line, which is absolutely correct. But the same with PetaPoco, it returns 3 rows in the list. Any idea what the problem is?