ajax jquery dataTable 不会转到指定的 actionMethod

Question

我正在使用带有 asp.net MVC的jQuery dataTable。

我的操作方法代码是：

public ActionResult getEmployeesInfo(JQueryDataTableParams param) {
EmployeeBL obj_EmployeeBL = new EmployeeBL();
var result = obj_EmployeeBL.getEmployeesData(param);
return Json(result, JsonRequestBehavior.AllowGet);
//    return PartialView("_EmployeesList", Json( result,
//        JsonRequestBehavior.AllowGet));
}

BL 和 DAL 是：

public string getEmployeesData(JQueryDataTableParams param) {
using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conne"].ConnectionString)) {
List < EmployeeDataViewModel > lst_Employees = new List < EmployeeDataViewModel > ();
int filteredCount = 0;
using(SqlCommand command = new SqlCommand("spGetEmployees", conn)) {
command.CommandType = CommandType.StoredProcedure;
//  command.Parameters.AddWithValue("@CurrentCulture", currentCultuer);
command.Parameters.AddWithValue("@DisplayLength", param.iDisplayLength);
command.Parameters.AddWithValue("@DisplayStart", param.iDisplayStart);
command.Parameters.AddWithValue("@SortCol", param.iSortCol_0);
command.Parameters.AddWithValue("@SortDir", param.sSortDir_0);
command.Parameters.AddWithValue("@Search", param.sSearch);
conn.Open();
command.CommandTimeout = 500;
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read()) {
EmployeeDataViewModel emp = new EmployeeDataViewModel();
filteredCount = Convert.ToInt32(rdr["TotalCount"]);
emp.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
emp.Name = rdr["Name"].ToString();
emp.Level_1Name = rdr["level1"].ToString();
emp.Level_2Name = rdr["level2"].ToString();
emp.Level_3Name = rdr["level3"].ToString();
emp.Level_4Name = rdr["level4"].ToString();
emp.Level_5Name = rdr["level5"].ToString();
emp.RankName = rdr["RankNO"].ToString();
if (rdr["Rank"] != DBNull.Value) {
emp.Rank = Convert.ToInt32(rdr["Rank"]);
}
emp.EmployeeTypeName = (rdr["EmployeeType"] != DBNull.Value) ? rdr["EmployeeType"].ToString() : "-";
lst_Employees.Add(emp);
}
var result = new {
// sEcho = param.sEcho,
iTotalRecords = GetEmployeeTotalCount(),
iTotalDisplayRecords = filteredCount,
data = lst_Employees
};
JavaScriptSerializer js = new JavaScriptSerializer();
return js.Serialize(result);
}
}
}

$(document).ready(function () {
$('#EmployeesDataTable').dataTable({
"bProcessing": true,
"columns": [
{ 'data': 'EmployeeId' },
{ 'data': 'Name' },
{ 'data': 'Level_1Name' },
{ 'data': 'Level_2Name' },
{ 'data': 'Level_3Name' },
{ 'data': 'Level_4Name' },
{ 'data': 'Level_5Name' },
{ 'data': 'RankName' },
{ 'data': 'Rank' },
{ 'data': 'EmployeeTypeName' }
],
"bServerSide": true,
"ajax": {
"url":'@Url.Action("getEmployeesInfo","Emp_Employee")',
"dataSrc": ""
}
});
});

<div class="table-responsive">
<table class="table table-hover" id="EmployeesDataTable">
<thead>
<tr class="">
<th>@Resource.ID</th>
<th>@Resource.EmployeeName</th>
<th>@Resource.FirstLevel</th>
<th>@Resource.SecondLevel</th>
<th>@Resource.ThirdLevel</th>
<th>@Resource.FourthLevel</th>
<th>@Resource.FifthLevel</th>
<th>@Resource.RankNumber</th>
<th>@Resource.Rank</th>
<th>@Resource.EmpType</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>

问题是我的代码没有转到 Ajax 内部的 URL。 然后出现以下警报框

DataTables警告(table id = 'EmployeesDataTable'):DataTables警告：无法解析来自服务器的JSON数据。这是由JSON格式错误引起的。

我认为我的数据表不明白 ajax 是什么意思。

Answer 1

这里一个字符串被传递给 Json() 方法，而不是将结果作为匿名对象返回

public ActionResult getEmployeesInfo(JQueryDataTableParams param) {
EmployeeBL obj_EmployeeBL = new EmployeeBL();
var result = obj_EmployeeBL.getEmployeesData(param);
return Json(result, JsonRequestBehavior.AllowGet);
}

我更改了代码以使用对象

public object getEmployeesData(JQueryDataTableParams param) {
using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conne"].ConnectionString)) {
List < EmployeeDataViewModel > lst_Employees = new List < EmployeeDataViewModel > ();
int filteredCount = 0;
using(SqlCommand command = new SqlCommand("spGetEmployees", conn)) {
command.CommandType = CommandType.StoredProcedure;
//  command.Parameters.AddWithValue("@CurrentCulture", currentCultuer);
command.Parameters.AddWithValue("@DisplayLength", param.iDisplayLength);
command.Parameters.AddWithValue("@DisplayStart", param.iDisplayStart);
command.Parameters.AddWithValue("@SortCol", param.iSortCol_0);
command.Parameters.AddWithValue("@SortDir", param.sSortDir_0);
command.Parameters.AddWithValue("@Search", param.sSearch);
conn.Open();
command.CommandTimeout = 500;
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read()) {
EmployeeDataViewModel emp = new EmployeeDataViewModel();
filteredCount = Convert.ToInt32(rdr["TotalCount"]);
emp.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
emp.Name = rdr["Name"].ToString();
emp.Level_1Name = rdr["level1"].ToString();
emp.Level_2Name = rdr["level2"].ToString();
emp.Level_3Name = rdr["level3"].ToString();
emp.Level_4Name = rdr["level4"].ToString();
emp.Level_5Name = rdr["level5"].ToString();
emp.RankName = rdr["RankNO"].ToString();
if (rdr["Rank"] != DBNull.Value) {
emp.Rank = Convert.ToInt32(rdr["Rank"]);
}
emp.EmployeeTypeName = (rdr["EmployeeType"] != DBNull.Value) ? rdr["EmployeeType"].ToString() : "-";
lst_Employees.Add(emp);
}
var result = new {
// sEcho = param.sEcho,
iTotalRecords = GetEmployeeTotalCount(),
iTotalDisplayRecords = filteredCount,
data = lst_Employees
};
return result;
}
}
}

Answer 2

此问题的解决方案是： 1. 将 jquery.dataTables.min.js 更新到新版本，以便我的数据表理解"ajax"的含义。通过将以下内容插入到LayOut.cshtml中：

<script src="//cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css"
href="//cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />

2. 将"getEmployeesInfo"操作修改为以下内容：

   public ActionResult getEmployeesInfo()
   {
   string search = Request.Form.GetValues("search[value]")[0];
   string draw = Request.Form.GetValues("draw")[0];
   string order = Request.Form.GetValues("order[0][column]")[0];
   string orderDir = Request.Form.GetValues("order[0][dir]")[0];
   int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
   int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
   EmployeeBL obj_EmployeeBL = new EmployeeBL();
   var employeeDataViewModel = obj_EmployeeBL.getEmployeesData(draw,search, order,orderDir, startRec, pageSize);
   int recordsTotal = employeeDataViewModel.TotalCount;
   int recordsFiltered = employeeDataViewModel.filteredCount;
   var data = employeeDataViewModel.lst_employeeData;
   var result = this.Json(new { draw = Convert.ToInt32(draw), recordsTotal = recordsTotal, recordsFiltered = recordsFiltered, data = data }, JsonRequestBehavior.AllowGet);
   
   return result;
   }
   

   3. 修改"getEmployeesData"函数如下：

      public EmployeeDataViewModel getEmployeesData(string draw，string search， string order， string orderDir，int startRec， int pageSize) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ATASSqlCon"].连接字符串)) { EmployeeDataViewModel obj_EmployeeDataViewModel = new EmployeeDataViewModel(); obj_EmployeeDataViewModel.lst_employeeData = new List(); int filteredCount = 0; using (SqlCommand command = new SqlCommand("spGetEmployees"， conn)) { 命令。CommandType = CommandType.StorageProcedure;

      command.Parameters.AddWithValue("@DisplayLength",pageSize);
      command.Parameters.AddWithValue("@DisplayStart",startRec);
      command.Parameters.AddWithValue("@SortCol",Convert.ToInt32(order));
      command.Parameters.AddWithValue("@SortDir", orderDir);
      command.Parameters.AddWithValue("@Search", search);
      conn.Open();
      command.CommandTimeout = 500;
      SqlDataReader rdr = command.ExecuteReader();
      while (rdr.Read())
      {
      EmployeeDataModel emp = new EmployeeDataModel();
      filteredCount = Convert.ToInt32(rdr["TotalCount"]);
      emp.EmployeeId = Convert.ToInt64(rdr["EmployeeId"]);
      emp.Name = rdr["Name"].ToString();
      emp.Level_1Name = rdr["level1"].ToString();
      emp.Level_2Name = rdr["level2"].ToString();
      emp.Level_3Name = rdr["level3"].ToString();
      emp.Level_4Name = rdr["level4"].ToString();
      emp.Level_5Name = rdr["level5"].ToString();
      emp.RankName = rdr["RankNO"].ToString();
      if (rdr["Rank"] != DBNull.Value)
      {
      emp.Rank = Convert.ToInt32(rdr["Rank"]);
      }
      emp.EmployeeTypeName = (rdr["EmployeeType"] != DBNull.Value) ? rdr["EmployeeType"].ToString() : "-";
      
      obj_EmployeeDataViewModel.lst_employeeData.Add(emp);
      }
      obj_EmployeeDataViewModel.TotalCount = GetEmployeeTotalCount();
      obj_EmployeeDataViewModel.filteredCount = filteredCount;
      
      return obj_EmployeeDataViewModel;
      }
      }
      

      }

   4. 修改脚本如下：

$(document).ready(function () {
$('#EmployeesDataTable').DataTable({
"columnDefs": [
{
"width": "5%",
"targets": [0]
},
{
"className": "text-center custom-middle-align",
"targets": [0, 1, 2, 3, 4, 5, 6]
}, ],
"processing": true,
"serverSide": true,
"ajax":
{
"url": "/Emp_Employee/getEmployeesInfo",
"type": "POST",
"dataType": "JSON"
},
"columns": [
{
"data": "EmployeeId"
},
{
"data": "Name"
},
{
"data": "Level_1Name"
},
{
"data": "Level_2Name"
},
{
"data": "Level_3Name"
},
{
"data": "Level_4Name"
},
{
"data": "Level_5Name"
},
{
"data": "RankName"
},
{
"data": "Rank"
},
{
"data": "EmployeeTypeName"
}]
});
});