Datatables + Classic ASP

Orphan

Hi,

I have searched high and low, and have also gone through around 15 pages on this forum but couldn't find an answer. Alot of the examples that I am finding for Datatables (which is a fantastic jquery addition I might add) seem to focus on mysql and php. The problem I have is that I am currently coding in classic asp, and using a mssql server.

Now, I've managed to switch any mysql links to mssql without any issues, but what I'm currently falling down on is the handling of sAjaxSource having some results returned from an asp page instead of a php page. The two issues that I can see at the moment are:

1. The named array for the output
2. The json encoding of the output back to the calling page

At the moment I don't really see a clean solution to either of these. I've looked at the alternative data sources though I'm wary about using text files. I would very much prefer to use the server-side model if I could.

Just wondering if anyone else is successfully using the server-side model with classic asp, and how they accomplished it.

Thanks

allan

What exactly does the JSON that the server is returning look like? Have you tried using mData to tell DataTables what object property to read for each column? http://datatables.net/blog/Extended_data_source_options_with_DataTables

Allan

robertbrower

You can follow these steps:

1. Use the fnServerData option to add any custom query parameters to the array of parameters that datatablles sends to the Web Service as a JSON string:
[code]

.
.
.
, "fnServerData": function (sSource, aoData, fnCallback) {

var parameters = getParameters($this, settings);
aoData.push({ "name": "MachineId", "value": parameters.MachineId });
aoData.push({ "name": "AlarmLevelNames", "value": parameters.AlarmLevelNames });
aoData.push({ "name": "SubsystemNames", "value": parameters.SubsystemNames });
aoData.push({ "name": "Message", "value": parameters.Message });
aoData.push({ "name": "StartTime", "value": parameters.StartTime });
aoData.push({ "name": "EndTime", "value": parameters.EndTime });

var data = {};
$.each(aoData, function (index, obj) {
data[obj.name] = obj.value;
});

var dto = { 'json': JSON.stringify(data) };

$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
context: $this,
dataType: "json",
url: "WebService.asmx/GetAlarmsInTimeRange",
data: JSON.stringify(dto),
error: function (xhr, status, error) {
alert(eval("(" + xhr.responseText + ")").Message);
},
success: function (result) {

fnCallback(eval("(" + result.d + ")"));
settings.$tableLayout.resizeAll();
}
});
}
[/code]
2. Deserialize the JSON string passed to the web service.

[code]
///
/// Deserializes a JSON string.
///
/// The type of the return value.
/// The JSON string to deserialize.
/// An object of type T.
public static T DeserializeJSON(string json)
{
DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(T));
MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(json));
T obj = (T)serializer.ReadObject(ms);
return obj;
}
[/code]

3. Execute your query based on the parameters passed to the web service.

4. Package the results into an object:
[code]
List data = new List();
foreach (Alarm alarm in queryResult.Alarms)
{
data.Add(
new string[]
{
alarm.Timestamp.ToString(),
alarm.AlarmLevel,
alarm.Subsystem,
alarm.Message,
alarm.Duration.ToString()
});
}

Result result = new Result()
{
iTotalRecords = queryResult.TotalCount,
iTotalDisplayRecords = queryResult.FilteredCount,
sEcho = parameters.sEcho.ToString(),
aaData = data.ToArray()
};

return Helpers.SerializeJSON(result);
[/code]

The Result class:
[code]
public class Result
{
///
/// Gets or sets the TBD.
///
[DataMember]
public int iTotalRecords { get; set; }

///
/// Gets or sets the TBD.
///
[DataMember]
public int iTotalDisplayRecords { get; set; }

///
/// Gets or sets the TBD.
///
[DataMember]
public string sEcho { get; set; }

///
/// Gets or sets the TBD.
///
[DataMember]
public string sColumns { get; set; }

///
/// Gets or sets the TBD.
///
[DataMember]
public string[][] aaData { get; set; }
}
[/code]

robertbrower

[code]
///
/// Serializes an object to a JSON string.
///
/// The type of the object to serialize.
/// The object to serialize.
/// The JSON string.
public static string SerializeJSON(T obj)
{
DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(T));
MemoryStream ms = new MemoryStream();
serializer.WriteObject(ms, obj);
return Encoding.UTF8.GetString(ms.ToArray());
}
[/code]

robertbrower

Important! The Parameters class has to contain all the required properties plus any custom ones:

[code]
public class Parameters
{
///
/// Gets or sets the sEcho.
///
[DataMember]
public int sEcho { get; set; }

///
/// Gets or sets the iColumns.
///
[DataMember]
public int iColumns { get; set; }

///
/// Gets or sets the sColumns.
///
[DataMember]
public string sColumns { get; set; }

///
/// Gets or sets the iDisplayStart.
///
[DataMember]
public int iDisplayStart { get; set; }

///
/// Gets or sets the iDisplayLength.
///
[DataMember]
public int iDisplayLength { get; set; }

///
/// Gets or sets the mDataProp_0.
///
[DataMember]
public int mDataProp_0 { get; set; }

///
/// Gets or sets the mDataProp_1.
///
[DataMember]
public int mDataProp_1 { get; set; }

///
/// Gets or sets the mDataProp_2.
///
[DataMember]
public int mDataProp_2 { get; set; }

///
/// Gets or sets the mDataProp_3.
///
[DataMember]
public int mDataProp_3 { get; set; }

///
/// Gets or sets the mDataProp_4.
///
[DataMember]
public int mDataProp_4 { get; set; }

///
/// Gets or sets the sSearch.
///
[DataMember]
public string sSearch { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bRegex { get; set; }

///
/// Gets or sets the sSearch_0.
///
[DataMember]
public string sSearch_0 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bRegex_0 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSearchable_0 { get; set; }

///
/// Gets or sets the sSearch_1.
///
[DataMember]
public string sSearch_1 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bRegex_1 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSearchable_1 { get; set; }

///
/// Gets or sets the sSearch_2.
///
[DataMember]
public string sSearch_2 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bRegex_2 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSearchable_2 { get; set; }

///
/// Gets or sets the sSearch_3.
///
[DataMember]
public string sSearch_3 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bRegex_3 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSearchable_3 { get; set; }

///
/// Gets or sets the sSearch_4.
///
[DataMember]
public string sSearch_4 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bRegex_4 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSearchable_4 { get; set; }

///
/// Gets or sets the iSortCol_0.
///
[DataMember]
public int iSortCol_0 { get; set; }

///
/// Gets or sets the sSortDir_0.
///
[DataMember]
public string sSortDir_0 { get; set; }

///
/// Gets or sets the iSortingCols.
///
[DataMember]
public int iSortingCols { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSortable_0 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSortable_1 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSortable_2 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSortable_3 { get; set; }

///
/// Gets or sets a value indicating whether TBD.
///
[DataMember]
public bool bSortable_4 { get; set; }

///
/// Gets or sets the MachineId.
///
[DataMember]
public int MachineId { get; set; }

///
/// Gets or sets the AlarmLevelNames.
///
[DataMember]
public string[] AlarmLevelNames { get; set; }

///
/// Gets or sets the SubsystemNames.
///
[DataMember]
public string[] SubsystemNames { get; set; }

///
/// Gets or sets the Message.
///
[DataMember]
public string Message { get; set; }

///
/// Gets or sets the StartTime.
///
[DataMember]
public DateTime StartTime { get; set; }

///
/// Gets or sets the EndTime.
///
[DataMember]
public DateTime EndTime { get; set; }
}
[/code]

robertbrower

If you're using a classic asp page then you'd be deserializing the query string I guess. But it is better to use a web service in my mind.