Server Side Call without a Draw

akorff

Hi, All.

I've been using datatables with server-side processing for quite a while. I absolutely love the flexibility and extensibility (I have a pretty cool post-sort display override using fnRowCalls I should probably post one of these days). I'm also using fnFormatDetails (where you can hide and show record details) with ajax loading of entire web pages. This is a very powerful architecture in my opinion (I'll post a screenshot of what I mean if anyone is interested).

Anyway, on to my question. I'd like to accomplish something that I'm not sure is possible. It all comes down to calling the server-side web-service that reads the filter box, current page, display length, sort column, and sort direction on the web service without redrawing the table.

Let me explain...

As of late I've been getting into ClosedXML which is basically a wrapper for OpenXML which let's you create spreadsheets without needing Excel on the server. Since I already have a nice set of SQL stored procs that works with the parameters passed from the datatable (e.g. filter, sort column, and various things I'm sending via aodata.push), I'd like to refactor the server side code I already have for datatables, just sort of skip that last part where it passes the JSON back, (or it can pass the JSON back but I don't want to redraw the table). Instead, when I'm reading through the recordset, I would just talk to Excel using ClosedXML. I've tried various things but either I kill the server-side call completely, or it gets called but the table refreshes.

I should note that I do currently use TableTools which is very cool, but it just creates a csv, and certainly doesn't let me load in existing Excel templates which I can do with ClosedXML.

Worse-case, I'll just make the server-side call, let the datatable do its thing, and piggy back onto it to create the spreadsheet, but that just feels like such an unnecessary use of resources. Also it will kill any existing UI activity with the record detail forms (that I mentioned in the first paragraph) so I'd really like to avoid having to go that route. Alternately, if I could figure out a way to pass in the datatable parameters (filter, sort, etc.) into a different web-service proc that doesn't service the table, that would also work.

Thanks in advance for any help with this.

allan

> I have a pretty cool post-sort display override using fnRowCalls I should probably post one of these days

Sounds interesting :-)

Good question! So basically you want the parameters that DataTables would send to the server?

There is an internal function which generates the Ajax parameters for a server-side processing request, so a little API function would expose it:

[code]
$.fn.dataTableExt.oApi.fnAjaxParams = function ( settings )
{
return this.oApi._fnAjaxParameters( settings );
};
[/code]

All the usual cravats about using internal functions apply (might break on a major upgrade etc), but that will at least get the information you are looking for. Then you can make a custom Ajax call.

> I should note that I do currently use TableTools which is very cool, but it just creates a csv, and certainly doesn't let me load in existing Excel templates which I can do with ClosedXML.

Yup - its a cheap trick that TableTools uses there. I'll look into ClosedXML - it sounds interesting. Certainly an XLSX output is something I plan on looking at in future.

Allan

akorff

Thanks Allan! I'll try it and let you know. I believe ClosedXML only supports .NET but there might be something more generic that wraps OpenXML out there. I'll be more than happy to share my source code but ClosedXML basically does all the heavy lifting (e.g. a few lines to instantiate the workbook or load an existing one and then it's just writing to the cells).

The sorting function I mentioned ("override" is probably a strong word) basically just takes a different UI approach based on the column being sorted. My code is far from elegant. I'm sure there is a better way for me to get the sort column rather than carrying it in a data column. In fact, your response to my original post would probably do the trick there.

The basic algorithm is using a static variable (staticlast) that is dimensioned further up the page which "remembers" what the previous value in the sort column was (the last time fnRowCallback was called) and if it changes, it adds some classes to add a visual separation. The assumption is that the user will want to visually group the column s/he is sorting. I'm guessing there is a way to do this more generically so I don't have to manually edit the switch statement every time I add or move columns, but I would want to exclude certain columns from changing the UI.

I included some images below of how it appears visually.

[code]
"fnRowCallback": function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {
sortname = aData[18]; //location of hidden sort column name, returned in dataset
switch (sortname) {
case 'TruckNumber':
sortcol = 5;
break;
case 'Sts':
sortcol = 6;
break;
case 'Begin City':
sortcol = 13;
break;
case 'End City':
sortcol = 15;
break;
default:
sortcol = 99;
}
endstring = '';
if (sortcol != 99) {
if (staticlast != aData[sortcol]) {
//new group
endstring = 'end';
if (nextcss == 'oddgroup') {
nextcss = 'evengroup';
}
else {
nextcss = 'oddgroup';
}
}
finalcss = nextcss + endstring;
$('td', nRow).addClass(finalcss);
$('td:eq(' + (sortcol - 1).toString() + ')', nRow).css("font-weight", "bold");
staticlast = aData[sortcol];
}
},
[/code]

I'm not sure how to link images so I'm just posting some imgur links here.

This is what it looks like when you sort on End City:
http://i.imgur.com/uOkgztF.png

This is what it looks like when you sort on Status (Sts):
http://i.imgur.com/oFqnWPr.png

On another topic, this is what I meant that you can use an entirely independent page in the hidden divs. A simple $("#divname").load("/yourpagename") does it with a slight modification to the fnOpen/fnClose example from the examples page (if I knew how to link to the examples page, I would). I also created a slightly tweaked version of the fnOpen and fnClose function so the first time it will load the page, but afterword it will only hide and show that TR (so if there is any activity on the flyout page like checking and unchecking boxes or filling in inputs, it will not get refreshed and lose the UI activity). The yellow border was added in photoshop to indicate where the new page is and the gray rectangles is to keep private info hidden.
http://i.imgur.com/gouEETK.png

Anyway, thanks again for the quick response and guidance, Allan. I'll give it a try.

akorff

Worked perfect, thanks! You're a miracle worker, Allan.

For anyone else interested - to get the sSearch parameter, for example:

[code]
function TestIt() {
var ServerParams;
ServerParams = oTable.fnAjaxParams();
for (var i = 0; i < ServerParams.length; i++) {
if (ServerParams[i].name == 'sSearch') {
alert(ServerParams[i].value);
}
}
//alert('Parameter #' + i.toString() + ' is ' + ServerParams[i].name + ' with a value of ' + ServerParams[i].value);
};

$.fn.dataTableExt.oApi.fnAjaxParams = function (oSettings) {
return this.oApi._fnAjaxParameters(oSettings);
};
[/code]

Now it's just a matter of grabbing the parameters I need, and making a separate ajax call to a web service that produces the xlsx file.

Thanks again!

On an unrelated topic...the Amazon wishlist...can I assume anything on there hasn't been purchased yet?

allan

Very nice - great to hear that did the business for you. And thanks for showing us your resulting method.

Also yes, the wish list is bang up-to-date :-)

Thanks,
Allan

akorff

I have a strong feeling that a couple of those 7/23 entries did not stay on the wishlist for too long ;)

Enjoy.

Aaron