时间戳

首页 > 解决方案 > 如何将 Json 数据加载到 jQuery Datatable 中？

jquery - 如何将 Json 数据加载到 jQuery Datatable 中？

问题描述

我正在尝试通过 mvc 将数据加载到 jQuery 数据表中，但数据只是在浏览器中以 json 格式检索，而不是加载到数据表中，如下所示：

{"data":[{"ID":1,"FullName":"Jhon Smith","Email":"adil@gmail.com","Address":"huwai Street 789"}

Datatable 可以完美地处理虚拟数据，但是当数据来自数据库时，它只会返回 json 格式的记录。

AllUsersDetail.cshtml：

    <table id="myTable" class="display">
    <thead>
        <tr>
            <th>
                ID
            </th>
            <th>
                FullName
            </th>
            <th>
                Email

            </th>
            <th>
                Address
            </th>
        </tr>
    </thead>
</table>

    <link href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap.min.css" rel="stylesheet" />
    @section Scripts{
    <script src="https://cdn.datatables.net/v/dt/dt-1.10.18/datatables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap.min.js"></script>
        <script>
            $(document).ready(function () {
                $('#myTable').DataTable({
                    "ajax": {
                        "url": "/Dashboard/AllUsersDetail",
                        "type": "Get",
                        "datatype": "Json",
                    },
                    "Columns": [
                        { "data": "UserId" },
                        { "data": "FullName" },
                        { "data": "Email" },
                        { "data": "Address" }

                    ]

                });
            });
        </script>

控制器

 public ActionResult AllUsersDetail()
        {

                List<UserProfileModel> Allstudent = DashboardViewModel.AllUserDetail();
            return Json(new { data = Allstudent }, JsonRequestBehavior.AllowGet);


        }

  public static List<UserProfileModel> AllUserDetail()
        {
            List<UserProfileModel> emps = new List<UserProfileModel>();
            using (SqlConnection con = new SqlConnection(AppSetting.ConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand("Select * from Users", con))
                {
                   // cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    con.Open();

                    SqlDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        UserProfileModel emp = new UserProfileModel();
                        emp.ID = Convert.ToInt16(rdr["UserId"]);
                        emp.FullName = rdr["FullName"].ToString();
                        emp.Email = rdr["Email"].ToString();
                        emp.Address = rdr["Address"].ToString();

                        emps.Add(emp);

                    }
                }
            }

            return emps;
        }

标签： jqueryajaxasp.net-mvcdatatables

解决方案

这个解决方案怎么样？

为 UserProfile 创建网格模型类...

public class UserProfileGridModel
{
  public int Id {get; set;}
  public string FullName {get; set;}
  public string Email {get; set;}
  public string Address {get; set;}

  public static List<UserProfileGridModel> GetRows(IQueryable<dbo_Users>)
  {
     var data = dbQuery.ToList().Select(r => new UserProfileGridModel()
     {
         DT_RowId = r.Id,
         FullName= r.FullName,
         Address = r.Address,
         Email= r.Email
     });

     return data.ToList();
  }
}

你可以做的控制器...

public JsonResult AllUserDetail()
{
   // Get user data from database
   var dbData = AppData.UserProfile.GetAll();

   // Call created class for grid model
   var data = UserProfileGridModel.GetRows(dbData);

   // Return JSON 
   return new JsonResult
   {
      Data = new { data },
      JsonRequestBehavior = JsonRequestBehavior.AllowGet,
      MaxJsonLength = int.MaxValue
   };
 }

并且在视野中...

// Datatable columns
var myColums = [
  {
    data: 'DT_RowId',
    title: 'ID',
    filter: 'input',
    visible: false
  },
  {
    data: 'FullName',
    title: 'FullName',
    filter: 'input'
  },
  {
    data: 'Email',
    title: 'Email',
    filter: 'input'
  },
  {
    data: 'Address',
    title: 'Address',
    filter: 'input'
  },  
];



// Init datatable  
var myTable = $("#myTable ").DataTable({
   ajax: {
      url: "/Dashboard/AllUsersDetail",
   },
   columns: myColumns,
});

希望能帮助到你...

推荐阅读