jQuery ASP.Net MVC 3 JQGrid

ASP.Net MVC 3 JQGrid

提问by tcode

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.

在阅读了 JQGrid 控件之后，我决定最好在我的 ASP.Net MVC 3 Web 应用程序之一中使用它。

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

首先，我遵循了 Phil Haacks 教程http://haacked.com/archive/2009/04/14/using-jquery-grid-with-asp.net-mvc.aspx，这一切都很好。然后我尝试在我的应用程序中实现类似的东西，唯一的区别是，我使用 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

我的视图页面导入了所有的 css 和 Jquery 类，然后我有我的 JavaScript 函数和保存数据的表

<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

然后在我的控制器中，我有以下方法，假设返回 Json 数据

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?

有谁知道如何解决这个错误？而且，我这样做是正确的方式，还是应该以与 Phil Haack 解释不同的方式来做，因为他使用的是 Linq to SQL？

Any feedback would be much appreciated.

任何反馈将不胜感激。

Thanks Folks.

谢谢各位。

回答by Kim Tranjan

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

EF 不支持 ToString 方法，您必须检索没有 ToString 和格式的数据

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);

}

回答by Oleg

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>

1. sortname: 'Id'是错误的参数，因为您没有名称为“Id”的列。可能你的意思是sortname:'equipmentID'。
2. 您应该删除imgpath: '/scripts/themes/coffee/images'它的jqGrid的参数depricated。
3. 您应该从 HTML 代码中删除除 id 之外的所有属性： <table id="list"></table><div id="pager"></div>

回答by guildsbounty

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.

啊，我发现问题了。.ToString 在 LINQ to Entity 中不起作用。是的，这很奇怪，而且 IMO 非常愚蠢。但这是核心问题。至于解决方法……当 JSON 序列化事物时，无论如何，当 jQuery 开始阅读它们时，它们最终看起来非常像一个字符串。因此，本质上，您应该能够完全省略 .ToString() 并且它应该可以工作。

回答by wayne.blackmon

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.

我将解决内联编辑和向 jqGrid 添加新行的问题，因为它适用于 ASP.NET MVC 3 和 Razor C#。我还将包含 C# 控制器代码来填充网格并将数据保存到网格。首先让我们看看如何使用 NuGet 包管理器在 MVC3 Web 应用程序中安装 jqGrid 4.4.1。

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

1. 安装 jQuery 1.7.2 或更高版本。
2. 安装 jQuery.UI.Combined。
3. 安装 jqGrid 4.4.1

You can download jqGrid separately from

您可以单独下载 jqGrid

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

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

and the jqGrid documentation can be found at

可以在以下位置找到 jqGrid 文档

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

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.

我不打算测试这篇文章中的代码，但它基于有效的代码。我将采用蛮力方法来解决从操作方法填充 jqGrid、编辑单行或添加新的可编辑行，然后将该行保存到操作方法的困难和复杂问题。我相信可以找到更优化的方法，但这是一个很好的起点。我不会向您展示如何调整 jqGrid 的外观，我将把它留给您。我将使用 JSON 作为 jqGrid 和 ASP.NET MVC 3 之间的数据交换格式。我不打算解决删除网格中一行的问题。

Lets start with the GET action method in the Controller

让我们从控制器中的 GET 操作方法开始

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

然后是POST方法

[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.

有更好的方法可以做到这一点，但这是一个好的开始。我不是在解决动态网格问题。我不确定如何实现。可以说动态 jqGrid 需要更多的 JavaScript 和/或 C# 代码。我会看看 jqGrid 中的“网格内网格”功能，用于将静态网格与动态网格相结合。

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.

我确实尝试构建可以接受对象类型、记录列表并为网格生成 jqGrid 数组和 Json 数据的功能，而无需执行上面显示的所有额外工作。我认为这可以通过反思来完成，但我现在没有时间去做。

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.

最后，我还尝试构建从 FormCollection 中提取数据并填充仅给定对象类型和 FormCollection 的对象的功能。同样，我认为这可以使用反射来完成，但我现在没有时间这样做。如果有人想尝试构建 MVC3 C# jqGrid Json 生成器和提取器，我建议您对模型使用带有 POCO 类的实体框架代码优先方法。对于此类任务，POCO 类比实体对象更容易使用。

I hope this helps :)

我希望这有帮助 ：）