asp.net中ajax返回数据太大，如何分页处理

Question

asp.net中 ajax返回的json数据量很大，如何进行分页处理呢
后台获取ajax数据为

  [WebMethod]
  private static string  DataTable BinData(string dtFrom, string dtTo, string workshop, string line, string type)
        {
             string where = "";
            if ((dtFrom != null) && (dtFrom.Length > 0))
            {
                where += "and CONVERT(varchar(10), f.operate_date, 120) >='" + dtFrom + "'";
            }
            if ((dtTo != null) && (dtTo.Length > 0))
            {
                where += " and CONVERT(varchar(10), f.operate_date, 120) <='" + dtTo + "'";
            }

            if ((workshop.Length > 0) && (workshop != "请选择"))
            {
                where += "and c.name ='" + workshop + "'";
            }
            if ((line.Length > 0) && (line != "请选择"))
            {
                where += "and d.name ='" + line + "'";
            }
            if (type != "请选择")
            {
                where += "and b.type = '" + type + "'";
            }
            string sql = "    select distinct   a.name as 设备名称,a.code as 设备编码,c.name as 所属车间, d.name as 所属产线,case b.type when '1'then '日保养'when '2'then '周保养' when '3'then '月保养' end  as 保养类型,e.year as 保养年度, case e.status  when '2' then '正在执行' when '4' then '已经完成'end as 保养状态,item as 日期索引,   f.operate_date as 保养时间    from equipment a left join equipment_maintain b on a.id = b.equipment_id   left join work_shop c on a.shop_id =c.id    left join work_line d on a.line_id =d.id left join equipment_maintain_plan e on a.id = e.equipment_id left join equipment_maintain_plan_detail f on f.order_id = e.id where  b.status='0'    and b.type != '1' and is_done =1 and operate_date !='0001-01-01 00:00:00.0000000' " + where + " ORDER BY e.year DESC, item ASC";
            DataTable tb = DBSql.GetDataTable(sql, "1");
               string json = Newtonsoft.Json.JsonConvert.SerializeObject(tb); //使用json工具类的对象转为JSON                         
            return json;
        }

前台处理为

 <table class="site-table table-hover">
                    <thead>
                        <tr>
                            <th style="text-align: center;">设备编码</th>
                            <th style="text-align: center;">设备名称</th>                      
                            <th style="text-align: center;">保养类型</th>
                            <th style="text-align: center;">所属车间</th>
                            <th style="text-align: center;">所属产线</th>
                            <th style="text-align: center;">保养年度</th>
                            <th style="text-align: center;">保养状态</th>
                            <th style="text-align: center;">日期索引</th>
                            <th style="text-align: center;">保养时间</th>
                        </tr>
                    </thead>
                    <tbody  id="table-body">
                    </tbody>
                </table>
               <script>
        var myDiv = document.getElementById("Submit1");
        myDiv.addEventListener("click", function () {
            var dtFrom = document.getElementById("dtFrom").value;
            var dtTo = document.getElementById("dtTo").value;
          


            const selectedworkshopValue = document.getElementById("workshop");
            const selectedValue1 = selectedworkshopValue.value;

            const selectedlineValue = document.getElementById("line");
            const selectedValue2 = selectedlineValue.value;

            const selectedTypeValue = document.getElementById("type");
            const selectedValue3 = selectedTypeValue.value;
  
          
                $.ajax({
                    type: 'POST',
                    url: 'Web4.aspx/Get_Data',
                    dataType: 'json',
                    data: JSON.stringify({ dtFrom: dtFrom, dtTo: dtTo, workshop: selectedValue1, line: selectedValue2, type: selectedValue3 }),
                    async: true,
                    contentType: 'application/json; charset=utf-8',
                    success: function (data) {

                        if (data.d != null) {
                            var tableBody = document.getElementById("table-body");
                            tableBody.innerHTML = "";
                            $.each(JSON.parse(data.d), function (index, item) {

                                var tr = document.createElement("tr");
                                tr.innerHTML += `
                                     <td>${item.设备编码}</td>
                                     <td>${item.设备名称}</td>
                                     <td>${item.保养类型}</td>
                                     <td>${item.所属车间}</td>
                                     <td>${item.所属产线}</td>
                                     <td>${item.保养年度}</td>                                   
                                     <td>${item.保养状态}</td>
                                     <td>${item.日期索引}</td>
                                     <td>${item.保养时间}</td>
                                     </tr>`
                                tableBody.appendChild(tr);
                            });
                        }
                    }, error: function (xhr, status, error) {
                        alert(error);
                    }

                });
            
        });
    </script>

请问应该如何分页处理呢，才能展示数据

Answer 1

使用 ROW_NUMBER() 函数，进行分页。

SELECT column1, column2, ...
FROM (
    SELECT column1, column2, ..., ROW_NUMBER() OVER (ORDER BY column1) AS row_num
    FROM your_table
) AS numbered_table
WHERE row_num BETWEEN 11 AND 15;

以上示例也将从表中按照 column1 的顺序获取第 11 到 15 行的数据。