DataTables ajax data source from Web API
DataTables ajax data source from Web API
ShirleyW
Posts: 16Questions: 4Answers: 2
Hello,
I am trying to load DataTables with JSON data returned from .NET Web API.
If I stored the returned JSON data to a text file and use ajax:"JSONcontent.txt", it worked fine.
But when I point the ajax source to the api path, the data table is empty. I tried various syntax to no avail.
Haven't yet located an example from my research yet, I thought I post this question and ask the experts.
This is my 1st time using DataTables, have much to learn. Thanks in advance 
Shirley
My HTML looks like this:
<script>
var hostUrl = "http://" + document.location.hostname + ":" + window.location.port;
var apiUrl = '/api/bookings/';
$(document).ready(function () {
debugger;
var table = $("#tbProject").DataTable({
ajax: { url: hostUrl+apiUrl, dataSrc: '' },
//ajax: { url: 'api/bookings/', dataSrc: 'data' },
//ajax: {url: 'api/bookings/GetAllBookings' }
//ajax: "JSONcontent.txt",
dataType: 'json',
contentType: 'application/json; charset=utf-8',
type: "GET",
columns: [
{ data: "ResponsibleOrganizationName" },
{ data: "Code" },
{ data: "Name" },
{ data: "ClientName" },
{ data: "PMName" },
{ data: "FeeType" },
{ data: "BNFM" },
{ data: "OriginalProjectFee" },
{ data: "CurrentProjectFee" },
{ data: "LaborFee" },
{ data: "BSTCreatedDate" },
{ data: "PercentAllocated" },
{ data: "UnAllocated" }
]
});
}); /*document.ready*/
</script>
The controller.cs (ApiController) returns JSON like this:
namespace BookingsAllocation.Controllers
{
public class BookingsController : ApiController
{
[Route("api/bookings")]
[HttpGet]
public string GetAllBookings() //public IEnumerable<Booking> Bookings()
{ //code to SQL to get data and then format into JSON}
}
}
{"data":
[{"Code":"12550000",
"Name":"Memorial Nursing Home",
"PMCode":"005730",
"PMName":"Fouche, Amy",
"ClientCode":"C12182",
"ClientName":"Riverbend Health System",
"FeeType":"LS",
"BNFM":2.057100000,
"OriginalProjectFee":1278000.000000000,
"CurrentProjectFee":1278000.000000000,
"LaborFee":1278000.0,
"BSTCreatedDate":"1/15/2017 12:00:00 AM",
"ResponsibleOrgCode":"5500",
"ResponsibleOrganizationName":"BSP - Kansas City",
"PercentAllocated":1.00,
"UnAllocated":0.00,
"Comments":"TESING123 and TESTING456",
"ModifiedDate":null,
"ModifiedBy":null,
"Status":"A",
"EmpCode6GL":"009039",
"EmpName6GL":"Taylor, Michael S",
"ProjectAllocation":[{"Code":"12550000","empCode":"005730","empName":"Fouche, Amy","AllocatedPercent":0.25,"AllocatedAmount":319500.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"},
{"Code":"12550000","empCode":"009039","empName":"Taylor, Michael S","AllocatedPercent":0.25,"AllocatedAmount":319500.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"},
{"Code":"12550000","empCode":"003957","empName":"Roberts, Jon","AllocatedPercent":0.25,"AllocatedAmount":319500.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"},
{"Code":"12550000","empCode":"006442","empName":"Wilson, Katie","AllocatedPercent":0.25,"AllocatedAmount":319500.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"}]},
{"Code":"17420430","Name":"MRMC Marshall Entertainment PT","PMCode":"004806","PMName":"Jenkins III, Hilda M","ClientCode":"C01010","ClientName":"Hart Greenland Roberts, Inc.","FeeType":"LS","BNFM":2.140000000,"OriginalProjectFee":500000.000000000,"CurrentProjectFee":495000.000000000,"LaborFee":495000.0,"BSTCreatedDate":"4/21/2017 12:00:00 AM","ResponsibleOrgCode":"4200","ResponsibleOrganizationName":"BSP-Nashville","PercentAllocated":1.00,"UnAllocated":0.00,"Comments":"Anything and everything","ModifiedDate":null,"ModifiedBy":null,"Status":"A","EmpCode6GL":"009039","EmpName6GL":"Taylor, Michael S.","ProjectAllocation":[{"Code":"17420430","empCode":"004806","empName":"Jenkins III, Hilda M","AllocatedPercent":0.25,"AllocatedAmount":123750.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"},{"Code":"17420430","empCode":"009039","empName":"Taylor, Michael S","AllocatedPercent":0.35,"AllocatedAmount":173250.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"},{"Code":"17420430","empCode":"003957","empName":"Roberts Jr. Jonathan","AllocatedPercent":0.3,"AllocatedAmount":148500.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"},{"Code":"17420430","empCode":"006442","empName":"Wilson, Katie J","AllocatedPercent":0.1,"AllocatedAmount":49500.0,"ModifiedDate":"5/15/2017","ModifiedBy":"me"}]}]}
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
That looks like the most likely url. Did it not work at all?
Also did "dataSrc: 'data' " not work?
I tried both with or without data and with or without GetAllBookings and the the datatables is either empty or show this
You must follow the Diagnostic process explained at the link in the image.
Tell us specifically what error message(s) you are receiving.
change your code to look like below. That will let you examine your data returned the actual error.
Using .net, issues I have seen are:
.net tends to put serialized data in an about called "d" so it would be d:{data:{}}
I have seen it double serialized, once by the coder and once by .net on its own.
I changed the ajax to look like yours
var table = $("#tbProject").DataTable({
//ajax: { url: hostUrl+apiUrl, dataSrc: '' },
//ajax: { url: 'api/bookings/GetAllBookings', dataSrc: 'data' },
//ajax: "JSONcontent.txt",
ajax: {
url: 'api/bookings/',
dataFilter: function (resp) {
debugger;
return resp;
},
error: function (err) {
debugger;
}
},
dataType: 'json',
resp looks like this:
Is the extra () causing the problem, is this what you mean by double serialize? Thanks.
"{\"data\":[{\"Code\":\"12550000\",\"Name\":\"RHS Shore Memorial Hospital\",\"PMCode\":\"005730\",\"PMName\":\"Fouche, Andre\",\"ClientCode\":\"C12182\",\"ClientName\":\"Riverside Health System\",\"FeeType\":\"LS\",\"BNFM\":2.057100000,\"OriginalProjectFee\":1278000.000000000,\"CurrentProjectFee\":1278000.000000000,\"LaborFee\":1278000.0,\"BSTCreatedDate\":\"1/15/2017 12:00:00 AM\",\"ResponsibleOrgCode\":\"5500\",\"ResponsibleOrganizationName\":\"BSP - Kansas City\",\"PercentAllocated\":1.00,\"UnAllocated\":0.00,\"Comments\":\"TESING123 and TESTING456\",\"ModifiedDate\":null,\"ModifiedBy\":null,\"Status\":\"A\",\"EmpCode6GL\":\"009039\",\"EmpName6GL\":\"Rogers, Michael S\",\"ProjectAllocation\":[{\"Code\":\"12550000\",\"empCode\":\"005730\",\"empName\":\"Fouche, Andre\",\"AllocatedPercent\":0.25,\"AllocatedAmount\":319500.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"},{\"Code\":\"12550000\",\"empCode\":\"009039\",\"empName\":\"Rogers, Michael S\",\"AllocatedPercent\":0.25,\"AllocatedAmount\":319500.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"},{\"Code\":\"12550000\",\"empCode\":\"003957\",\"empName\":\"Ross, Jon\",\"AllocatedPercent\":0.25,\"AllocatedAmount\":319500.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"},{\"Code\":\"12550000\",\"empCode\":\"006442\",\"empName\":\"Williams, Katie\",\"AllocatedPercent\":0.25,\"AllocatedAmount\":319500.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"}]},{\"Code\":\"17420430\",\"Name\":\"MRMC Marshall Medical PT\",\"PMCode\":\"004806\",\"PMName\":\"Jenkins III, Herbert M\",\"ClientCode\":\"C01010\",\"ClientName\":\"Hart Freeland Roberts, Inc.\",\"FeeType\":\"LS\",\"BNFM\":2.140000000,\"OriginalProjectFee\":500000.000000000,\"CurrentProjectFee\":495000.000000000,\"LaborFee\":495000.0,\"BSTCreatedDate\":\"4/21/2017 12:00:00 AM\",\"ResponsibleOrgCode\":\"4200\",\"ResponsibleOrganizationName\":\"BSP-Nashville\",\"PercentAllocated\":1.00,\"UnAllocated\":0.00,\"Comments\":\"Anything and everything\",\"ModifiedDate\":null,\"ModifiedBy\":null,\"Status\":\"A\",\"EmpCode6GL\":\"009039\",\"EmpName6GL\":\"Rogers, Michael S.\",\"ProjectAllocation\":[{\"Code\":\"17420430\",\"empCode\":\"004806\",\"empName\":\"Jenkins III, Herbert M\",\"AllocatedPercent\":0.25,\"AllocatedAmount\":123750.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"},{\"Code\":\"17420430\",\"empCode\":\"009039\",\"empName\":\"Rogers, Michael S\",\"AllocatedPercent\":0.35,\"AllocatedAmount\":173250.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"},{\"Code\":\"17420430\",\"empCode\":\"003957\",\"empName\":\"Ross Jr. Jonathan\",\"AllocatedPercent\":0.3,\"AllocatedAmount\":148500.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"},{\"Code\":\"17420430\",\"empCode\":\"006442\",\"empName\":\"Williams, Katie J\",\"AllocatedPercent\":0.1,\"AllocatedAmount\":49500.0,\"ModifiedDate\":\"5/15/2017\",\"ModifiedBy\":\"me\"}]}]}"
Tangerine:
Per my attached error message image....
The error (per event viewer) was traced to......, "caching compressed content C:\Users\Shirley\AppData\Local\Temp\iisexpress\IIS Temporary Compressed Files\Clr4IntegratedAppPool is invalid. Static Compression is being disabled."
The folder does not exist...
So I manually created the folders \IIS Temporary Compressed Files\Clr4IntegratedAppPool in my C:.. and that error (from the attached image) does no show up again in the Windows event log, though my DataTables is still blank.
I thought I post this if anybody else may find it useful... Thanks.
Shirley
HI Shirley,
Could you run the debugger on your page and give us the resulting link please. That should let us figure out what is going wrong.
Allan
bindrid,
You are correct, I looked up .NET Web API and learned that when my models.cs is inherited from ApiController like this:
public class BookingsController : ApiController
{
public string<Booking> Bookings()
{
....
Json = JsonConvert.SerializeObject(new { data = bookings });
return Json;
}
}
my result is double serialized, (1) via JsonConvert.SerializeObject, done by me; (2) via .NET ApiController class where the controller is inherited from, hence DataTables.net won't read my result cause it is NOT in the correct JSON format.
I changed my code to return the object instead of a string like this and that fixed this particular issue for me.
public class BookingsController : ApiController
{
public IEnumerable<Booking> Bookings()
{
....
return bookings; //return the object and let ApiController serialize it for me
}
}
Learn something everyday....Thanks.