I try to showing data with jquery data table but the result is no value at all (only showing header)

Here's my view script

@model IEnumerable<Vitashopper.Models.Goods>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table  id="productTable">
    <tr>
        <th>ProductID</th>
        <th>ProductName</th>
        <th>BuyPrice</th>
        <th>SellPrice</th>
        <th>Stock</th>
        <th>Description</th>
        <th>Remarks</th>
    </tr>

</table>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css"</script>
<script src="https://cdn.datatables.net/autofill/2.5.1/css/autoFill.dataTables.min.css"></script>


@section Scripts
{

    <script type="text/javascript">
     $(document).ready(function () {
        $.ajax({
            url: '@Url.Action("GetData", "Goods")',
            method: 'get',
            dataType: 'json',
            success: function (data) {
                $('#productTable').DataTable({
                    data: data,
                    columns: [
                         { "data": "ProductId" },
                           { "data": "ProductName" },
                           { "data": "BuyPrice" },
                        { "data": "SellPrice" },
                        { "data": "Stock" },
                        { "data": "Description" },
                        { "data": "Remarks" },
                    ]
                });
            }
        });
    });

    </script>
}

Here's my controller

public ActionResult GetData()
        {
            GoodsDBHandle dbhandle = new GoodsDBHandle();
            var goods = dbhandle.GetGoods();
            goods.ToList().ForEach(x => x.ProductId = x.ProductId);
            return View(goods);

        }

and this is DB handle that is use for showing data

public List<Goods> GetGoods()
        {
            connection();
            List<Goods> goodslist = new List<Goods>();

            SqlCommand cmd = new SqlCommand("GetAllGoods", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sd = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            con.Open();
            sd.Fill(dt);
            con.Close();
            string json = JsonConvert.SerializeObject(dt);
            DataTable dt2 = JsonConvert.DeserializeObject<DataTable>(json);
            foreach (DataRow dr in dt2.Rows)
            {
                goodslist.Add(
                    new Goods
                    {
                        ProductId = Convert.ToInt32(dr["ProductID"]),
                        ProductName = Convert.ToString(dr["ProductName"]),
                        BuyPrice = Convert.ToDecimal(dr["BuyPrice"]),
                        SellPrice = Convert.ToDecimal(dr["SellPrice"]),
                        Stock = Convert.ToInt32(dr["Stock"]),
                        Description = Convert.ToString(dr["Description"]),
                        Remarks = Convert.ToString(dr["Remarks"])
                        
                    });

                
            }
            
            return goodslist;
        }

and on controller side, I also try with return Json(new { data = goods }, JsonRequestBehavior.AllowGet); and its working properly with json output

is it something wrong with my implementation?

CodePudding user response：

Make sure your jquery is imported properly

Can try with this script

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"> </script>
<script src="https://cdn.datatables.net/1.10.8/js/jquery.dataTables.min.js" defer="defer"></script>

<script>

    $(document).ready(function () {

        $('#myTable').DataTable(

            {

                "ajax": {

                    "url": "/Goods/GetData",

                    "type": "Get",

                    "datatype": "json"

                },

                "columns": [

                    { "data": "ProductId" },

                    { "data": "ProductName" },

                    { "data": "BuyPrice" },

                    { "data": "SellPrice" },

                    { "data": "Stock" },

                    { "data": "Description" },

                    { "data": "Remarks" },

                ]

            });

    });

</script>
<body>

    <div style="margin:300px;margin-top:60px">

        <table id="myTable" >

            <thead>

                <tr>

                    <th>Product ID</th>

                    <th>Product Name</th>

                    <th>Buy Price</th>

                    <th>Sell Price</th>

                    <th>Stock</th>

                    <th>Description</th>

                    <th>Remarks</th>

                </tr>

            </thead>

        </table>

    </div>

</body>

and this is the controller to load json data

public JsonResult GetData()

        {

            GoodsDBHandle obj = new GoodsDBHandle();

            List<Goods> goodsList = obj.GetDataGoods();

            return Json(new { data = goodsList }, JsonRequestBehavior.AllowGet);

        }

then here is the DB call that I'm modified from your side (actually your code is already correct but no need jsonobject there)

 public List<Goods> GetDataGoods()

        {

            List<Goods> goodsList = new List<Goods>();

            try

            {

                connection();

                {

                   SqlCommand cmd = new SqlCommand("GetAllGoods", con);
            cmd.CommandType = CommandType.StoredProcedure;

                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader sdr = cmd.ExecuteReader();

                    while (sdr.Read())

                    {

                        Goods g = new Goods();

                        g.ProductId = Convert.ToInt32(sdr["ProductId"].ToString());

                        g.ProductName = sdr["ProductName"].ToString();

                        g.BuyPrice = Convert.ToDecimal(sdr["BuyPrice"].ToString());

                        g.SellPrice = Convert.ToDecimal(sdr["SellPrice"].ToString());

                        g.Stock = Convert.ToInt32(sdr["Stock"].ToString());

                        g.Description = sdr["Description"].ToString();

                        g.Remarks = sdr["Remarks"].ToString();
                        goodsList.Add(g);

                    }

                }

            }

            catch (Exception ex)

            {

            }

            return goodsList;

        }

    }

and then use partial view to index.cshtml to get all data from json value

<div style="margin-top:20px">
    @Html.Partial("GetData")
</div>

hope your problem solved with this sample script