ASP.Net MVC 3 JQGrid
Asked Answered
G

4

7

After reading up on the JQGrid control, I decided it would be good to use it in one of my ASP.Net MVC 3 Web applications.

Firstly I followed Phil Haacks tutorial http://haacked.com/archive/2009/04/14/using-jquery-grid-with-asp.net-mvc.aspx which is all good. I then tried to implement something similar into my app, the only difference being, I use Linq To Entities.

My View page has all the css and Jquery classes imported, then I have my JavaScript Function and table which holds the data

<script type="text/javascript">
jQuery(document).ready(function () {
    jQuery("#list").jqGrid({
        url: '/Home/LinqGridData/',
        datatype: 'json',
        mtype: 'GET',
        colNames: ['equipmentID', 'categoryTitle', 'title'],
        colModel: [
      { name: 'equipmentID', index: 'equipmentID', width: 40, align: 'left' },
      { name: 'categoryTitle', index: 'categoryTitle', width: 40, align: 'left' },
      { name: 'title', index: 'title', width: 200, align: 'left'}],
        pager: jQuery('#pager'),
        width: 660,
        height: 'auto',
        rowNum: 10,
        rowList: [5, 10, 20, 50],
        sortname: 'Id',
        sortorder: "desc",
        viewrecords: true,
        imgpath: '/scripts/themes/coffee/images',
        caption: 'My first grid'
    });
}); 

<h2>My Grid Data</h2>
<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align:center;"></div>

Then in my controller, I have the following method which is suppose to return the Json data

public ActionResult LinqGridData(string sidx, string sord, int page, int rows)
    {
        AssetEntities context = new AssetEntities();

        var query = from e in context.Equipments
                    select e;

        var count = query.Count();

        var result = new
        {
            total = 1,
            page = page,
            records = count,
            rows = (from e in query
                    select new
                    {
                        id = e.equipmentID,
                        cell = new string[]
                        {
                        e.equipmentID.ToString(),
                        e.Category.categoryTitle,
                        e.Department.title
                        }

                    }).ToArray()
        };

        return Json(result, JsonRequestBehavior.AllowGet);

    }

When I run this, the code falls over with the following error

LINQ to Entities does not recognize the method 'System.String ToString()' method

Does anyone know how to fix this error? And also, am I doing this the correct way, or should I be doing it a different way from the Phil Haack explanation since he is using Linq to SQL?

Any feedback would be much appreciated.

Thanks Folks.

Glyptograph answered 23/2, 2011 at 15:19 Comment(4)
what line of code is the error being generated on? Because by my best guess, it looks like it is happening in your controller (the only place I see the .ToString() method)Carmellacarmelle
Yes, you're correct. The application falls over at e.equipment.ToString() . Any solutions? Thanks.Glyptograph
what is the data type of e.equipmentID? If it is already a string, that may be your problem. Alternatively, if it is something that doesn't have the .toString() functionality built-in, like a custom data-type, that could also be your problem.Carmellacarmelle
e.equipmentID is of type int, it needs to be casted to type string, thats why i tried .ToString()Glyptograph
W
14

EF doesn't support ToString method, you must retrieve the data without ToString and format

this should work

public ActionResult LinqGridData(string sidx, string sord, int page, int rows)
{
    AssetEntities context = new AssetEntities();

    var query = from e in context.Equipments
                select e;

    var count = query.Count();

    var result = new
    {
        total = 1,
        page = page,
        records = count,
        rows = query.Select(x => new { x.equipamentID, x.Category.categoryTitle,x.Department.title })
                    .ToList() // .AsEnumerable() whatever
                    .Select(x => new { 
                        id = x.equipamentID,
                        cell = new string[] {
                            x.equipamentID.ToString(),
                            x.categoryTitle,
                            x.title
                        }})
                    .ToArray(),
    };

    return Json(result, JsonRequestBehavior.AllowGet);

}
Waw answered 23/2, 2011 at 16:22 Comment(0)
L
3

Look at the code example from the another answer. I hope it will be helpful.

Small remarks:

  1. sortname: 'Id' is wrong parameter because you have no column with the name 'Id'. Probably you mean sortname:'equipmentID'.
  2. You should remove imgpath: '/scripts/themes/coffee/images' parameter of jqGrid which is depricated.
  3. You should remove all attributes excepting id from the HTML code: <table id="list"></table><div id="pager"></div>
Laryngology answered 23/2, 2011 at 16:9 Comment(4)
Thanks for your suggestions Oleg. Unfortunately, that other answer doesn't really help me, I still have the problem of converting equipmentID to string :(Glyptograph
@tgriffiths : Could you include declaration of the Equipment class? In another comment you wrote that e.equipmentID has type int and you say that the error is in e.equipmentID.ToString(). On the other side you have error message about 'System.String ToString()' method. Some from the information is wrong. Try to comment the line with e.equipmentID.ToString() and verify that you will have no error.Laryngology
@tgriffiths. I posted you reference to my old answer because you current code do no paging or sorting. You use also total = 1. The referenced answer show how to implement paging and sorting.Laryngology
Thanks Oleg, but Kim got it working. Thanks for your time though.Glyptograph
C
0

Ah, I have found the issue. .ToString doesn't work in LINQ to Entity. Yes, this is weird and IMO very dumb. But that is the core problem. As for a work-around...when JSON serializes things, they end up looking very much like a string anyway, by the time jQuery gets around to reading them. So, essentially, you should be able to completely leave out the .ToString() and it should work.

Carmellacarmelle answered 23/2, 2011 at 15:53 Comment(1)
When I leave out the .ToString(), an errors appears at e.equipmentID again, it says, Cannot implicitly convert type 'int' to 'string'Glyptograph
S
0

I will address the issue of inline editing and adding a new row to jqGrid as it applies to ASP.NET MVC 3 and Razor C#. I will also include C# Controller code to populate the grid and save data to the grid. First lets look at how to install jqGrid 4.4.1 in an MVC3 Web Application using the NuGet package manager.

  1. Install jQuery 1.7.2 or higher.
  2. Install jQuery.UI.Combined.
  3. Install jqGrid 4.4.1

You can download jqGrid separately from

http://www.trirand.com/blog/?page_id=6

and the jqGrid documentation can be found at

http://www.trirand.com/jqgridwiki/doku.php

I am not going to test the code in this post but it is based on code that does work. I am going to take the brute force approach to solving the difficult and complex problem of populating a jqGrid from an action method, editing a single row or adding a new editable row, then saving the row to an action method. I am sure that more optimal ways of doing this can be found but this is a good starting point. I am not going to show you how to tweak the appearance of your jqGrid, I will leave that to you. I will be using JSON as the data interchange format between jqGrid and ASP.NET MVC 3. I am not going to addres the issue of deleting a row in the grid.

Lets start with the GET action method in the Controller

public JsonResult GetProduct(int productId = 0)
{
    var productsQuery = dbContext.FirstOrDefault(p => p.ProductId == productId);
    var productsList = new List<Products>();

    // SQL does not understand ToString() so we have to do this or something like it
    foreach(var p in productsQuery)
    {
        var product = new Product{
           ProductId = p.ProductId,
           Product.Name = p.Name,
           Product.Date = p.Date.ToShortDateString()
           // and so on...
        };
        productsList.Add(product);
    }

    // You must build an anonymous object that can then be converted into a 2-dimensional 
    // array formatted for jqGrid, convert it to a 2d array then Json.  Note that all grid 
    // data must be in string format.
    var jsonData = new {
        total = 1,
        page = 1,
        records = productsQuery.Count(),
        rows = productsList.Select(p => new {
            id = p.id.ToString(),
            cell = new string[] {
                p.Name,
                p.Date.ToShortDateString(),
                // and so on...
               }
            }).ToArray();
        };

       return Json(jsonData, JsonRequestBehavior.AllowGet);
}

And the View...

<script type="text/javascript">
    $(document).ready(function () {

        var lastSelectedId;
        var grid = $('#grid');
        grid.jqGrid({
            url: "@Url.Action("GetProducts", "Products")",
            datatype: 'json',
            mtype: 'post',
            colNames: ['ProductId', 'Name', 'Date',
                // and so on...
                ],
            colModel: [
                { name: 'ProductId', index: 'ProductId', editable: false },
                { name: 'Name', index: 'Name', editable: true, edittype: 'text' },
                { name: 'Date', index: 'Date', editable: true, edittype: 'text' }
                // and so on...
                ],
            onSelectRow: function(rowid) {
                if (rowid && rowid !== lastSelectedId) {
                    grid.jqGrid('resotreRow', lastSelectedId);
                    lastSelectedId = rowid;
                }
                grid.jqGrid('editRow', rowid, { keys: true });
            },
            editurl: "@Url.Action("SaveProducts", "Products");
            rownum: [10],
            rowList: [5,10,20,50],
            pager: '#grid_pager',
            sortName: 'Name',
            viewrecords: true,
            gridview: true,
            caption: 'Sample Grid'
    });
    grid.jqGrid('navGrid', '#pager', { edit: false, add: false: del: false,
         refresh: false });
    grid.jqGrid('inlineNav', '#pager', {
        addParams: {
            position: 'first',
            addRowParams: {
                keys: true,
                oneditfunc: onInlineEdit
            }
            add: true,
            edit: false,
            save: false,
            cancel: true
    });
    function onInlineEdit(rowid) {
        // add inline editing functionality here
    }             
</script>
@using (Html.BeginForm("","", FormMethod.Post, new { id = "ProductsForm" }))
{
    <table id="grid">
    </table>
    <div id="pager">
    </div>
}

and then the POST method

[HttpPost]
public JsonResult SaveProduct(FormCollection frm)
{
    Product product;

    if (frm["oper"] == "add")
    {
        product = new Product();
    }
    else
    {
        int productId = Int32.Parse(frm["id"]);
        product = dbContext.Products.FirstOrDefault(p => p.ProductId == productId);
    }

    foreach (var key in frmAllKeys)
    {
        switch(key)
        {
            case "Name":
                product.Name = frm[key];
                break;
            case "Date":
                product.Date = DateTime.Parse(frm[key]);
                break;
            // and so on...
        }
    }

    try
    {
        if (frm["oper"] == "add")
        {
            dbContext.AddObject(product);
        }
        dbContext.SaveChanges();
    }
    catch (Exception ex)
    {
        Debug.WriteLine(exception.StackTrace);
        return Json(false);
    }
    return Json(true);
}

There are better ways to do this but this is a good start. I am not addressing the dynamic grid issue. I am not sure how that could be accomplished. Suffice it to say that a dynamic jqGrid would require a lot more JavaScript and/or C# code. I would take a look at the "grid within a grid" functionality in jqGrid for combining a static grid with a dynamic grid.

I did try to build functionality that would accept the object type, a list of records and generate the jqGrid Array and Json data for the grid without having to do all of the extra work shown above. I think it can be done with reflection but I don't have time to do it right now.

Finally, I also tried to build functionality that would extract the data from the FormCollection and populate an object given only the object type and the FormCollection. Again, I think this can be done using reflection but I dont have time to do it right now. If anyone wants to try to build an MVC3 C# jqGrid Json generator and extractor, I would reccomend that you use the Entity Framework Code First method with POCO classes for your model. POCO classes are much easier to work with than entity objects for such a task.

I hope this helps :)

Serval answered 31/10, 2012 at 20:11 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.