We are using Virtual Scroll to support a table with about 5000+ rows, to filter + sort, and display data. While doing so we were able to display the data via remote and call new data using virtual scroll.
Link:
https://paramquery.com/pro/demos/virtual_scrollWe are currently facing an issue in sending sorting and filtering requests from the table.
We are not being able to fetch the value we have filled in the filter header, or the sorting direction clicked by the user because of which we are not being able to pass the filter/sort instruction to the server.
I have attached the aspx and cs file with web method
Kindly guide us how this can be fixed.
---- paramquery-gridv7.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="paramquery-gridv7.aspx.cs" Inherits="paramquery_gridv7" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" type="text/css" href="/include/style_sheet.css?v=1.0.5" />
<link rel="stylesheet" href="/pq-grid/jquery-ui-1.11.4.custom/jquery-ui.min.css" />
<script src="/pq-grid/jquery-ui-1.11.4.custom/external/jquery/jquery.js"></script>
<script src="/pq-grid/jquery-ui-1.11.4.custom/jquery-ui.min.js"></script>
<style>
.pq-grid, .pq-grid * {
box-sizing: content-box;
}
.pq-grid table {
border-collapse: separate;
}
</style>
<script src="/pq-grid/jszip-2.5.0/jszip.min.js"></script>
<script src="/pq-grid/jquery-ui-touch-punch/touch-punch/touch-punch.min.js"></script>
<!--pqSelect-->
<link rel="Stylesheet" href="/pq-grid/pqselect/trial/pqselect.min.css" />
<script src="/pq-grid/pqselect/trial/pqselect.min.js"></script>
<link rel="stylesheet" href="/pq-grid/Content/css3.5.1/pqgrid.min.css" />
<link rel="stylesheet" href="/pq-grid/Content/css3.5.1/pqgrid.ui.min.css" id="pqgrid_ui_link" />
<link rel="stylesheet" href="/pq-grid/Content/css3.5.1/themes/steelblue/pqgrid.css" id="pqgrid_office_link" />
<script src="/pq-grid/Content/js3.5.1/pqgrid.min.js"></script>
</head>
<body>
<script class="ppjs">
$(function () {
var pqVS = {
rpp: 10, //records per page.
init: function () {
this.totalRecords = 0;
this.requestPage = 1; //begins from 1.
this.data = [];
}
};
pqVS.init();
var obj = {
scrollModel: {
autoFit: true
},
numberCell: { width: 60 },
title: "Virtual Scrolling",
virtualX: true,
virtualY: true,
resizable: true,
filterModel: { on: true, header: true, type: 'remote' },
sortModel: { type: 'remote', sorter: [{ dataIndx: 'Symbol', dir: 'up' }] },
beforeSort: function (evt) {
if (evt.originalEvent) {//only if sorting done through header cell click.
pqVS.init();
}
},
beforeTableView: function (evt, ui) {
var initV = ui.initV,
finalV = ui.finalV,
data = pqVS.data,
rpp = pqVS.rpp,
requestPage;
if (initV != null) {
//if records to be displayed in viewport are not present in local cache,
//then fetch them from remote database/server.
if (data[initV] && data[initV].pq_empty) {
requestPage = Math.floor(initV / rpp) + 1;
}
else if (data[finalV] && data[finalV].pq_empty) {
requestPage = Math.floor(finalV / rpp) + 1;
}
if (requestPage >= 1) {
if (pqVS.requestPage != requestPage) {
pqVS.requestPage = requestPage;
//initiate remote request.
this.refreshDataAndView();
}
}
}
}
};
obj.colModel = [
//{
// title: "Company", dataIndx: 'company',
// filter: {
// condition: 'begin',
// listeners: [{
// 'change': function () {
// pqVS.init();
// var $grid = $(this).closest(".pq-grid");
// $grid.pqGrid('filter', {
// oper: 'replace',
// data: [{ dataIndx: 'company', value: $(this).val() }]
// });
// }
// }],
// type: 'textbox',
// value: 'C', on: true
// }
//},
//{ title: "Notes", dataIndx: 'notes' },
//{ title: "Revenues", dataType: "float", dataIndx: 'revenue', format: '£#,###.00' },
//{ title: "Profit", dataType: "float", dataIndx: 'profits', format: '£#,###.00' }
{ title: "#", width: 100, dataIndx: "inRowIndex", align: "center" },
{ title: "Exchange", width: 130, dataIndx: "Exchange" },
{
title: "Symbol", width: 190, dataIndx: "Symbol",
filter: {
condition: 'begin',
listeners: [{
'change': function () {
pqVS.init();
var $grid = $(this).closest(".pq-grid");
$grid.pqGrid('filter', {
oper: 'replace',
data: [{ dataIndx: 'Symbol', value: $(this).val() }]
});
}
}],
type: 'textbox',
value: 'ACC', on: true
}
},
{ title: "Type", width: 100, dataIndx: "OrderType", align: "center" },
{ title: "B/S", width: 100, dataIndx: "BuySell", align: "center" },
{ title: "O. Qty", width: 100, dataIndx: "Qty", align: "right" },
{ title: "P. Qty", width: 100, dataIndx: "QtyRemaining", align: "right" },
{ title: "Price", width: 100, dataIndx: "OrderPrice", align: "right" },
{ title: "Status", width: 100, dataIndx: "OrderStatus" },
{ title: "Time", width: 100, align: "right", dataIndx: "stCreationTime" },
{ title: "Row Index", width: 120, dataIndx: "OrderUID" }
];
obj.dataModel =
{
//method: "GET",
location: "remote",
//contentType: "application/json; charset=utf-8",
beforeSend: function (jqXHR, settings)
{
console.log(settings.url);
jqXHR.setRequestHeader("Content-Type", "application/json");
},
getUrl: function ()
{
debugger;
//console.log("pq_sort: " + this.sortIndx);
// console.log("pq_filter: " + obj.filter.pq_filter);
var sortDir = (pqVS.sortDir == "up") ? "asc" : "desc";
var sort = ['inRowIndex', 'Exchange', 'Symbol', 'OrderType', 'BuySell', 'Qty', 'QtyRemaining', 'OrderPrice', 'OrderStatus', 'stCreationTime', 'OrderUID'];
var loData = 'fsSymbol=&pq_datatype="JSON"&pq_filter=""&pq_sort=""&pq_curpage=' + pqVS.requestPage + '&pq_rpp=' + pqVS.rpp;
//alert("1a");
return {
//url: "jquerygrid.aspx/GetCustomers", data: "cur_page=" + this.curPage + "&records_per_page=" + this.rPP + "&sortBy=" + sort[this.sortIndx] + "&dir=" + sortDir
url: "/paramquery-gridv7.aspx/getOrders",
data: loData
//postData: function () {
// return {
// pq_datatype: "JSON",
// pq_filter: "",
// pq_sort: "",
// pq_curpage: pqVS.requestPage,
// pq_rpp: pqVS.rpp
// };
//}
};
},
getData: function (response) {
var response = $.parseJSON(response.d);
//debugger;
//console.log("pqVS.requestPage: " + pqVS.requestPage);
//console.log("response.curPage: " + response.curPage);
var data = response.data,
totalRecords = response.totalRecords,
len = data.length,
curPage = response.curPage,
pq_data = pqVS.data,
init = (curPage - 1) * pqVS.rpp;
if (!pqVS.totalRecords) {
//first time initialize the rows.
for (var i = len; i < totalRecords; i++) {
pq_data[i + init] = { pq_empty: true };
}
pqVS.totalRecords = totalRecords;
}
for (var i = 0; i < len; i++) {
pq_data[i + init] = data[i];
pq_data[i + init].pq_empty = false;
}
return { data: pq_data }
},
error: function (jqXHR, textStatus, errorThrown) {
//debugger;
if (jqXHR.statusText != "abort")
alert(jqXHR.responseText);
}
};
//obj.dataModel = {
// dataType: "JSON",
// location: "remote",
// //url: "https://paramquery.com/pro/infiniteData",
// url: "paramquery-gridv7.aspx/getOrders",
// postData: function () {
// return {
// pq_curpage: pqVS.requestPage,
// pq_rpp: pqVS.rpp
// };
// },
// getData: function (response) {
// //debugger;
// var data = response.data,
// totalRecords = response.totalRecords,
// len = data.length,
// curPage = response.curPage,
// pq_data = pqVS.data,
// init = (curPage - 1) * pqVS.rpp;
// if (!pqVS.totalRecords) {
// //first time initialize the rows.
// for (var i = len; i < totalRecords; i++) {
// pq_data[i + init] = { pq_empty: true };
// }
// pqVS.totalRecords = totalRecords;
// }
// for (var i = 0; i < len; i++) {
// pq_data[i + init] = data[i];
// pq_data[i + init].pq_empty = false;
// }
// return { data: pq_data }
// },
// error: function (jqXHR, textStatus, errorThrown) {
// //alert(errorThrown);
// }
//};
$("#grid_infinite").pqGrid(obj);
});
</script>
<form id="form1" runat="server">
<div>
<div id="grid_infinite"></div>
</div>
</form>
</body>
</html>
--- paramquery-gridv7.aspx.cs
using MongoDB.Bson;
using MongoDB.Bson.IO;
using System;
using System.Collections.Generic;
using System.IO.Compression;
using System.Linq;
using System.Web;
using System.Web.Script.Services;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class paramquery_gridv7 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
public static string getOrders(string fsSymbol, string pq_datatype, string pq_filter, string pq_sort, int pq_curpage, int pq_rpp)
{
//HttpContext.Current.Response.AppendHeader("Content-encoding", "gzip");
//HttpContext.Current.Response.Filter = new GZipStream(HttpContext.Current.Response.Filter, CompressionMode.Compress);
string lsOrders = string.Empty;
int liUserID = CommonFunction.getActiveSessionUserID();
List<MTOrders> loOrderList = GlobalData.moOrders.Take(100).ToList();
loOrderList = loOrderList.Where(p => p.inCreatedBy == liUserID &&
((p.dtCreatedOn >= CommonFunction.GetISTDateTime().Date) || p.dtModifiedOn >= CommonFunction.GetISTDateTime().AddSeconds(-10))).ToList();
SRCWorkspaceOutput loAllOrders = new SRCWorkspaceOutput();
List<dynamic> loOrdersByFilter = new List<dynamic>();
if (loOrderList != null && loOrderList.Count > 0)
{
foreach (MTOrders loItem in loOrderList.OrderBy(p => p.inRowIndex))
{
loOrdersByFilter.Add(new SRCOrderBook
{
inRowIndex = loItem.inRowIndex,
Exchange = loItem.InstrumentInfo.Exchange,
Symbol = loItem.InstrumentInfo.Symbol,
OrderType = loItem.OrderType.ToString(),
BuySell = loItem.BuySell.ToString(),
Qty = loItem.Qty,
QtyRemaining = loItem.QtyRemaining,
OrderPrice = loItem.OrderPrice,
OrderStatus = (char)loItem.OrderStatus,
stCreationTime = loItem.stCreationTime,
OrderUID = loItem._id.ToString(),
});
}
//if (fiExchange != 0)
//{
// string lsExchange = "NSECM";
// if (fiExchange == 3)
// lsExchange = "BSE";
// else if (fiExchange == 2)
// lsExchange = "FNO";
// loOrderList = loOrderList.Where(p => p.InstrumentInfo.Exchange == lsExchange).ToList();
//}
//if (!string.IsNullOrEmpty(fsSymbol))
// loOrderList = loOrderList.Where(p => p.InstrumentInfo.Symbol == fsSymbol).ToList();
//if (fiStatus != 0)
// loOrderList = loOrderList.Where(p => p.OrderStatus == (char)fiStatus).ToList();
loAllOrders.data = loOrdersByFilter;
loAllOrders.totalRecords = 10000; // loOrdersByFilter.Count;
loAllOrders.curPage = pq_curpage;
}
lsOrders = loAllOrders.ToJson(new MongoDB.Bson.IO.JsonWriterSettings { OutputMode = JsonOutputMode.Strict });
return lsOrders;
}
[WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
public static string getModifiedOrders(int fiExchange, string fsSymbol, int fiStatus)
{
HttpContext.Current.Response.AppendHeader("Content-encoding", "gzip");
HttpContext.Current.Response.Filter = new GZipStream(HttpContext.Current.Response.Filter, CompressionMode.Compress);
string lsOrders = string.Empty;
int liUserID = CommonFunction.getActiveSessionUserID();
List<MTOrders> loOrderList = GlobalData.moOrders.ToList();
loOrderList = loOrderList.Where(p => p.inCreatedBy == liUserID && p.dtModifiedOn >= CommonFunction.GetISTDateTime().AddSeconds(-20)).ToList();
SRCAllOrderBook loAllOrders = new SRCAllOrderBook();
if (loOrderList != null && loOrderList.Count > 0)
{
List<SRCOrderBook> loOrdersByFilter = new List<SRCOrderBook>();
List<SRCOrderBook> loModifiedOrders = new List<SRCOrderBook>();
foreach (MTOrders loItem in loOrderList.OrderBy(p => p.inRowIndex))
{
loModifiedOrders.Add(new SRCOrderBook
{
inRowIndex = loItem.inRowIndex,
Exchange = loItem.InstrumentInfo.Exchange,
Symbol = loItem.InstrumentInfo.Symbol,
OrderType = loItem.OrderType.ToString(),
BuySell = loItem.BuySell.ToString(),
Qty = loItem.Qty,
QtyRemaining = loItem.QtyRemaining,
OrderPrice = loItem.OrderPrice,
OrderStatus = (char)loItem.OrderStatus,
stCreationTime = loItem.stCreationTime,
OrderUID = loItem._id.ToString(),
});
}
//var loOrders = loOrderList.Select(p => new
//{
// inRowIndex = p.inRowIndex,
// Exchange = p.InstrumentInfo.Exchange,
// Symbol = p.InstrumentInfo.Symbol,
// OrderType = p.OrderType.ToString(),
// BuySell = p.BuySell.ToString(),
// Qty = p.Qty,
// QtyRemaining = p.QtyRemaining,
// OrderPrice = p.OrderPrice,
// OrderStatus = (char)p.OrderStatus,
// stCreationTime = p.stCreationTime,
// OrderUID = p._id.ToString()
//}).OrderBy(p => p.inRowIndex).ToList();
loAllOrders.OrdersByFilter = loOrdersByFilter;
loAllOrders.OrdersByModified = loModifiedOrders;
}
lsOrders = loAllOrders.ToJson(new MongoDB.Bson.IO.JsonWriterSettings { OutputMode = JsonOutputMode.Strict });
return lsOrders;
}
}
Thanks!