Блог

Я не могу связать более 500 записей в jqGrid через JSON, в то время как мое требование — связать 150000 записей в jqGrid и добавить разбивку на страницы.

Если я добавлю TOP 400 SELECT запрос, я смогу просматривать записи, но для TOP 500 него отображается пустой. Ниже приведен мой код:

 $(document).ready(function() {
    jQuery("#jQGridRawData").jqGrid({
        url: 'Transaction.aspx/GetData',
        datatype: 'json',
        mtype: 'POST',
        height: 'auto',
        serializeGridData: function(postData) {
            return JSON.stringify(postData);
        },
        ajaxGridOptions: {
            contentType: "application/json"
        },
        colNames: ["ID", "Pos", "Dep", "Systems", "Batch ID", "Check", "Code", "Amount"],
        colModel: [{
            name: 'testpmsID',
            index: 'testpmsID',
            width: 60,
            sortable: false,
            search: false,
            align: 'center'
        }, {
            name: "PostingDate",
            index: "PostingDate",
            width: 126,
            align: 'center',
            search: true,
            sorttype: 'date',
            formatter: 'date',
            formatoptions: {
                newformat: 'm-d-y'
            }, //formatoptions: { srcformat: 'd/m/Y', newformat: 'd/m/Y'}
            searchoptions: {
                dataInit: function(element) {
                    $(element).datepicker({
                        autoclose: true,
                        format: 'mm-dd-yy',
                        orientation: 'left'
                    });
                },
                sopt: ['eq', "ge", "le"],
                clearSearch: false
            }
        }, {
            name: "DepositDate",
            index: "DepositDate",
            width: 126,
            align: 'center',
            search: true,
            sorttype: 'date',
            formatter: 'date',
            formatoptions: {
                newformat: 'm-d-y'
            },
            searchoptions: {
                dataInit: function(element) {
                    $(element).datepicker({
                        autoclose: true,
                        format: 'mm-dd-yy',
                        orientation: 'left'
                    });
                },
                sopt: ['eq', "ge", "le"],
                clearSearch: false
            }
        }, {
            name: "Systems",
            index: "Systems",
            width: 110,
            align: 'left',
            searchoptions: {
                sopt: ['eq', 'bw', 'bn', 'cn', 'nc', 'ew', 'en'],
                clearSearch: false
            }
        }, {
            name: "BatchID",
            index: "BatchID",
            width: 100,
            sorttype: 'number',
            align: 'left',
            searchoptions: {
                sopt: ['eq', 'bw', 'bn', 'cn', 'nc', 'ew', 'en'],
                clearSearch: false
            }
        }, {
            name: "CheckNumber",
            index: "CheckNumber",
            width: 140,
            align: 'left',
            searchoptions: {
                sopt: ['eq', 'ne', 'le', 'lt', 'gt', 'ge'],
                clearSearch: false
            }
        }, {
            name: "PaymentCode",
            index: "PaymentCode",
            width: 175,
            align: 'left',
            search: true,
            searchoptions: {
                sopt: ['eq', "ge", "le"],
                clearSearch: false
            }
        }, {
            name: "PaymentAmount",
            index: "PaymentAmount",
            width: 160,
            align: 'right',
            search: true,
            searchoptions: {
                sopt: ['eq', "ge", "le"],
                clearSearch: false
            },
            formatter: 'currency',
            formatoptions: {
                prefix: '$ ',
                thousandsSeparator: ',',
                decimalPlaces: 2
            }
        }],
        pager: "#jQGridRawDataPager",
        rowNum: 20,
        rowTotal: 2000,
        rowList: [20, 30, 50],
        loadonce: true,
        //rownumbers: true,
        //add: false,
        //edit: false,
        width: '100%',
        hidegrid: false,
        viewrecords: true,
        headertitles: true,
        responsive: true,
        styleUI: 'Bootstrap',
        shrinkToFit: false,
        forceFit: false,
        hoverrows: false,
        //gridview: true,
        viewsortcols: [false, 'vertical', true],
        jsonReader: {
            page: function(obj) {
                return 1;
            },
            total: function(obj) {
                return 1;
            },
            records: function(obj) {
                return obj.d.length;
            },
            root: function(obj) {
                return obj.d;
            },
            repeatitems: false,
            id: "0"
        }
    }); //grid intiallization code end

    // Setup buttons
    jQuery("#jQGridRawData").jqGrid('navGrid', '#jQGridRawDatPager', {
        edit: false,
        add: false,
        del: false,
        search: true
    }, {
        height: 200,
        reloadAfterSubmit: true
    });

    // Setup filters
    jQuery("#jQGridRawData").jqGrid('filterToolbar', {
        defaultSearch: true,
        stringResult: true,
        searchOperators: true
    });
});
  

 [WebMethod]
public static List<Dictionary<string, object>> GetRawData()
{
    SqlConnection sqlConnection = new SqlConnection();
    sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["abcd"].ConnectionString;
    sqlConnection.Open();
    string sqlStatement = "SELECT  * FROM tablename";
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlStatement, sqlConnection);
    DataTable dtResult = new DataTable();
    sqlDataAdapter.Fill(dtResult);
    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    Dictionary<string, object> row;
    foreach (DataRow dr in dtResult.Rows)
    {
        row = new Dictionary<string, object>();
        foreach (DataColumn col in dtResult.Columns)
        {
            row.Add(col.ColumnName, dr[col]);
        }
        rows.Add(row);
    }
    return rows;
}
  

1) Мы должны проверить, превышает ли наша maxJsonLength значение exceed или нет.

что делать: перейдите в web.config и добавьте следующий код

 <system.web.extensions>
    <scripting>
        <webServices>
            <!-- Try increasing this value to a larger value (Int.MaxValue used below) -->
            <jsonSerialization maxJsonLength="2147483644"></jsonSerialization>
        </webServices>
    </scripting>
</system.web.extensions>