排序时在ASP.NET 2.0中进行有效的自定义分页 [英] Efficient Custom Paging in ASP.NET 2.0 while sorting

问题描述

我在ASP.NET 2.0中有一个Web应用程序，需要在其中进行分页.我的数据访问方法是将DataSet从数据库调用中拉出，然后将其转换为List<Foo>(其中Foo是我要从数据库中拉出的我的类型)并将我的GridView绑定到它.我这样做的原因是，我不想整个应用程序都在DataTables上使用字符串索引器，并且可以通过将显示逻辑实现为类的属性来将显示逻辑与数据库分离.这也意味着我正在使用.NET而不是SQL进行排序.

I've got an web app in ASP.NET 2.0 in which I need to do paging. My method of data access is to pull a DataSet out of a database call, then convert that to a List<Foo> (where Foo is my type I'm pulling out of the DB) and bind my GridView to it. My reason for this is that I didn't want to be having to use string indexers on DataTables all through my application, and that I could separate the display logic from the database by implementing display logic as properties on my classes. This also means I'm doing sorting in .NET instead of SQL.

然后，要实现分页，我需要将所有Foo从数据库中拉出，对列表进行排序，然后从完整列表中取出想要显示的内容:

To implement paging, then, I need to pull all Foo out of the database, sort the list, then take what I want out of the full list to display:

List<Foo> myFoo = MyDB.GetFoos();
myFoo.Sort(new Foo.FooComparer());
List<Foo> toDisplay = new List<Foo>();
for (int i = pageIndex * pageSize; i < (pageIndex + 1) * pageSize && i < myFoo.Count; i++)
{
  toDisplay.Add(myFoo[i]);
}
//bind grid

有了足够的元素，这就会成为延迟的根源；在连接到测试数据库的开发机器上，从数据库中提取5000条记录需要大约0.5秒的时间来绑定屏幕上的一个网格.

With enough elements, this becomes a source of delay; on my development machine connecting to the test database, it takes almost 0.5 seconds to bind one grid on the screen when pulling 5000 records from the DB.

要解决此问题，我是否必须将所有显示逻辑都移至SQL，以便可以在此处进行排序和分页，还是有更好的方法?

To solve this problem, am I going to have to move all my display logic to SQL so the sorting and paging can take place there, or is there a better way?

而且，Linq to SQL是否可以解决此问题?如果我按.NET类中实现的自定义属性进行排序，然后使用.Skip(pageIndex * pageSize).Take(pageSize)，它将按照

Also, does Linq to SQL solve this? If I'm sorting by a custom property implemented in my .NET class and then using .Skip(pageIndex * pageSize).Take(pageSize), will it convert that to SQL as noted in this question?

推荐答案

是-我建议您将记录选择移至SQL(排序和分页)-在SQL中执行分页的经典方法是使用CTE.我会为您找到一个很好的例子，并更新我的答案. http://softscenario.blogspot.com/2007/11/sql-2005-server-side-paging-using-cte.html -我在Google上搜索了"sql page cte".

Yes - I would recommend you move your record selection to SQL (sorting and paging) - the classic way to perform paging in SQL is to use a CTE. I'll find you a good example and update my answer. There's a good example here http://softscenario.blogspot.com/2007/11/sql-2005-server-side-paging-using-cte.html - I googled for "sql paging cte".

这篇关于排序时在ASP.NET 2.0中进行有效的自定义分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！