DataTables and asp.net mvc
DataTables and asp.net mvc
Hello guys, this plugin seems to be very nice, i used jqgrid with asp.net mvc and i would like to check out DataTables plugin but i do not find a place to start. Is there a tutorial about this? especially of how server-side code should look like. Thanks!
This discussion has been closed.
Replies
I'm not that "up" with ASP.NET myself, so I'm not sure I can help other than to point you towards a few resources:
Basic initialisation of a table (client-side) - nice to start simple: http://datatables.net/examples/basic_init/zero_config.html
Basici initialisation using server-side processing with PHP: http://datatables.net/examples/server_side/server_side.html
Using DataTables with ASP.NET (written by naspinski on this forum): http://naspinski.net/post/REAL-AJAX-with-AspNet-(not-AspNet-AJAX).aspx
There are also a number of questions about ASP.NET on this forum, so it might be worth having a search through it for 'ASP'.
Regards,
Allan
I will let you know how things are going and if it is the wrong way i will disturb you again:)
Nice one - thanks for the link. I was sure that thread was around, but I totally couldn't find it when replying to your thread...
Regards,
Allan
[code]
public JsonResult GetData(
/* paging */ int iDisplayStart, int iDisplayLength,
/* filtering */ string sSearch, bool bEscapeRegex,
/* not used */ int iColumns,
/* sorting */ int iSortingCols, int iSortCol_0, string iSortDir_0,
int sEcho)
[/code]
and my client-side code is this:
[code]
$(function() {
$('#example').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../Home/GetData"
});
});
[/code]
I am using FireBug and when i get to this "sAjaxSource": "../Home/GetData", it sends me to the DataTable.js where i get an error..[code]line 4645 for ( i=0, iLen=bUseCols ? oInit.aoColumns.length : nThs.length ; i
In the above code you don't have any columns defined - but DataTables requires to know how many columns you are going to be using at initialisation time (since there is currently no support for adding and removing columns on-the-fly). So you either need to define the columns using tags, or using aoColumns.
Regards,
Allan
[code]
$(function() {
$('#example').dataTable({
"bProcessing": true,
"bServerSide": true,
"aoColumns": [
{ "sTitle": "Id" },
{ "sTitle": "Name" },
{ "sTitle": "Level" },
{ "sTitle": "Race" }],
"sAjaxSource":'<%=Url.Action("GetData","Hero")%>'
});
});
[/code]
Right now i can see the following:
-the dropdown for number or rows to be displayed
-the search textbox
-the table head
-the two navigation arrows
-the "processing.." div
But my server-side method doesn't fire
That was really funny...i was posting in the same time with you:)
Very odd indeed that the Ajax call doesn't occur there. I don't see anything wrong with your initialisation code, so I'm surprised that it isn't shooting of a Ajax call. Are you sure it isn't, but perhaps just dodgy Ajax coming back? Firebug might give some insight into what is going on.
Regarding this question:
> How do i know which is the correct order for defining the columns?
Not 100% sure what you mean. The order you give DataTables is the order that it will appear on the screen. However, if you want to know the order on the server-side, you can use sName. You will be able to see in this example - http://datatables.net/examples/server_side/column_ordering.html - that sName can be used by DataTables to have arbitrary column positions returned by the server-side, re-ordered by DataTables.
Regards,
Allan
The reguest is this:
http://localhost:2654/Hero/GetData?sEcho=1&iColumns=4&sColumns=&iDisplayStart=0&iDisplayLength=10&sSearch=&bEscapeRegex=true&sSearch_0=&bEscapeRegex_0=true&sSearch_1=&bEscapeRegex_1=true&sSearch_2=&bEscapeRegex_2=true&sSearch_3=&bEscapeRegex_3=true&iSortingCols=1&iSortCol_0=0&iSortDir_0=asc
The parameters list is the following:
[code]
bEscapeRegex true
bEscapeRegex_0 true
bEscapeRegex_1 true
bEscapeRegex_2 true
bEscapeRegex_3 true
iColumns 4
iDisplayLength 10
iDisplayStart 0
iSortCol_0 0
iSortDir_0 asc
iSortingCols 1
sColumns
sEcho 1
sSearch
sSearch_0
sSearch_1
sSearch_2
sSearch_3
[/code]
Allan, do you see anything suspicious if you take a look at these parameters?
I get into the server-side method..the json seems ok, but i get the alert Warning - added data does not match known number of columns.
This is how the json looks like
[code]
{"sEcho":1,"iTotalRecords":6,"iTotalDisplayRecords":6,"aaData":[{"HeroId":1,"Name":"Warden","Level":10,"Race":"Night elf"},{"HeroId":2,"Name":"Blademaster","Level":6,"Race":"Orc"},{"HeroId":3,"Name":"Demon Hunter","Level":9,"Race":"Night elf"},{"HeroId":4,"Name":"Death Knight","Level":8,"Race":"Undead"},{"HeroId":5,"Name":"Lich","Level":6,"Race":"Undead"},{"HeroId":6,"Name":"Montain King","Level":5,"Race":"Human"}]}
[/code]
and this is the columns definition
[code]
"aoColumns": [
{ "sTitle": "HeroId" },
{ "sTitle": "Name" },
{ "sTitle": "Level" },
{ "sTitle": "Race" }],
[/code]
[code]
$(function() {
$('#example').dataTable({
"aaData": [
[1, "Warden", 10, "Night elf"],
[2, "Blademaster", 6, "Orc"],
[3, "Death knight", 5, "Undead"]
],
"aoColumns": [
{ "sTitle": "HeroId" },
{ "sTitle": "Name" },
{ "sTitle": "Level" },
{ "sTitle": "Race" }],
});
});
[/code]
But with this json coming from the server-side it doesn't
[code]
"aaData":[{"HeroId":1,"Name":"Warden","Level":10,"Race":"Night elf"},
{"HeroId":2,"Name":"Blademaster","Level":6,"Race":"Orc"},
{"HeroId":3,"Name":"Demon Hunter","Level":9,"Race":"Night elf"},
{"HeroId":4,"Name":"Death Knight","Level":8,"Race":"Undead"},
{"HeroId":5,"Name":"Lich","Level":6,"Race":"Undead"},
{"HeroId":6,"Name":"Montain King","Level":5,"Race":"Human"}]}
[/code]
I can hardly wait to get this done so i can post there the whole solution to be a full example for asp.net mvc
Thanks a lot Allan..i will post the code in the thread you created
Cheers.
I got the same issue and fixed it just few minutes ago!
So exciting! It took me a hole night!
I download the js file form jquery.com which is a beta vesion. Suddenly i notice this i replace it with the newest one ,and it works!
[code]
public virtual JsonResult DataTables(int iDisplayStart,
int iDisplayLength,
string sSearch,
bool bEscapeRegex,
int iColumns,
int iSortingCols,
int iSortCol_0,
string iSortDir_0,
int sEcho)
{
string orderString = string.Empty;
StringBuilder sb = new StringBuilder();
string[] searchField = {"Message"};
string[] sortField = {"ID","Date"};
if(!string.IsNullOrEmpty(sSearch))
{
sb.Append("where");
foreach(string s in searchField)
{
sb.AppendFormat("p.{0} like '%{1}%' or",s,sSearch);
}
orderString = sb.ToString();
if(orderString.EndsWith("or"))
{
int index = orderString.LastIndexOf('o');
orderString.Remove(index,2);
}
}
string eSQL = string.Format("SELECT VALUE p FROM {0} AS p {1} order by p.{2} {3} SKIP({4}) LIMIT({5})",
"Logs",
orderString,
sortField[iSortCol_0],
iSortDir_0,
iDisplayStart,
iDisplayLength);
List logs = _ctx.CreateQuery(eSQL).ToList();
int iTotalDisplayRecords = logs.Count;
int iTotalRecords = _BLL.Count();
var json =
new
{
sEcho = sEcho,
iTotalRecords = iTotalRecords,
iTotalDisplayRecords = iTotalDisplayRecords,
aaData = (from q in logs
select new
string[]
{
q.Id.ToString(), q.Logger, q.Level, q.Date.ToString(), q.Message
})
};
return Json(json, JsonRequestBehavior.AllowGet);
}
[/code]
json:
[code]
{"sEcho":1,"iTotalRecords":696,"iTotalDisplayRecords":10,"aaData":[["4","System222","INFO","2009/11/10 12:23:04","Log system success created !"],["6","System","INFO","2009/11/10 12:24:06","Log system success created !"],["7","System22","2009/12/12 17:48:18","2009/11/10 12:40:24","Log system success created !"],["8","System","INFO","2009/11/10 12:40:26","Log system success created !"],["10","System","INFO","2009/11/10 12:42:25","Log system success created !"],["11","System","INFO","2009/11/10 12:48:41","Log system success created !"],["12","System","INFO","2009/11/10 12:48:44","Log system success created !"],["13","System","INFO","2009/11/10 12:48:45","Log system success created !"],["14","System","INFO","2009/11/10 12:50:00","Log system success created !"],["15","System","INFO","2009/11/10 12:50:02","Log system success created !"]]}
[/code]
Hope it is helpful!
Sorry for my pool english :)
I am not the guy that writes the most clean code but i figured it like this
Controller:
[code]
public ActionResult GetData(GridParams gridParams)
{
//data
var query = from x in ctx.HeroSet
select x;
int total = query.Count();
//search whatever property you like
if (!string.IsNullOrEmpty(gridParams.sSearch))
{
query = from x in query
where x.Name.Contains(gridParams.sSearch)
select x;
}
//sort
if (gridParams.iSortingCols > 0)
query = query.OrderBy(GridsUtils.cols[gridParams.iSortCol_0], (gridParams.iSortDir_0 == "asc"));
//display
int totaldisp = query.Count();
if (gridParams.iDisplayLength > 0)
query = query.Skip(gridParams.iDisplayStart).Take(gridParams.iDisplayLength);
return Json(new { sEcho = gridParams.sEcho, iTotalRecords = total, iTotalDisplayRecords = totaldisp, aaData = query.GetStrings(GridsUtils.GetString) });
}
[/code]
GridParams class
[code]
public class GridParams
{
public int iDisplayStart { get; set; }
public int iDisplayLength { get; set; }
public string sSearch { get; set; }
public bool bEscapeRegex { get; set; }
public int iColumns { get; set; }
public int iSortingCols { get; set; }
public int iSortCol_0 { get; set; }
public string iSortDir_0 { get; set; }
public int sEcho { get; set; }
public bool bSortable_0 { get; set; }
public bool bSearchable_0 { get; set; }
}
[/code]
Order method
[code]
public static class QuerableExtensions
{
public static IQueryable OrderBy(this IQueryable source, string propertyName, bool asc)
{
var type = typeof(T);
string methodName = asc ? "OrderBy" : "OrderByDescending";
var property = type.GetProperty(propertyName);
var parameter = Expression.Parameter(type, "p");
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName, new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExp));
return source.Provider.CreateQuery(resultExp);
}
}
[/code]
Some helpers for creating a string[][] to send to the client side
[code]
public static class GridsUtils
{
public static readonly Dictionary cols = new Dictionary { { 0, "Id" }, { 1, "Name" }, { 2, "Level" }, { 3, "Race" },{4,"IsImba"} };
public static string[] GetString(Hero h)
{
return new string[] { h.Id.ToString(), h.Name, h.Level.ToString(), h.Race,h.IsImba.ToString() };
}
public delegate string[] GetStringDelegate(T obj);
public static string[][] GetStrings(this IQueryable source, GetStringDelegate del)
{
string[][] retArray = new string[source.Count()][];
int i = 0;
foreach (var s in source)
{
retArray.SetValue(del(s), i);
i++;
}
return retArray;
}
}
[/code]
If you have a cleaner option of code please make it public :)
[code]
return new JavaScriptSerializer().Serialize(
new
{
sEcho,
iTotalRecords = YOURENTITY.Count,
iTotalDisplayRecords = iDisplayLength,
aaData = YOURENTITY.Select(s => new string[] { s.StockCode, s.Data, s.Recommendations, s.InstitutionID, s.SectorID, s.MCI })
}
);
[/code]
and in js use this to post only the variables that you'll use
[code]
"fnServerData": function ( sSource, aoData, fnCallback) {
var Data= { sEcho: aoData[0].value,
iDisplayStart: aoData[3].value,
iDisplayLength: aoData[4].value,
iSortCol_0: aoData[26].value,
sSortDir_0: aoData[27].value,
sSearch: aoData[5].value};
$.ajax( {
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
url: sSource,
data: JSON.stringify(Data),//from json.js plugin
success: function(data) { return fnCallback( eval("(" + data.d + ")") ) },
error:function(msg){alert("Error")}
});
},
[/code]