小贝子编程

jqGrid url 未被服务器端分页和排序和 postData 调用：MVC 4 中的 Form.searialize()



我在MVC 4中为jqGrid实现服务器端分页和排序。我正在将视图模型对象作为 postData 传递给 jqGrid url 操作方法。看看网格定义。

var isGridDefined = false;
$(document).ready(function () {
    function DefineGrid(Year, Month) {
        var mygrid = $("#RptUpload");
        mygrid.jqGrid({
            loadonce: false,
            async: false,
            datatype: 'json',
            postData: { bReload: true, Year: Year, Month: Month },
            url: '@Url.Action("DMEUploadDetailsList", "Reports")',
            jsonReader: { repeatitems: false, root: "DataRows" },
            colNames: ['@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_OrderID',
                        '@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_CompanyName',
                        '@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_PatientID',
                        '@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_PatientName',
                        "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_DOB",
                        '@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_Insurance',
                        "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_UploadDate"
            ],
            colModel: [
                { name: 'ReadingID', index: 'ReadingID', width: 55, fixed: true, sorttype: 'integer', align: 'center' },
                {
                    name: 'CompanyName', index: 'CompanyName', align: 'center', width: 200,
                    cellattr: function (rowId, tv, rawObject, cm, rdata) { return 'style="white-space: normal!important;' },
                },
                { name: 'PatientID', index: 'PatientID', width: 55, fixed: true, sorttype: 'integer', align: 'center' },
                {
                    name: 'PatientName', index: 'PatientName', align: 'center', width: 200,
                    cellattr: function (rowId, tv, rawObject, cm, rdata) { return 'style="white-space: normal!important;' },
                },
                {
                    name: 'DOB', index: 'DOB', width: 80, fixed: true, sorttype: 'date', formatter: 'date', formatoptions: { srcformat: 'm/d/Y', newformat: 'm/d/Y' },
                    align: 'center'
                },
                { name: 'InsuranceType', index: 'InsuranceType', align: 'center', width: 150, cellattr: function (rowId, tv, rawObject, cm, rdata) { return 'style="white-space: normal!important;' }, },
                {
                    name: 'UploadDate', index: 'UploadDate', width: 80, fixed: true, sorttype: 'date', formatter: 'date', formatoptions: { srcformat: 'm/d/Y', newformat: 'm/d/Y' },
                    align: 'center'
                }
            ],
            rowNum: 20,
            rowList: [20, 50, 100, 200],
            pager: '#UploadPager',
            caption: '@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_Title',
            viewrecords: true,
            height: 'auto',
            width: 770,
            hidegrid: false,
            shrinkToFit: true,
            scrollOffset: 0,
            headertitles: true,
            loadError: function (xhr, status, error) {
                alert(status + " " + error);
            },
            //onPaging: function (pgButton) {
            //    $("#RptUpload").jqGrid("setGridParam", { postData: { bReload: false } });
            //},
            loadCompete: function () {
                $("#RptUpload").jqGrid("setGridParam", { datatype: 'json', postData: { bReload: false } });
            }
        });
        mygrid.navGrid('#UploadPager', { edit: false, add: false, del: false, search: false, refresh: false });
        isGridDefined = true;
    }
    $("#rptRefresh").click(function (e) {
        e.preventDefault();
        var Form = $("form[id='FrmDMEUploadDetails']");
        Form.validate();
        if (Form.valid()) {
            RemoveValidatioMessages();
            $("#gridContainer").show();
            var Year = $("#Year").val();
            var Month = $("#Month").val();
            if (!isGridDefined)
                DefineGrid(Year, Month);
            else
                $("#RptUpload").jqGrid("setGridParam", { datatype: "json", page: 1, postData: { bReload: true, Year: Year, Month: Month } }).trigger("reloadGrid");
        }
        else {
            $("#RptUpload").clearGridData();
            $("#gridContainer").hide();
        }
        $(".chzn-select-deselect").trigger("liszt:updated");
        return false;
    });
});

&我的操作方法如下

public ActionResult DMEUploadDetailsList(bool bReload, string Year, string Month, string nd, int rows, int page, string sidx, string sord, string filters)
    {
        DataSet SearchResult = null;
        List<ReportData> ResultRows = new List<ReportData>();
        JQGridResult Result = new JQGridResult();
        if (bReload)
        {
            SearchResult = DB.ExecuteDataset("ConnectionString", "pc_GetUploadDetail",
                                            new SqlParameter("@Year", Year),
                                            new SqlParameter("@Month", Month));
            Common.SetSession(SearchResult, null, "DMEUploadByMonth");
        }
        else
            SearchResult = SessionManager.GetSession().GetAttribute("DMEUploadByMonth") as DataSet;
        if (SearchResult != null)
        {
            DataTable dtSearchResult = SearchResult.Tables[0];
            # region Handle server side Filtering, sorting and paging
            int totalRecords = dtSearchResult.Rows.Count; //before paging
            int totalPages = (int)Math.Ceiling((decimal)totalRecords / (decimal)rows); //--- number of pages
            int startIndex = ((page > 0 ? page - 1 : 0) * rows);
            if (sidx != "")
            {
                dtSearchResult.DefaultView.Sort = sidx + " " + sord;
                dtSearchResult = dtSearchResult.DefaultView.ToTable();
            }
            # endregion
            for (int i = startIndex; i < dtSearchResult.Rows.Count; i++)
            {
                ResultRows.Add(new ReportData()
                {
                    ReadingID = Convert.ToInt32(dtSearchResult.Rows[i][0]),
                    CompanyName = Convert.ToString(dtSearchResult.Rows[i][1]),
                    PatientID = Convert.ToInt32(dtSearchResult.Rows[i][2]),
                    PatientName = Convert.ToString(dtSearchResult.Rows[i][3]),
                    DOB = (dtSearchResult.Rows[i][4] != DBNull.Value ? Convert.ToDateTime(dtSearchResult.Rows[i][4]) : (DateTime?)null),
                    InsuranceType = Convert.ToString(dtSearchResult.Rows[i][5]),
                    UploadDate = (dtSearchResult.Rows[i][6] != DBNull.Value ? Convert.ToDateTime(dtSearchResult.Rows[i][6]) : (DateTime?)null)
                });
                if (ResultRows.Count == rows) break;
            }
            Result.DataRows = ResultRows;
            Result.page = page;
            Result.total = totalPages;
            Result.records = totalRecords;
        }
        return Json(Result, JsonRequestBehavior.AllowGet);
    }

当前实现的问题在于，尽管视图模型对象被成功传递给请求，但我的操作方法 DMEUploadDetailsList 没有被调用。当使用客户端分页和排序时，此实现工作正常。如果我遗漏了任何内容或纠正我的错误，请建议我让服务器端分页和排序正常工作。

此网格在刷新按钮上定义或重新加载。现在我想要的是确定是在刷新按钮单击还是分页和排序操作时调用操作方法？

[ 现在我想描述问题陈述的最后两句话。它指定何时加载我的页面时不定义网格。一旦我选择过滤器并单击刷新按钮，我的网格就会首次定义并重新加载以进行后续刷新点击。如果您浏览操作方法代码，您将看到我正在尝试使用 bReload 位变量，当它为 true 时 [在刷新按钮单击的情况下] 我想从 SQL 查询数据，否则从存储在会话中的数据集中查询数据 [在分页或排序请求的情况下]。现在，如果您在定义或重新加载调用中查看 postData 参数，我将 breload 传递给 true。我不知道当用户请求排序和分页时如何将此参数覆盖为 false。或者，如果有任何其他简单的方法可以在操作方法中获取此请求是加载数据还是分页和排序。

抱歉，但我在您的代码中没有看到任何分页实现。您计算需要跳过的记录数并将其保存在startIndex中，但以后不会使用startIndex。当前代码只是从DataTable获取数据并返回表的所有项。您需要跳过startIndex项。例如，您可以从i = startIndex而不是i = 0开始循环for。

一般来说，用SqlCommand构造SELECT语句会更有效TOP该语句使用答案中所述的构造或使用存储过程（另请参阅另一个答案）。以您的服务器代码从SQL服务器仅获取一页数据的方式，而不是获取所有数据记录并仅返回一页。

更新：我会将您的客户端代码重写为如下所示的内容

$(document).ready(function () {
    var templateDate = {
            width: 80,
            fixed: true,
            sorttype: "date",
            formatter: "date",
            formatoptions: { srcformat: "m/d/Y", newformat: "m/d/Y" }
        },
        templateInt = { width: 55, fixed: true, sorttype: "integer" },
        templateText = {
            width: 200,
            cellattr: function () {
                return 'style="white-space: normal!important;'
            }
        },
        mygrid = $("#RptUpload");
    // create the grid without filling it (datatype: "local")
    mygrid.jqGrid({
        datatype: "local", // to revent initial loading of the grid
        postData: {
            bReload: true,
            Year: function () { return $("#Year").val(); },
            Month: function () { return $("#Month").val(); }
        },
        url: '@Url.Action("DMEUploadDetailsList", "Reports")',
        jsonReader: { repeatitems: false, root: "DataRows" },
        colNames: [ "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_OrderID",
                    "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_CompanyName",
                    "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_PatientID",
                    "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_PatientName",
                    "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_DOB",
                    "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_Insurance",
                    "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_UploadDate"
        ],
        colModel: [
            { name: "ReadingID", template: templateInt },
            { name: "CompanyName", template: templateText },
            { name: "PatientID", template: templateInt },
            { name: "PatientName", template: templateText },
            { name: "DOB", template: templateDate },
            { name: "InsuranceType", width: 150, template: templateText },
            { name: "UploadDate", template: templateDate }
        ],
        cmTemplate: { align: "center" },
        rowNum: 20,
        rowList: [20, 50, 100, 200],
        pager: "#UploadPager",
        caption: "@VirtuOxAdmin.DMEUploadDetails_Grid_RptUpload_Title",
        viewrecords: true,
        height: "auto",
        width: 770,
        hidegrid: false,
        headertitles: true,
        loadError: function (xhr, status, error) {
            alert(status + " " + error);
        }
    });
    mygrid.navGrid("#UploadPager",
        { edit: false, add: false, del: false, search: false, refresh: false });
    mygrid.closest(".ui-jqgrid").hide(); // hide the grid
    $("#rptRefresh").click(function (e) {
        var Form = $("form[id=FrmDMEUploadDetails]");
        e.preventDefault();
        Form.validate();
        if (Form.valid()) {
            RemoveValidatioMessages();
            mygrid.jqGrid("setGridParam", { datatype: "json" })
                .trigger("reloadGrid", [{page: 1}])
                .closest(".ui-jqgrid").show(); // show the grid;
        } else {
            mygrid.clearGridData();
            mygrid.closest(".ui-jqgrid").hide(); // hide the grid
        }
        $(".chzn-select-deselect").trigger("liszt:updated");
        return false;
    });
});

网格将使用 datatype: "local" 创建，因此url和postData将被忽略。在那之后，在我看来，在postData和服务器端使用bReload属性似乎我不需要。尽管如此，我仍然bReload包含在JavaScript代码中，直到您将其从服务器代码中删除。

此外，我通过使用列模板（cmTemplate jqGrid的选项template和colModel的属性）简化了colModel。有关更多信息，请参阅旧答案。我还删除了一些不需要的jqGrid选项，这些选项的值是默认值（请参阅选项文档中的"默认"列）。

关于新版本的STORED PROCEDURE的使用（pc_GetUploadDetail在你的代码中），你可以考虑引入支持更多参数的新版本（如pc_GetUploadDetailPaged）。它不会破坏使用旧过程的现有代码，但您仍然可以在 SQL Server 上使用数据的排序和分页，而不是将所有查询结果获取到 Web 服务器并在 C# 中实现排序和分页。

我通过在jqGrid定义中删除Form.serialize（）作为postData参数来解决jqGrid url未被调用的初始问题并重新加载调用。此问题与服务器端排序和分页无关。这是由于我之前的 postData 参数定义为 Form.serialize（） 引起的。相反，我使用将单个参数传递给 postData 数组。我的下一个问题与服务器端的排序和分页有关。其中我想在用户翻阅网格或想要对网格数据进行排序时从会话数据集中获取数据;否则，通过查询 SQL 服务器重新加载新数据。根据Oleg的回答，我一定采用了在SQL端而不是在c#中进行分页和排序的简化方法。但是我不允许按照 Oleg 的建议添加新版本的存储过程。所以我坚持在名为 operCode 的 postData 数组中使用额外的参数，就像在网格定义中这样。

 postData: {
            operCode: "Reload",
            Year: function () { return $("#Year").val(); },
            InsuranceID: function () { return $("#InsuranceType").val(); },
            CustomerID: function () { return $("#CompanyName").val(); }
        },

现在我添加了 onPaging 和 onSortCol 事件来覆盖 operCode postData 参数值，如下所示

onPaging: function (pgButton) {
            mygrid.jqGrid("setGridParam", { datatype: 'json', postData: { operCode: "Paging" } });
        },
        onSortCol: function (index, iCol, sortorder) {
            mygrid.jqGrid("setGridParam", { datatype: 'json', postData: { operCode: "Sorting" } });
        }

现在，每当用户单击刷新按钮时，operCode 都会作为重新加载发送，在分页时作为"分页"发送，在排序时作为"排序"发送

我的服务器端操作方法代码如下

public ActionResult DMEUploadDetails(string operCode, string Year, string Month, string nd, int rows, int page, string sidx, string sord, string filters)
    {
        DataSet SearchResult = null;
        List<ReportData> ResultRows = new List<ReportData>();
        JQGridResult Result = new JQGridResult();
        if (operCode == "Reload")
        {
            SearchResult = DB.ExecuteDataset("ConnectionString", "pc_GetUploadDetail",
                                            new SqlParameter("@Year", Year),
                                            new SqlParameter("@Month", Month));
            Common.SetSession(SearchResult, null, "POXMonthlyUploads");
        }
        else
            SearchResult = (SessionManager.GetSession().GetAttribute("POXMonthlyUploads") as System.Web.UI.WebControls.GridView).DataSource as DataSet;
        if (SearchResult != null)
        {
            DataTable dtSearchResult = SearchResult.Tables[0];
            # region Handle server side Filtering, sorting and paging
            int totalRecords = dtSearchResult.Rows.Count; //before paging
            int totalPages = (int)Math.Ceiling((decimal)totalRecords / (decimal)rows); //--- number of pages
            int startIndex = ((page > 0 ? page - 1 : 0) * rows);
            if (sidx != "" && operCode == "Sorting")
            {
                dtSearchResult.DefaultView.Sort = sidx + " " + sord;
                dtSearchResult = dtSearchResult.DefaultView.ToTable();
                SearchResult.Tables.RemoveAt(0);
                SearchResult.Tables.Add(dtSearchResult);
                Common.SetSession(SearchResult, null, "POXMonthlyUploads");
            }
            # endregion
            for (int i = startIndex; i < dtSearchResult.Rows.Count; i++)
            {
                ResultRows.Add(new ReportData()
                {
                   //code to fill data to structure object
                });
                if (ResultRows.Count == rows) break;
            }
            Result.DataRows = ResultRows;
            Result.page = page;
            Result.total = totalPages;
            Result.records = totalRecords;
        }
        return Json(Result, JsonRequestBehavior.AllowGet);
    }

非常感谢 Oleg 给了我一些关于 jqGrid 的额外知识，并给了我关于服务器端分页和排序的好主意。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium