时间戳

首页 > 解决方案 > 数据表中的多个子行，来自asp.net核心中sql server的数据

javascript - 数据表中的多个子行，来自asp.net核心中sql server的数据

问题描述

我是 ASP.NET Core 的新手，从事 ASP.NET Core 项目，我需要使用从 sql server 接收数据的数据表。在没有子行的情况下我没有问题，但我必须显示具有多个子行的数据表。我无法显示具有子行的数据表。有很多关于 ajax 的例子，但我找不到任何来自 asp.net 核心中 sql server 的数据的例子。

如果我们简单地谈谈数据库结构，有 2 个表：Order 和 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

我的模型类 OrderList 是这样的：

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; }
}

我的控制器类 OrderController 是这样的：

    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);
    }
}

我的视图是这样的 OrderIndex.cshtml：

@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>
}

如何在没有 ajax 的情况下获取数据并像这样显示数据表： 在此处输入图像描述

标签： javascriptjquerysql-serverasp.net-coredatatables

解决方案

根据您的代码和描述，我建议您可以参考以下步骤来显示结果。

  1. 创建以下视图模型，它用于将数据绑定到 JQuery DataTable 插件。

     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. 添加以下action方法，调用GetAllOrderList()方法获取所有OrderList，然后使用GroupBy运算符对结果进行分组（基于外部属性：OrderId、Customer和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. 使用以下代码调用上述动作方法并显示结果（Index.cshtml页面中的代码）：

     <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>
 }

结果是这样的：

在此处输入图像描述

[笔记]

  • 如果遇到“table.row is not a function”错误，请在 JQuery 脚本中更改dataTable()为。DataTable()更多详细信息，检查table.row 不是函数
  • 将数据绑定到 JQuery DataTable 插件时，请记住将属性的第一个字符更改为小写。

有关使用 JQuery DataTable 插件的更多详细信息，请查看文档。

推荐阅读