serverside processing for asp.net c# and linq with search and sort

dave pointbypoint

hi everyone, I havn't had time to check the forums to see if anyone has posted anything like this recently, but I know last time I looked for some tutorials etc for asp.net in these forums, it was a little light on the ground...
well I've been having a little play for a few hours and have got something working, and wanted to post it to help anyone else in who needs it.....
my table is in a simple aspx page and database is using linq to sql... anyone wants to refine this, please critique, this is a bit of a hash to get it working, so I am sure there is loads of room for improvement...

####EDIT####
have had to remove most of aspx page to squeeze into limits for post... but just do 4 col table #####
aspx page
[code]
......
function fillCustomers() {
$('#tblCustomers').dataTable({
"bProcessing": true,
"bServerSide": true,
"bPaginate": true,
"sAjaxSource": "server_processing.aspx"
});
...
[/code]


i use a custom class too......
[code]
public class lightTable
{
public int sEcho { get; set; }
public int iTotalRecords { get; set; }
public int iTotalDisplayRecords { get; set; }
public List aaData { get; set; }
}
[/code]

then a server processing page, no content on the aspx, just the aspx.cs has the code...
[code]
dbDataContext db = new dbDataContext();
JavaScriptSerializer ser = new JavaScriptSerializer();

int iDisplayLength = Convert.ToInt32(HttpContext.Current.Request["iDisplayLength"]);
int iDisplayStart = Convert.ToInt32(HttpContext.Current.Request["iDisplayStart"]);
int iEcho = Convert.ToInt32(HttpContext.Current.Request["sEcho"]);
int iSortingCols = Convert.ToInt32(HttpContext.Current.Request["iSortingCols"]);

int iSortCol = Convert.ToInt32(HttpContext.Current.Request["iSortCol_0"]);
string sSortDir = HttpContext.Current.Request["iSortDir_0"];



IQueryable allCustomers = from u in db.Customers select u;
IQueryable filteredCustomers = allCustomers;
if (HttpContext.Current.Request["sSearch"] != null)
{
// there is a search field....
string searchString = HttpContext.Current.Request["sSearch"];
filteredCustomers = from s in filteredCustomers
where s.custno.Contains(searchString) ||
s.name.Contains(searchString) ||
s.rep.Contains(searchString)
select s;
}

// iSortCol_0 // 0=cust Ac 1=cust name 2=rep
// iSortDir_0 // asc or desc...
if (iSortingCols == 1)
{
if (sSortDir == "asc" && iSortCol == 0) { filteredCustomers = filteredCustomers.OrderBy(p => p.custno); }
if (sSortDir == "desc" && iSortCol == 0) { filteredCustomers = filteredCustomers.OrderByDescending(p => p.custno); }

if (sSortDir == "asc" && iSortCol == 1) { filteredCustomers = filteredCustomers.OrderBy(p => p.name); }
if (sSortDir == "desc" && iSortCol == 1) { filteredCustomers = filteredCustomers.OrderByDescending(p => p.name); }

if (sSortDir == "asc" && iSortCol == 2) { filteredCustomers = filteredCustomers.OrderBy(p => p.rep); }
if (sSortDir == "desc" && iSortCol == 2) { filteredCustomers = filteredCustomers.OrderByDescending(p => p.rep); }
}


filteredCustomers = filteredCustomers.Skip(iDisplayStart).Take(iDisplayLength);
List lightCustomerList = new List();
foreach (Customer currCustomer in filteredCustomers)
{
List newCustomer = new List();
newCustomer.Add(currCustomer.custno);
newCustomer.Add(currCustomer.name);
newCustomer.Add(currCustomer.rep);
newCustomer.Add("view / edit");
lightCustomerList.Add(newCustomer);
}
// lightCustomerList;

utilityClass.lightTable returnTable = new utilityClass.lightTable();
returnTable.sEcho = iEcho;
returnTable.iTotalRecords = allCustomers.Count();
returnTable.iTotalDisplayRecords = allCustomers.Count();
returnTable.aaData = lightCustomerList;


Response.Clear();

Response.ContentType = ("text/html");
Response.BufferOutput = true;
Response.Write(ser.Serialize(returnTable));
Response.End();

[/code]


i think that is all you should need.... good luck,

dave pointbypoint

aspx page was
[code]


$(document).ready(function() {
fillCustomers();
});
function fillCustomers() {
$('#tblCustomers').dataTable({
"bProcessing": true,
"bServerSide": true,
"bPaginate": true,
"sAjaxSource": "server_processing.aspx"
});
}


table testing
i should be a list of all the customers in the database that you can see



Cust AcCust NameRepView Details






[/code]

allan

That's awesome! Thanks for sharing your code :-). Do you think it might be possible to generalise the code a little bit, so I would be able to include it in the server-side script gallery: http://datatables.net/development/server-side/ ? This is the schema that is used for all of the examples in the gallery (for consistency): http://datatables.net/development/server-side/sql . A C# / ASP script is the obvious missing link at the moment from the gallery.

Regards,
Allan

abemanden

I cannot get this to work, is this for MVC or Webform?

I'm using .net4 c# webform, and gives me 17 errors, not sure why... one of the at:

dbDataContext db = new dbDataContext();

where it cannot find if it is a type or namespace

GreggB

Have you looked at this implementation for C# with LINQ?

http://activeengine.wordpress.com/2011/02/09/datatablepager-now-has-multi-column-sort-capability-for-datatables-net/

I'm not using it since I'm querying an Oracle database with stored procedures but it seems to support nearly any DataTables feature you can think of.