如何在 Jqgrid 中显示间接数据 [英] How to display indirect data in Jqgrid

问题描述

我正在我的 ASP.net MVC Web 应用程序中实现 Jqgrid.我有这样的数据:

 SID SNAME CITY1 ABC 112 XYZ 123 ACX 134 KHG 145 自动进稿器 156 KKR 16

还有一张桌子

 CID CNAME11 钦奈12 孟买13 德里喜欢这样

但是，在网格中我想这样显示:

 SID SNAME 城市1 ABC钦奈2 XYZ 孟买3 ACX德里4 KHG班格罗尔5 ADF 海得拉巴6 KKR 加尔各答

我无法使用join，因为类结构是这样的:

 班级学生{长长的，字符串名称，长城}

所以，当我从数据库中读取数据时，我得到的是城市 ID 而不是城市名称.

但是，我想在网格数据中向最终用户显示城市名称而不是城市 ID

我需要一个类似 lookup 的功能，以便在将数据绑定到 jQgrid 之前，城市 id 将与城市名称进行映射并显示它而不是显示 ID

我没有找到完成这项工作的方法.

请帮忙..

我使用的控制器方法如下:公共 JsonResult 学生(){列出<学生>liStudents = 新列表<学生>();SortedList<long, string>slLocations = new SortedList<long, string>();slLocations = Student.LoadLocations();liStudents = Students.GetStudents();返回 Json(liStudents,JsonRequestBehavior.AllowGet);}

如何修改return语句以在json响应中也抛出slLocations

解决方案

我之前已经回答了已关闭的问题(请参阅 插件，我在 中描述了如何动态设置colNames.In 可用于管理来自服务器端的更多信息.

已更新:对应的控制器动作Students大概如下

公开课学生{公共长 SID { 获取；放;}公共字符串 SNAME { 获取；放;}公共长城{得到;放;}}公共类城市{公共长 CID { 获取；放;}公共字符串 CNAME { 获取；放;}}...公共类 HomeController:控制器 {...公共 JsonResult 学生 () {var students = 新列表<学生>{新学生 { SID = 1, SNAME = "ABC", CITY = 11 },新学生 { SID = 2, SNAME = "ABC", CITY = 12 },新学生 { SID = 3, SNAME = "ABC", CITY = 13 },新学生 { SID = 4, SNAME = "ABC", CITY = 13 },新学生 { SID = 5, SNAME = "ABC", CITY = 12 },新学生 { SID = 6, SNAME = "ABC", CITY = 11 }};var locations = 新列表<城市>{新城市 { CID = 11, CNAME = "Chennai"},新城市 { CID = 12, CNAME = "孟买"},新城市 { CID = 13, CNAME = "德里"}};//排序和拼接位置对应jqGrid editoptions.value格式var sortedLocations = locations.OrderBy(location => location.CNAME);var sbLocations = new StringBuilder();foreach (var sortedLocation in sortedLocations) {sbLocations.Append(sortedLocation.CID);sbLocations.Append(':');sbLocations.Append(sortedLocation.CNAME);sbLocations.Append(';');}如果(sbLocations.Length > 0)sbLocations.Length -= 1;//删除最后一个 ';'返回 JSON(新 {colModelOptions = 新 {城市 = 新 {格式化程序=选择"，编辑类型=选择"，编辑选项 = 新 {值 = sbLocations.ToString()},stype = "选择",搜索选项 = 新 {sopt = new[] { "eq", "ne" },价值=:任何；"+ sb位置}}},行 = 学生},JsonRequestBehavior.AllowGet);}}

I am implementing Jqgrid in my ASP.net MVC web application. I have data some thing like this:

 SID SNAME CITY
  1   ABC   11
  2   XYZ   12
  3   ACX   13
  4   KHG   14
  5   ADF   15
  6   KKR   16

and another table

 CID   CNAME

  11   Chennai   
  12   Mumbai
  13   Delhi   like this

but, in the grid i would like to display like this:

  SID SNAME  City
  1   ABC   Chennai
  2   XYZ   Mumbai
  3   ACX   Delhi
  4   KHG   Banglore
  5   ADF   Hyderabad
  6   KKR   Kolkatta

I was not able to use join because the class structure is like this:

 Class Student

{
   long sid,
   string sname,
   long city
}

So, when i am reading from the data base i am getting the city id not city name.

But, i would like to display city name instead of City ID in the grid data to end user

i need some thing like a lookup function so that before binding data to the jQgrid,the city id will be mapped with city name and displays it instead of displaying ID

I didnt find a way to get this done.

Please help..

The controller method i am using is as follows:


public JsonResult Students()
    {
        List<Students> liStudents = new  List<Students>();
        SortedList<long, string> slLocations = new SortedList<long, string>();
        slLocations = Students.LoadLocations();
        liStudents = Students.GetStudents();
        return Json(liStudents,JsonRequestBehavior.AllowGet);
    }

How to modify the return statement to throw slLocations too in the json response

解决方案

I answered already on the closed question before (see here). Nevertheless I decide to answer on your question in detail because the problem which you describe is really very common.

I start with reminding that jqGrid provides formatter: "select" which uses formatoptions.value or editoptions.value to decode ids to texts. The formatter: "select" uses value and optional separator, delimiter and defaultValue properties, but it can't uses editoptions.dataUrl to get required data from the server instead of usage static value. The problem is very easy: processing dataUrl works asynchronous, but during formatting of the column of grid body one don't support delayed filling. So to use formatter: "select" one have to set formatoptions.value or editoptions.value before the server response will be processed by jqGrid.

In the old answer I suggested to extend JSON response returned from the server with additional data for editoptions.value of the columns having formatter: "select". I suggest to set the beforeProcessing. For example one can generate the server response in the following format:

{
    "cityMap": {"11": "Chennai", "12": "Mumbai", "13": "Delhi"},
    "rows": [
        { "SID": "1",  "SNAME": "ABC", "CITY": "11" },
        { "SID": "2",  "SNAME": "XYZ", "CITY": "12" },
        { "SID": "3",  "SNAME": "ACX", "CITY": "13" },
        { "SID": "4",  "SNAME": "KHG", "CITY": "13" },
        { "SID": "5",  "SNAME": "ADF", "CITY": "12" },
        { "SID": "6",  "SNAME": "KKR", "CITY": "11" }
    ]
}

and uses the following jqGrid options

colModel: [
    {name: "SNAME", width: 250},
    {name: "CITY", width: 180, align: "center"}
],
beforeProcessing: function (response) {
    var $self = $(this);
    $self.jqGrid("setColProp", "CITY", {
        formatter: "select",
        edittype: "select",
        editoptions: {
            value: $.isPlainObject(response.cityMap) ? response.cityMap : []
        }
    });
},
jsonReader: { id: "SID"}

The demo demonstrates the approach. It displays

One can use the same approach to set any column options dynamically. For example one can use

{
    "colModelOptions": {
        "CITY": {
            "formatter": "select",
            "edittype": "select",
            "editoptions": {
                "value": "11:Chennai;13:Delhi;12:Mumbai"
            },
            "stype": "select",
            "searchoptions": {
                "sopt": [ "eq", "ne" ],
                "value": ":Any;11:Chennai;13:Delhi;12:Mumbai"
            }
        }
    },
    "rows": [
        { "SID": "1",  "SNAME": "ABC", "CITY": "11" },
        { "SID": "2",  "SNAME": "XYZ", "CITY": "12" },
        { "SID": "3",  "SNAME": "ACX", "CITY": "13" },
        { "SID": "4",  "SNAME": "KHG", "CITY": "13" },
        { "SID": "5",  "SNAME": "ADF", "CITY": "12" },
        { "SID": "6",  "SNAME": "KKR", "CITY": "11" }
    ]
}

and the following JavaScript code

var filterToolbarOptions = {defaultSearch: "cn", stringResult: true, searchOperators: true},
    removeAnyOption = function ($form) {
        var $self = $(this), $selects = $form.find("select.input-elm");
        $selects.each(function () {
            $(this).find("option[value='']").remove();
        });
        return true; // for beforeShowSearch only
    },
    $grid = $("#list");

$.extend($.jgrid.search, {
    closeAfterSearch: true,
    closeAfterReset: true,
    overlay: 0,
    recreateForm: true,
    closeOnEscape: true,
    afterChange: removeAnyOption,
    beforeShowSearch: removeAnyOption
});

$grid.jqGrid({
    colModel: [
        {name: "SNAME", width: 250},
        {name: "CITY", width: 180, align: "center"}
    ],
    beforeProcessing: function (response) {
        var $self = $(this), options = response.colModelOptions, p,
            needRecreateSearchingToolbar = false;
        if (options != null) {
            for (p in options) {
                if (options.hasOwnProperty(p)) {
                    $self.jqGrid("setColProp", p, options[p]);
                    if (this.ftoolbar) { // filter toolbar exist
                        needRecreateSearchingToolbar = true;
                    }
                }
            }
            if (needRecreateSearchingToolbar) {
                $self.jqGrid("destroyFilterToolbar");
                $self.jqGrid("filterToolbar", filterToolbarOptions);
            }
        }
    },
    jsonReader: { id: "SID"}
});
$grid.jqGrid("navGrid", "#pager", {add: false, edit: false, del: false})
$grid.jqGrid("filterToolbar", filterToolbarOptions);

The demo uses the above code.

We recreate the searching filter if any option are changed dynamically. The way allows implement more flexible solutions. For example the server can detect the language preferences of the client (of the web browser) and return formatting options for numbers, dates and so on based on the options. I'm sure that everyone can suggest other interesting scenarios.

One more remark. If you have too many items in select in (searchoptions.value and editoptions.value) I would recommend you don't use strings instead of objects as the value of searchoptions.value and editoptions.value. It allows you to specify the order of items in the select element.

If you will have too many items in select (for example all cities of your country) then you can consider to use select2 plugin which usage I demonstrate in the answer. It simplify selection of options because it convert select in element which is very close to jQuery UI Autocomplete.

The next demo demonstrate the usage of select2 plugin. If one click on the dropdown arrow of "select" element of the searching toolbar or the searching dialog one get additional input filed which can be used for quick searching. If one starts to type some text in the input box (for example "e" on an example on the picture below) the list of options will be reduced to the options having the typed text as substring:

I personally find such "select-searching" control very practical.

By the way I described in the another answer how to set colNames dynamically. In can be used to manage more information from the server side.

UPDATED: The corresponding controller action Students can be about the following

public class Student {
   public long SID { get; set; }
   public string SNAME { get; set; }
   public long CITY { get; set; }
}
public class City {
    public long CID { get; set; }
    public string CNAME { get; set; }
}
...
public class HomeController : Controller {
    ...
    public JsonResult Students () {
        var students = new List<Student> {
                new Student { SID = 1, SNAME = "ABC", CITY = 11 },
                new Student { SID = 2, SNAME = "ABC", CITY = 12 },
                new Student { SID = 3, SNAME = "ABC", CITY = 13 },
                new Student { SID = 4, SNAME = "ABC", CITY = 13 },
                new Student { SID = 5, SNAME = "ABC", CITY = 12 },
                new Student { SID = 6, SNAME = "ABC", CITY = 11 }
            };
        var locations = new List<City> {
                new City { CID = 11, CNAME = "Chennai"},
                new City { CID = 12, CNAME = "Mumbai"},
                new City { CID = 13, CNAME = "Delhi"}
            };
        // sort and concatinate location corresponds to jqGrid editoptions.value format
        var sortedLocations = locations.OrderBy(location => location.CNAME);
        var sbLocations = new StringBuilder();
        foreach (var sortedLocation in sortedLocations) {
            sbLocations.Append(sortedLocation.CID);
            sbLocations.Append(':');
            sbLocations.Append(sortedLocation.CNAME);
            sbLocations.Append(';');
        }
        if (sbLocations.Length > 0)
            sbLocations.Length -= 1; // remove last ';'
        return Json(new {
                   colModelOptions = new {
                       CITY = new {
                           formatter = "select",
                           edittype = "select",
                           editoptions = new {
                               value = sbLocations.ToString()
                           },
                           stype = "select",
                           searchoptions = new {
                               sopt = new[] { "eq", "ne" },
                               value = ":Any;" + sbLocations
                           }
                       }
                   },
                   rows = students    
               },
               JsonRequestBehavior.AllowGet);
    }
}

这篇关于如何在 Jqgrid 中显示间接数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！