如何利用MVC_WEB API用Jquery DateTable，將SQL資料呈現在畫面的對應表格欄位?

asp.net c# asp.net mvc jquery

甜點螞蟻教主 2021-10-19 14:54:39 ‧ 2507 瀏覽

分享至

想請教各位前輩，因部門沒有前端.....所以就變成全端仔@@" (對前端還不是很熟)

我要將資料庫內的資料撈出來，已有寫API讓前端去接並需求要呈現在網頁畫面上，目前是使用jQuery DataTable套件直接作出表格欄位，但試了很久還是不太曉得要怎麼把接到API的值，會將資料放進指定的表格欄位，還請各位前輩指教要怎麼放進指定欄位表?
或是有其它的建議跟方法?(不一定要使用jQuery)，在此感謝。

MessageBase

public class MessageBase
    {
        #region
        [System.ComponentModel.DefaultValue(0)]
        public int Code { get; set; }
        [System.ComponentModel.DefaultValue("")]
        public string Message { get; set; }
        [System.ComponentModel.DefaultValue("")]
        public string Json { get; set; }
        [System.ComponentModel.DefaultValue(null)]
        public List<object> Lists { get; set; }
        #endregion
        public MessageBase()
        {
            Code = 0;
            Message = "";
            Lists = new List<object>();
        }
    }

Model

 public class DataModel: MessageBase
    {
        public void GetYieldData(string from_date, string end_date , string FACTORY)
        {
            Json = "";
            string sql = string.Format(@"資料庫連線字串",from_date);
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["SQL名稱"].ConnectionString);
            try
            {
                conn.Open();
                OracleDataAdapter adpt = new OracleDataAdapter();
                using (OracleCommand cmd = new OracleCommand())
                {
                    DataSet ds = new DataSet();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    adpt.SelectCommand = cmd;
                    adpt.Fill(ds);
                    foreach (DataTable dt in ds.Tables)
                    {
                        Lists.Add(dt);
                    }
                }
            }
            catch (Exception e)
            {
                Code = 9999;
                Message = e.ToString() as string;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
       
    }

Controller

 public class DataController : Controller
    {
        public ActionResult YieldData()
        {
            ViewBag.title = "數據資料";
            return View();
        }

        [HttpPost]
        public string GetData(string from_date, string end_date, string FACTORY)
        {
            DataModel con = new DataModel();
            con.GetYieldData(from_date,end_date,FACTORY);
            return JsonConvert.SerializeObject(con);
        }
    }

View

@{
    Layout = "null";
}
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title></title>
    <link href="@Url.Content("~/Scripts/datepicker-1.9.0/css/bootstrap-datepicker.css")" rel="stylesheet" />
    <link href="@Url.Content("~/Scripts/datepicker-1.9.0/css/bootstrap-datepicker.min.css")" rel="stylesheet" />
    <link href="@Url.Content("~/DataTables/DataTables-1.10.24/css/jquery.dataTables.min.css")" rel="stylesheet" />
    <link href="@Url.Content("~/bootstrap-4.2.1-dist/css/bootstrap.min.css")" rel="stylesheet" />
    <script src="@Url.Content("~/DataTables/jQuery-3.3.1/jquery-3.3.1.min.js")"></script>
    <script src="@Url.Content("~/DataTables/DataTables-1.10.24/js/jquery.dataTables.min.js")"></script>
    <script src="@Url.Content("~/bootstrap-4.2.1-dist/js/bootstrap.bundle.js")"></script>
    <script src="@Url.Content("~/Scripts/datepicker-1.9.0/js/bootstrap-datepicker.min.js")"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
</head>
<body>
<form>
    <div class="container">
        <div class="form-row">
            <div class="form-group col-md">
                <label for="start_date">選擇日期</label>
<input class="form-control" id="start_date" style="width:200px">
                <span class="input-group-addon">
                    <span class="fa fa-calendar"></span>
                </span>
            </div>
            @*<div class="form-group col-md">
                <label for="end_date">結束日期</label>
<input class="form-control" id="end_date" style="width:200px">
            </div>*@
            @*<div class="form-inline">
 <button type="button" id="query" class="btn btn-success">查詢</button>
            </div>*@   
        </div>
    </div>
</form>

    <table id="ListTable" class="display style=" width:100%">
        <thead>
            <tr>
                <th>品目類別</th>
                <th>投入量1</th>
                <th>產出量1</th>
                <th>良率1</th>
                <th>投入量2</th>
                <th>產出量2含回算</th>
                <th>良率2含回算</th>
                <th>總良率</th>
            </tr>
        </thead>
        <tbody>                    
        </tbody>
    </table>
    <button type="submit" id="btnQuery" class="btn btn-success" >查詢</button>  
    <script>
        
        var query = $('#btnQuery').val();
        $(document).ready(function () {
            var date = new Date();
var from_date = new Date(date.getFullYear(), date.getMonth(), date.getDate() - 30);
var today = new Date(date.getFullYear(), date.getMonth(), date.getDate());
            $('#start_date').datepicker({
                "autoclose": true,
                format: 'yyyymmdd'
            }).datepicker('setDate', from_date);
            $('#end_date').datepicker({
                "autoclose": true,
                format: 'yyyymmdd'
            }).datepicker('setDate', today);
        });    
        $('#ListTable').DataTable({
            //data:dataList,
            "searching": false,//搜尋功能, 預設是開啟
            "lengthMenu": [5, 10, 15, 20, 25, 30, 35, 50],
            language: {
                "emptyTable": "無資料...",
                "processing": "處理中...",
                "loadingRecords": "載入中...",
                "lengthMenu": "每頁 _MENU_ 筆資料",
                "zeroRecords": "無搜尋結果",
                "info": "_START_ 至 _END_ / 共 _TOTAL_ 筆",
                "infoEmpty": "尚無資料",
                "infoFiltered": "(從 _MAX_ 筆資料過濾)",
                "infoPostFix": "",
                "search": "搜尋字串:",
                "paginate": {
                    "first": "首頁",
                    "last": "末頁",
                    "next": "下頁",
                    "previous": "前頁"
                },
                "aria": {
                    "sortAscending": ": 升冪",
                    "sortDescending": ": 降冪"
                }
            },            
        });
        $('#btnQuery').on('click',function (event) {
            var start_date = $('#start_date').val();
            var end_date = $('#end_date').val();
            $.ajax({
                type: 'post',
                dataType: 'json',
                async: false,
                url: 'GetData',
   data: { from_date: start_date, end_date:end_date, FACTORY: 'TAIFLEX'},
                cache: false,
                success: function (data) {
                $.each(data.Lists[0],
                        function (i, item) {
                            debugger;
    $('.btnQuery').append(
    $('<tr>')
    .append($('<td>').append([i + 1] + "." + "品目類別:" + item.品目類別))
    .append($('<td>').append("投入量1:" + item.投入量1))
    .append($('<td>').append("產出量1:" + item.產出量1))
    .append($('<td>').append("良率1:" + item.良率1))
    .append($('<td>').append("投入量2:" + item.投入量2))
    .append($('<td>').append("產出量2含回算:" + item.產出量2含回算))
    .append($('<td>').append("良率2含回算:" + item.良率2含回算))
    .append($('<td>').append("總良率:" + item.總良率))
                            );
                        });
                },
                error: function (e) {
                    console.log(e);
                },
                statusCode: {
                    404: function () {
                        alert("page not found");
                    }
                }
            });
        });
    </script>
<br/>
    <div class="btnQuery"></div>
</body>
</html>

畫面

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2021-10-19 15:27:29

參考看看
這篇合不合用

回應 1
分享
檢舉

甜點螞蟻教主 iT邦新手 3 級 ‧ 2021-10-19 21:10:48 檢舉

謝謝分享，我再試試看

登入發表回應

Hankz

iT邦新手 2 級 ‧ 2021-10-19 15:34:22

都用Datatables了
就不用自己渲染啦
只要輸出的json格式符合datatable的要求
就可以直接在建構datatable時抓資料了

js：

datatable = $("#ListTable").DataTable( {
    "ajax": {
        url: "GetData",
        data: { from_date: start_date, end_date:end_date, FACTORY: 'TAIFLEX'},
        type: "POST"
    },
    ...
} );

// 重新抓API資料
datatable.ajax.reload();

json：

{
  "data": [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$320,800"
    ],
    [
      "Garrett Winters",
      "Accountant",
      "Tokyo",
      "8422",
      "2011/07/25",
      "$170,750"
    ],
    ....
}

詳細請看官網範例