jQuery DataTable集成 - 动态列和记录
5.00/5 (8投票s)
JQuery Datatable（动态列）在Ajax JSON响应后通过服务器端处理填充 - 使用EF Raw SQL查询
引言
我个人在金融/非金融/企业行业工作多年后的经验。有几个页面需要使用数据表格/网格。每个页面都有相似之处，除了列/表头名称以及各自的行/记录值。
在大多数情况下，我们必须在一个项目中为不同的数据源模型/实体单独开发许多页面。我的意思是，在一个大型项目中，我们可以轻松地设想有多个页面带有DataTable网格，并且每个网格都绑定到独立的数据源或实体。
最后，我们执行各种操作，例如全局搜索（搜索所有列中的文本、搜索单列中的文本、按特定列排序记录、分页和导出数据作为报表）。
使用以下架构，我们可以快速绑定或初始化 jQuery DataTable - 我们只需要提供原始的 select SQL 查询，它就会显示带数据的网格。
以下架构能够处理动态列和相应的行。因此，我尝试制作一个动态的 jQuery Datatable，它根据返回的 JSON 结果填充网格。
逻辑/思路：如何动态绑定 Jquery DataTables 使用匿名 JSON 结果？
网格的动态绑定意味着，我将从任何类型的 select 原始 SQL 查询中填充数据实体，然后返回匿名 JSON 结果。在初始化 Datatable 之前，我们需要从 JSON 结果中收集 N 个列，或者确定有多少列可用。
我遇到了两个挑战
- Entity Framework 在提供原始 SQL 查询后不支持动态/匿名对象。当我们为数据库操作提供原始
selectSQL 查询时，Entity Framework 需要特定的返回类型/模型。所以我准备了一个自定义的动态扩展方法，它的作用是轻松执行任何原始selectSQL 查询，而无需知道其返回类型。在这个文章中，这个自定义函数的名字是DynamicSqlQuery()。 - 我们使用
JsonConvert.SerializeObject()方法获得 JSON 结果。它会将模型数据序列化为 JSON 结果。当我要初始化 jQueryDataTable时，客户端将消耗这个结果。我们有 JSON 结果，但没有列详细信息，因为结果是从动态对象准备的。我们需要这些 JSON 结果中的所有列。我只取了第一条记录，放入一个数组，然后初始化表格。（您可以在客户端实现中的GenerateQuery()函数中看到它）。
优点
- 使用这种方法，您不需要每次都创建单独的模型/类来绑定实体结果。
- 这可以节省大量时间，减少代码行数，并且易于维护。
- 一旦网格初始化完成，我们就可以轻松地执行多个操作，例如在任何列中进行全局文本搜索、搜索单列中的文本、列排序以及将数据导出为报表。
限制
- 这是基于客户端的 jQuery
DataTable初始化，因此不建议使用大量/广泛的记录，否则初始化网格将花费大量时间。所有记录一次性从 IIS 服务器的 SQL 中检索，而不是像分页记录那样分块获取数据。
指南：集成步骤
我将步骤主要分为两部分如下
-
服务器端：Entity Framework (Code First Approach) 应该能够执行原始 SQL 查询。我准备了一个扩展方法并称之为
DynamicQueryBuilder()。它将通过提供原始 SQL 查询来帮助从 SQL Server 返回匿名数据。我将实体/数据模型传递给JsonConvert.SerializeObject()，这有助于序列化 JSON 格式的结果集。 - 客户端：jQuery
DataTables 应该能够显示动态列和相应的记录。客户端（浏览器）从服务器请求 JSON 结果。收到 Ajax 请求的结果后，确定 N 个列的数量。我使用 jQuery 动态准备了 HTML 表的列标题。
程序/步骤
我还使用了图示以及自解释的代码，这将帮助我们并且易于理解这种方法的架构。
步骤 1：使用 NuGet 命令 Install-Package EntityFramework 安装 Entity Framework
步骤 2：在 Web.Config 中为您的数据库设置连接字符串
步骤 3：为 Code First Approach 启用迁移
我使用了多个表来生成单独的 SQL 原生/原始 SQL 查询。
ScientistData 和 InventionData 是用于示例说明的实体/表。
public class ScientistData
{
public int Id { get; set; }
public string Name { get; set; }
public string Duration { get; set; }
public string Description { get; set; }
}
public class InventionData
{
public int Id { get; set; }
public string Inventor { get; set; }
public string Invention { get; set; }
}
准备数据库表迁移的上下文
public class QueryContext : DbContext
{
public QueryContext() : base("name=QueryManager") { }
public DbSet<ScientistData> ScientistData { get; set; }
public DbSet<InventionData> InventionData { get; set; }
}
触发 NuGet 命令以启用迁移以及创建表
我使用了多个表来生成单独的 SQL 原生/原始 SQL 查询。
- Enable-Migrations
- Add-Migration InitialCreate
- Update-Database
最后，它会创建数据库表。
步骤 4：创建一个支持执行原始原生 SQL 查询的自定义扩展方法
public static class DynamicQueryBuilder
{
public static IEnumerable DynamicSqlQuery
(this Database database, string sql, params object[] parameters)
{
TypeBuilder builder = createTypeBuilder(
"MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
using (System.Data.IDbCommand command = database.Connection.CreateCommand())
{
try
{
database.Connection.Open();
command.CommandText = sql;
command.CommandTimeout = command.Connection.ConnectionTimeout;
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
using (System.Data.IDataReader reader = command.ExecuteReader())
{
var schema = reader.GetSchemaTable();
foreach (System.Data.DataRow row in schema.Rows)
{
string name = (string)row["ColumnName"];
//var a=row.ItemArray.Select(d=>d.)
Type type = (Type)row["DataType"];
if (type != typeof(string) && (bool)row.ItemArray
[schema.Columns.IndexOf("AllowDbNull")])
{
type = typeof(Nullable<>).MakeGenericType(type);
}
createAutoImplementedProperty(builder, name, type);
}
}
}
finally
{
database.Connection.Close();
command.Parameters.Clear();
}
}
Type resultType = builder.CreateType();
return database.SqlQuery(resultType, sql, parameters);
}
private static TypeBuilder createTypeBuilder(
string assemblyName, string moduleName, string typeName)
{
TypeBuilder typeBuilder = AppDomain
.CurrentDomain
.DefineDynamicAssembly(new AssemblyName(assemblyName),
AssemblyBuilderAccess.Run)
.DefineDynamicModule(moduleName)
.DefineType(typeName, TypeAttributes.Public);
typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
return typeBuilder;
}
private static void createAutoImplementedProperty(
TypeBuilder builder, string propertyName, Type propertyType)
{
const string PrivateFieldPrefix = "m_";
const string GetterPrefix = "get_";
const string SetterPrefix = "set_";
// Generate the field.
FieldBuilder fieldBuilder = builder.DefineField(
string.Concat(PrivateFieldPrefix, propertyName),
propertyType, FieldAttributes.Private);
// Generate the property
PropertyBuilder propertyBuilder = builder.DefineProperty(
propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);
// Property getter and setter attributes.
MethodAttributes propertyMethodAttributes =
MethodAttributes.Public | MethodAttributes.SpecialName |
MethodAttributes.HideBySig;
// Define the getter method.
MethodBuilder getterMethod = builder.DefineMethod(
string.Concat(GetterPrefix, propertyName),
propertyMethodAttributes, propertyType, Type.EmptyTypes);
// Emit the IL code.
// ldarg.0
// ldfld,_field
// ret
ILGenerator getterILCode = getterMethod.GetILGenerator();
getterILCode.Emit(OpCodes.Ldarg_0);
getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
getterILCode.Emit(OpCodes.Ret);
// Define the setter method.
MethodBuilder setterMethod = builder.DefineMethod(
string.Concat(SetterPrefix, propertyName),
propertyMethodAttributes, null, new Type[] { propertyType });
// Emit the IL code.
// ldarg.0
// ldarg.1
// stfld,_field
// ret
ILGenerator setterILCode = setterMethod.GetILGenerator();
setterILCode.Emit(OpCodes.Ldarg_0);
setterILCode.Emit(OpCodes.Ldarg_1);
setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
setterILCode.Emit(OpCodes.Ret);
propertyBuilder.SetGetMethod(getterMethod);
propertyBuilder.SetSetMethod(setterMethod);
}
}
步骤 5：准备返回数据库操作结果的帮助方法
以下 SQL 查询返回所有列值，如下所示
//Essential to execute Raw Sql Query
public object ExecuteRawSql(string Query)
{
return this.Database.DynamicSqlQuery(Query);
}
//Helpful to return all Table object from Database.
//Essential to make intellisense for Query Builder
public List<string> TableObject()
{
return this.Database.SqlQuery<string>("SELECT TABLE_SCHEMA+'.'+
TABLE_NAME as TableObject FROM INFORMATION_SCHEMA.TABLES order by TABLE_NAME").ToList();
}
ExecuteRawSql 自定义扩展方法，其 return 类型为 object，当前 Entity Framework 不支持。
步骤 6：设置 jQuery Datatable 为以下基本结构
注意：强烈建议在 Ajax 响应后基于 JSON 结果处理 table 标题。
var GenerateQuery = function () {
$("#resultContainer").css("display", "none");
var Payload = { 'Query': $("#Query").val() };
$.ajax({
url: '/QueryManager/GenerateResult',
type: 'POST',
data: Payload,
success: function (response) {
if (response.status != true) {
console.log("Exception", response);
alert('There is some error has occured!.
Please see in console for details of error');
}
else {
$("#resultContainer").css("display", "block");
//Handling JQuery Datatable Dynamically...
//Check Data Table has if already initialize then need to destroy first!
if ($.fn.DataTable.isDataTable('#example')) {
$('#example').DataTable().destroy();
$('#example').empty();
}
//Listing Columns (Table Header) from json ajax response
var Columns = [];
var TableHeader = "<thead><tr>";
$.each(response.result[0], function (key, value) {
Columns.push({ "data": key })
TableHeader += "<th>" + key + "</th>"
});
TableHeader += "</thead></tr>";
$("#example").append(TableHeader);
$('#example').dataTable({
"oLanguage": {
"sLengthMenu": "_MENU_ "
},
"data": response.result,
"columns": Columns,
"JQueryUI": true,
dom: 'Bfrtip',
dom: 'lBfrtip',
});
}
}
});
}
需要使用提供的动态原始 SQL 查询调用 Ajax 请求，服务器将相应地返回数据/结果。
- 根据 Ajax 响应的 JSON 结果准备表标题。
- 销毁或取消初始化已初始化的 jquery datatable（如果已初始化）。
- 使用 Ajax 响应初始化 jquery datatable
结果/输出：您可以编写原始 SQL 查询并执行它！
关注点
- 将动态 JSON 结果绑定到 jquery datatable 时，我遇到的主要挑战是需要使用脚本创建
table标题。 - Entity Framework (Version 6.0.0.0) 目前不支持动态返回类型，因此您需要构建动态可执行的自定义扩展方法。
- 这种实用程序可以在我们准备或初始化 Jquery Datatable 时节省大量时间，您只需要传递 SQL
select查询。当执行存储过程时（存储过程结果/输出应为 n-记录/类似 select 的查询结果），它也能正常工作。它的功能就像一个基于 Web 的查询 SQL 管理器或编辑器。