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