I am new in ASP.NET Core, working on a ASP.NET Core project and I need to use datatable that receives data from sql server. I have no problem in cases where there is no child rows, but I must show datatable with multiple child rows.I can't show datatable with child rows. there are many examples with ajax but I could not find any example that is data from sql server in asp core.

If we talk briefly about the database structure, there are 2 Tables: Order and OrderList.

Order:OrderId(PK-int),Customer(string),OrderDate(datetime)

OrderList:KimlikId(PK-int),OrderId(int),Product(string),Color(string),Qntty(int)

Order INNER JOIN OrderList ON Order.OrderId = OrderList.OrderId

My Model class OrderList like that:

public class OrderList
{
    public int OrderId { get; set; }
    public int KimlikId { get; set; }
    public string Customer { get; set; }
    public string OrderDate { get; set; }
    public string Product { get; set; }
    public string Color { get; set; }
    public int Qntty { get; set; }
}

My Controller class OrderController like that:

    public class OrderController : Controller
{

    public IEnumerable<OrderList> GetAllOrderList()
    {
        string connectionString = "My Connection String of sql server";
        List<OrderList> sipList = new List<OrderList>();
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Order INNER JOIN OrderList ON Order.OrderId = OrderList.OrderId ORDER BY OrderList.OrderId DESC;", con);

            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                OrderList sip = new OrderList();
                sip.OrderId = Convert.ToInt32(dr["OrderId"].ToString());
                sip.Customer = dr["Customer"].ToString();
                sip.OrderDate = DateTime.Parse(dr["OrderDate"].ToString()).ToString("dd/MM/yyyy");
                sip.Product = dr["Product"].ToString();
                sip.Color = dr["Color"].ToString();
                sip.Qntty = Int32.Parse(dr["Qntty"].ToString());

                sipList.Add(sip);
            }
            con.Close();
        }

        return sipList;
    }

    public IActionResult OrderIndex()
    {
        List<OrderList> sipList = new List<OrderList>();
        sipList = GetAllOrderList().ToList();

        return View(sipList);
    }
}

My View is OrderIndex.cshtml like that:

@model IEnumerable<AlplerCRM.Models.OrderList>
@{
ViewData["Title"] = "Ordesr";
Layout = "~/Views/Shared/_AnaLayout.cshtml";
}
    <table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th></th>
            <th>OrderId</th>
            <th>Customer</th>
            <th>OrderDate</th>
        </tr>
    </thead>
</table>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}


<script>
    function format(d) {
        return '<table id="childtable" cellpadding="5" cellspacing="0" border="0" style="padding-left: 50px; ">' +
            '<tr>' +
            '<td>Kimlik No</td>' +
            '<td>Product Detail</td>' +
            '<td>Product Color</td>' +
            '<td>Product Quantity</td>' +
            '</tr><tr>' +
            '<td>' + d.KimlikId + '</td>' +
            '<td>' + d.Product + '</td>' +
            '<td>' + d.Color + '</td>' +
            '<td>' + d.Qntty + '</td>' +
            '</tr>' +
            '</table>';
    }
    $(document).ready(function () {
        var table = $("#example").dataTable({
            "columns": [
                {
                    "className": 'details-control',
                    "orderable": false,
                    "data": null,
                    "defaultContent": ''
                },
                { "data": "OrderId" },
                { "data": "Customer" },
                { "data": "OrderDate" },
            ],
            "order": [[0, 'desc']]
        });
    });
    $('#example tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass('shown');
        }
    });

</script>
}

How can I get data without ajax and show datatable like that:

I am new in ASP.NET Core, working on a ASP.NET Core project and I need to use datatable that receives data from sql server. I have no problem in cases where there is no child rows, but I must show datatable with multiple child rows.I can't show datatable with child rows. there are many examples with ajax but I could not find any example that is data from sql server in asp core.

If we talk briefly about the database structure, there are 2 Tables: Order and OrderList.

Order:OrderId(PK-int),Customer(string),OrderDate(datetime)

OrderList:KimlikId(PK-int),OrderId(int),Product(string),Color(string),Qntty(int)

Order INNER JOIN OrderList ON Order.OrderId = OrderList.OrderId

My Model class OrderList like that:

public class OrderList
{
    public int OrderId { get; set; }
    public int KimlikId { get; set; }
    public string Customer { get; set; }
    public string OrderDate { get; set; }
    public string Product { get; set; }
    public string Color { get; set; }
    public int Qntty { get; set; }
}

My Controller class OrderController like that:

    public class OrderController : Controller
{

    public IEnumerable<OrderList> GetAllOrderList()
    {
        string connectionString = "My Connection String of sql server";
        List<OrderList> sipList = new List<OrderList>();
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Order INNER JOIN OrderList ON Order.OrderId = OrderList.OrderId ORDER BY OrderList.OrderId DESC;", con);

            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                OrderList sip = new OrderList();
                sip.OrderId = Convert.ToInt32(dr["OrderId"].ToString());
                sip.Customer = dr["Customer"].ToString();
                sip.OrderDate = DateTime.Parse(dr["OrderDate"].ToString()).ToString("dd/MM/yyyy");
                sip.Product = dr["Product"].ToString();
                sip.Color = dr["Color"].ToString();
                sip.Qntty = Int32.Parse(dr["Qntty"].ToString());

                sipList.Add(sip);
            }
            con.Close();
        }

        return sipList;
    }

    public IActionResult OrderIndex()
    {
        List<OrderList> sipList = new List<OrderList>();
        sipList = GetAllOrderList().ToList();

        return View(sipList);
    }
}

My View is OrderIndex.cshtml like that:

@model IEnumerable<AlplerCRM.Models.OrderList>
@{
ViewData["Title"] = "Ordesr";
Layout = "~/Views/Shared/_AnaLayout.cshtml";
}
    <table id="example" class="display" style="width:100%">
    <thead>
        <tr>
            <th></th>
            <th>OrderId</th>
            <th>Customer</th>
            <th>OrderDate</th>
        </tr>
    </thead>
</table>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}


<script>
    function format(d) {
        return '<table id="childtable" cellpadding="5" cellspacing="0" border="0" style="padding-left: 50px; ">' +
            '<tr>' +
            '<td>Kimlik No</td>' +
            '<td>Product Detail</td>' +
            '<td>Product Color</td>' +
            '<td>Product Quantity</td>' +
            '</tr><tr>' +
            '<td>' + d.KimlikId + '</td>' +
            '<td>' + d.Product + '</td>' +
            '<td>' + d.Color + '</td>' +
            '<td>' + d.Qntty + '</td>' +
            '</tr>' +
            '</table>';
    }
    $(document).ready(function () {
        var table = $("#example").dataTable({
            "columns": [
                {
                    "className": 'details-control',
                    "orderable": false,
                    "data": null,
                    "defaultContent": ''
                },
                { "data": "OrderId" },
                { "data": "Customer" },
                { "data": "OrderDate" },
            ],
            "order": [[0, 'desc']]
        });
    });
    $('#example tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass('shown');
        }
    });

</script>
}

How can I get data without ajax and show datatable like that:

• javascript
• jquery
• sql-server
• asp-core
• datatables

• 1 I don't know how efficient this would be, but you could store child row values in data- attribute of parent tr and use that as shown here. But I remend to use datatable ajax for data loading as it's more faster and easier and you'll have more control over datatable functionalities. – Pirate Commented Dec 22, 2020 at 14:50
• Did you mean you have already gotten the result as the screenshot present (using JQuery ajax and the JQuery dataTable plugin, the Ajax is used to load data from controller), and now you don't want to use Ajax to load data? If that is the case, you could pass the data to view using ViewData or ViewBag, then, in the view page using the following code to get the value: var data = JSON.parse('@Html.Raw(ViewData["json"])'); //json string var items = JSON.parse('@Html.Raw(JsonSerializer.Serialize(ViewData["data"]))'); //object list. Then, you could display the data using jquery datatable plugin. – Zhi Lv Commented Dec 23, 2020 at 9:06
• screenshot is just an illustration for my goal, thousands of data will e from the database. I think I need to load by using Ajax. There are so many examples of datatable with ajax by getting 1 database table data, but I need to get data with Ajax by getting 2 table's union data. – halil balcıoğlu Commented Dec 23, 2020 at 10:31

2 Answers 2

Based on your code and description, I suggest you could refer the following steps to display the result.

1. Create the following view model, it is used to bind data to the JQuery DataTable plugin.

    public class OrderListViewModel
    {
        public int OrderId { get; set; }
        public string Customer { get; set; }
        public string OrderDate { get; set; }
   
        public List<OrderListDetailViewModel> OrderListDetails { get; set; }
   
    }
    public class OrderListDetailViewModel
    {  
        public int KimlikId { get; set; }  
        public string Product { get; set; }
        public string Color { get; set; }
        public int Qntty { get; set; }
    }
   

2. Add the following action method, call the GetAllOrderList() method to get all OrderList, then using GroupBy operators to group the result(base on the outer properties: OrderId, Customer and OrderDate).

    public IEnumerable<OrderListViewModel> GetOrderList()
    {
        return GetAllOrderList().GroupBy(c => new { c.OrderId, c.Customer })
            .Select(c => new OrderListViewModel()
            {
                OrderId = c.Key.OrderId,
                Customer = c.Key.Customer,
                OrderDate = c.FirstOrDefault().OrderDate,
                OrderListDetails = c.Select(d => new OrderListDetailViewModel() { Qntty = d.Qntty, Color = d.Color, KimlikId = d.KimlikId, Product = d.Product }).ToList()
            }).ToList();
    }
   

3. using the following code to call the above action method and display the result (code in the Index.cshtml page):

    <link href="https://cdn.datatables/1.10.22/css/jquery.dataTables.min.css" rel="stylesheet" /> 
   
    <style>
        td.details-control { background: url('https://datatables/examples/resources/details_open.png') no-repeat center center; cursor: pointer; }
        tr.shown td.details-control { background: url('https://datatables/examples/resources/details_close.png') no-repeat center center; }
    </style>
    <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th></th>
                <th>OrderId</th>
                <th>Customer</th>
                <th>OrderDate</th>
            </tr>
        </thead>
    </table>
    @section Scripts {
        @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
        <script src="https://code.jquery./jquery-3.5.1.js"></script>
        <script src="https://cdn.datatables/1.10.22/js/jquery.dataTables.min.js"></script>
        <script>
            function format(d) {
                var result = '<table id="childtable" cellpadding="5" cellspacing="0" border="0" style="padding-left: 50px; width:80% ">' +
                    '<tr><td>Kimlik No</td><td>Product Detail</td><td>Product Color</td><td>Product Quantity</td></tr>';
                //loop thouth the OderListDetails and add the child items.
                for (var i = 0; i < d.orderListDetails.length; i++) {
                    var child = '<tr><td>' + d.orderListDetails[i].kimlikId + '</td>' +
                        '<td>' + d.orderListDetails[i].product + '</td>' +
                        '<td>' + d.orderListDetails[i].color + '</td>' +
                        '<td>' + d.orderListDetails[i].qntty + '</td></tr>';
                    result += child;
                }
                result += '</table>';
                return result;
            }
            $(document).ready(function () { 
                //call the action method and get the data.
                $.ajax({
                    url: "/Order/GetOrderList",
                    type: "Get", 
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (data) {
                        console.log("succsss" + data);
                        //after getting the data, bind the DataTable.
                        var table = $("#example").DataTable({
                            "data": data,
                            "columns": [
                                {
                                    "className": 'details-control',
                                    "orderable": false,
                                    "data": null,
                                    "defaultContent": ''
                                },
                                { "data": "orderId" },
                                { "data": "customer" },
                                { "data": "orderDate" },
                            ],
                            "order": [[0, 'desc']]
                        });
   
                        //Expand/Collapse the nested objects.
                        $('#example tbody').on('click', 'td.details-control', function () {
                            var tr = $(this).closest('tr');
                            var row = table.row(tr);
   
                            if (row.child.isShown()) {
                                // This row is already open - close it
                                row.child.hide();
                                tr.removeClass('shown');
                            }
                            else {
                                // Open this row
                                row.child(format(row.data())).show();
                                tr.addClass('shown');
                            }
                        });
                    },
                    error: function (ex) {
                        console.log(ex);
                    }
                }); 
            });
        </script>
    }
   

The result like this:

[Note]

• If you meet the "table.row is not a function" error, change the dataTable() to DataTable() in the JQuery script. More detail information, check table.row is not a function
• When bind data to the JQuery DataTable plugin, remember to change the properties' first character to the lower case.

More detail information about using JQuery DataTable plugin, check the document.

Here is a relatively simple way I've acplished this.

I use a hidden row below the "parent" row that expands the full cell count and is shown/hidden via a div's click event in a parent cell. This div contains the attribute data-id for the row's order id value which I use to create a reference (e.g., detail_151) to the child/hidden row. Of course, this assumes the parent ids are unique. You can add these values when constructing your source HTML.

See my snippet for an example.

// Add the .expand class click events once the page is loaded.
window.onload = e => {

  document.querySelectorAll('.expand').forEach(div => {

    div.addEventListener('click', e => {

      // Toggle the detail display.
      const detail = document.querySelectorAll(`#detail_${e.target.getAttribute('data-id')}`)[0];
      if (detail.classList.contains('hide'))
        detail.classList.remove('hide');
      else
        detail.classList.add('hide');

    });

  });

}

body {
  font-size: 1em;
}

th,
td {
  position: relative;
  width: 125px;
  text-align: left;
}

.expand {
  position: absolute;
  font-size: 1.4em;
  margin-top: -5px;
  margin-left: -20px;
  color: green;
  font-weight: bold;
  cursor: pointer;
}

.hide {
  display: none;
}

.show {
  display: block;
}

<table style="margin-left: auto; margin-right: auto;">
  <thead>
    <tr>
      <th>OrderId</th>
      <th>Customer</th>
      <th>OrderDate</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>
        <div data-id="151" class="expand">+</div>151</td>
      <td>Customer21</td>
      <td>22.12.2020</td>
    </tr>
    <tr id="detail_151" class="hide">
      <td colspan="3">
        <table>
          <tr>
            <th>Kimlik</th>
            <th>Detail</th>
            <th>Color</th>
            <th>Qty</th>
          </tr>
          <tr>
            <td>2114</td>
            <td>Product-14</td>
            <td>Green</td>
            <td>3</td>
          </tr>
          <tr>
            <td>2358</td>
            <td>Product-48</td>
            <td>Yellow</td>
            <td>2</td>
          </tr>
          <tr>
            <td>2365</td>
            <td>Product-08</td>
            <td>Red</td>
            <td>6</td>
          </tr>
          <tr>
            <td>2371</td>
            <td>Product-33</td>
            <td>Red</td>
            <td>1</td>
          </tr>
        </table>
      </td>
    </tr>
    <tr>
      <td>
        <div data-id="155" class="expand">+</div>155</td>
      <td>Customer18</td>
      <td>22.12.2020</td>
    </tr>
    <tr id="detail_155" class="hide">
      <td colspan="3">
        <table>
          <tr>
            <th>Kimlik</th>
            <th>Detail</th>
            <th>Color</th>
            <th>Qty</th>
          </tr>
          <tr>
            <td>2019</td>
            <td>Product-11</td>
            <td>Red</td>
            <td>3</td>
          </tr>
          <tr>
            <td>2110</td>
            <td>Product-17</td>
            <td>Blue</td>
            <td>5</td>
          </tr>
        </table>
      </td>
    </tr>
  </tbody>
</table>