ASP.NET8 使用 DataTables.net – 第9部分 – 高级筛选器
在 Asp.Net 8 MVC 应用程序中使用 jQuery DataTables.net 组件的实用指南。
1 ASP.NET8 使用 jQuery DataTables.net
我正在评估 jQuery DataTables.net 组件 [1] 在 ASP.NET8 项目中的使用，并创建了几个原型（概念验证）应用程序，这些应用程序将在这些文章中介绍。
1.1 本系列文章
本系列文章包括：
- ASP.NET8 使用 DataTables.net – 第1部分 – 基础
- ASP.NET8 使用 DataTables.net – 第2部分 – 操作按钮
- ASP.NET8 使用 DataTables.net – 第3部分 – 状态保存
- ASP.NET8 使用 DataTables.net – 第4部分 – 多语言
- ASP.NET8 使用 DataTables.net – 第5部分 – 在 AJAX 中传递附加参数
- ASP.NET8 使用 DataTables.net – 第6部分 – 在 AJAX 中返回附加参数
- ASP.NET8 使用 DataTables.net – 第7部分 – 常规按钮
- ASP.NET8 使用 DataTables.net – 第8部分 – 选择行
- ASP.NET8 使用 DataTables.net – 第9部分 – 高级筛选器
2 最终结果
本文的目标是创建一个概念验证应用程序，演示 DataTables.net 组件，并使用自定义高级筛选器。让我们展示本文的结果。
在此示例中，我们禁用了 DataTables.net 组件右上角的默认筛选器，并创建了一个单独的自定义表单高级筛选器。默认筛选器（搜索）框很简单，对所有列应用相同的值。通常，在专业使用中，需要更精细的筛选功能。
我非常仔细地查看了网站 [1] 上 DataTables.net 组件提供的所有高级筛选扩展和插件。但我不喜欢那里提供的任何东西，没有一个对我的用户来说足够专业。因此，我决定用 ASP.NET 进行一些编码，创建我自己的自定义高级筛选器，并集成 DataTables.net 组件来呈现表格。这里提供的解决方案是我最终想出的。
3 高级筛选器表单
自定义高级筛选器只是另一个 ASP.NET 表单，我决定将筛选器状态保存到 ASP.NET Session 对象中，以便在每次 AJAX 调用期间，DataTables 组件都可以访问该信息。这样，“筛选器状态”就可以在应用程序中的不同操作/表单之间共享。以下是相关代码。
//HomeController.cs ======================================
public class HomeController : Controller
{
public const string EMPLOYEES_ADVANCED_FILTER_STATE = "EMPLOYEES_ADVANCED_FILTER_STATE";
public IActionResult EmployeesAdvancedFilter(EmployeesAdvancedFilterVM model)
{
try
{
ISession ? CurrentSession=this.HttpContext?.Session;
if (model.IsSubmit)
{
//we have submit
//we save the advanced-filter state to session
if (CurrentSession != null && model != null)
{
model.FirstName=model.FirstName?.Trim();
model.LastName = model.LastName?.Trim();
model.City = model.City?.Trim();
model.Country = model.Country?.Trim();
string jsonUserEmployeesAdvancedFilterState = JsonSerializer.Serialize(model);
CurrentSession.SetString(EMPLOYEES_ADVANCED_FILTER_STATE, jsonUserEmployeesAdvancedFilterState);
}
return RedirectToAction("Employees", "Home");
}
else if (model.IsReset)
{
//we have reset
//we clear advanced-filter state in session
CurrentSession?.Remove(EMPLOYEES_ADVANCED_FILTER_STATE);
}
//go for presentation
{
//we get advanced-filter state from session if there is one
string? jsonUserEmployeesAdvancedFilterState = CurrentSession?.GetString(EMPLOYEES_ADVANCED_FILTER_STATE);
if (!string.IsNullOrEmpty(jsonUserEmployeesAdvancedFilterState))
{
model = JsonSerializer.Deserialize<EmployeesAdvancedFilterVM>(jsonUserEmployeesAdvancedFilterState)
?? new EmployeesAdvancedFilterVM();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return View(model);
}
//EmployeesAdvancedFilterVM.cs
namespace Example09.Models.Home
{
public class EmployeesAdvancedFilterVM
{
//model
public string? FirstName { get; set; } = null;
public string? LastName { get; set; } = null;
public string? City { get; set; } = null;
public string? Country { get; set; } = null;
public bool IsSubmit { get; set; } = false;
public bool IsReset { get; set; } = false;
//view model
}
}
<!-- EmployeesAdvancedFilter.cshtml -->
@using Example09.Models.Home;
@model EmployeesAdvancedFilterVM
@{
<div class="text-center">
<h3 class="display-4">Employees Advanced Filter</h3>
</div>
<fieldset class="border rounded-3 p-3" style="width:600px">
<legend class="float-none w-auto px-3">Choose filter parameters</legend>
<p class="bg-light m-1 border p-1">
* = whildchar, zero or more characters <br/>
? = whildchar, one character
</p>
<form id="form1" method="post">
<div class="form-group">
<label asp-for=FirstName>Given Name</label>
<input class="form-control" asp-for="FirstName" />
</div>
<div class="form-group">
<label asp-for=LastName>Family Name</label>
<input class="form-control" asp-for="LastName" />
</div>
<div class="form-group">
<label asp-for=City>Town</label>
<input class="form-control" asp-for="City" />
</div>
<div class="form-group">
<label asp-for=Country></label>
<input class="form-control" asp-for="Country" />
</div>
<button type="submit" form="form1" class="btn btn-primary mt-3 ms-3 float-end" asp-area=""
asp-controller="Home" asp-action="EmployeesAdvancedFilter" asp-route-IsSubmit="true">
Submit
</button>
<a class="btn btn-primary mt-3 float-end" asp-area=""
asp-controller="Home" asp-action="EmployeesAdvancedFilter" asp-route-IsReset="true">
Reset
</a>
</form>
</fieldset>
}
4 客户端 DataTables.net 组件
在这里，我将只展示使用 DataTables 组件的 ASP.NET 视图的样子。
<!-- Employees.cshtml -->
@using Example09.Models.Home;
@model EmployeesVM
<partial name="_LoadingDatatablesJsAndCss" />
@{
<div class="text-center">
<h3 class="display-4">Employees table - Advanced Filter</h3>
</div>
<h4 class="bg-info m-4 border p-3">
@{
string text1 = "Filter= " + Model?.AdvancedFilterState;
@text1
}
</h4>
<!-- Here is our table HTML element defined. JavaScript library Datatables
will do all the magic to turn it into interactive component -->
<table id="EmployeesTable01" class="table table-striped table-bordered ">
</table>
}
<script type="text/javascript">
// Datatables script initialization =========================================
// we used defer attribute on jQuery so it might not be available at this point
// so we go for vanilla JS event
document.addEventListener("DOMContentLoaded", InitializeDatatable);
function InitializeDatatable() {
$("#EmployeesTable01").dataTable(
//providing initialization parameters as JavaScript object
{
//processing-Feature control the processing indicator.
processing: true,
//paging-Enable or disable table pagination.
paging: true,
//info-Feature control table information display field
info: true,
//ordering-Feature control ordering (sorting) abilities in DataTables.
ordering: true,
//searching-Feature control search (filtering) abilities
searching: false,
//autoWidth-Feature control DataTables' smart column width handling.
autoWidth: true,
//lengthMenu-Change the options in the page length select list.
lengthMenu: [10, 15, 25, 50, 100],
//pageLength-Change the initial page length (number of rows per page)
pageLength: 10,
//order-Initial order (sort) to apply to the table.
order: [[1, 'asc']],
//serverSide-Feature control DataTables' server-side processing mode.
serverSide: true,
//Load data for the table's content from an Ajax source.
ajax: {
"url": "@Url.Action("EmployeesDT", "Home")",
"type": "POST",
"datatype": "json"
},
//Set column specific initialisation properties.
columns: [
//name-Set a descriptive name for a column
//data-Set the data source for the column from the rows data object / array
//title-Set the column title
//orderable-Enable or disable ordering on this column
//searchable-Enable or disable search on the data in this column
//type-Set the column type - used for filtering and sorting string processing
//visible-Enable or disable the display of this column.
//width-Column width assignment.
//render-Render (process) the data for use in the table.
//className-Class to assign to each cell in the column.
{ //0
name: 'id',
data: 'id',
title: "Employee Id",
orderable: true,
searchable: false,
type: 'num',
visible: false
},
{
//1
name: 'givenName',
data: "givenName",
title: "Given Name",
orderable: true,
searchable: true,
type: 'string',
visible: true
},
{
//2
name: 'familyName',
data: "familyName",
title: "Family Name",
orderable: true,
searchable: true,
type: 'string',
visible: true
},
{
//3
name: 'town',
data: "town",
title: "Town",
orderable: true,
searchable: true,
type: 'string',
visible: true
},
{
//4
name: 'country',
data: "country",
title: "Country",
orderable: true,
searchable: true,
type: 'string',
visible: true,
width: "150px",
className: 'text-center '
},
{
//5
name: 'email',
data: "email",
title: "Email",
orderable: true,
searchable: true,
type: 'string',
visible: true
},
{
//6
name: 'phoneNo',
data: "phoneNo",
title: "Phone Number",
orderable: false,
searchable: true,
type: 'string',
visible: true
}
],
layout: {
top1Start: {
buttons:
[
{
text: 'Filter',
action: AdvancedFilter
}
]
}
}
} // end of initialization object
);
function AdvancedFilter(e, dt, node, config) {
let EmployeesAdvancedFilter = "@Url.Action("EmployeesAdvancedFilter", "Home")";
//redirect to another page
window.location.replace(EmployeesAdvancedFilter);
};
}
</script>
有关 JavaScript 属性的更多信息，请参见 [1] 的手册。这里的应用程序只是 ASP.NET 环境的概念验证。
5 ASP.NET 后端处理
现在我们进入 C#/.NET 部分，编写我们的 ASP.NET 代码。请注意，在 action Employees() 和方法 FilterRowsPerSavedAdvancedFilterState() 中，我们从 ASP.NET Session 对象检索筛选器状态。这里的关键在于，在每次 AJAX 调用期间，我们从 ASP.NET Session 对象获取筛选器状态，并将筛选器内容应用于表格的后端处理。
////EmployeesVM.cs
namespace Example09.Models.Home
{
public class EmployeesVM
{
//view model
public String? AdvancedFilterState { get; set; } = null;
}
}
//HomeController.cs ======================================
namespace Example09.Controllers
{
public class HomeController : Controller
{
public const string EMPLOYEES_ADVANCED_FILTER_STATE = "EMPLOYEES_ADVANCED_FILTER_STATE";
public IActionResult Employees(EmployeesVM model)
{
try
{
ISession? CurrentSession = this.HttpContext?.Session;
{
//we get advanced-filter state from session if there is one
string? jsonUserEmployeesAdvancedFilterState = CurrentSession?.GetString(EMPLOYEES_ADVANCED_FILTER_STATE);
if (!string.IsNullOrEmpty(jsonUserEmployeesAdvancedFilterState))
{
EmployeesAdvancedFilterVM? AdvancedFilterState =
JsonSerializer.Deserialize<EmployeesAdvancedFilterVM>(jsonUserEmployeesAdvancedFilterState);
if (AdvancedFilterState != null)
{
string filterState =
"Given Name: " + AdvancedFilterState.FirstName +
"; Family Name: " + AdvancedFilterState.LastName +
"; Town: " + AdvancedFilterState.City+
"; Country: " + AdvancedFilterState.Country ;
model.AdvancedFilterState = filterState;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return View(model);
}
//this is target of AJAX call and provides data for
//the table, based on selected input parameters
public IActionResult EmployeesDT(DataTables.AspNet.Core.IDataTablesRequest request)
{
// There is dependency in this method on names of fields
// and implied mapping. I see it almost impossible to avoid.
// At least, in this method, we avoided dependency on the order
// of table fields, in case order needs to be changed
//Here are our mapped table columns:
//Column0 id -> Employee.Id
//Column1 givenName -> Employee.FirstName
//Column2 familyName -> Employee.LastName
//Column3 town -> Employee.City
//Column4 country -> Employee.Country
//Column5 email -> Employee.Email
//Column6 phoneNo -> Employee.Phone
try
{
IQueryable<Employee> employees = MockDatabase.MockDatabase.Instance.EmployeesTable.AsQueryable();
//here we get the count that needs to be presented by the UI
int totalRecordsCount = employees.Count();
employees = FilterRowsPerSavedAdvancedFilterState(employees);
var iQueryableOfAnonymous = employees.Select(p => new
{
id = p.Id,
givenName = p.FirstName,
familyName = p.LastName,
town = p.City,
country = p.Country,
email = p.Email,
phoneNo = p.Phone,
});
//here we get the count that needs to be presented by the UI
int filteredRecordsCount = iQueryableOfAnonymous.Count();
iQueryableOfAnonymous = SortRowsPerRequestParamters(iQueryableOfAnonymous, request);
iQueryableOfAnonymous = iQueryableOfAnonymous.Skip(request.Start).Take(request.Length);
//here we materialize the query
var dataPage = iQueryableOfAnonymous.ToList();
var response = DataTablesResponse.Create(request, totalRecordsCount, filteredRecordsCount, dataPage);
return new DataTablesJsonResult(response, false);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
var response = DataTablesResponse.Create(request, "Error processing AJAX call on server side");
return new DataTablesJsonResult(response, false);
}
}
private IQueryable<Example09.MockDatabase.Employee> FilterRowsPerSavedAdvancedFilterState(
IQueryable<Example09.MockDatabase.Employee> iQueryableOfEmployee)
{
try
{
ISession? CurrentSession = this.HttpContext?.Session;
{
//we get advanced-filter state from session if there is one
string? jsonUserEmployeesAdvancedFilterState = CurrentSession?.GetString(EMPLOYEES_ADVANCED_FILTER_STATE);
if (!string.IsNullOrEmpty(jsonUserEmployeesAdvancedFilterState))
{
EmployeesAdvancedFilterVM? advancedFilter =
JsonSerializer.Deserialize<EmployeesAdvancedFilterVM>(jsonUserEmployeesAdvancedFilterState);
if (advancedFilter != null)
{
//FirstName
advancedFilter.FirstName = advancedFilter.FirstName?.Trim();
if (!string.IsNullOrEmpty(advancedFilter.FirstName))
{
/*
* In EF we would go for DbFunctions.Like
if (advancedFilter.FirstName.Contains('*') || advancedFilter.FirstName.Contains('?'))
{
string pattern = advancedFilter.FirstName.Replace('*', '%').Replace('?', '_');
iQueryableOfEmployee = iQueryableOfEmployee.Where(vk => DbFunctions.Like(vk.FirstName, pattern));
}
else
{
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.FirstName != null && vk.FirstName.Equals(advancedFilter.FirstName));
}
*/
//in pure Linq going for Regex
if (advancedFilter.FirstName.Contains('*') || advancedFilter.FirstName.Contains('?'))
{
string pattern = advancedFilter.FirstName.Replace("*", ".*").Replace("?", ".{1}");
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.FirstName != null && Regex.IsMatch(vk.FirstName, pattern));
}
else
{
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.FirstName != null && vk.FirstName.Equals(advancedFilter.FirstName));
}
}
//LastName
advancedFilter.LastName = advancedFilter.LastName?.Trim();
if (!string.IsNullOrEmpty(advancedFilter.LastName))
{
//in pure Linq going for Regex
if (advancedFilter.LastName.Contains('*') || advancedFilter.LastName.Contains('?'))
{
string pattern = advancedFilter.LastName.Replace("*", ".*").Replace("?", ".{1}");
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.LastName != null && Regex.IsMatch(vk.LastName, pattern));
}
else
{
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.LastName != null && vk.LastName.Equals(advancedFilter.LastName));
}
}
//City
advancedFilter.City = advancedFilter.City?.Trim();
if (!string.IsNullOrEmpty(advancedFilter.City))
{
//in pure Linq going for Regex
if (advancedFilter.City.Contains('*') || advancedFilter.City.Contains('?'))
{
string pattern = advancedFilter.City.Replace("*", ".*").Replace("?", ".{1}");
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.City != null && Regex.IsMatch(vk.City, pattern));
}
else
{
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.City != null && vk.City.Equals(advancedFilter.City));
}
}
//Country
advancedFilter.Country = advancedFilter.Country?.Trim();
if (!string.IsNullOrEmpty(advancedFilter.Country))
{
//in pure Linq going for Regex
if (advancedFilter.Country.Contains('*') || advancedFilter.Country.Contains('?'))
{
string pattern = advancedFilter.Country.Replace("*", ".*").Replace("?", ".{1}");
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.Country != null && Regex.IsMatch(vk.Country, pattern));
}
else
{
iQueryableOfEmployee = iQueryableOfEmployee.Where(
vk => vk.Country != null && vk.Country.Equals(advancedFilter.Country));
}
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return iQueryableOfEmployee;
}
6 结论
可以下载完整的示例代码项目。
7 参考文献
[1] https://datatables.net.cn/
[21] ASP.NET8 使用 DataTables.net – 第1部分 – 基础
https://codeproject.org.cn/Articles/5385033/ASP-NET-8-Using-DataTables-net-Part1-Foundation
[22] ASP.NET8 使用 DataTables.net – 第2部分 – 操作按钮
https://codeproject.org.cn/Articles/5385098/ASP-NET8-using-DataTables-net-Part2-Action-buttons
[23] ASP.NET8 使用 DataTables.net – 第3部分 – 状态保存
https://codeproject.org.cn/Articles/5385308/ASP-NET8-using-DataTables-net-Part3-State-saving
[24] ASP.NET8 使用 DataTables.net – 第4部分 – 多语言
https://codeproject.org.cn/Articles/5385407/ASP-NET8-using-DataTables-net-Part4-Multilingual
[25] ASP.NET8 使用 DataTables.net – 第5部分 – 在 AJAX 中传递附加参数
https://codeproject.org.cn/Articles/5385575/ASP-NET8-using-DataTables-net-Part5-Passing-additi
[26] ASP.NET8 使用 DataTables.net – 第6部分 – 在 AJAX 中返回附加参数
https://codeproject.org.cn/Articles/5385692/ASP-NET8-using-DataTables-net-Part6-Returning-addi
[27] ASP.NET8 使用 DataTables.net – 第7部分 – 常规按钮
https://codeproject.org.cn/Articles/5385828/ASP-NET8-using-DataTables-net-Part7-Buttons-regula
[28] ASP.NET8 使用 DataTables.net – 第8部分 – 选择行
https://codeproject.org.cn/Articles/5386103/ASP-NET8-using-DataTables-net-Part8-Select-rows
[29] ASP.NET8 使用 DataTables.net – 第9部分 – 高级筛选器
https://codeproject.org.cn/Articles/5386263/ASP-NET8-using-DataTables-net-Part9-Advanced-Filte