JQuery DataTables服务器端分页 [英] JQuery DataTables server-side pagination

问题描述

在我的网络应用程序中，我使用JQuery DataTables 插件来显示从数据库检索的数据。 p>

我正在使用客户端分页，但是我的表中的数据正在增长很多，而在ASP.NET页面中加载现在变得有点慢。所以我打算切换到服务器端分页。

我知道DataTables插件支持它，但是搜索周围我没有发现没有明确的实现它我的主要疑问是：如果我在服务器端实现分页，我也必须执行排序，或者我可以委托给客户端？

你有没有体验过这个？

注意我使用Linq to SQL连接到我的DB

解决方案

现有的答案可能适用于旧版的dataTable，但是当前版本我使用1.10+）传递开始记录和长度，所以任何建议 pageNo * pageSize 将提供不正确的结果。

第一个简单的手动方法

接受的答案对于我想要做的也很复杂经过一些调试，我发现页面大小和开始记录只是简单地传递为Http 请求名为开始的值， 长度。文本搜索被传递为 search [value] 排序顺序被传递到一个名为的成员中[0] [列] 和 order [0] [dir] 等中的排序方向。

我以前使用的基本代码排序和过滤器如下所示：

从HTTP请求对象获取分页，排序和过滤值：

  int startRec = 0; 
 int.TryParse（Request [start]，out startRec）; 
 int pageSize = 10; 
 int.TryParse（Request [length]，out pageSize）; 
 var search = Request [search [value]]; 
 var order = Request [order [0] [column]]; 
 var direction = Request [order [0] [dir]]; 
 
 var query = this._dataStore.Records.AsQueryable（）; 
  

应用（不区分大小写）首先搜索：

  if（！string.IsNullOrWhiteSpace（search））
 {
 query = query.Where（x => x.Label.ToLower（） search.ToLower（）））; 
} 
  

然后应用任何排序：

  switch（order）
 {
 //我的id列
 case0：
 query =（direction == desc）？ query.OrderByDescending（x => x.Id）：query.OrderBy（x => x.Id）; 
 break; 
 //我的标签列
 case1：
 query =（direction ==desc）？ query.OrderByDescending（x => x.Label）：query.OrderBy（x => x.Label）; 
 break; 
} 
  

最后应用分页：

  query = query.Skip（startRec）.Take（pageSize）; 
  

正确的记录现在可以返回。

更新（使用Datatables.net for MVC5）

一旦了解了服务器端数据表的基础知识，开始寻找现有的plugins / utils来简化这个代码。到目前为止，我发现的最适合的是MVC 5，它是 Datatables.net for MVC5 nuget包。

1. 安装NuGet软件包




2. 更改控制器操作以使用 DataTablesBinder 提供IDataTablesRequest接口

例如

  public JsonResult表（[ModelBinder（typeof（DataTablesBinder））] IDataTablesRequest requestmodel）
  

3. 首先应用任何搜索过滤器：

例如

  if（！string.IsNullOrEmpty（requestmodel .Search.Value））
 {
 query = query.Where（x => x.CompanyTypeName.Contains（requestmodel.Search.Value）|| x.CompanyTypeDescription.Contains（requestmodel.Search.Value ））; 
} 
  

4. 应用任何排序：

例如

  foreach （var sort in requestmodel.Columns.GetSortedColumns（））
 {
 switch（sort.Name）
 {
 caseCompanyTypeDescription：
 query = sort.SortDirection == Column.OrderDirection.Ascendant？ query.OrderBy（x => x.CompanyTypeDescription）：query.OrderByDescending（x => x.CompanyTypeDescription）; 
 break; 
 caseCompanyTypeName：
默认值：
 query = sort.SortDirection == Column.OrderDirection.Ascendant？ query.OrderBy（x => x.CompanyTypeName）：query.OrderByDescending（x => x.CompanyTypeName）; 
 break; 
} 
} 
  

5. 然后使用跳过和按照以前使用来应用分页：

例如

  var result = query.Skip（requestmodel.Start）.Take requestmodel.Length）.Select（x => new {x.CompanyTypeName，x.CompanyTypeDescription}）; 
  

6. 最后返回JSON结果， code> DataTablesResponse object：

例如

  return Json（new DataTablesResponse（requestmodel.Draw，result，query.Count（），base.RefSureContext.CompanyType.Count（）），JsonRequestBehavior.AllowGet）; 
  

这简化了所有的搜索，排序和分页到一个很好的简单重复模式。

这里的插件的文档在这里。

In my web-app I'm using JQuery DataTables plug-in to show data retrieved from database.

I'm currently using client-side pagination, but data in my tables are growing a lot, and loading in ASP.NET pages is now becoming a bit slow. So I was planning to switch to server-side pagination.

I know that DataTables plug-in supports it, but searching around I haven't found notting clear about implementing it.

My main doubt is: if I implement pagination on server-side I also have to implement ordering, or I can delegate it to client-side?

Have you ever experienced this?

NOTE I'm using Linq to SQL to connect to my DB

解决方案

The existing answers might apply to an old version of dataTable, but current versions (I am using 1.10+) pass the start record and length so anything suggesting pageNo * pageSize is going to give incorrect results.

First simple "manual" approach

The accepted answer was also very complicated for what I wanted to do so, after some debugging, I found that the page size and start record are simply passed as Http Request values named start and length. The text search is passed as search[value] The sort order is passed in a member named order[0][column] and the sort direction in order[0][dir] etc.

The basic code I used to sort and filter looks like this:

Get the paging, sorting and filtering values from the HTTP Request object:

int startRec = 0;
int.TryParse(Request["start"], out startRec);
int pageSize = 10;
int.TryParse(Request["length"], out pageSize);
var search = Request["search[value]"];
var order = Request["order[0][column]"];
var direction = Request["order[0][dir]"];

var query = this._dataStore.Records.AsQueryable();

Apply (case insensitive) search first:

if (!string.IsNullOrWhiteSpace(search))
{
    query = query.Where(x => x.Label.ToLower().Contains(search.ToLower()));
}

Then apply any sorting:

switch (order)
{
    // My id column
    case "0":
        query = (direction == "desc") ? query.OrderByDescending(x => x.Id) : query.OrderBy(x => x.Id);
        break;
    // My label column
    case "1":
        query = (direction == "desc") ? query.OrderByDescending(x => x.Label) : query.OrderBy(x => x.Label);
        break;
}

Finally apply the paging:

query = query.Skip(startRec).Take(pageSize);

The correct records are now ready to return.

Update (using "Datatables.net for MVC5")

Once I understood the basics of server-side dataTables, it was time to start looking for existing plugins/utils to simplify this code. The most appropriate one I have found so far, for MVC 5, is the Datatables.net for MVC5 nuget package.

1. Install the NuGet Package

2. Change the controller Action to use a DataTablesBinder to provide a IDataTablesRequest interface

e.g.

 public JsonResult Table([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestmodel)

3. Apply any search filter first:

e.g.

if (!string.IsNullOrEmpty(requestmodel.Search.Value))
{
    query = query.Where(x => x.CompanyTypeName.Contains(requestmodel.Search.Value) || x.CompanyTypeDescription.Contains(requestmodel.Search.Value));
}

4. The apply any sorting:

e.g.

foreach (var sort in requestmodel.Columns.GetSortedColumns())
{
    switch (sort.Name)
    {
        case "CompanyTypeDescription":
            query = sort.SortDirection == Column.OrderDirection.Ascendant ? query.OrderBy(x => x.CompanyTypeDescription) : query.OrderByDescending(x => x.CompanyTypeDescription);
            break;
        case "CompanyTypeName":
        default:
            query = sort.SortDirection == Column.OrderDirection.Ascendant ? query.OrderBy(x => x.CompanyTypeName) : query.OrderByDescending(x => x.CompanyTypeName);
            break;
    }
}

5. Then apply the paging using Skip and Take as before:

e.g.

var result = query.Skip(requestmodel.Start).Take(requestmodel.Length).Select(x => new { x.CompanyTypeName, x.CompanyTypeDescription });

6. And finally return the JSON result using the DataTablesResponse object:

e.g.

return Json(new DataTablesResponse(requestmodel.Draw, result, query.Count(), base.RefSureContext.CompanyType.Count()), JsonRequestBehavior.AllowGet);

This simplified all the searching, sorting & paging into a nice easy to repeat pattern.

The documentation for the addin is here.

这篇关于JQuery DataTables服务器端分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！