Hi,

 I am trying to complete a data provider for DNN and need to implement the "get" queries which return an IDataReader. That said, I have the query built but it is of type esDynamicQuery and the esDataProvider.ExecuteReader() expects an esDataRequest as a parameter. I don't see an implicit type conversion from esDynamicQuery to esDataRequest so how do I satisfy the requirements of the method? Any code samples to this end would be greatly appreciated.

 Thanks,

 Martin

Hi Scott,

Please let me clarify: I am actually creating new data providers for the DotNetNuke core to enable it to run on other platforms (MySql, PostgreSQL, etc). I am deriving directly from DotNetNuke.DataProvider and need to be able to call methods on the esDataProvider class, i.e. ExecuteReader(). Would you provide me an example of how to do this?

Thanks! 

Ok, we are getting alot closer :-D.

In these examples, you are building the t-sql statically.

In my case, I must build it dynamically depending on the data provider's type. Therefore, I have built an esDynamicQuery object. Is there a way that I can inspect the esDynamicQuery's resulting Sql to use for the special functions?

Thanks, Mike. I looked at the other post and my question is still outstanding at this point (unless i am missing something obvious). In order to leverage the database agnosticity of ES, I am building my queries dynamically using the esDynamicQuery object. Here is a code snippet:

public override IDataReader GetAffiliate(int AffiliateId, int VendorId, int PortalID)

AffiliatesQuery a = new AffiliatesQuery("a");

VendorsQuery v = new VendorsQuery("v");

object[] columns = new object[]{ a.AffiliateId,a.VendorId,

return new esUtility().ExecuteReader(a.SqlText or something like that?);

Martin, just a hint, when you post code use our "Code" button. It's the last toolbar button on the right when editing a post, I'll re-post your code (modified though) then comment.

public override IDataReader GetAffiliate(int AffiliateId, int VendorId, int PortalID)
{
    AffiliatesQuery a = new AffiliatesQuery("a");
    VendorsQuery v = new VendorsQuery("v");

    a.Select(a.AffiliateId,
                a.VendorId,
                a.StartDate,
                a.EndDate,
                a.Clicks,
                a.Cpc, 
                a.Cpa,
                a.Acquisitions);
    a.InnerJoin(v).On(v.VendorId == a.VendorId);
    a.Where(a.AffiliateId == AffiliateId & a.VendorId == VendorId);

    AffiliatesCollection collection = new AffiliatesCollection();
    coll.Load(a);  // JUST LOAD IT LIKE THIS
}

Notice how I created an AffiliatesCollection and just called load on it passing in the query. That's It !! 

Now, assume that you have the same database schema in PostgreSQL, Oracle, and SQL (as do our unit tests). Assume your default connection is SQL, the code above would work perfectly. However, if somebody wanted to run your code on Oracle all they would have to do is change the default connection in the config to Oracle, no changes in your code, the same physical binary would work. There is no reason to yank the raw sql out, however, you can see if it you want to like this after you call load:

Feel free to follow up ...

return new esUtility().ExecuteReader(a.INeedTheSqlTextHere);

In this statement (an extract from the prior post), a is an instance of esDynamicQuery. However, the ExecuteReader method requires the command text (T-SQL). If I were to leverage your esDataProvider.ExecuteReader() instead of using esUtility, it requires an esDataRequest object as a parameter. So here is the question: 

How do I take an esDynamicQuery object and use it as the query criteria for an ExecuteReader() method?

I already bought the developer license but yes, this is a dnn thing. I was asking about how to implement the ExecuteReader() method because it is the most pervasive return type in creating a custom DotNetNuke.DataProvider. It looks like I am using the EntitySpaces architecture in a way that has not been previously done. How about I just list the functionalities that I assumed were available  and you tell me if I am mistaken because I certainly don't want to start using the product in a way that it wasn't intended to work...

-ability to build complex criteria via esDynamicQuery (including stuff like pageSize and pageIndex)

-pass the built criteria to methods defined on the EntitySpaces.Interfaces.esDataProvider contract  

-ability to build complex criteria via esDynamicQuery (including stuff like pageSize and pageIndex)

Yes, PageSize and PageNumber are supported

coll.Query.es.PageNumber = 4;
coll.Query.es.PageSize = 20;

- pass the built criteria to methods defined on the EntitySpaces.Interfaces.esDataProvider contract