Here is some of my C# code of datatables and server-side/client-side
You can use this to translate it to python if you want, I translated this from Alans PHP example
Clientside:
/* Instantiate the Datatables on the ASP.NET GridView */
var dt = $('#gvJobs').dataTable({
"sDom": 'C<"clear">Rlfrtip', /* ColReOrder, ColVis */
"bFilter": true, /* Use custom filters, i have to revise this as I am doing the drop down filter manually now */
"bSort": true,
"bAutoWidth": false,
"bProcessing": true, /* Needed, Read Datatables documentation */
"bServerSide": true, /* Needed, Read Datatables documentation */
"aaSorting": [[0, "desc"]], /* Initial Default Sorting on the First Column */
"sPaginationType": "full_numbers",
"fnServerData": function (sSource, aoData, fnCallback) {
/* Adding Custom Drop Down Filter property to aoData which will be used server side */
aoData.push({ name: "ddlStatus", value: $("#ddlStatus option:selected").text() });
/* Here I do the PageMethods call (ASP.NETs Ajax Replacement) PageMethods.getData
"getData" is a Server Side C# Method which looks like this:
[WebMethod]
public static string getData(List<oaData> aoData)
{
//Method Code
}
With page methods you pass in all your parameters matching the server side method,
And right at the end, add your two callback functions for onsuccess and onerror
*/
PageMethods.getData(aoData, function (obj) {
/* On Success, datatables fnCallback with JSON object */
fnCallback(JSON.parse(obj));
}, function (XMLHttpRequest, textStatus, errorThrown) {
/* On Error, show error alert */
alert(XMLHttpRequest.status + ': ' + XMLHttpRequest.responseText);
});
},
"aoColumns": [
/* This (JobNo) my first column which is Custom with 3 Images and a HyperLink to another page,
The last 5 Columns is hidden from the User because those fields are only used to
Render the correct images in this first column, Only used as Eval fields basically
Note that mDataProp is the GridView column's "DataField" and not the "HeaderText"
*/
{ "mDataProp": "JobNo",
"fnRender": function (oObj) {
return "<img src='" + (oObj.aData.HasAttachments ? "css/paperclip-icon.png" : "css/empty-icon.png") + "' height='16px' />"
+ "<a href='#' onclick=\"gotoJobDetail('" + oObj.aData.JobId + "');\">" + oObj.aData.JobNo + "</a>"
+ "<img src='" + (oObj.aData.Visited ? "css/yes.png" : "css/no.png") + "' height='16px' />"
+ "<img src='" + (oObj.aData.Completed ? "css/completed.png" : "css/notcompleted.png") + "' height='16px' />";
},
"bUseRendered": true /* This renders the Column in HTML first, else you'd see the actual HTML text in the column */
},
{ "mDataProp": "JobStatusName" },
{ "mDataProp": "StatusDateTime" },
/* Only used as Eval Field for one of the images in the JobNo Column */
{ "mDataProp": "HasAttachments",
"bSearchable": false,
"bVisible": false
},
/* Only used as Eval Field for one of the images in the JobNo Column */
{"mDataProp": "Visited",
"bSearchable": false,
"bVisible": false
},
/* Only used as Eval Field for one of the images in the JobNo Column */
{"mDataProp": "Completed",
"bSearchable": false,
"bVisible": false
},
/* Just Hidden, not used at the moment neither by the user nor the application */
{"mDataProp": "JobStatusId",
"bSearchable": false,
"bVisible": false
},
/* Only used as Eval Field for one of the images in the JobNo Column */
{"mDataProp": "JobId",
"bSearchable": false,
"bVisible": false
}
]
});
Server Side Objects
//INCOMING OBJECT FROM CLIENTSIDE
public class oaData
{
public string name { get; set; }
public string value { get; set; }
}
//RETURNING OBJECT TO CLIENTSIDE
public class oaData<T>
{
public int sEcho { get; set; }
public int iTotalRecords { get; set; }
public int iTotalDisplayRecords { get; set; }
public T aaData { get; set; }
public string sColumns { get; set; }
}
//THIS IS 'T' IN oaData<T>
public class JobOverviewDynamic
{
public int JobNo { get; set; }
public string JobStatusName { get; set; }
public string StatusDateTime { get; set; }
public bool HasAttachments { get; set; }
public bool Visited { get; set; }
public bool Completed { get; set; }
public int JobStatusId { get; set; }
public int JobId { get; set; }
}
Populate the object, then JSON stringify it with Newtonsoft library and return
HERE is my Serverside method
[WebMethod]
public static string getData(List<oaData> aoData)
{
/* The Columns array
* If the array from Parameters is not empty, then use that
*/
string[] aColumns = { "JobNo", "JobStatusName", "StatusDateTime", "HasAttachments", "Visited", "Completed", "JobStatusId", "JobId" };
var newCols = aoData.Where(n => n.name == "sColumns").Select(n => n.value).FirstOrDefault().Split(',');
aColumns = (newCols.Length == 1 && newCols[0] == "") ? aColumns : newCols;
/* Paging */
var iDisplayStart = aoData.Where(n => n.name == "iDisplayStart").Select(n => n.value).FirstOrDefault();
var iDisplayLength = aoData.Where(n => n.name == "iDisplayLength").Select(n => n.value).FirstOrDefault();
/* Ordering */
var sOrder = "";
var iSortCol_0 = aoData.Where(n => n.name == "iSortCol_0").Select(n => n.value).FirstOrDefault();
if (iSortCol_0 != null && aColumns.Length > 0)
{
sOrder = "ORDER BY ";
var iSortingCols = aoData.Where(n => n.name == "iSortingCols").Select(n => int.Parse(n.value)).FirstOrDefault();
for (int i = 0; i < iSortingCols; i++)
{
var iSortCol = aoData.Where(n => n.name == "iSortCol_" + i).Select(n => int.Parse(n.value)).FirstOrDefault();
var bSortable = aoData.Where(n => n.name == "bSortable_" + iSortCol).Select(n => bool.Parse(n.value)).FirstOrDefault();
if (bSortable)
{
var sSortDir = aoData.Where(n => n.name == "sSortDir_" + i).Select(n => n.value).FirstOrDefault();
sOrder += aColumns[iSortCol] + " " + sSortDir + ", ";
}
}
sOrder = sOrder.Trim();
sOrder = sOrder.Substring(sOrder.Length - 1) == "," ? sOrder.Substring(0, sOrder.Length - 1) : sOrder;
sOrder = (sOrder == "ORDER BY") ? "" : sOrder;
}
/* Filtering
*/
var sWhere = "";
var sSearch = aoData.Where(n => n.name == "sSearch").Select(n => n.value).FirstOrDefault();
var status = aoData.Where(n => n.name == "ddlStatus").Select(n => n.value).FirstOrDefault();
if (sSearch != "" && aColumns.Length > 0)
{
sWhere = "WHERE (";
for (int i = 0; i < aColumns.Count(); i++)
{
sWhere += aColumns[i] + " LIKE '%" + sSearch + "%'" + (i < aColumns.Count() - 1 ? " OR " : " ");
}
sWhere = sWhere.Trim() + ")";
}
/* Individual column filtering */
for (int i = 0; i < aColumns.Count(); i++)
{
var bSearchableCol = aoData.Where(n => n.name == "bSearchable_" + i).Select(n => bool.Parse(n.value)).FirstOrDefault();
var sSearchCol = aoData.Where(n => n.name == "sSearch_" + i).Select(n => n.value).FirstOrDefault();
if (bSearchableCol && sSearchCol != "")
{
sWhere = (sWhere == "") ? "WHERE " : sWhere + " AND ";
sWhere += aColumns[i] + " LIKE '%" + sSearchCol + "%' ";
}
/* Added this second if statement for my custom drop down filter on a specific column
* The fnFilter setting with a column number does not work, and this way, ColReorder will still work
*/
if (status != "" && aColumns[i] == "JobStatusName")
{
sWhere = (sWhere == "") ? "WHERE " : sWhere + " AND ";
sWhere += aColumns[i] + " = '" + status + "' ";
}
}
/* SQL queries
* Get data to display
*/
List<LTS.JobOverviewDynamicResult> data;
using (var m = new Methods())
{
//Im not giving my SQL query
data = m.getJobOverviewDynamicData(sWhere, sOrder, iDisplayStart, iDisplayLength);
}
int iFilteredTotal = 0;
int iTotal = 0;
if (data.Count() > 0)
{
/* Filtered dataset and Total dataset length */
iFilteredTotal = (int)data.FirstOrDefault().ITotalFiltered;
iTotal = (int)data.FirstOrDefault().ITotal;
}
/* Output */
var sEcho = aoData.Where(n => n.name == "sEcho").Select(n => int.Parse(n.value)).FirstOrDefault();
var output = new oaData<List<JobOverviewDynamic>>()
{
sEcho = sEcho,
iTotalRecords = iTotal,
iTotalDisplayRecords = iFilteredTotal,
aaData = data.Select(n => new JobOverviewDynamic()
{
JobNo = (int)n.JobNo,
JobStatusName = n.JobStatusName,
StatusDateTime = ((DateTime)n.StatusDateTime).ToString("yyyy-MM-dd hh:mm"),
HasAttachments = (bool)n.HasAttachments,
Visited = (bool)n.Visited,
Completed = (bool)n.Completed,
JobStatusId = (int)n.JobStatusId,
JobId = (int)n.JobId
}).ToList(),
sColumns = string.Join(",", aColumns)
};
return JsonConvert.SerializeObject(output);
}
I hope this makes somewhat sense, if not - ASK and I shall assist.
