Ticket Q354824
Visible to All Users

ASPxGridView + XPODataSource: How to create additional read-only columns/fields based on related data with using parameters?

created 13 years ago

Dear DX Team,
I am a beginner in XPO and I need your assistance. I try to explain my trouble.
The environment and conditions on my side:
- Windows 7/64, ASP.NET, VS 2010, MS SQL, ASPxGridView
- XPODataSource ServerMode = true, readonly, mostly based on views
I am using XPODataSource with ServerMode=True and ASPxGridView only for presentation data. There are no UPDATE/DELETE/INSERT actions.
In MSSQL I created a view XPO_View_Oferty which selects a lot of columns from the several tables and also evaluates many custom columns.
Next, I created a real table XPO_Oferty based on this view and generated the XPO class. Next, I added the attribute [Persistent("XPO_View_Oferty")] to force that the view is to be used instead of real table:
[Persistent("XPO_View_Oferty")]
public class XPO_Oferty : XPLiteObject
[…]
and from this point ASPxGridView works very fast and shows data from the view not from the table.
My problem is that I need to add some columns which cannot be evaluated on MSSQL side because they need to have parameters. The parameters can not be passed to the views. I cannot use stored procedures, because XPODataSource does not allow to use SP along with ServerMode set to true - or I do know how to do it.
So, I tried another way. I created a new class BiurowinSession:Session and added several properties to keep the parameters.
public class BiurowinSession : Session
    {
        // Parameters
     public string KodAgencji;
        public Guid UserId;
          …
     }

In next step, i changed the main class XPO_Oferty which now accepts new class BiurowinSession:
[Persistent("XPO_View_Oferty")]
public class XPO_Oferty : XPLiteObject
{
     …
    public XPO_Oferty(BiurowinSession session) : base(session) { }
    public XPO_Oferty() : base(BiurowinSession.DefaultSession) { }
    public override void AfterConstruction() { base.AfterConstruction(); }
}
Now, I pass the parameters in Page_Init() method:
BiurowinSession session1;
protected void Page_Init(object sender, EventArgs e)
{
     session1 = new BiurowinSession();
     session1.KodAgencji = this.KodAgencji;
     session1.UserId = this.UserId;
     XpoDataSourceOferty.Session = session1;
}
From now, I can use these parameters in XPO_Oferty class. I tested and it works. I can use the parameters and create new fields based on the original fields and evaluated with using the parameters.
But I need to add custom column which is more complicated and here begins my problem.
On the MSSQL side this column is evaluated with the following script:
SELECT Col1, col2…,
      Convert( bit,
      CASE WHEN EXISTS(SELECT TOP(1) AdresId FROM OfertyAdresy WITH (NOLOCK) WHERE OfertyAdresy.OfertaId = Oferty.OfertaId AND OfertyAdresy.KodAgencji = @KodAgencji ) THEN 1 ELSE 0 END) As JestInformacjaAdresowa
     FROM XPO_Oferty as Oferty
To build the relation between XPO_Oferty and OfertyAdresy I added:
 public partial class OfertyAdresy : XPLiteObject
{
     // Apply the Association attribute to mark the Customer property
     // as the "one" end of the Customer-Orders association.
     XPO_Oferty fOferta;
     [Association("Oferty-OfertyAdresy"),Aggregated]
     public XPO_Oferty Oferta
     {
          get { return fOferta; }
          set { SetPropertyValue("Oferta", ref fOferta, value); }
     }
}
public partial class XPO_Oferty : XPLiteObject
{
     // Apply the Association attribute to mark the Orders property
        // as the "many" end of the Customer-Orders association.
        [Association("Oferty-OfertyAdresy", typeof(OfertyAdresy))]
        public XPCollection AdresyOfert { get { return GetCollection("AdresyOfert"); } }

I understand that this relation can return all related rows from "many" but they should be filtered with a session parameter.
I tried something like this:
public partial class XPO_Oferty : XPLiteObject
{
      public int AddressesCount
      {
          get
          {
      this.AdresyOfert.CriteriaString = "KodAgencji = '" + ((BiurowinSession)this.Session).KodAgencji + "'";
            this.AdresyOfert.TopReturnedObjects = 1;
            if (this.AdresyOfert.Count >= 1)
                    return true;
                else
                    return false;
          }
      }
}
But the above does not work, this.AdresyOfert is never loaded, ie. this.AdresyOfert.IsLoaded is false.
I think that I could use Session.ExecuteQuery() or similar, but this solution should be slower then when it used inside view on MSSQL side, because Query is to be called for each parent row. The advantage is that in return I need to get only TRUE/FALSE, instead of the whole group rows for the parent row.
Could you help me how to add additional columns (fields) based on related data and how to narrow (filter,criteria) the returned rows with parameters ?
Regards,
Marek

Answers approved by DevExpress Support

created 13 years ago (modified 12 years ago)

Hi Marek,
Thank you for the question. I am afraid it is impossible to calculate this property without sending a separate query for each object in server mode, because your expression depends on a runtime variable. Besides, you should never modify the Criteria property of an associated collection (in the current version). Instead, use the Session.Evaluate method to calculate an expression on the server side.

C#
public int AddressesCount { get { return Convert.ToInt32(this.Session.Evaluate(typeof(OfertyAdresy), CriteriaOperator.Parse("Count()"), CriteriaOperator.Parse("Oferta=? And KodAgencji=?", this, ((BiurowinSession)this.Session).KodAgencji))); } }

I'm looking forward to hearing from you.
Thanks,
Michael.

    Comments (2)

      Hi Michael,
      Confirm, I understand. Thank you for the fast response and support.
      Regards,
      Marek

      DevExpress Support Team 13 years ago

        You are welcome, Marek,
        Should you have any further questions, please feet free to contact us. We will be happy to help you.
        Thanks,
        Michael.

        Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

        Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.