Menu
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.net 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:
945
asked Mar 21 '26 13:03
Based on your code and description, I suggest you could refer the following steps to display the result.
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; }
}
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();
}
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.net/1.10.22/css/jquery.dataTables.min.css" rel="stylesheet" />
<style>
td.details-control { background: url('https://datatables.net/examples/resources/details_open.png') no-repeat center center; cursor: pointer; }
tr.shown td.details-control { background: url('https://datatables.net/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.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/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]
dataTable() to DataTable() in the JQuery script. More detail information, check table.row is not a functionMore detail information about using JQuery DataTable plugin, check the document.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
