如何将 jQuery Datatable 中的存储过程与 ASP.NET MVC 一起使用?

Question

[HttpPost]
public ActionResult LoadData()
{
var client = new RestClient(url);
var request = new RestRequest("api/abc/GetAllEmployee", Method.GET);
var response = client.Execute<List<UserViewModel>>(request);   
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int totalRecords = 0;
var v = (from a in userlist select a);
totalRecords = v.Count();
var data = v.Skip(skip).Take(pageSize).ToList();
return Json(new 
{ 
draw = draw, 
recordsFiltered = totalRecords, 
recordsTotal = totalRecords,   
data = data 
}, JsonRequestBehavior.AllowGet);
}

我想使用存储过程在我的jQuery数据表中进行分页。我的 SQL Server 存储过程是：

CREATE PROCEDURE [dbo].[Sp_getdata] 
@PageNo INT, 
@NoOfRecord INT, 
@TotalRecord INT OUTPUT
AS 
SELECT @TotalRecord = COUNT(*) 
FROM users 
SELECT * 
FROM   
(SELECT 
ROW_NUMBER() OVER (ORDER BY users.userid) AS SrNo, 
users.userid, 
users.username, 
users.employeeid, 
users.emailid, 
users.gender, 
country.countryname 
FROM 
users) AS Tab 
WHERE  
Tab.srno BETWEEN ((@PageNo - 1) * @NoOfRecord) + 1 AND (@PageNo * @NoOfRecord) 
ORDER BY 
Tab.srno ASC 
RETURN 

$(document).ready(function() {
$("#Users-data-table").DataTable({
"iDisplayLength": 10,
"processing": true,
// for show progress bar
"serverSide": true, // for process server side
"filter": false, // this is for disable filter (search box)
"orderMulti": false, // for disable multiple column at once
"aLengthMenu": false,
"ordering": false,
"info": false,
"paging": true,
"info": false,
"lengthChange": false,
"ajax": {
"url": "/Dashboard/LoadData",
"type": "POST",
"datatype": "json"
},
"columns": [{
"data": "SrNo",
"name": "Sr. No.",
"autoWidth": true
}, {
"data": "EmployeeId",
"name": "Employee ID",
"autoWidth": true
}, {
"data": "UserName",
"name": "User Name",
"autoWidth": true
}, {
"data": "Gender",
"name": "Gender",
"autoWidth": true
}, {
"data": "CountryName",
"name": "Country Name",
"autoWidth": true
}, {
"data": "UserId",
"render": function(data) {
return "<a class='editUser' href='@Url.Action("Edit","Dashboard")?id=" + data + "')><i class='fa fa-pencil'></i> Edit</a><a href='@Url.Action("Delete","Dashboard")?id=" + data + "'class='deleteUser')><i class='fa fa-trash'></i> Delete</a>";
}
},],
});
});

我不知道如何从存储过程中使用 ASP.NET MVC 中的页码、总记录数和记录数。如何将此存储过程与此数据表一起使用以进行分页？

Answer 1

将此模型添加到解决方案资源管理器。

public class JQueryDataTableParamModel
{
/// <summary>
/// Request sequence number sent by DataTable, same value must be returned in response
/// </summary>       
public string sEcho { get; set; }
/// <summary>
/// Text used for filtering
/// </summary>
public string sSearch { get; set; }
/// <summary>
/// Number of records that should be shown in table
/// </summary>
public int iDisplayLength { get; set; }
/// <summary>
/// First record that should be shown(used for paging)
/// </summary>
public int iDisplayStart { get; set; }
/// <summary>
/// Number of columns in table
/// </summary>
public int iColumns { get; set; }
/// <summary>
/// Number of columns that are used in sorting
/// </summary>
public int iSortingCols { get; set; }
/// <summary>
/// Comma separated list of column names
/// </summary>
public string sColumns { get; set; }
//public int iPage { get; set; }
}
}

给定下面的代码用于控制器，这是总数据表功能的代码，包括搜索，排序，分页等。

[HttpPost]
public ActionResult LoadData(JQueryDataTableParamModel param)
{
ActionResult response = null;
try
{
var verifyList = _repository.GetList();
var displayRecordCount = 0; //for passing displayed records count(if search is true pass the filtered record count, else pass the entire list count
IEnumerable<Sp_getdata_Result> filteredVerifyList = null; //used to hold the filtered data of taskList
if (!string.IsNullOrEmpty(param.sSearch))
{
string searchText = param.sSearch.ToLower();
filteredVerifyList = _repository.GetList()
.Where(c => ((c.username!= null) && c.username.ToLower().Contains(searchText) ||
(c.countryname != null) && c.countryname .ToLower().Contains(searchText)));

if (filteredVerifyList.Count() > 0)
{
displayRecordCount = filteredVerifyList.Count();
}
else
{
displayRecordCount = 0;
}
}
else
{
filteredVerifyList = verifyList;
displayRecordCount = verifyList.Count();
}

int sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]); //Gets the SortColumnIndex from the DataTable on User Interaction
Func<Sp_getdata_Result, string> orderingFunction = (c => sortColumnIndex == 2 ? Convert.ToString(c.StudentId) :
              Convert.ToString(c.FirstName)
              ); //Sorts the List based on supplied sortColumn index
string sortDirection = Request["sSortDir_0"]; // asc or desc
if (sortDirection == "asc")
filteredVerifyList = filteredVerifyList.OrderBy(orderingFunction); //Sort the List in ascending Order and Re assigns to the List
else
filteredVerifyList = filteredVerifyList.OrderByDescending(orderingFunction); //Sort the List in descending Order and Re assigns to the List
if (string.IsNullOrEmpty(param.sSearch)) //If search is null then display the records as seleted in 'records per page' DropDown
{
if (param.iDisplayLength != -1)
{
filteredVerifyList = filteredVerifyList.Skip(param.iDisplayStart)//skip to start Record in the List which will be supplied as param.iDisplayStart
.Take(param.iDisplayLength); //Gets up to param.iDisplayLength from param.iDisplayStart record     
}
}              
var displayedTaskList = filteredVerifyList; // filteredVerifyList;
var result = from Alert in displayedTaskList
select new[] {
Convert.ToString(Alert.userid),
Convert.ToString(Alert.username),
Convert.ToString(Alert.employeeid),
Convert.ToString(Alert.emailid),
Convert.ToString(Alert.gender),
Convert.ToString(Alert.countrynam)
};
response = Json(new
{
result= "Success",
sEcho = param.sEcho,
iTotalRecords = verifyList.Count(),
iTotalDisplayRecords = displayRecordCount,
aaData = result
}, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
throw ex;
}
return response;
}

在这里，我需要澄清两件事。
1._repository什么都不是，但如果你使用Data Repository Pattern，那就是它的参考。如果您没有使用存储库模式，则需要插入表引用，并且必须使用该引用调用表的每个字段参数。

2. GetList()是我的存储库方法。因为，我不知道你的存储库方法名称，我只是放置了我的。代替我的，你可以放置你的。

你的jquery脚本一切都很好，但需要再添加几行：

"aLengthMenu": [
[5, 10, 25, 50, -1],
[5, 10, 25, 50, "All"]
],
"bAutoWidth": true,
"bScrollCollapse": true,
"iDisplayLength": 5,
"pagingType": "simple_numbers",
"sDom": 'fl<t>ip',

"bJQueryUI": false,
"sPaginationType": "bootstrap",
"oLanguage": {
"sLengthMenu": "_MENU_ records per page",
"oPaginate": {
"sPrevious": "Prev",
"sNext": "Next"
},
},
"bServerSide": true,
"sAjaxSource": "LoadData",
"bProcessing": true,