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:
