Introduction
Most of cases we must develop many pages in project with different module/entity. Personally, experience after long time engage with financial /non-financial/corporate industries. There are several pages where we need to use data table/grid. Each page has similarities except columns/table header and respective rows/records.Using following architecture only require supplying raw native sql and it will show grid with data.
Following architecture will able to handle dynamic columns and respective rows. So, I have tried to make dynamic Jquery Datatable which populate grid basis on return json result.
Requirment
1. CLIENT SIDE: jQuery DataTables should able to display dynamic column and respective records.
2. SERVER SIDE: Entity Framework (Code First Approach) should able to execute Raw Sql Query.
Procedure/Steps
Steps : 1. Install Entity Framework using NuGet command Install-Package EntityFramework
Steps : 2. Set Connection string for your database in Web.Config
Steps : 3. Enable Migration for Code First Approach
I have taken multiple tables for generate separate sql native/raw sql query.
ScientistData and InventionData are Entity/tables for sample illustration
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; }
}
Prepare for context for database table migration
public class QueryContext : DbContext
{
public QueryContext() : base("name=QueryManager") { }
public DbSet<ScientistData> ScientistData { get; set; }
public DbSet<InventionData> InventionData { get; set; }
}
Trigger NuGet Command for Enable Migration as well Table creation
I have taken multiple tables for generate separate sql native/raw sql query.
- Enable-Migrations
- Add-Migration IntialCreate
- Update-Database
Finally, it creates database as well tables.
Steps : 4. Create custom extension method which supports to execute raw native sql query.
(Source : https://stackoverflow.com/questions/26749429/anonymous-type-result-from-sql-query-execution-entityframework/26749784#)
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"];
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_";
FieldBuilder fieldBuilder = builder.DefineField(
string.Concat(PrivateFieldPrefix, propertyName),
propertyType, FieldAttributes.Private);
PropertyBuilder propertyBuilder = builder.DefineProperty(
propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);
MethodAttributes propertyMethodAttributes =
MethodAttributes.Public | MethodAttributes.SpecialName |
MethodAttributes.HideBySig;
MethodBuilder getterMethod = builder.DefineMethod(
string.Concat(GetterPrefix, propertyName),
propertyMethodAttributes, propertyType, Type.EmptyTypes);
ILGenerator getterILCode = getterMethod.GetILGenerator();
getterILCode.Emit(OpCodes.Ldarg_0);
getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
getterILCode.Emit(OpCodes.Ret);
MethodBuilder setterMethod = builder.DefineMethod(
string.Concat(SetterPrefix, propertyName),
propertyMethodAttributes, null, new Type[] { propertyType });
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);
}
}
Steps : 5. Prepare helper method which will returns results from database operations.
Following sql query which returns all columns values as followings:
public object ExecuteRawSql(string Query)
{
return this.Database.DynamicSqlQuery(Query);
}
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 Custom extension methods which return type is object which is not support current entity framework.
Step 6: Setup jQuery Datatable as following basics structure :
Note: It is highly recommended to handle table header based on json result after ajax response.
var GenerateQuery = function () {
$("#resultContainer").css("display", "none");
var Payload = { 'Type': $("#Type").val(), '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");
if ($.fn.DataTable.isDataTable('#example')) {
$('#example').DataTable().destroy();
$('#example').empty();
}
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',
});
}
}
});
}
Need to call ajax request with supplied dynamic raw sql query and server will response data/result accordingly.
- Prepare table header basis on json result of ajax response.
- Destory or un initialize the jquery datatable if already initialized table.
- Initialize jquery datatable with ajax response
Result/Output: You can write Raw Sql Query and Execute it!
Points of Interest
- Main challenges, I have found while binding dynamic json result to jquery datatable, need to create table header using script.
- There is no dynamic return type supporting in Entity Framework (Version 6.0.0.0) right now, so need to build dynamic executable custome extention method.
Submit an article or tip
