How to create table dynamically with dynamic column names & rows too.

taj

controller -

        public JsonResult GetTableSchema(string Tablename)
        {
            // List<TABLESCHEMADATAMODEL> objTableSchemaDm = new MasterBL().GetTableSchema(Tablename);
            //List<MasterConfiguration.TableSchema> objTableVm = new MasterConverter().ConvertTableSchema(objTableSchemaDm);
            DataTable objTableSchemaDm = new MasterBL().GetTableSchema(Tablename);

            string JSONString = string.Empty;
            JSONString = JsonConvert.SerializeObject(objTableSchemaDm);

            return Json(JSONString);
        }

Ajax Call -

    $("#ddltable").change(function () {
            debugger;
            var Tablenameselect = $("#ddltable").find("option:selected").text();
            var columns = [];
            //alert(Tablenameselect);
            $.ajax({
            type: "POST",
                url: "@Url.Action("GetTableSchema", "Master")",
                data: { Tablename: Tablenameselect },
                dataType: "json",

                success: function (data) {
                    debugger;

                    console.log(data);
                    columnNames = Object.keys(data[0]);
                    console.log(columnNames);
                    for (var i in columnNames) {
                        columns.push({
                            data: columnNames[i],
                            title: columnNames[i]
                        });
                    }

                    $("#divGridSales").css({ display: "block" });
                    $('#tblViewPartDetails').DataTable({
                        "processing": true, // for show progress bar
                        "serverSide": false, // for process server side
                        "filter": true, // this is for disable filter (search box)
                        "orderMulti": false, // for disable multiple column at once
                        "bDestroy": true,   //for reinitialize the datatable
                        "data": data,
                        "columns": columns,
                        "responsive": true
                    });
                    },
                error: function (data) {
                    alert("Error while fetching Data");
                }
            });

        });

e.g. from API code as below

        public DataTable GetTableSchema(string pTablename)
        {
            try
            {
                DataSet dsTable = _sql.ExecuteDataSet(null, "Select * from " + Tablename).ResultSet;

                dsTable.Tables[0].TableName = "TableSchema";
                return dsTable.Tables["TableSchema"];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

**here table name will be any table it is not fixed, so as per table schema Datatable has to create dynamic table with column names & rows.
**

Note: Here by using above code I am getting blank Datatable

kthorngren

Here by using above code I am getting blank Datatable

Are you getting a properly formatted Datatable with the search and paging elements and the dynamic columns are built properly?

Do you get errors in the browser's console?

What are the results of console.log(data); on line 15? Is there data and is it an array of objects?

Please provide a link to your page or a test case showing the issue so we can help debug. If you are unable to do that then provide more details of the debugging you have done and what the JSON response is.

Kevin

taj

On console.log(data); on line 15 I am getting data like below

[{"CountryID":1,"CountryCode":"AF","CountryName":"Afghanistan","ISO_CODES":"AF / AFG","STR2":"AF","STR3":"AFG"},{"CountryID":2,"CountryCode":"AL","CountryName":"Albania","ISO_CODES":"AL / ALB","STR2":"AL","STR3":"ALB"},{"CountryID":3,"CountryCode":"DZ","CountryName":"Algeria","ISO_CODES":"DZ / DZA","STR2":"DZ","STR3":"DZA"},{"CountryID":4,"CountryCode":"AS","CountryName":"American Samoa","ISO_CODES":"AS / ASM","STR2":"AS","STR3":"ASM"}]

On console.log(columnNames); on Line 17 I am getting data as below

["0"]
0:"0"
length:1
_proto_:Array(0)

Hi @Kevin,
Test scenario you can easily create. I will give one example
here I am taking any master data table from given DB. then by API taking data from that table for further CRUD operation.(e.g. select * from tblCountryMaster)

Imp. - main thing is that table structure/schema is not fixed i.e. main issue for binding columns & rows to Data table plugin is data coming in json format not an array of object.

kthorngren

It doesn't seem like columnNames = Object.keys(data[0]); is providing the expected result. That statement with your data example works here:
http://live.datatables.net/bejexevo/1/edit

Is your data code snippet showing the full structure of the response?

Your code snippets don't have anything obvious to indicate the problem. Live debugging of the page or a test case showing the issue is the only we we will be able to understand what is happening to help debug.

Kevin

taj

Hi @kthorngren your are right when I am assigning json string manually (hardcoded) to data then it works fine like below,

e.g. -

data = [{ "CountryID": 1, "CountryCode": "AF", "CountryName": "Afghanistan", "ISO_CODES": "AF / AFG", "STR2": "AF", "STR3": "AFG" }, { "CountryID": 2, "CountryCode": "AL", "CountryName": "Albania", "ISO_CODES": "AL / ALB", "STR2": "AL", "STR3": "ALB" }, { "CountryID": 3, "CountryCode": "DZ", "CountryName": "Algeria", "ISO_CODES": "DZ / DZA", "STR2": "DZ", "STR3": "DZA" }, { "CountryID": 4, "CountryCode": "AS", "CountryName": "American Samoa", "ISO_CODES": "AS / ASM", "STR2": "AS", "STR3": "ASM" }];

But when I am directly using data which is coming from Ajax call (line no. 12), this time I am getting blank data table.

kthorngren

There is something different with your data response (function (data)) then. Is the structure different, ie, is the row data nested in another property? Is it a sting or an object?

Kevin

taj

Hi @kthorngren now working fine with following code

 data = JSON.parse(data);

but now next issue is when I am going to change table from dropdown(line no.1) that time following error occurs

dataTables.js:240 Uncaught TypeError: Cannot read property 'style' of undefined
at Ya (dataTables.js:240)
at za (dataTables.js:198)
at f (dataTables.js:340)
at HTMLTableElement.<anonymous> (dataTables.js:341)
at Function.each (jquery-3.4.1.min.js:381)
at jQuery.fn.init.each (jquery-3.4.1.min.js:203)
at jQuery.fn.init.u [as dataTable] (dataTables.js:322)
at jQuery.fn.init.k.fn.DataTable (dataTables.js:601)
at Object.success (ConfigureTables:429)
at fire (jquery-3.4.1.min.js:3497)

and datatable shows columns of new table but data of previous table

kthorngren

Sounds similar to this thread.

Kevin

taj

Hi @kthorngren I changed my java script like below

                    $('#tblViewPartDetails').DataTable({
                        "processing": true, // for show progress bar
                        "serverSide": false, // for process server side
                        "filter": true, // this is for disable filter (search box)
                        "orderMulti": false, // for disable multiple column at once
                        "bDestroy": true,   //for reinitialize the datatable
                        "data": data,
                        "columns": columns,
                        "responsive": true,
                        "empty":true
                    });

but still same issue persists.

taj

Hi @kthorngren here I am not getting how do I give call to empty function.

taj

Hi @colin can you help me out from above issue?

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

taj

Hi @colin I have uploaded code & details here in comment but getting here

kthorngren

how do I give call to empty function

Use the $.fn.dataTable.isDataTable() to determine if you have initialized the Datatable. If you have then destroy and use jQuery empty() to empty the table. Something like this:

if ( $.fn.DataTable.isDataTable( '#tblViewPartDetails' ) ) {
    table = $('#tblViewPartDetails').DataTable();
    table.destroy();
    $('#tblViewPartDetails').empty();
}
$('#tblViewPartDetails').DataTable({ ... });  // Your init options

Kevin

taj

thanks lot @kthorngren, its working now.
please one more help - tell me how do I implement Inline CRUD operations on Datatable created as above.

kthorngren

The best option is to use the Editor. Here is an inline editing example.

Kevin

taj

@kthorngren I know this example but this is related to php & I am working with c#.
is it suitable for c#?

kthorngren

Looks like Datatables provides PHP, .NET and NodeJS server side libraries libraries. You can write your own that follows this client server protocol. I use Python so I wrote my own. You might be able to find third party C# Datatables Editor libraries. Or you can create your own client and server side solution.

Kevin