Datatable Mdata Not Reading Alias From Sql
- Download source - vi.five KB
Introduction
Personally, experience after long time engage with financial /non-financial/corporate industries. There are several pages where we need to use data tabular array/grid. Each page has similarities except columns/table header names and corresponding rows/records values.
In most cases, nosotros must develop many pages separately in a project with unlike data source Model/Entity. I mean to say, we tin easily visualize that at that place are multiple pages available along with DataTable grid and each filigree has bind with separate information source or entity in any big scale project.
Finally, we are doing various operations, e.g., Global Search (Search Text in every Column, Search Text in Individual Column, Sorting records on specific cavalcade, Pagination and Exporting data as report).
Using the following compages, nosotros can quickly demark or initialize jQuery DataTable - nosotros just require to supply raw select SQL query and it will testify filigree with information.
Following architecture will exist able to handle dynamic columns and respective rows. So, I have tried to make dynamic jQuery Datatable which populates grid ground on return json result.
Logic/Idea: How to Dynamically Bind Jquery DataTables Using Anonymous JSON Consequence ?
Dynamic binding of grid ways, I am going to populate data entity from whatever kind of select raw SQL query and then return anonymous json result. Earlier initializing of Datatable, we need to collect Northward numbers of columns or decide how many number of columns are available from json effect.
I have found 2 challenges:
- Entity framework doesn't support dynamically/anonymous object after supplying raw SQL query. Entity framework needs specific return types/model while we are going to supply raw
selectSQL query for database operations. So I have prepared a custom dynamic extension method whose task is to hands execute whatsoever rawselectSQL query without knowing its return blazon. The name of this custom function isDynamicSqlQuery()in this article. - Nosotros have json result using
JsonConvert.SerializeObject()method. It will serialize model data to Json result. This result is going to consume from client side while I am going to initialize jQueryDataTablesouth. We accept json result but don't take column details considering outcome has been prepared from dynamic object. We need all columns from these json results. I have taken only the showtime records and put in array and and then after initializing the table. (You can run across information technologyGenerateQuery()function in client side implementation).
Benefits
- Using this approach, y'all don't crave every time to create carve up model/class to demark entity outcome.
- It saves a lot of time and less line numbers of code and is easy to maintain.
- Once grid has been initialized, we can perform multiple operations easily, e.g., Global Text Searching in whatsoever columns, Search Text in Private Column, Sorting of cavalcade and Export data every bit written report.
Limitations
- It is customer side based jQuery
DataTableinitialization then it is non advisable to utilise more bulky/extensive records, otherwise it will have lots to time to initialize the grid. All records are retrieved from SQL in IIS server at a time not chunked data like pagination records.
Guideline: Step by Footstep for Integration
I take separated steps in majorly two parts as follows:
-
SERVER SIDE: Entity Framework (Code First Approach) should able to execute Raw SQL Query. I accept prepared an extension method and called information technology as
DynamicQueryBuilder(). Information technology will help to return anonymous information from SQL Server by supplying raw SQL query. I have passed entity/data model toJsonConvert.SerializeObject()which helps to serialize json formatted consequence prepare. - CLIENT SIDE: jQuery
DataTablesouth should able to brandish dynamic column and corresponding records. Clients(Browser) request for json result from server. Decide N number of columns once the event has been received from Ajax request. I have prepared dynamically HTML table's columns header using jQuery.
Procedure/Steps
I have used a pictorial representation as well as self explanatory lawmaking which will help us and is like shooting fish in a barrel to sympathize architecture of this approach.
Step 1: Install Entity Framework using NuGet command Install-Package EntityFramework
)
Step 2: Prepare Connexion String for Your Database in Spider web.Config
Step 3: Enable Migration for Code Outset Approach
I have taken multiple tables for generating split up SQL native/raw SQL query.
ScientistData and InventionData are Entity/tables for sample illustration.
public class ScientistData { public int Id { get; set; } public cord Proper name { get; set; } public cord Duration { go; set; } public cord Clarification { become; set; } } public grade InventionData { public int Id { get; set; } public string Inventor { get; set; } public string Invention { get; ready; } }
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 { become; ready; } }
Trigger NuGet Command for Enable Migration every bit Well as Table Creation
I have taken multiple tables for generating a separate SQL native/raw SQL query.
- Enable-Migrations
- Add-Migration InitialCreate
- Update-Database
Finally, information technology creates database tables.
Step 4: Create a Custom Extension Method which Supports to Execute Raw Native SQL Query
public static grade DynamicQueryBuilder { public static IEnumerable DynamicSqlQuery (this Database database, string sql, params object[] parameters) { TypeBuilder builder = createTypeBuilder( " MyDynamicAssembly", " MyDynamicModule", " MyDynamicType"); using (Organization.Data.IDbCommand command = database.Connexion.CreateCommand()) { try { database.Connection.Open(); command.CommandText = sql; command.CommandTimeout = command.Connectedness.ConnectionTimeout; foreach (var param in parameters) { control.Parameters.Add together(param); } using (System.Information.IDataReader reader = command.ExecuteReader()) { var schema = reader.GetSchemaTable(); foreach (System.Information.DataRow row in schema.Rows) { cord proper noun = (string)row[" ColumnName"]; Type type = (Type)row[" DataType"]; if (type != typeof(string) && (bool)row.ItemArray [schema.Columns.IndexOf(" AllowDbNull")]) { blazon = typeof(Nullable<>).MakeGenericType(type); } createAutoImplementedProperty(architect, name, blazon); } } } finally { database.Connectedness.Close(); command.Parameters.Clear(); } } Blazon resultType = architect.CreateType(); return database.SqlQuery(resultType, sql, parameters); } private static TypeBuilder createTypeBuilder( cord assemblyName, cord moduleName, string typeName) { TypeBuilder typeBuilder = AppDomain .CurrentDomain .DefineDynamicAssembly(new AssemblyName(assemblyName), AssemblyBuilderAccess.Run) .DefineDynamicModule(moduleName) .DefineType(typeName, TypeAttributes.Public); typeBuilder.DefineDefaultConstructor(MethodAttributes.Public); render typeBuilder; } private static void createAutoImplementedProperty( TypeBuilder builder, string propertyName, Type propertyType) { const string PrivateFieldPrefix = " m_"; const cord 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( cord.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, zippo, new Blazon[] { 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); } }
Pace 5: Prepare Helper Method which volition Return Results from Database Operations
Following SQL query returns all columns values equally follows:
public object ExecuteRawSql(string Query) { return this.Database.DynamicSqlQuery(Query); } public List<cord> 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 method whose render type is object which does not back up current entity framework.
Step 6: Setup jQuery Datatable as Following Bones Construction
Note: It is highly recommended to handle tabular array header based on json outcome later on Ajax response.
var GenerateQuery = function () { $(" #resultContainer").css(" display", " none"); var Payload = { ' Query': $(" #Query").val() }; $.ajax({ url: ' /QueryManager/GenerateResult', blazon: ' POST', information: Payload, success: function (response) { if (response.condition != true) { panel.log(" Exception", response); alert(' There is some fault has occured!. Please see in console for details of error'); } else { $(" #resultContainer").css(" display", " block"); if ($.fn.DataTable.isDataTable(' #example')) { $(' #instance').DataTable().destroy(); $(' #example').empty(); } var Columns = []; var TableHeader = " <thead><tr>"; $.each(response.result[0], function (cardinal, value) { Columns.push button({ " data": key }) TableHeader += " <th>" + key + " </th>" }); TableHeader += " </thead></tr>"; $(" #example").suspend(TableHeader); $(' #instance').dataTable({ " oLanguage": { " sLengthMenu": " _MENU_ " }, " data": response.result, " columns": Columns, " JQueryUI": true, dom: ' Bfrtip', dom: ' lBfrtip', }); } } }); }
Need to phone call Ajax request with supplied dynamic raw SQL query and server volition reply with data/result appropriately.
- Set table header basis on json effect of Ajax response.
- Destroy or uninitialize 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 Involvement
- The main challenge that I have constitute while binding dynamic json issue to jquery datatable is the need to create
tableheader using script. - There is no dynamic return type supporting in Entity Framework (Version vi.0.0.0) right now, so you need to build dynamic executable custom extension method.
- This kind of utility saves a lot of time while nosotros prepare or initialize Jquery Datatable, you only require to pass SQL
selectquery. It besides works if we execute Stored Procedure (Stored procedure result/issue should n-records/select like query consequence). It volition work like web based query SQL manager or editor.
Source: https://www.codeproject.com/Articles/1256759/jQuery-DataTable-Integration-Dynamic-Columns-and-R