Calling C#, ASP.NET, Perl, Python and anything else developers
Calling C#, ASP.NET, Perl, Python and anything else developers
Hello all,
As you all know one of the big benefits of DataTables is it's sever-side processing option. With this you can use massive databases with DataTables doing all the display work - but this requires a script on the server-side to do some processing. I have my example PHP script, but what would be really excellent is if we could give example scripts for how to do this in other languages such as C#, ASP.NET, Perl, Python etc.
As such what I would like to do is create a "gallery" of server-side scripts, which would work with the same basic data set as I use (the SQL is available here: http://datatables.net/release-datatables/examples/examples_support/data.sql), so developers can then go to that to see an example using their language of choice and customise it as they require.
If you would like contributing to this, please post back here, or drop me a note ( http://datatables.net/contact ) and I'll try to set this up.
Regards,
Allan
As you all know one of the big benefits of DataTables is it's sever-side processing option. With this you can use massive databases with DataTables doing all the display work - but this requires a script on the server-side to do some processing. I have my example PHP script, but what would be really excellent is if we could give example scripts for how to do this in other languages such as C#, ASP.NET, Perl, Python etc.
As such what I would like to do is create a "gallery" of server-side scripts, which would work with the same basic data set as I use (the SQL is available here: http://datatables.net/release-datatables/examples/examples_support/data.sql), so developers can then go to that to see an example using their language of choice and customise it as they require.
If you would like contributing to this, please post back here, or drop me a note ( http://datatables.net/contact ) and I'll try to set this up.
Regards,
Allan
This discussion has been closed.
Replies
Serer-side code(i used asp.net-mvc and entity framework)
[code]
public ActionResult GetData(int iDisplayStart, int iDisplayLength, string sSearch, bool bEscapeRegex, int iColumns, int iSortingCols, int iSortCol_0, string iSortDir_0, int sEcho)
{
//get the total number of records
var query = (from x in ctx.HeroSet
select x).AsQueryable();
int total = query.Count();
//filtering
if (!string.IsNullOrEmpty(sSearch))
{
query = from x in query
where x.Name.Contains(sSearch)
select x;
}
//sorting
if (iSortingCols > 0)
{
bool basc = (iSortDir_0 == "asc");
switch (iSortCol_0)
{
case 0:
query = basc ? query.OrderBy(x => x.Id) : query.OrderByDescending(x => x.Id);
break;
case 1:
query = basc ? query.OrderBy(x => x.Name) : query.OrderByDescending(x => x.Name);
break;
case 2:
query = basc ? query.OrderBy(x => x.Level) : query.OrderByDescending(x => x.Level);
break;
case 3:
query = basc ? query.OrderBy(x => x.Race) : query.OrderByDescending(x => x.Race);
break;
default:
query = basc ? query.OrderBy(x => x.Id) : query.OrderByDescending(x => x.Id);
break;
}
}
//paging
int totaldisp = query.Count();
if (iDisplayLength > 0)
query = query.Skip(iDisplayStart).Take(iDisplayLength);
//creting the object to send to the client
var data = new
{
sEcho = sEcho,
iTotalRecords = total,
iTotalDisplayRecords = totaldisp,
aaData = GetStrings(query)
};
JsonResult json = Json(data);
return json;
}
private string[] GetString(Hero h)
{
return new string[] {h.Id.ToString(),h.Name,h.Level.ToString(),h.Race };
}
private string[][] GetStrings(IQueryable hs)
{
int length=hs.Count();
int i=0;
string[][] s=new string[length][];
foreach (var h in hs)
{
s.SetValue(GetString(h), i);
i++;
}
return s;
}
[/code]
Client-side code
[code]
$(function() {
$('#example').dataTable({
"bProcessing": true,
"bServerSide": true,
"bAutoWidth": false,
"aoColumns": [
{ "sTitle": "HeroId" },
{ "sTitle": "Name" },
{ "sTitle": "Level" },
{ "sTitle": "Race" }],
"sAjaxSource":'<%=Url.Action("GetData","Home")%>'
});
});
[/code]
I hope this will help you.
Allan, is there any option to send a json in the client-side and there to specify the data format that is expected because this string conversion seems horrible to me but i wanted this to work , anyway, if you guys find a better solution for sending a json and not a string[][] in the client-side please post it here.
Thanks very much for your code.
One question about it - where does the data come from? Is there a SQL database somewhere? Does it need to do a connect or anything? Lines 9-11, wow - didn't know you could do that! I guess SQL in integrated very closely... Also in the Url.Action - I guess the first parameter is the function name which gets called, but what is the 'Home' for?
One other thing, does the ASP.NET script you posted cope with everything my PHP one does (just for feature parity so all the server-side scripts are the same)? Namely: filtering on all columns (OR combination), filtering on individual columns, paginating and ordering (I'm sure it does do the last two from the code, but not sure about the former two).
Regarding your own question - the only data format that DataTables expects is the 2D array, with string elements. In my PHP scripts, I just echo the JSON out, rather than using the built in parsers - I'm not sure what the best way might be here.
Regards,
Allan
2)Url.Action is a method that takes 2 parameters: action(the method) and the controller and returns the absolute path to that method(which can return a json or a view(page) or whatever you want)
3)I added some comments out there. I don't know much about php but from what i've seen it seems to be the same.
4)I really don't like those conversion methods also
5)Tomorrow i will try out some other features and more than sure i will bother you again :)
6)I want to create a application with many features of , i have to repeat myself, this great plugin and post it here.
nice work with DataTables: I'm about to try and push it into some work I've been doing with Ruby on Rails, and so was hunting around for documentation. What I found regarding the integration of DataTables into Rails was Christopher Warren's plugin (with documentation) at
http://github.com/phronos/rails_datatables
This should get things underway for anyone using this combination of tools.
Cheers,
Paul
Nice one - thanks very much for the link! I'm sure that will prove to be most useful.
Regards,
Allan
Allan, I was just thinking about contacting you about my Rails plugin. Hopefully it can help some people out. Any input anyone has would be appreciated. We're actively using it in our own development and I expect to continue adding features as we need them.
Thanks Allan for the DataTables plugin, it's been very handy for us.
Cheers,
Christopher
I'm headed in that direction.
DataTables is a great project.
I am sorry I don't use your database but I think that a small example is better than nothing. Erase the comment otherwise.
I have been experimenting with C#,an "ordinary" web service and JSON.
Server side:
[code]
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class MyWebService : System.Web.Services.WebService
{
[WebMethod]
public List GetAll()
{
var db = new DataClasses1DataContext(); // Linq to SQL.
var query = from i in db.Issues select i;
var ret = new List();
foreach (var i in query)
{
ret.Add(new Issue(i.IssueID, i.Name, i.Date, i.Prio));
}
return ret;
}
}
public class Issue
{
public int IssueID { get; set; }
public string Name { get; set; }
public DateTime DateTime { get; set; }
public int Prio { get; set; }
public Issue() { }
public Issue(int issueID, string name, DateTime datetime, int prio)
{
Set( issueID, name, datetime, prio );
}
public void Set(int issueID, string name, DateTime datetime, int prio)
{
this.IssueID = issueID;
this.Name = name;
this.DateTime = datetime;
this.Prio = prio;
}
}
[/code]
Client side:
[code]
function loadData() {
$.ajax({
url: "MyWebService.asmx/GetAll",
data: "{}",
success: function(lstIssue) {
for (var i = 0; i < lstIssue.length; i++) {
var issue = lstIssue[i];
oTable.fnAddData(["" + issue.IssueID, issue.Name, "" + issue.Date, "" + issue.Prio, "?", "?"]);
}
}
});
}
$(document).ready(function() {
// Make a basic setup so we don't have to type all parameters at every ajax call.
// http://encosia.com/2009/07/21/simplify-calling-asp-net-ajax-services-from-jquery/#more-888
$.ajaxSetup({
type: "POST",
contentType: "application/json; charset=utf-8",
data: "{}",
dataFilter: function(data) {
var msg;
if (typeof (JSON) !== 'undefined' &&
typeof (JSON.parse) === 'function')
msg = JSON.parse(data);
else
msg = eval('(' + data + ')');
if (msg.hasOwnProperty('d'))
return msg.d;
else
return msg;
}
});
loadData();
}
[/code]
/OF
The idea of sharing code to use with datatables is great! We've been recently looking for something like that for our Django(http://djangoproject.com) projects and we ended with developing our solution. Since this code is pretty new it probably can be improved (all comments are welcome) but at the momment it covers all our need according to datatables usage with Django.
Example Django project where we show how we use datatables in two modes:
- 'load once' - pretty straight-forward, no server side processing
- 'server side' - code that handles datatables request and prepares valid json used to generate datatable
Code should be very easy to understand for those who completed Django tutorial (http://docs.djangoproject.com/en/1.1/intro/tutorial01/) or wrote simple django project. All demos are done for simple Country model (sqlite database with data is provided). More information in README.txt in project directory.
Code of this demo is hosted at:
http://www.assembla.com/spaces/datatables_demo/
zipped versions are at:
https://www.assembla.com/spaces/datatables_demo/documents
Hope it helps someone. All comments are welcome. This code can probably be easily changed to use in other Python web frameworks.
Kind Regards,
Lukasz Dziedzia
Here's a link to the code file:
http://weblogs.asp.net/blogs/zowens/DataTables.zip
The code is too long to post here. So take a look at the download.
Thanks
Zack
http://weblogs.asp.net/zowens/archive/2010/01/19/jquery-datatables-plugin-meets-c.aspx
what's up? Did anything move regarding the "gallery"?
Best regards, Alex
Still very much planned - just not had a chance to get around to it yet! Time for support for DataTables is really starting to mount up! Hopefully I'll get a chance to get around to this sometime soon and it will be a great thing to have.
Regards,
Allan
As a few others I also work with ASP.NET MVC, and have created my own code and not used the one Bogus had, because I thought his code was a bit verbose, the search only worked for the name, and the sorting is just not my coding style. So here's the code:
[code]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web.Mvc;
namespace MvcAndDataTablesServerSide.Controllers
{
[HandleError]
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
public JsonResult GetPersons(int iDisplayStart, int iDisplayLength,
string sSearch, bool bEscapeRegex, int iColumns,
int iSortingCols, int iSortCol_0, string sSortDir_0,
int sEcho)
{
var list = GetPersons();
var filteredlist =
list
.Select(x => new[] {x.Name, x.Number.ToString(), x.Date.ToShortDateString()})
.Where(x => string.IsNullOrEmpty(sSearch) || x.Any(y => y.IndexOf(sSearch, StringComparison.InvariantCultureIgnoreCase) >= 0));
var orderedlist = filteredlist
.OrderByWithDirection(x => (x[iSortCol_0]).Parse(), sSortDir_0 == "desc")
.Skip(iDisplayStart)
.Take(iDisplayLength);
var model = new
{
aaData = orderedlist,
iTotalDisplayRecords = filteredlist.Count(),
iTotalRecords = list.Count(),
sEcho = sEcho.ToString()
};
return Json(model);
}
private IEnumerable GetPersons()
{
var list = new List();
for (int i = 0; i < 10000; i++)
list.Add(new Person { Name = "Gaute" + i, Number = i, Date = DateTime.Now.AddDays(-i) });
return list;
}
}
internal class Person
{
public string Name { get; set; }
public int Number { get; set; }
public DateTime Date { get; set; }
}
public static class QueryHelpers
{
public static IOrderedEnumerable OrderByWithDirection
(this IEnumerable source,
Func keySelector,
bool descending)
{
return descending
? source.OrderByDescending(keySelector)
: source.OrderBy(keySelector);
}
public static IOrderedQueryable OrderByWithDirection
(this IQueryable source,
Expression keySelector,
bool descending)
{
return descending ? source.OrderByDescending(keySelector)
: source.OrderBy(keySelector);
}
public static object Parse(this string s)
{
int i;
DateTime d;
if (int.TryParse(s, out i))
return i;
if (DateTime.TryParse(s, out d))
return d;
return s;
}
}
}
[/code]
The full solution is also available on GitHub at: http://github.com/gautem/MVC-DataTables
In my example I just create a list of persons, but the code can be used on any IEnumerable or IQueryable. Using the IQueryable interface in combination with Linq to Nhibernate, Linq to SQL or Entity Framework or something else, and the sorting, filtering ++ will be done in the database.
Regards Gaute
Initialisation
[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.cfm"
} );
} );
[/code]
Server side ColdFusion (this leaves out error checking that should be in place, but should get somebody going if they were stuck) I used the tag based syntax rather than scripting, because more people are familiar with that.
[code]
<!--- yeah, this is lazy --->
SELECT SQL_CALC_FOUND_ROWS id, engine, browser, platform, version, grade
FROM ajax
WHERE engine LIKE OR
browser LIKE OR
platform LIKE OR
version LIKE OR
grade LIKE
ORDER BY , #fnColumnToField(url["iSortCol_"&thisS])# #url["sSortDir_"&thisS]#
SELECT COUNT(id) as total
FROM ajax
{"sEcho": #val(url.sEcho)#, "iTotalRecords": #qCount.total#, "iTotalDisplayRecords": #qFiltered.recordCount#, "aaData": [ ,["#jsStringFormat(engine)#","#jsStringFormat(browser)#","#jsStringFormat(platform)#","-""#version#","#jsStringFormat(grade)#"] ] }
[/code]
Regards,
Allan
I'm interested to use the datatables in coldfusion however I'm not able to run it. Could you please attach an example page if you wouldn't mind. Thanks
the code above should replace the initialization and server_processing pages from
http://datatables.net/examples/server_side/server_side.html
the data it uses came from the top of this thread
If you haven't already, I would highly recommend you installing firebug plugin for firefox, and watching console tab.
That's what I did. Currently I'm using cf9 and put the server_processing.cfm in the same folder. Then I edited the initialization to change the cf version for processing page in server_side.html
But I only getting the "Loading Data" message as if there is nothing to load the data.
I tried to use the firebug but I dont know how to use it.
Maybe you have a clue on this?
I think that I have already did everything right but still no luck.
If not causing too much trouble, could you put the whole files in zip and email to me which you can get the info from my profile or to any other filesharing available
Server side code
[code]
<%@page import="java.util.*"%>
<%@page import="com.chest.web.db.ConnectManager"%>
<%@page import="java.sql.*"%>
<%@page import="org.json.*"%>
<%
String[] cols = { "engine", "browser", "platform", "version","grade" };
JSONObject result = new JSONObject();
JSONArray array = new JSONArray();
int amount = 10;
int start = 0;
int echo = 0;
int col = 0;
String dir = "asc";
String sStart = request.getParameter("iDisplayStart");
String sAmount = request.getParameter("iDisplayLength");
String sEcho = request.getParameter("sEcho");
String sCol = request.getParameter("iSortCol_0");
String sdir = request.getParameter("sSortDir_0");
if (sStart != null) {
start = Integer.parseInt(sStart);
if (start < 0)
start = 0;
}
if (sAmount != null) {
amount = Integer.parseInt(sAmount);
if (amount < 10 || amount > 100)
amount = 10;
}
if (sEcho != null) {
echo = Integer.parseInt(sEcho);
}
if (sCol != null) {
col = Integer.parseInt(sCol);
if (col < 0 || col > 5)
col = 0;
}
if (sdir != null) {
if (!sdir.equals("asc"))
dir = "desc";
}
String colName = cols[col];
int total = 0;
Connection conn = ConnectManager.getConnection();
try {
String sql = "SELECT count(*) FROM ajax";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()){
total = rs.getInt("count(*)");
}
}catch(Exception e){
}
int totalAfterFilter = total;
//result.put("sEcho",echo);
try {
String searchSQL = null;
String sql = "SELECT * FROM ajax";
String searchTerm = request.getParameter("sSearch");
if(searchTerm!=""){
searchSQL = " where (engine like '%"+searchTerm+"%'"
+ " or browser like '%"+searchTerm+"%'"
+ " or platform like '%"+searchTerm+"%'"
+ " or version like '%"+searchTerm+"%'"
+ " or grade like '%"+searchTerm+"%')";
sql +=searchSQL;
}
sql += " order by " + colName + " " + dir;
sql += " limit " + start + ", " + amount;
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
JSONArray ja = new JSONArray();
ja.put(rs.getString("engine"));
ja.put(rs.getString("browser"));
ja.put(rs.getString("platform"));
ja.put(rs.getString("version"));
ja.put(rs.getString("grade"));
array.put(ja);
}
String sql2 = "SELECT count(*) FROM ajax";
if (searchTerm != "") {
sql2 += searchSQL;
PreparedStatement ps2 = conn.prepareStatement(sql2);
ResultSet rs2 = ps2.executeQuery();
if (rs2.next()) {
totalAfterFilter = rs2.getInt("count(*)");
}
}
result.put("iTotalRecords", total);
result.put("iTotalDisplayRecords", totalAfterFilter);
result.put("aaData", array);
response.setContentType("application/json");
response.setHeader("Cache-Control", "no-store");
out.print(result);
conn.close();
} catch (Exception e) {
}
%>
[/code]
[code]
<%@page import="java.util.*"%>
<%@page import="com.chest.web.db.ConnectManager"%>
<%@page import="java.sql.*"%>
<%@page import="org.json.*"%>
<%
String[] cols = { "engine", "browser", "platform", "version","grade" };
JSONObject result = new JSONObject();
JSONArray array = new JSONArray();
int amount = 10;
int start = 0;
int echo = 0;
int col = 0;
String engine = "";
String browser = "";
String platform = "";
String version = "";
String grade = "";
String dir = "asc";
String sStart = request.getParameter("iDisplayStart");
String sAmount = request.getParameter("iDisplayLength");
String sEcho = request.getParameter("sEcho");
String sCol = request.getParameter("iSortCol_0");
String sdir = request.getParameter("sSortDir_0");
engine = request.getParameter("sSearch_0");
browser = request.getParameter("sSearch_1");
platform = request.getParameter("sSearch_2");
version = request.getParameter("sSearch_3");
grade = request.getParameter("sSearch_4");
List sArray = new ArrayList();
if (!engine.equals("")) {
String sEngine = " engine like '%" + engine + "%'";
sArray.add(sEngine);
//or combine the above two steps as:
//sArray.add(" engine like '%" + engine + "%'");
//the same as followings
}
if (!browser.equals("")) {
String sBrowser = " browser like '%" + browser + "%'";
sArray.add(sBrowser);
}
if (!platform.equals("")) {
String sPlatform = " platform like '%" + platform + "%'";
sArray.add(sPlatform);
}
if (!version.equals("")) {
String sVersion = " version like '%" + version + "%'";
sArray.add(sVersion);
}
if (!grade.equals("")) {
String sGrade = " grade like '%" + grade + "%'";
sArray.add(sGrade);
}
String individualSearch = "";
if(sArray.size()==1){
individualSearch = sArray.get(0);
}else if(sArray.size()>1){
for(int i=0;i 100)
amount = 10;
}
if (sEcho != null) {
echo = Integer.parseInt(sEcho);
}
if (sCol != null) {
col = Integer.parseInt(sCol);
if (col < 0 || col > 5)
col = 0;
}
if (sdir != null) {
if (!sdir.equals("asc"))
dir = "desc";
}
String colName = cols[col];
int total = 0;
Connection conn = ConnectManager.getConnection();
try {
String sql = "SELECT count(*) FROM ajax";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()){
total = rs.getInt("count(*)");
}
}catch(Exception e){
}
int totalAfterFilter = total;
//result.put("sEcho",echo);
try {
String searchSQL = "";
String sql = "SELECT * FROM ajax";
String searchTerm = request.getParameter("sSearch");
String globeSearch = " where (engine like '%"+searchTerm+"%'"
+ " or browser like '%"+searchTerm+"%'"
+ " or platform like '%"+searchTerm+"%'"
+ " or version like '%"+searchTerm+"%'"
+ " or grade like '%"+searchTerm+"%')";
if(searchTerm!=""&&individualSearch!=""){
searchSQL = globeSearch + " and " + individualSearch;
}
else if(individualSearch!=""){
searchSQL = " where " + individualSearch;
}else if(searchTerm!=""){
searchSQL=globeSearch;
}
sql += searchSQL;
sql += " order by " + colName + " " + dir;
sql += " limit " + start + ", " + amount;
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
JSONArray ja = new JSONArray();
ja.put(rs.getString("engine"));
ja.put(rs.getString("browser"));
ja.put(rs.getString("platform"));
ja.put(rs.getString("version"));
ja.put(rs.getString("grade"));
array.put(ja);
}
String sql2 = "SELECT count(*) FROM ajax";
if (searchTerm != "") {
sql2 += searchSQL;
PreparedStatement ps2 = conn.prepareStatement(sql2);
ResultSet rs2 = ps2.executeQuery();
if (rs2.next()) {
totalAfterFilter = rs2.getInt("count(*)");
}
}
result.put("iTotalRecords", total);
result.put("iTotalDisplayRecords", totalAfterFilter);
result.put("aaData", array);
response.setContentType("application/json");
response.setHeader("Cache-Control", "no-store");
out.print(result);
conn.close();
} catch (Exception e) {
}
%>
[/code]
________________________________________________________________________
that 's an exception ,i am use the api ivoke the controller
[code]
$(function() {
$('#example').dataTable({
"bProcessing": true,
"bServerSide": true,
"bAutoWidth": false,
"aoColumns": [
{ "sTitle": "HeroId" },
{ "sTitle": "Name" },
{ "sTitle": "Level" },
{ "sTitle": "Race" }],
"sAjaxSource":'<%=Url.Action("GetData","Home")%>'
});
});
[/code]
i am curious about it ,that's api not pass the parmaters to the controller .certaily it show that's exception.
how i can solve it ,let it rend on the data in the datatables.thanks!
_________________________________________________________
A great many thanks to everyone who has given their code to this effort! I'm happy to say that the new section of the DataTables site which builds on this thread is now available here: http://datatables.net/development/server-side/ .
We currently have server-side scripts for:
ColdFusion
Django
Java Server Pages (JSP)
Perl with MySQL
PHP with MySQL
PHP with PostgreSQL
Python (CGI) with MySQL
Ruby on Rails
Ruby (CGI) with MySQL
C# scripts are hopefully on their way soon...!
This is not to say that we are done with this yet! If you can author in other languages please let me know and I'll include it in the server-side "gallery".
Regards,
Allan
[code]
<%
'here we get some parameters passed by the DataTables using GET
sEcho = Cint(Request("sEcho"))
iDisplayLength = Cint(Request("iDisplayLength"))
iDisplayStart = Cint(Request("iDisplayStart"))
sSearch = Request("sSearch")
'SEARCH - here we make the Where clause that will be used in the SQL querry. You only put here the fields you want to search
strWhere = " WHERE engine LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " browser LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " platform LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " version LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " grade LIKE '%" & sSearch & "%'"
'ORDERING
'passes through all cols and first check if the column is sortable, if yes then construct
'the variable "order" that list in order the sequence of ordering
for k=0 to 4
if Request("bSortable_" & k)="true" then
order = order & Request("iSortCol_" & k) & " " & Request("sSortDir_" & k)
end if
next
'here we replace the number corresponding the column position by the corresponding name of the column in the database
order = Replace(order,"0",", engine")
order = Replace(order,"1",", browser")
order = Replace(order,"2",", platform")
order = Replace(order,"3",", version")
order = Replace(order,"4",", grade")
'here we eliminate the first comma of the variable "order"
order = Right(order, Len(order)-1)
'here we create the variable "strOrderBy" that will be used in the SQL querry
strOrderBy = "ORDER BY " & order
'here we create the connection with de database (I used Microsoft SQL Server 2008)
Set Session("objConn") = Server.CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Server};Server=SERVER-NAME;Initial Catalog=DATABASE-NAME;User Id=LOGIN;Password=PASSWORD;"
Session("objConn").open strConnection
'here we create the SQL querry using the variables "strWhere" and "strOrderBy"
SQL = "SELECT * FROM ajax " & strWhere & strOrderBy
Set rs = Session("objConn").Execute(SQL)
'here is counted how many records we have in the querry using the search criterion and call this as "iTotalDisplayRecords"
iTotalDisplayRecords = 0
if rs.eof = false then
do while not rs.eof
iTotalDisplayRecords = iTotalDisplayRecords + 1
rs.movenext
loop
rs.movefirst
end if
'here we just count how many records we have in the table, WITHOUT any search criterion and call this as "iTotalRecords"
SQL2 = "SELECT count(*) FROM ajax"
Set rs2 = Session("objConn").Execute(SQL2)
iTotalRecords = rs2(0)
'here we begin to mount the ajax reponse
%>
{"sEcho": <%=sEcho%>, "iTotalRecords": <%=iTotalRecords%>, "iTotalDisplayRecords": <%=iTotalDisplayRecords%>, "aaData": [
<% i= 0
'PAGINATION
'First we move the recordset for the first record of the page that is being displayed in the table using the parameters(iDisplayStart)
'then we create a loop wich the limits are the parameters (iDisplayLength) or the end of the querry
if rs.eof = false then
rs.move(iDisplayStart)
do while i < iDisplayLength and not rs.eof
'we create the variable "aaData" that has the data that will be displayed. This variable is in a format known by the DataTable
aaData = aaData + "['" & rs("engine") & "','" & rs("browser") & "','" & rs("platform") & "','" & rs("version") & "','" & rs("grade") & "'],"
'here we replace the single quotes by double quotes (chr(34))
aaData = Replace(aaData,"'",chr(34))
rs.movenext
i=i+1
loop
'here we eliminate the last comma in the aaData
aaData = Left(aaData,Len(aaData)-1)
else
'if the querry result has no records the aaData will be empty and the Table will give an answer that no record was find
aaData = ""
end if
'here we finishes to mount the response
response.write aaData & "] }"%>
[/code]
Hi zaharin,
I eventually worked out that you must have the CF Admin "Enable Request Debugging Output" turned off or it includes information in the response and breaks the jSon data returned.
Regards,
Chris.
http://usmanshabbir.blogspot.com/2010/10/simplest-way-to-use-jquery-datatable-in.html
Thanks for the post - I'm sure others will find it most interesting! The main reason for this thread though, is to show how server-side processing can be implemented, allowing a table to handle up-to many millions of rows, which client-side processing simply can't do (not designed for that - an SQL engine is...). Great to see how a table can be output in C# though.
Regards,
Allan