Server side processing + c# Editor 1.4.0-beta

dagnew37

Hello,

Hoping someone can help a newbie to DataTables & Editor. I'm using the datatables editor c# beta package. https://editor.datatables.net/download/

I'm working with a table with a large number of rows (250,000 + and growing) and therefore trying to implement server side processing. Is it possible to create custom filtering to be selective on the first data returned? to create a custom where clause? Ive tried this using data function in the ajax call using the example here but not sure how this should be implemented: http://datatables.net/examples/server_side/custom_vars.html

$('#example').DataTable({
            "columnDefs": [
            { "visible": false, "targets": [6,7,8,26,27,28,29,31,32,33,34,39,40,41,45,46] }
                ],
            displayLength: 100,   //rows per page
            sScrollX: "100%",
            sScrollXInner: "110%",
            scrollY: 400,
            scrollX: true,
            deferRender: true,
            dom: "Tfrtip",
            //dom: '<"toolbar">frtip',
            ajax: {           
                url: "/api/staff",
                type: "POST",
                data: function ( d ) {
                    d.Key = "id",
                    d.Value = "123",
                    d.Operator = "="
                }
            },
            serverSide: true,
            processing: true,
            length: 100, //rows from server
            columns: [
            etc...

Any pointers would be much appreciated.

From reading around fnServerData looks to be the way to go, but not sure how to implement.

Thanks

allan

fnServerData has effectively been replaced by ajax in 1.10, which you are already using.

If you want to apply a where condition, you can use the where() method on the server-side - however this shouldn't really be needed if you are using server-side processing (unless of course you need it!).

The Editor class will automatically detect and use server-side processing request data, assuming it can be read from the POST input.

Perhaps you can link to the page so I can see what is happening please?

Allan

dagnew37

Im starting to understand how the out of box column search works using example here: https://datatables.net/examples/api/regex.html.

I need to implement custom search (where) for a couple of reasons. The main reason being Boolean columns. When you use the column search, it creates a sql statement with a where clause such as select * from myTable where myCol LIKE '%searchVal%' (@where_0=N'%True%'). This isnt working for booleans.

I understand the function and the parameters which need to be passed in on the where clause http://editor.datatables.net/docs/1.4.0-beta/net/html/71b35ab7-94c5-ed2b-a001-1b55f3127fc6.htm, but Im struggling to work out how and where to implement the where clause. Basically how to code and where it fits in.

Something like this using the data function doesnt work. How and where do we implement the where?

ajax: {
url: "api/EGTransactionsCNINV",
type: "POST"

                ,
                data: function ( d ) {
                    d.Key = "ExcludeFromInvoicing",
                    d.Value = "True",
                    d.Operator = "="
                }
            }, 

Is there an example anyone can point me to. c# would be ideal.

thanks

allan

The example you linked to uses client-side processing for regular expressions. But then you mention server-side processing with SQL. Which is it that you are using?

The Editor libraries on the server-side (C# and PHP) don't support regex filtering. Do you need server-side processing? Are you using 50'000+ rows?

Allan

dagnew37

Its a relatively big table - 200K rows but growing 15K per week - so need server side proceesing.

The aim is to use datatables editor but have custom input params to drive the rows returned (where clause). The params will be the following https://app.box.com/s/nqovikkrazzyo29abiy6gpadubruvboe

Im just not sure how and where to put the where clause(!). An example would really help.

This works well when using standard data tables using own web service to pull the data. i.e. https://app.box.com/s/6txctmz030bivrugzj3wyk9mgxaxi730
Here I put the parameter values in the data attribute JSON.stringify(DTO).

$.ajax({
type: "POST",
url: "/Webservices/EGws.asmx/PriceListLookup",
data: JSON.stringify(DTO),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (json) {
//console.log(json);
//console.log(json.d.aaData);
$('#DataTable').dataTable({
"aaData": json.d.aaData,

An example like above to help me in the right direction would be great. Is it something that goes in the ajax call? and how is it structured? i.e.

ajax: {
url: "api/Example",
type: "POST",
where: { key: "Shipment", value: "SM060", op: '=' }
},

thanks

allan

There is no where option is the ajax configuration (it is just a standard jQuery ajax object more or less). So you would need to use ajax.data to send the extra data to the server and then update the server-side script to do whatever filtering you need.

Allan

dagnew37

ok - thanks Allan - got this working now. Thanks again

allan

Best way is probably just to use the controller's Request object as you would for any other HTTP parameter.

Allan