Having introduced jqGrid and written LINQ extension methods to make supplying data to the grid easy, we’re now ready to put together a demo application. The solution I’m going to build demonstrates sorting and paging. In a future post, I will enhance it to demonstrate search, formatting, and editing data. I’ve made the demo application available for download, but be advised that I intend to update it in the next few days; it’s currently a work in progress.
Rather than make you set up the Northwind database, I’ve written a quick, mock repository which supplies random data for the application. But the data persists for the lifetime of the app, so I’ll be able to demonstrate sorting, paging, etc. This way you can just run the application, without having to set up SQL Server.
So let’s write some code display a grid.
The data I’m going to display in the grid is a fairly useless type:
public class SomeEntity { public int Id { get; set; } public int IntProperty { get; set; } public string StringProperty { get; set; } public DateTime DateProperty { get; set; } }
We need two actions. The first will display the page containing the grid. The second supplies data for one page of the grid. Remember, the grid can page through large datasets. It will only request one page of data at a time.
public ActionResult GridDemo()
{
return View();
}
public ActionResult GridDemoData(int page, int rows, string search, string sidx, string sord)
{
var repository = new Repository();
var model = repository.SelectAll().ToJqGridData(page, rows, sidx + " " + sord, search,
new[] { "IntProperty", "StringProperty", "DateProperty" });
return Json(model);
}The first action simply returns a view containing the HTML table which will eventually become the grid. It doesn’t need a model, because the grid will request its data asynchronously. The second action handles the data requested by the grid.
In order to make this work, we need HTML elements for the grid and its pager in the view, along with a reference to the JavaScript which will turn those elements into the grid.
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> <h2>GridDemo</h2> <table id="grid" class="scroll" cellpadding="0" cellspacing="0"></table> <div id="pager" class="scroll" style="text-align:center;"></div> </asp:Content> <asp:Content ID="Script" ContentPlaceHolderID="ScriptContent" runat="server"> <script language="javascript" type="text/javascript" src="<%= Url.Content("~/Scripts/Home.GridDemo.js") %>"></script> </asp:Content>
The JavaScript file is where the grid itself is configured.
$(document).ready(function() { GridDemo.Home.GridDemo.setupGrid($("#grid"), $("#pager")); }); GridDemo.Home.GridDemo = { setupGrid: function(grid, pager) { grid.jqGrid({ colNames: ['Int', 'String', 'Date'], colModel: [ { name: 'IntProperty', index: 'IntProperty' }, { name: 'StringProperty', index: 'StringProperty' }, { name: 'DateProperty', index: 'DateProperty' }, ], pager: pager, sortname: 'IntProperty', rowNum: 10, rowList: [10, 20, 50], sortorder: "asc", url: "GridDemoData" }).navGrid(pager, { edit: false, add: false, del: false, search: false }); } };
Important to note here are:
colNames, the grid column captions the user sees.colModel,which includes the propertyname, which is the field name in the returned JSON data which supplies the data for that column, andindex, which is the name which will be sent to theGridDemoDataaction as thesidx(field name to sort on) argument when you click on a grid header. There are a number of other properties you can set here which control editing, with, formatting, etc. See the documentation for full details. Importantly,colNamesandcolModelmust have the same number of entries, and be in the same order.url, which tells the grid where to get its data. You’ll notice that the value I’ve passed is the name of the second action.
As I mentioned in the last post, I set a number of the grid options in a script referenced by the Site.Master, in order to prevent having to set them on every grid I create. The configuration idea for the grid on an individual page should be only the things that are unique to that page. So there is a fair bit of configuration for the grid which is not included here. In addition, the Site.Master contains references to the grid’s JavaScript and CSS files.
Finally, it’s worth mentioning that when you add jqGrid to a project you need to edit the jQuery.jqGrid.js file to tell the grid where to find the rest of the JS files it needs. Once you look at this file, it is obvious what you need to change.
At this point, I’m going to make the demo application available for download, so that you can experiment with it yourself. However, I’ll caution that I intend to update it when I write the next post in this series. In other words, the current download is a work in progress. It will change in the next few days.
Download the Solution (587 KB)
So let’s run the solution and see what we get:
OK, the hideous and eyestrain-inducing clash between the default MVC theme and the jQuery UI "lightness" theme included with the grid download notwithstanding, this is actually not bad. Sorting and paging both work, and performance is very good.
But what’s the deal with the data in that date column? It turns out that there is no standard representation of a date in JSON, so Microsoft invented their own. The grid doesn’t know about this special format. We’ll clean that up in the next post in this series and also cover search. Stay tuned!
{ 20 } Comments
Nice Blog…
Hi Craig,
I appreciate your example very much!
Just want to inform you about a bug: page numbering is started from 1, not from 0.
So, your linq extension methods return records starting from 11 to 20 on the first page, while should 1-10.
If change code like the following:
var jsonData = new DbModelDataContext().TourOrderGrids
.ToJqGridData(
page-1, rows, sidx + " " + sord, search,
new[]
{
"Id", "OrderNo", "OrderDate", "OrderCreatedByUserName",
"TourName", "TourDate", "AgencyName", "Sum", "StatusName"
},
null);
jsonData.Page++;
everything work fine.
Thanks, Andrey. I changed from our internal PagedList to Troy Goode’s publicly available PagedList for this demo, and his works different than ours does. I will fix that bug for the next drop, which will come when I post the next article in this series. I appreciate you pointing this out.
Thank you, I’ve been looking how to use grid in MVC.
Craig - Thanks for the demo. I’m trying to get my head around MVC and Linq and I’m having a disconnect in your repository function where you generate sample data. Could you please show me how to get a real LINQ select query (with multiple joins) to work. I’m getting errors I can’t get around trying to use this. I’m sure its simple I just can’t get past it.
Thanks - Mike
Mike, the query in my project works fine as it is. If you need help with LINQ to SQL, I’d suggest posting the details of your problem on http://www.stackoverflow.com
If you think the errors are in my code, you’d need to tell me what they are for me to be of any help. But do read the "LINQ Errors" entry in this series first, as it may already answer your questions.
Thanks Craig for a nice article. However, when I download and run the code, I get a javascript error "Microsoft JScript runtime error: ‘$.jgrid’ is null or not an object" in Site.Master.js. I am new to jQuery grid so I am not sure what could be wrong. I tried playing with paths in the jQuery.jqGrid.js file but no luck. I simply downloaded the code, extracted it, opened VS2008 that automatically created the virtual directory and hit F5. I see the error right away. Any help would be appreciated. Thanks a lot !!
Hi craig, awesome work on creating this solution. I just seem to be missing something. According to jqgrid the rows data structure is as follows:
{id=numbervalue, {cell=[array of values]}}
and yours is just the key-value pairs?
I had my own manual setup to pass the json data to the grid but wanted to implement yours due to the less code required. But, now the grid doesn’t understand the data… I tried using the version of jqgrid in your sample sollution, but still nothing…
hi craig,
i downloaded ur solutions and run it but it doesnot shows any data in the gridview only empty grid it shows.why is like that. i made similar demo project of mine but the result is similar.so please guide me whats a problem with it?
any solutions for that.
hi bhavin patel,
Change the code like this if you used ASP.NET MVC 2
return Json(model.ToJqGridData(page, rows, null, search, new[] { "IntProperty", "StringProperty", "DateProperty" }), JsonRequestBehavior.AllowGet);
}
NOTE : HomeController.cs
Hey Craig,
Your code seems cool however in my Visual Web Developer 2010 Express it shows no rows! My OS is Windows 7 x64 Enterprise (if that helps…).
I’ve tried different solutions to get the data flowing into the grid like defining the model at runtime and creating a string representation but no luck. Also set this attribute, [AcceptVerbs(HttpVerbs.Get)], but didn’t get any results. Please let me know what I can do to get it to work, I’ll really like to develop my app in ASP.NET MVC.
Cheers,
Hi Phil,
Vou’re obviously using ASP.NET MVC 2.0, which throws an exception when an action attempts to return JSON in response to a GET request. Adding "JsonRequestBehavior.AllowGet" as the last argument to the Json() call in the Home.Controller will solve the issue.
Craig please comment on point 7. because I have the same problem if I use a link /Site/Test everything is Ok, if I use /Site/Test/Index the error occurs.
problem of point 7. Solved.
do like this: first line in masterpage
var baseUrl=”
use this variable baseUrl in: jquery.jqrid.js like this: var pathtojsfiles = baseUrl + "Scripts/jqGridJs/";
Hi Craig,
thanks for your blog. It’s very helpfull. But im having a problem.
I’m using EF4 and Linq to Entities to get data out of my sybase sql anywhere database.
When i try to run your code im getting an error in PagedList.cs on line 82 with the message (translatet from german to english):
Method ‘Skip’ is only supported for sorted input in ‘LINQ to Entities’. The method ‘OrderBy’ must be called before the method ‘Skip’.
Do you have an idea?
Regards,
Tim
Tim, that message means precisely what it says: You have to call OrderBy before anything (like PagedList) calls Skip. So sort your records!
Sorry for that…
The Grid gets json data but it doesn’t display it.
I installed jqGrid 3.8.2. Maybe thats the Problem.
When i use the jqGrid withouth your great solution i pass json data in this format to the grid:
{"page":1,"records":2,"rows":[{"id":1,"cell":["1","Test","24ed76a3-3b15-4de2-95f4-e558932ea809", null]}],"total":1}
Your solution creates a json result like this:
{"Total":2,"Page":1,"Records":12,"Rows":[{"Titel":"Test local","GUID":"24ed76a3-3b15-4de2-95f4-e558932ea809","ReferenzNr":null},"UserData":null}
Regards,
Tim
Tim, it’s not 3.8.2 that is the issue; it’s the fact that I am using
repeatitemsand you are not. I explained this option in the "LINQ Extensions" post. You must either change yourrepeatitemssetting or change the format of the data you are sending.anybody can help me . I need checkbox controls inside of grid., how can use this grid..plz advice me
Craig,
In your action method GridDemoData() you are fetching all the records from the repository before extracting the required subset (to fill up the page):
var model = repository.SelectAll().ToJqGridData(page, rows, sidx + " " + sord, search,
new[] { "IntProperty", "StringProperty", "DateProperty" });
I’m concerned that it may have a negative impact on the performance, especially if SelectAll() returns large number of records. To avoid the performance issues shouldn’t the design allow for
fetching only a subset of records from the repository (instead of fetching all of the records)?
Thanks
Mateusz
{ 1 } Trackback
[...] jqGrid with ASP.NET MVC: Introduction Using jqGrid with ASP.NET MVC: LINQ Extensions Using jqGrid with ASP.NET MVC: Finally, A Solution Using jqGrid with ASP.NET MVC: Search and Formatting Using jqGrid with ASP.NET MVC: Deleting [...]
Post a Comment