FilterToUse sometimes ignored by LLBLGenProDataSource2?

Posts   
1  /  2
 
    
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 15-Apr-2009 18:49:45   

.NET 2.0 Adapter SQL Server 2008 2.6 Oct 6, 2008 v2.0.50727 DevExpress 9.1.2

I am binding a Devex AspxGridView to LLBLGenProDataSource2 which is configured to use an EntityCollection. In the Page_Load I assign a filter. There are 7574 records in the db table, but with the filter, only 7200 are returned. When the page first loads, everything works as expected.

But, as soon as I start paging in the grid, it thinks there are 7574 records. I actually didn't notice this right away, it wasn't until I added an unbound column and then the grid started asking me for data, passing to the event handler row indexes > 7200. The datasource's EntityCollection.Count still returns 7200 at that point, so of course when I index into it to get an entity, an exception gets thrown.

At first I thought it was a Devex problem (perhaps a bug with bad indexes being sent to the event handler), but they suggested looking into the datasource more closely.

I ran SQL Server Profiler, and can see that when the page is first loaded, the query is correct, i.e. it has the expected WHERE clause. When I start paging in the grid, another query gets sent to the server, but without the WHERE clause. Note it's not a SELECT COUNT(), this query returns all records(!). Also I noticed it's sent as a raw SQL statement, not a call to sp_executesql().

So far as I can tell, the datasource never loses the filter I set in Page_Load, and EntityCollection.Count is always 7200. Why would the datasource send this second query? And why would it ever tell the grid there are > EntityCollection.Count records? The only other thing I can figure is that somehow the grid goes around the datasource, it is seeing for itself that there are 7574 records in the db table, perhaps by sending that second query, but I don't see how that could be correct behavior, or even possible.

confused

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 16-Apr-2009 03:06:03   

Hi Jim,

Could you please post the relevant declarative aspx of your grid and the datasource?

Also the generated sql for the first load, and then when you pass to the next page.

David Elizondo | LLBLGen Support Team
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 16-Apr-2009 22:49:52   

Sure, it's all pretty straightforward stuff, I think.


    <dxwgv:ASPxGridView ID="uiGrid" runat="server" DataSourceID="registrationDS" KeyFieldName="RegistrationID" AutoGenerateColumns="False" onhtmlrowcreated="uiGrid_HtmlRowCreated" oncustomcolumndisplaytext="uiGrid_CustomColumnDisplayText" OnAutoFilterCellEditorCreate="uiGrid_AutoFilterCellEditorCreate" OnProcessColumnAutoFilter="uiGrid_ProcessColumnAutoFilter" Width="750px" oncustomunboundcolumndata="uiGrid_CustomUnboundColumnData">
        <Settings ShowFilterRow="true" />
        <SettingsPager PageSize="20" />
        <Columns>
            <dxwgv:GridViewCommandColumn Caption="Action">
                <ClearFilterButton Visible="true" />
            </dxwgv:GridViewCommandColumn>
            <dxwgv:GridViewDataTextColumn Caption="ID*" FieldName="RegistrationID">
                <DataItemTemplate>
                    <asp:Label ID="RegistrationIDLabel" runat="server" Text="..." />
                </DataItemTemplate>
            </dxwgv:GridViewDataTextColumn>
            <dxwgv:GridViewDataComboBoxColumn FieldName="RenewalYear" Caption="Year">
                <PropertiesComboBox TextField="Year" ValueField="Year" DataSourceID="yearDS" />
            </dxwgv:GridViewDataComboBoxColumn>
            <dxwgv:GridViewDataTextColumn FieldName="Locations" />
            <dxwgv:GridViewDataTextColumn Caption="All Entered?" FieldName="LocationsEntered" />
            <dxwgv:GridViewDataTextColumn Caption="Reviewed?" FieldName="LocationReviewComplete" />
            <dxwgv:GridViewDataTextColumn FieldName="Amount">
                <PropertiesTextEdit DisplayFormatString="c" />
            </dxwgv:GridViewDataTextColumn>
            <dxwgv:GridViewDataTextColumn Caption="Paid?" FieldName="Paid" />
            <dxwgv:GridViewDataComboBoxColumn FieldName="PaymentStatusID" Caption="Payment Status">
                <PropertiesComboBox TextField="PaymentStatus" ValueField="PaymentStatusID" DataSourceID="paymentStatusDS" />
            </dxwgv:GridViewDataComboBoxColumn>
            <dxwgv:GridViewDataComboBoxColumn FieldName="CalculatedValue" Caption="User Action Required" UnboundType="String">
                <PropertiesComboBox>
                    <Items>
                        <dxe:ListEditItem Value="Make payment" Text="Make payment" />
                        <dxe:ListEditItem Value="Add locations" Text="Add locations" />
                        <dxe:ListEditItem Value="None" Text="None" />
                    </Items>
                </PropertiesComboBox>
            </dxwgv:GridViewDataComboBoxColumn>
        </Columns>
    </dxwgv:ASPxGridView>


    <llblgenpro:LLBLGenProDataSource2 ID="registrationDS" 
                                      runat="server" 
                                      CacheLocation="Session"
AdapterTypeName="OCCC.RALRegistration.DAL.DatabaseSpecific.DataAccessAdapter, OCCC.RALRegistration.DALDBSpecific" 
                                      DataContainerType="EntityCollection" 
EntityFactoryTypeName="OCCC.RALRegistration.DAL.FactoryClasses.RegistrationEntityFactory, OCCC.RALRegistration.DAL">
    </llblgenpro:LLBLGenProDataSource2>


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Master.PageTitle = "View Registrations";
                registrationDS.FilterToUse = manager.GetRegistrationFilter();
            }
        }


        public RelationPredicateBucket GetRegistrationFilter()
        {
            RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(RegistrationFields.Dead == false);
            return bucket;
        }

When the page loads this gets sent to SQL Server:


exec sp_executesql N'SELECT [OCCC].[dbo].[Registrations].[RegistrationID], [OCCC].[dbo].[Registrations].[RegistrantID], [OCCC].[dbo].[Registrations].[RegistrationDate], [OCCC].[dbo].[Registrations].[RenewalYear], [OCCC].[dbo].[Registrations].[Locations], [OCCC].[dbo].[Registrations].[Amount], [OCCC].[dbo].[Registrations].[ProcessingFee], [OCCC].[dbo].[Registrations].[PaymentStatusID], [OCCC].[dbo].[Registrations].[ePayPrimaryCode] AS [EPayPrimaryCode], [OCCC].[dbo].[Registrations].[ePaySecondaryCode] AS [EPaySecondaryCode], [OCCC].[dbo].[Registrations].[ePayTertiaryCode] AS [EPayTertiaryCode], [OCCC].[dbo].[Registrations].[ePayBatchID] AS [EPayBatchID], [OCCC].[dbo].[Registrations].[ePayOrderID] AS [EPayOrderID], [OCCC].[dbo].[Registrations].[LocationsEntered], [OCCC].[dbo].[Registrations].[LocationReviewComplete], [OCCC].[dbo].[Registrations].[Dead], [OCCC].[dbo].[Registrations].[Paid] FROM [OCCC].[dbo].[Registrations]  WHERE ( ( ( [OCCC].[dbo].[Registrations].[Dead] = @Dead1)))',N'@Dead1 bit',@Dead1=0

See top half of the attached image, the grid has 7200 records. I then click on the > on the pager to go to page 2. Now this gets sent to SQL Server:


SELECT [OCCC].[dbo].[Registrations].[RegistrationID], [OCCC].[dbo].[Registrations].[RegistrantID], [OCCC].[dbo].[Registrations].[RegistrationDate], [OCCC].[dbo].[Registrations].[RenewalYear], [OCCC].[dbo].[Registrations].[Locations], [OCCC].[dbo].[Registrations].[Amount], [OCCC].[dbo].[Registrations].[ProcessingFee], [OCCC].[dbo].[Registrations].[PaymentStatusID], [OCCC].[dbo].[Registrations].[ePayPrimaryCode] AS [EPayPrimaryCode], [OCCC].[dbo].[Registrations].[ePaySecondaryCode] AS [EPaySecondaryCode], [OCCC].[dbo].[Registrations].[ePayTertiaryCode] AS [EPayTertiaryCode], [OCCC].[dbo].[Registrations].[ePayBatchID] AS [EPayBatchID], [OCCC].[dbo].[Registrations].[ePayOrderID] AS [EPayOrderID], [OCCC].[dbo].[Registrations].[LocationsEntered], [OCCC].[dbo].[Registrations].[LocationReviewComplete], [OCCC].[dbo].[Registrations].[Dead], [OCCC].[dbo].[Registrations].[Paid] FROM [OCCC].[dbo].[Registrations] 

See bottom half of the attached image, the grid now has 7574 records. Yet in the event handler where it asks for the data for the unbound column, registrationDS.FilterToUse is still set, and registrationDS.EntityCollection.Count is still 7200.

I hope you see why I'm a little mystified!

Attachments
Filename File size Added on Approval
Screenshots.png 17,976 16-Apr-2009 22:50.11 Approved
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Apr-2009 04:55:36   

Looks like related to this: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=13297 and this: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=15348&StartAtMessage=0&#85577

Enabling server side paging should work:

<llblgenpro:LLBLGenProDataSource2 ID="registrationDS"
     ...
     EnablePaging="True">
</llblgenpro:LLBLGenProDataSource2>

...but I don't know if that is what you want disappointed

David Elizondo | LLBLGen Support Team
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 19-Apr-2009 21:10:12   

Actually doing that seems to have no effect. When I go to the next page the filter is again lost.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Apr-2009 11:45:57   

First thing I would try is to test this on the MS Grid. The second thing I'd try is to set the filter outside the IsPostBack check.

btw, please specify the correct runtime library version used.

JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 20-Apr-2009 18:19:52   

btw, please specify the correct runtime library version used.

Yeah that's the .NET Framework version isn't it? But that's what is in the "Runtime Version" row of the property grid, so that's why I grabbed that. Version is 2.6.0.0, and the actual DLL has a file version and product version of 1.6.08.1013, dated 10/16/2008.

The second thing I'd try is to set the filter outside the IsPostBack check.

First thing I would try is to test this on the MS Grid.

Yeah, I'm resigned to doing that, I was hoping first someone might have some insight here. I might also try Janus, and Telerik. There are a lot of things I like about Devex, but sometimes it drives me nuts.

It may be a week or so before I can post something new here. sunglasses

JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 20-Apr-2009 18:20:45   

Of course I meant "2.6.08.1013". stuck_out_tongue_winking_eye

JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 20-Apr-2009 18:21:41   

The second thing I'd try is to set the filter outside the IsPostBack check.

And, I meant to type in response to this, I already tried it but to no effect.

Sorry, I usually use the preview function, but it's Monday morning!

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 21-Apr-2009 06:15:10   

Jim. Just to chek, could you please try to chance the control's actual state information storage from Viewstate to Session?

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Apr-2009 10:28:50   

Or better: please check whether you have switched off viewstate on the datasourcecontrol. If so, that's the answer, as the datasource control stores its own state tracking (so not the data but its own state) in the viewstate.

Frans Bouma | Lead developer LLBLGen Pro
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 21-Apr-2009 18:14:35   

daelmo wrote:

Jim. Just to chek, could you please try to chance the control's actual state information storage from Viewstate to Session?

You mean the datasource control? What property?

JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 21-Apr-2009 18:16:20   

Otis wrote:

Or better: please check whether you have switched off viewstate on the datasourcecontrol. If so, that's the answer, as the datasource control stores its own state tracking (so not the data but its own state) in the viewstate.

No, I have not.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Apr-2009 10:25:55   

daelmo wrote: Jim. Just to chek, could you please try to chance the control's actual state information storage from Viewstate to Session?

You mean the datasource control? What property?

I think David ment the Bound Control.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Apr-2009 10:40:30   

This is taking too long, I'll look into it.

The paging of this grid is a problem though, as it requires their own propriety interface though I do recall there's a setting they added. The filter should be applied though, I'll have a look.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Apr-2009 11:43:58   

At first I couldn't make the grid page on the server. So I switched on 'DataSourceForceStandardPaging' by setting it to true. You don't have that setting, so the grid will fetch ALL rows and page on the client. Why it executes a second query on postback is beyond me though.

After that it paged on the server, and it simply worked. To get proper paging AND the more fancier features of the grid, use the linq datasource they're shipping.

code:


<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register assembly="SD.LLBLGen.Pro.ORMSupportClasses.NET20" namespace="SD.LLBLGen.Pro.ORMSupportClasses" tagprefix="llblgenpro" %>
<%@ Register assembly="DevExpress.Web.ASPxGridView.v9.1, Version=9.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxGridView" tagprefix="dxwgv" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v9.1, Version=9.1.2.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <dxwgv:ASPxGridView ID="ASPxGridView1" runat="server" 
            AutoGenerateColumns="False" DataSourceID="_ordersDS" KeyFieldName="OrderId" 
            DataSourceForceStandardPaging="True">
            <Columns>
                <dxwgv:GridViewDataTextColumn FieldName="OrderId" ReadOnly="True" 
                    VisibleIndex="0">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="CustomerId" VisibleIndex="1">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="EmployeeId" VisibleIndex="2">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataDateColumn FieldName="OrderDate" VisibleIndex="3">
                </dxwgv:GridViewDataDateColumn>
                <dxwgv:GridViewDataDateColumn FieldName="RequiredDate" VisibleIndex="4">
                </dxwgv:GridViewDataDateColumn>
                <dxwgv:GridViewDataDateColumn FieldName="ShippedDate" VisibleIndex="5">
                </dxwgv:GridViewDataDateColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipVia" VisibleIndex="6">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="Freight" VisibleIndex="7">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipName" VisibleIndex="8">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipAddress" VisibleIndex="9">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipCity" VisibleIndex="10">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipRegion" VisibleIndex="11">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipPostalCode" VisibleIndex="12">
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="ShipCountry" VisibleIndex="13">
                </dxwgv:GridViewDataTextColumn>
            </Columns>
        </dxwgv:ASPxGridView>
    
    </div>
        <llblgenpro:LLBLGenProDataSource2 ID="_ordersDS" runat="server" 
            AdapterTypeName="NW26.Adapter.DatabaseSpecific.DataAccessAdapter, NW26.AdapterDBSpecific" 
            CacheLocation="Session" DataContainerType="EntityCollection" 
            EnablePaging="True" 
            EntityFactoryTypeName="NW26.Adapter.FactoryClasses.OrderEntityFactory, NW26.Adapter">
        </llblgenpro:LLBLGenProDataSource2>
    </form>
</body>
</html>


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SD.LLBLGen.Pro.ORMSupportClasses;
using NW26.Adapter.HelperClasses;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
        {
            _ordersDS.FilterToUse = GetFilter();
        }
    }


    private IRelationPredicateBucket GetFilter()
    {
        RelationPredicateBucket toReturn = new RelationPredicateBucket();
        toReturn.PredicateExpression.Add(OrderFields.EmployeeId == 2);
        return toReturn;
    }
}

Frans Bouma | Lead developer LLBLGen Pro
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 22-Apr-2009 19:27:40   

At first I couldn't make the grid page on the server. So I switched on 'DataSourceForceStandardPaging' by setting it to true. You don't have that setting, so the grid will fetch ALL rows and page on the client.

You're right, I didn't set that, as my understanding was that would allow the data source control to page (more efficient, and therefore desirable, obviously), but would cost me the filter/sort capabilities, and I need those. I did, however, try setting this, and it didn't fix the problem.

Why it executes a second query on postback is beyond me though.

Ok, so that is definitely the grid doing that, and not the data source control? It doesn't look like the SQL you emit, and it doesn't have the where clause, but how is the grid even able to submit a statement to the server? And yeah, why would it??

After that it paged on the server, and it simply worked. To get proper paging AND the more fancier features of the grid, use the linq datasource they're shipping.

Yeah, I may very well have to do this. But, I want to get to the bottom of this first. I still intend to try this with a couple of other grids, but it will be a few more days before I can do this.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Apr-2009 10:20:41   

JimFoye wrote:

At first I couldn't make the grid page on the server. So I switched on 'DataSourceForceStandardPaging' by setting it to true. You don't have that setting, so the grid will fetch ALL rows and page on the client.

You're right, I didn't set that, as my understanding was that would allow the data source control to page (more efficient, and therefore desirable, obviously), but would cost me the filter/sort capabilities, and I need those. I did, however, try setting this, and it didn't fix the problem.

Hmmm...

Well, the grid controls what happens: every postback it is re-rendered and calls the bound datasource for the data. The datasource gets a call to ExecuteSelect and normally it should get the paging values passed in as well. The datasource then checks whether it already had fetched this page of data and if so, returns the data from its cache location, and if not, it fetches it. (also, if a filter value has changed, it refetches it, it's very straight forward).

If the grid doesn't pass in paging parameters, the datasourcecontrol has no idea it has to do paging, as it needs to know the page number and page size of course. So it then simply fetches all data (it simply passes 0 for pagesize/number).

DevExpress' grid by default doesn't pass in these paging parameters, and this has been reported to them a couple of years ago already (when they released v8 I think) and they added the property to force these values to get passed to the datasourcecontrol. They said we should implement their interface which we of course won't do, as they should follow the (albeit crippled) standard MS has put forward.

Why it executes a second query on postback is beyond me though.

Ok, so that is definitely the grid doing that, and not the data source control? It doesn't look like the SQL you emit, and it doesn't have the where clause, but how is the grid even able to submit a statement to the server? And yeah, why would it??

If the datasource sees that paging parameters, filter parameters are different, it will decide to refetch. However you don't seem to use filter parameters and the paging parameters are 0 (as that's how this grid works by default)

Frans Bouma | Lead developer LLBLGen Pro
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 24-Apr-2009 20:30:56   
If the datasource sees that paging parameters, filter parameters are different, it will decide to refetch. However you don't seem to use filter parameters and the paging parameters are 0 (as that's how this grid works by default)

Ok, but I am setting FilterToUse in code, and that filter property remains always set. Yet, the data source control is sending a query to the server without the where clause? After which, EntityCollection still has the correct number of records, 7200, but now the grid thinks there are 7574? How can this be happening? I'm still as puzzled as when I started.confused

I hope to try this with the Janus grid this weekend.

JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 26-Apr-2009 20:50:38   

OK, I have some more information, and (ouch), I'm becoming convinced there is actually a bug in the LLBLGenProDataSource2 control.

I tried this with the Janus grid and the MS grid. In both cases the filter doesn't get "lost". At first this makes Devex look like the culprit, but actually this is just due to the difference in how the grids manage data. Both Janus and MS do the following (at least, by default): ask for all the data from the data source control, save all that in the viewstate, and make no further calls on the datasource.

Everything is a tradeoff; in this case, no further queries are made to the database, but all the data is sent back and forth in the request when the user pages through the grid, it has to be done as a postback and the request size gets larger as the dataset does.

As a side note, my partner has an application he uses the Janus grid on, and was experiencing some performance degradation precisely because of this. He adds this to his pages that use larger datasets to force viewstate to be saved in memory on the server, and his app got faster immediately (but note it's on a dedicated server, not a shared hosting environment).


     protected override PageStatePersister PageStatePersister
    {
        get
        {
            return new SessionPageStatePersister(this);
        }
    }

The Devex grid, on the other hand (and again, this is just the default behavior), uses a callback when paging and isn't sending all the data back and forth. So, that's nice. But, the downside is that it has to ask the data source control for the data every time. And without paging enabled, it has to ask for all of it. That's why the second query is going to the database when I page, and given how Devex is managing the data, that is to be expected. BUT....the WHERE clause should still reflect the filter I have set on the LLBLGenProDataSource2 control, should it not? What appears to be happening is that the grid asks the control for the data again, the control sends the query without the WHERE clause and informs the grid there are now 7574 records instead of 7200, but it doesn't modify EntityCollection. The grid doesn't know about EntityCollection, it now uses the greater record count to render itself, so the extra pages appear and of course problems result when I need to index into EntityCollection and the extra records aren't there.

Now, earlier daelmo has said

Enabling server side paging should work

and then I said

Actually doing that seems to have no effect. When I go to the next page the filter is again lost.

I need to clarify that. I tried this again:


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                registrationDS.FilterToUse = manager.GetRegistrationFilter();
                registrationDS.EnablePaging = true;
                uiGrid.DataSourceForceStandardPaging = true;
            }
        }


If I click to go to page 2, there is no problem, the query submitted to the server still has the WHERE clause:

exec sp_executesql N'CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[RegistrationID][Int] NULL,[RegistrantID][Int] NULL,[RegistrationDate][SmallDateTime] NULL,[RenewalYear][Int] NULL,[Locations][Int] NULL,[Amount][Money] NULL,[ProcessingFee][SmallMoney] 
NULL,[PaymentStatusID][Int] NULL,[EPayPrimaryCode][NVarChar](5) NULL,[EPaySecondaryCode][NVarChar](5) NULL,[EPayTertiaryCode][NVarChar](5) 
NULL,[EPayBatchID][NVarChar](50) NULL,[EPayOrderID][NVarChar](10) NULL,[LocationsEntered][Bit] NULL,[LocationReviewComplete][Bit] NULL,[Dead][Bit] NULL,[Paid][Bit] NULL);INSERT INTO #TempTable ([RegistrationID],[RegistrantID],[RegistrationDate],[RenewalYear],[Locations],[Amount],[ProcessingFee],[PaymentStatusID],[EPayPrimaryCode],[EPaySecondaryCode],[EPayTertiaryCode],[EPayBatchID],[EPayOrderID],[LocationsEntered],[LocationReviewComplete],[Dead],[Paid]) SELECT TOP 41 [OCCC].[dbo].[Registrations].[RegistrationID], [OCCC].[dbo].[Registrations].[RegistrantID], [OCCC].[dbo].[Registrations].[RegistrationDate], [OCCC].[dbo].[Registrations].[RenewalYear], [OCCC].[dbo].[Registrations].[Locations], [OCCC].[dbo].[Registrations].[Amount], [OCCC].[dbo].[Registrations].[ProcessingFee], [OCCC].[dbo].[Registrations].[PaymentStatusID], [OCCC].[dbo].[Registrations].[ePayPrimaryCode] AS [EPayPrimaryCode], [OCCC].[dbo].[Registrations].[ePaySecondaryCode] AS [EPaySecondaryCode], [OCCC].[dbo].[Registrations].[ePayTertiaryCode] AS [EPayTertiaryCode], [OCCC].[dbo].[Registrations].[ePayBatchID] AS [EPayBatchID], [OCCC].[dbo].[Registrations].[ePayOrderID] AS [EPayOrderID], [OCCC].[dbo].[Registrations].[LocationsEntered], [OCCC].[dbo].[Registrations].[LocationReviewComplete], [OCCC].[dbo].[Registrations].[Dead], [OCCC].[dbo].[Registrations].[Paid] FROM [OCCC].[dbo].[Registrations]  WHERE ( ( ( [OCCC].[dbo].[Registrations].[Dead] = @Dead1)));SELECT [RegistrationID],[RegistrantID],[RegistrationDate],[RenewalYear],[Locations],[Amount],[ProcessingFee],[PaymentStatusID],[EPayPrimaryCode],[EPaySecondaryCode],[EPayTertiaryCode],[EPayBatchID],[EPayOrderID],[LocationsEntered],[LocationReviewComplete],[Dead],[Paid] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable',N'@Dead1 bit,@__rownoStart int,@__rownoEnd int',@Dead1=0,@__rownoStart=20,@__rownoEnd=40

However, when I select an item from a dropdown on the grid's filter row, the next query that gets sent is this:

exec sp_executesql N'CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[RegistrationID][Int] NULL,[RegistrantID][Int] NULL,[RegistrationDate][SmallDateTime] NULL,[RenewalYear][Int] NULL,[Locations][Int] NULL,[Amount][Money] NULL,[ProcessingFee][SmallMoney] 
NULL,[PaymentStatusID][Int] NULL,[EPayPrimaryCode][NVarChar](5) NULL,[EPaySecondaryCode][NVarChar](5) NULL,[EPayTertiaryCode][NVarChar](5) NULL,[EPayBatchID][NVarChar](50) NULL,[EPayOrderID][NVarChar](10) NULL,[LocationsEntered][Bit] NULL,[LocationReviewComplete][Bit] NULL,[Dead][Bit] NULL,[Paid][Bit] NULL);INSERT INTO #TempTable ([RegistrationID],[RegistrantID],[RegistrationDate],[RenewalYear],[Locations],[Amount],[ProcessingFee],[PaymentStatusID],[EPayPrimaryCode],[EPaySecondaryCode],[EPayTertiaryCode],[EPayBatchID],[EPayOrderID],[LocationsEntered],[LocationReviewComplete],[Dead],[Paid]) SELECT TOP 41 [OCCC].[dbo].[Registrations].[RegistrationID], [OCCC].[dbo].[Registrations].[RegistrantID], [OCCC].[dbo].[Registrations].[RegistrationDate], [OCCC].[dbo].[Registrations].[RenewalYear], [OCCC].[dbo].[Registrations].[Locations], [OCCC].[dbo].[Registrations].[Amount], [OCCC].[dbo].[Registrations].[ProcessingFee], [OCCC].[dbo].[Registrations].[PaymentStatusID], [OCCC].[dbo].[Registrations].[ePayPrimaryCode] AS [EPayPrimaryCode], [OCCC].[dbo].[Registrations].[ePaySecondaryCode] AS [EPaySecondaryCode], [OCCC].[dbo].[Registrations].[ePayTertiaryCode] AS [EPayTertiaryCode], [OCCC].[dbo].[Registrations].[ePayBatchID] AS [EPayBatchID], [OCCC].[dbo].[Registrations].[ePayOrderID] AS [EPayOrderID], [OCCC].[dbo].[Registrations].[LocationsEntered], [OCCC].[dbo].[Registrations].[LocationReviewComplete], [OCCC].[dbo].[Registrations].[Dead], [OCCC].[dbo].[Registrations].[Paid] FROM [OCCC].[dbo].[Registrations] ;SELECT [RegistrationID],[RegistrantID],[RegistrationDate],[RenewalYear],[Locations],[Amount],[ProcessingFee],[PaymentStatusID],[EPayPrimaryCode],[EPaySecondaryCode],[EPayTertiaryCode],[EPayBatchID],[EPayOrderID],[LocationsEntered],[LocationReviewComplete],[Dead],[Paid] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable',N'@__rownoStart int,@__rownoEnd int',@__rownoStart=20,@__rownoEnd=40

Of course the grid now renders itself incorrectly, etc.

[Edit] The first query has two where clauses and the second query has one. What's missing is

WHERE ( ( ( [OCCC].[dbo].[Registrations].[Dead] = @Dead1)))

which of course corresponds to the predicate I set on FilterToUse. I just added this because I know the queries are hard to read. [End edit]

I understand that when I implement paging in this manner, I lose the filtering capability of the grid (my selection of an item on the filter row did nothing other than force a callback). But, that's not the issue here. This is now the second instance I've seen where the LLBLGenProDataSource2 control is sending a query to the server without the WHERE clause. It seems to me that this should never be happening, regardless of how the grid is managing data and paging.

Is this a bug? frowning IMHO it is looking like one.

I also realize that the default behavior of the Devex grid is inefficient with larger datasets, such as I have here, and I need to figure out how to cache the data like the Janus grid does, or, probably better, to implement the LINQ data source mode. But that means some extra work (like, actually learning LINQ, ha ha), requiring the hosting server install 3.5, etc. Also, it seems like someone said that it's read-only then? That would be a problem.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Apr-2009 21:31:33   

You are using the latest runtime library build?

Frans Bouma | Lead developer LLBLGen Pro
JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 27-Apr-2009 00:46:10   

2.6.08.1013.

There's a later one, right? I can upgrade and try it again if you want.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Apr-2009 08:24:51   

2.6.08.1013.

There's a later one, right? I can upgrade and try it again if you want.

Yes, please upgrade and test this agan.

Much appreciated.

JimFoye avatar
JimFoye
User
Posts: 656
Joined: 22-Jun-2004
# Posted on: 27-Apr-2009 17:26:03   

Same problem with 2.6.9.327.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Apr-2009 17:58:55   

JimFoye wrote:

Same problem with 2.6.9.327.

Hmm.

Well, I'm a bit lost what works and what not. You have a grid, with a combobox and this works in the .NET vanilla grid but not in devexpress' grid? Could you briefly describe how your form looks like (i.e. with the combobox filter) so we can reproduce this?

The filter constructed from comboboxes etc. should be added to the filter you already set, so it shouldn't replace it entirely. (So the behavior you're seeing is not what is expected.). It is in line with what you're seeing all along though: the filter you set in the page load is apparently gone and only the filter created at runtime is applied...

Frans Bouma | Lead developer LLBLGen Pro
1  /  2