jquery DataTables 服务器模式的实现 .NET MVC

最近学习DataTables，服务器模式下走了许多弯路，现将实现细节记录如下，供参考，希望能帮助到需要的人。
首先声明，我是初学者，不对之处，请不要见笑。
环境：VS2017 .net4.6 mssql2012

VIEW的部分代码

我使用的是MVC模式，jquery-3.3.1.min.js在Views/Shared/_Layout.cshtml文件中已经引用，注意不能重复引用jquery-3.3.1.min.js。

<script type="text/javascript" charset="utf-8" src="~/Scripts/DataTables/datatables.min.js"></script>
<link rel="stylesheet" type="text/css" href="~/Scripts/DataTables/datatables.css" />

    <table id="table1" class="display">
        <thead>
            <tr>
                <th>ProductID</th>
                <th>ProductName</th>
                <th>CategoryID</th>
                <th>Unit</th>
                <th>UnitPrice</th>
                <th>Remark</th>
                <th>Quantity</th>
            </tr>
        </thead>
    </table>

<script type="text/javascript">
    $(document).ready(function () {
        $('#table1').DataTable({
            "language": { "url": "/Scripts/DataTables/datatables_language.json" },//语言设置，可以注释掉
            "serverSide": true,//开启服务器模式
            "ajax": {
                "url": "/test/lpSearch",
                "type": "post"              
            },
            "ordering": true,//排序
            "processing": true,
            "searching": true,//搜索
            "columns": [
                { data: "ProductID", "defaultContent": "" },
                { data: "ProductName", "defaultContent": "" },
                { data: "CategoryID", "defaultContent": "" },
                { data: "Unit", "defaultContent": "" },
                { data: "UnitPrice", "defaultContent": "" },
                { data: "Remark", "defaultContent": "" },
                { data: "Quantity", "defaultContent": "" }
            ]

        })
    });
</script>

Model部分

//数据表对应
 public class Product
    {
        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public int CategoryID { get; set; }
        public string Unit { get; set; }
        public decimal UnitPrice { get; set; }
        public string Remark { get; set; }
        public int Quantity { get; set; }
    }
//传入数据库查询需要的参数
 public class PagingModel
    {
        public int PageIndex { get; set; }//页号
        public int PageSize { get; set; }//每页记录数
        public int TotalRecord { get; set; }  //总记录数 
        public string Search { get; set; }//搜索内容
        public string OrderColumn { get; set; }//排序字段
        public string OrderDir { get; set; }//升或降序
    }


/// <summary>
/// JqueryDataTable插件交互的DT格式的数据(DT参数区分大小写)
///这个类来源于网络，在其基础上加以修改的，主要用来接收DataTable传到后台的参数
/// </summary>
public class DataTable
{  public int draw { get; set; }// 请求次数（前端==》后端）
   public int recordsTotal { get; set; }// 总记录数（前端《==后端）
   public int recordsFiltered { get; set; }// 过滤后的总记录数（前端《==后端）
   public int start { get; set; }// 记录开始索引（前端==》后端）
   public int pageIndex { get; set; }// PageIndex（前端==》后端）
   public int length { get; set; }// PageSize（前端==》后端）
   public IList data { get; set; }// 集合分页数据（前端《==后端）
   public List<ColumnsDetail> columns { get; set; }// 各列详情
   public ColumnsSearch search { get; set; }// 搜索
   public List<Order> order { get; set; }//排序详情
}    
//搜索
public class ColumnsSearch
{
    public string value { get; set; }//:单独要查询的值
    public bool regex { get; set; }//是否支持正则
}
//排序
public class Order
{
    public int column { get; set; } //排序列的序号，从0开始
    public string dir { get; set; }//asc  or desc
}
//各列详情
public class ColumnsDetail
{
    public string name { get; set; }
    public string data { get; set; }
    public bool searchable { get; set; }
    public bool orderable { get; set; }
    public ColumnsSearch search { get; set; }
}

Controller

//JqueryDataTable 服务器后台提取数据
        public ActionResult lpSearch(DataTable dt)
        {
            PagingModel pm = new PagingModel();
            pm.PageIndex = dt.start / dt.length + 1;
            pm.PageSize = dt.length;
            pm.TotalRecord = 0;//总记录数 output变量
            pm.Search = dt.search.value;//搜索
            if (pm.Search == null) { pm.Search = ""; }
            //在此只考虑单字段排序
            pm.OrderColumn = dt.columns[dt.order[0].column].data;//排序字段
            pm.OrderDir = dt.order[0].dir;//asc or desc
            //数据的提取，我这里使用了博客园fishli的ClownFish,执行了一个存储过程，返回list(Product)和TotalRecord（存储过程的Output参数）
            dt.data = DbHelper.FillList<Product>("getProductsByPage",pm);
            dt.recordsTotal = pm.TotalRecord;
            dt.recordsFiltered = pm.TotalRecord;
            return Json(dt);
        }

mssql2012 存储过程

我在存储过程中直接采用的拼接sql的方式，不能有效防止注入，可以改为参数化，或者在Controller中增加参数中特殊字符的过滤

ALTER  PROC getProductsByPage
 @PageIndex INT,
 @PageSize INT,
 @TotalRecord INT OUTPUT,
 @Search VARCHAR(20)='',
 @OrderColumn VARCHAR(20)='ProductID',
 @OrderDir VARCHAR(4)='asc'
 AS 
 BEGIN 
  IF (@PageIndex<=0) SET @PageIndex=1
  IF @OrderColumn='' SET @OrderColumn='ProductID'
  SELECT @TotalRecord=count(1) FROM Products  WHERE ProductName LIKE '%'+@Search+'%'
  DECLARE @sql VARCHAR(2000)
  SET @sql='SELECT * FROM Products a where ProductName like ''%'+@Search+'%'' '
  SET @sql=@sql+' order by '+@OrderColumn+' '+ @OrDerDir
  SET @sql=@sql+' offset '+convert(VARCHAR(9),(@PageIndex-1)*@PageSize)+' Rows fetch next '
               +convert(VARCHAR(5),@PageSize)+' Rows only'
  EXEC(@sql)
END
GO

效果图

按ProductName排序，搜索内容为“冲”