JSON, ASHX and Datatables

stevenwoolston

Hi anyone who can help.

I am trying to get my datatables working with an C# ASHX file. Firstly, let me show you how I am building up the JSON data.
[code]
>8 --snip--
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/json";
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConn"].ToString();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.sp_StoredProcedureName";
SqlDataReader rdr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
using (JsonWriter jsonWriter = new JsonTextWriter(sw))
{
jsonWriter.WriteStartArray();
int totalrecords = 0;
while (rdr.Read())
{
jsonWriter.WriteStartObject();
int fields = rdr.FieldCount;
for (int i = 0; i < fields; i++)
{
jsonWriter.WritePropertyName(rdr.GetName(i));
jsonWriter.WriteValue(rdr[i]);
}
jsonWriter.WriteEndObject();
totalrecords++;
}
jsonWriter.WriteEndArray();
context.Response.Write("{\"sEcho\":1,\"iTotalRecords\":" + totalrecords + ",\"iTotalDisplayRecords\":" + totalrecords + ", ");
context.Response.Write("\"aaData\":" + sb + "}");
}
}
conn.Close();
}
}
--snip--8<
[/code]

This code gives me a correctly formatted JSON stream (as confirmed by JSLint). This is what I see in Chrome when I browse directly to the ASHX file.
[code]
{"sEcho":1,"iTotalRecords":5,"iTotalDisplayRecords":5,
aaData:[
{"SmplKey":905995,"CoyName":"Really Nice CoyName Value","CoyUnitId":"210"},
{"SmplKey":928804,"CoyName":"Really Nice CoyName Value","CoyUnitId":"210"},
{"SmplKey":950127,"CoyName":"Really Nice CoyName Value","CoyUnitId":"210"},
{"SmplKey":980006,"CoyName":"Really Nice CoyName Value","CoyUnitId":"210"},
{"SmplKey":1007621,"CoyName":"Really Nice CoyName Value","CoyUnitId":"210"}
]}
[/code]

When I try to build this into my HTML file, it get an error and an empty table (link to screenshot - http://www.stevenwoolston.com/wp-content/uploads/2013/06/datatables-debug1.png). Below is the HTML code (here is the debug data too - http://debug.datatables.net/iforeq).
[code]



SmplKey
CoyName
CoyUnitID









$(function () {
$("#example").dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxDataProp": "aaData",
"sAjaxSource": "/Handlers/data.ashx"
});
});

[/code]

The debug data is kind of weird because it is showing 3 additional columns with null values. However, the JSON only has three columns. I am guessing there is something wrong with the way I am binding the JSON to the table in the Javascript but I cannot see the problem.

Any assistance would be appreciated.

Thanks
Steven
e: dev@webdesignwith.net
w: http://www.webdesignwith.net

allan

Hi Steven,

You need to tell DataTables which property to use for which column in the table. You do that with the mData option: http://datatables.net/blog/Extended_data_source_options_with_DataTables

Allan

stevenwoolston

Thanks very much Allan. I read that article and added the necessary column definitions to my javascript.

[code]
$("#example").dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxDataProp": "aaData",
"sAjaxSource": "/Handlers/data.ashx",
"aoColumns": [
{ "mData": "SmplKey" },
{ "mData": "CoyName" },
{ "mData": "CoyUnitId" }
]
});
[/code]

My datatables are working a treat now.

Thanks
Steven
e: dev@webdesignwith.net
w: http://www.webdesignwith.net