All geek questions in one place

Paging using Linq-To-Sql based on two parameters in asp.net mvc

As two parameters, I say currentPage and pagesize ..... I still used sql server stored procedures and implemented paging like this,

 GO ALTER PROCEDURE [dbo].[GetMaterialsInView] -- Add the parameters for the stored procedure here @CurrentPage INT, @PageSize INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT *,ROW_NUMBER() OVER (ORDER BY Id) AS Row FROM ( SELECT *,ROW_NUMBER() OVER (ORDER BY Id) AS Row FROM InTimePagingView ) AS InTimePages WHERE Row >= (@CurrentPage - 1) * @PageSize + 1 AND Row <= @CurrentPage*@PageSize SELECT COUNT(*) as TotalCount FROM InTimePagingView SELECT CEILING(COUNT(*) / CAST(@PageSize AS FLOAT)) NumberOfPages FROM InTimePagingView END

Now I am using Linq-to-sql and I am using this,

 public IQueryable<MaterialsObj> FindAllMaterials() { var materials = from m in db.Materials join Mt in db.MeasurementTypes on m.MeasurementTypeId equals Mt.Id where m.Is_Deleted == 0 select new MaterialsObj() { Id = Convert.ToInt64(m.Mat_id), Mat_Name = m.Mat_Name, Mes_Name = Mt.Name, }; return materials; }

Now I want to return records,TotalCount , where I use Total count to generate pagenumbers ..... Is this possible ... Any suggestion ...

EDIT:

Just found this ...

 NorthWindDataContext db = new NorthWindDataContext(); var query = from c in db.Customers select c.CompanyName; //Assuming Page Number = 2, Page Size = 10 int iPageNum = 2; int iPageSize = 10; var PagedData = query.Skip((iPageNum - 1) * iPageSize).Take(iPageSize); ObjectDumper.Write(PagedData);
+4
source share
1 answer

You can do this “manually” using IQueryable.Skip () ,. Take () and .Count (), or you can use this convenient template: -

 public class PagedList<T> : List<T> { public PagedList(IEnumerable<T> source, int index, int pageSize) { this.TotalCount = source.Count(); this.PageSize = pageSize; this.PageIndex = index; this.AddRange(source.Skip(index * pageSize).Take(pageSize).ToList()); } public int TotalCount { get; set; } public int PageIndex { get; set; } public int PageSize { get; set; } public bool HasPreviousPage { get { return (PageIndex > 0); } } public bool HasNextPage { get { return ((PageIndex + 1) * PageSize) < TotalCount; } } }

Use it like this: -

 public ActionResult Materials(int page) { var materials = MaterialsRepository.FindAllMaterials(); var results = new PagedList<MaterialsObj>(materials, page - 1, 10); return new View(results) }

There are many realizations around the world with helpers and tests and all this jazz. I think it was ScottGu who came up with the original code, but don't quote me on that.

+6
source

Source: https://habr.com/ru/post/1308735/

More articles:


All Articles