Sorting and Paging issues

Question

Hello,

I would like to sort the whole dataset instead of page by page sorting.But for some reason it is sorting page by page instead of the whole dataset.Could you please help me in this issue.Iam using a grouping store.

    var store = new FMP.AspNetJsonStore({
        fields: [
                       { name: 'AssetID' },
                       { name: 'AssociationID' },
                       { name: 'Image' },
                       { name: 'StatusName' },
                       { name: 'ModelName' },
                       { name: 'IPAddress' },
                       { name: 'InScope', type: 'boolean' },
                       { name: 'ServicePlanName' },
                       { name: 'PricePlanName' },
                       { name: 'PricePlanDescription' },
                       { name: 'Program' },
                       { name: 'ServicePlanID' },
                       { name: 'Customer' },
                       { name: 'Black', type: 'float' },
                       { name: 'Cyan', type: 'float' },
                       { name: 'Magenta', type: 'float' },
                       { name: 'Yellow', type: 'float' },
                       { name: 'BlackPct' },
                       { name: 'CyanPct' },
                       { name: 'MagentaPct' },
                       { name: 'YellowPct' },
                       { name: 'PrinterMarkerSupplies' },
                       { name: 'PageCount' },
                       { name: 'BlackImpressions' },
                       { name: 'ColorImpressions' },
                       { name: 'PricePlanID' },
                       { name: 'ResponsibilityForAction' },
                       { name: 'PrinterSerialNumber' }

                    ],

        totalProperty: "TotalCount",
        autoLoad: { params: { start: 0, limit: myPageSize} },
        //autoLoad: true,
        proxy: new Ext.data.HttpProxy({
            // Call web service method using GET syntax
            url: 'GetPrintersGrid.asmx/buildGrid',
            // Ask for Json response
            headers: { 'Content-type': 'application/json' },
            method: "GET"
        }),
        //remoteSort: true,
        sortInfo: { field: 'PageCount', direction: "DESC" },
        groupField: 'Customer',
        root: 'Records'
    });

Here is my web service.

public PagedResult<FMPAsset> buildGrid(int start, int limit)
{
 Guid AccountID = (Guid)Session["AccountID"];
 string whereClause = "SELECT value a FROM XSP_AssetList_V AS a WHERE a.AccountID = GUID'" + AccountID + "'";
 List<FMPAsset> fmpAssets = new List<FMPAsset>();



 using (XSPAssetModel.XSPAssetEntities assetEntities = new XSPAssetEntities(b.BuildEntityConnectionString1("XSMDSN")))
 {
  ObjectQuery<XSP_AssetList_V> assets = new ObjectQuery<XSP_AssetList_V>(whereClause, assetEntities);
  var assetPage = assets.OrderBy(x=>x.StatusName).Skip(start).Take(limit);
  var totalAssetCount = assets.Count();


  currentAssets = assetPage.ToList();
  int currentAssetsCount = currentAssets.Count;
  string imgprefix = System.Configuration.ConfigurationManager.AppSettings["ImgPrefix"];
  char[] separators = { '/' };
  string appname = "";
  int lastloc = imgprefix.Substring(0, imgprefix.Length - 1).LastIndexOfAny(separators);
  if (lastloc > 6)
  {
   appname = imgprefix.Substring(lastloc + 1);
  }


  FMPAsset asset = new FMPAsset();

  //StreamWriter sw = new StreamWriter("C:\\test.txt");

  XSPPrinterMarkerSupplyModel.XSPPrinterMarkerSupplyEntities markerCtx = new XSPPrinterMarkerSupplyModel.XSPPrinterMarkerSupplyEntities(b.BuildEntityConnectionString1("XSMDSN"));

  for (int x = 0; x < currentAssetsCount; x++)
  {
   asset = new FMPAsset();

   asset.AssetID = currentAssets[x].AssetID.ToString();
   asset.PricePlanID = currentAssets[x].PricePlanID.ToString();
   asset.AssociationID = currentAssets[x].AssociationID;
   asset.ModelName = currentAssets[x].ModelName;
   asset.ResponsibilityForAction = currentAssets[x].ResponsibilityForAction;
   asset.IPAddress = (String.IsNullOrEmpty(currentAssets[x].PrinterIPAddress)) ? "No IP" : currentAssets[x].PrinterIPAddress; ;

       Guid id = currentAssets[x].AssetID;

   asset = SetCMYKvalues(asset, x);

   BuilldImpressionsValues(currentAssets[x], ref asset);
   fmpAssets.Add(asset);
  }


  var y = new PagedResult<FMPAsset>();
  y.Records =  fmpAssets;
  y.TotalCount = totalAssetCount;
  return y;




 }

}

I also tried sorting all the records and then returning only first 10 records but i was not able to sort the whole dataset instead it was doing page page .

Thanks.

Answer 1

remoteSort : boolean true if sorting is to be handled by requesting the Proxy to provide a refreshed version of the data object in sorted order, as opposed to sorting the Record cache in place (defaults to false).

Try setting remoteSort to true.

Answer 2

I tried this before but when i say remoteSort: true, sortInfo: { field: 'PageCount', direction: "DESC" },

columns are not getting sorted in any way

Answer 3

instead i i did in this manner var xg = Ext.grid; Ext.override(Ext.grid.GridPanel, { applyState: function(state) { var cm = this.colModel; var cs = state.columns; if (cs) { for (var i = 0, len = cs.length; i < len; i++) { var s = cs[i]; var c = cm.getColumnById(s.id); if (c) { c.hidden = s.hidden; c.width = s.width; var oldIndex = cm.getIndexById(s.id); if (oldIndex != i) { cm.moveColumn(oldIndex, i); } } } } if (state.sort) { this.storethis.store.remoteSort ? 'setDefaultSort' : 'sort'; } if (state.group && this.store.groupBy) { this.store.groupBy(state.group); } }, getState: function() { var o = { columns: [] }; for (var i = 0, c; c = this.colModel.config[i]; i++) { o.columns[i] = { id: c.id, width: c.width }; if (c.hidden) { o.columns[i].hidden = true; } } var ss = this.store.getSortState(); if (ss) { o.sort = ss; } if (this.store.getGroupState) { var gs = this.store.getGroupState(); if (gs) { o.group = gs; } } return o; } });

but the whole dataset is not getting sorted it is doing page by page.

Answer 4

string SortExpression = sortfield + " " + (!String.IsNullOrEmpty(sortdir) ? sortdir : String.Empty);

string whereClause = "SELECT value a, ROW_NUMBER() OVER (ORDER BY" + " " + SortExpression + ") As RowNumber FROM XSP_AssetList_V AS a WHERE a.AccountID = GUID'" + AccountID + "'";

when i debug this the actual sql query is

"SELECT value a, ROW_NUMBER() OVER (ORDER BY pagecount DESC) As RowNumber FROM XSP_AssetList_V AS a WHERE a.AccountID = GUID'5d6b1eab-1697-de11-a2d1-00505617006d'"

Iam getting an error saying The query syntax is not valid., near identifier 'OVER', line 1, column 30.

Could you please help me in this issue.

Also can you tell me how to post reply as a comment and not answer bacause i dont see an option to add comment to this thread