C# JQuery DataTables 服务器端分页

JQuery DataTables server-side pagination

提问by davioooh

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

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

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.

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

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

我知道 DataTables 插件支持它，但四处搜索我还没有发现对实现它不清楚。

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?

你有过这样的经历吗？

NOTEI'm using Linq to SQL to connect to my DB

注意我正在使用 Linq to SQL 连接到我的数据库

回答by Jupaol

Since you are using LINQ to SQL, paginate is really easy:

由于您使用的是 LINQ to SQL，因此分页非常简单：

var c = new MyDataContext("your string");

c.Employees.Skip(pageIndex * pageSize).Take(pageSize);

This code will effectively paginate on the server

此代码将有效地在服务器上分页

I haven't used the DataTablesjQuery plug-in, but I assume you use AJAX to get the data (since you are not using MVC), so simply send as parameters the current page index, and the number of rows per page - page size, and that's it

我没有使用过DataTablesjQuery 插件，但我假设你使用 AJAX 来获取数据（因为你没有使用 MVC），所以只需将当前页面索引和每页行数作为参数发送 - page大小，就是这样

To fulfill the requirement, you would need to order your query on the server as well, so you would need to send the order criteria to the server and apply the order.

为了满足要求，您还需要在服务器上订购查询，因此您需要将订购条件发送到服务器并应用订单。

To order on the server based on a string, check the following question:

要根据 a 在服务器上订购string，请检查以下问题：

Dynamic LINQ OrderBy on IEnumerable<T>

IEnumerable<T> 上的动态 LINQ OrderBy

回答by Yasser Shaikh

Little late to the party, but this is worth sharing :)

聚会有点晚，但这值得分享:)

Employees
    .OrderBy(sortColumn + " " + sortOrder)
    .Skip(pageNo * pageSize)
    .Take(pageSize)
    .ToList();

回答by Gone Coding

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

现有答案可能适用于旧版本的 dataTable，但当前版本（我使用的是 1.10+）通过了起始记录和长度，因此任何建议pageNo * pageSize都会给出不正确的结果。

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 Requestvalues named startand 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.

接受的答案对于我想要做的事情来说也非常复杂，经过一些调试后，我发现页面大小和开始记录只是作为Request名为start和 的Http值传递length。文本搜索传递为search[value]排序顺序传递给名为的成员order[0][column]，排序方向传递给order[0][dir]等。

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

我用来排序和过滤的基本代码如下所示：

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

从 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();

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")

更新（使用“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 MVC5nuget package.

一旦我了解了服务器端数据表的基础知识，就该开始寻找现有的插件/实用程序来简化此代码。到目前为止，我发现的最合适的 MVC 5 是Datatables.net for MVC5nuget 包。

1. Install the NuGet Package

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

1. 安装 NuGet 包

2. 改变控制器Action使用aDataTablesBinder来提供IDataTablesRequest接口

e.g.

例如

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

1. Apply any search filter first:

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

e.g.

例如

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

1. The apply any sorting:

4. 应用任何排序：

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;
    }
}

1. Then apply the paging using Skipand Takeas before:

5. 然后像以前一样使用Skip和应用分页Take：

e.g.

例如

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

1. And finally return the JSON result using the DataTablesResponseobject:

6. 最后使用DataTablesResponse对象返回 JSON 结果：

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.

插件的文档在这里。