This project has moved. For the latest updates, please go here.

Binding to Column Alias

Developer
Apr 28, 2013 at 9:29 PM
Edited Apr 28, 2013 at 11:02 PM
How do you map a column alias in CslaGenFork. With the parent/child relationship (PO/Detail), I created a business rule to calculate the extended cost but now I want to show a list of POs without the detail. The following query returns the 'TotalPurchases' and 'TotalFreight' but I can't figure out how to bind to these columns in CslaGenFork. I can define the field without setting the 'DB Bind Column' in 00.Database but then have to modify the generated code in order for the field to be populated.
        SELECT
            [PO].[Number],
            [PO].[PODate],
            [PO].[StatusNumber],
            [PO].[StatusDate],
            (SELECT SUM([PODetail].[Quantity] * [PODetail].[Cost]) FROM [dbo].[PODetail] WHERE [PODetail].[POID] = @ID GROUP BY [PODetail].[POID]) AS TotalPurchases,
            (SELECT SUM([PO].[FreightCost]) FROM [dbo].[PO] WHERE [PO].[ID] = @ID GROUP BY [PO].[ID]) AS TotalFreight,
            [PO].[FreightCost],
            [PO].[Comments],
            [PO].[CreateDT],
            [PO].[CreateUserID],
            [PO].[ChangeDT],
            [PO].[ChangeUserID],
            [PO].[ID]
        FROM [dbo].[PO]
            INNER JOIN [dbo].[Vendor] ON [dbo].[PO].[VendorID] = [dbo].[Vendor].[ID]

Coordinator
Apr 30, 2013 at 12:48 AM
Edited Apr 30, 2013 at 12:54 AM
Hi Keith,

Csla is all about object and collections (or lists) of objects. Properties are always in objects and never in collections. These are the building blocks.

Using those building blocks (and not DB columns), can you explain what you want?

[EDIT]
Keep in mind an object graph is made for a single use case and not for all related use cases. This translates to: an object graph is made for a single screen/form/view. If you have related use cases that aren't exactly the same, use different objects.
Developer
Apr 30, 2013 at 2:43 AM
tiago wrote:
Hi Keith,

Csla is all about object and collections (or lists) of objects. Properties are always in objects and never in collections. These are the building blocks.

Using those building blocks (and not DB columns), can you explain what you want?

[EDIT]
Keep in mind an object graph is made for a single use case and not for all related use cases. This translates to: an object graph is made for a single screen/form/view. If you have related use cases that aren't exactly the same, use different objects.
<br/>

When retrieving a vendor I want to retrieve calculated value for 'TotalPurchases', 'TotalFreight', 'Order Count', and 'AveragePurchase' which come fromm calculated columns in the database query as follows. Because the details of the POs are not a part of the object graph a business rule cannot be used to calculate these values. My question is how to map to these columns without having to manually add code to the generated file.
ALTER PROCEDURE [dbo].[GetVendor]
    @ID int
AS
    BEGIN

        SET NOCOUNT ON

        /* Get Vendor from table */
        SELECT
            [Vendor].[ID],
            [Vendor].[Number],
            [Vendor].[Name],
            (SELECT SUM([PODetail].[Quantity] * [PODetail].[Cost]) FROM [dbo].[PODetail] WHERE [PODetail].[POID] = @ID GROUP BY [PODetail].[POID]) AS TotalPurchases,
            (SELECT COUNT([PO].[ID]) FROM [PO] WHERE [PO].[VendorID] = @ID GROUP BY [PO].[VendorID]) AS OrderCount,
            CASE
                WHEN    (SELECT COUNT([PO].[ID]) FROM [PO] WHERE [PO].[VendorID] = @ID GROUP BY [PO].[VendorID]) > 0
                THEN    ((SELECT SUM([PODetail].[Quantity] * [PODetail].[Cost]) FROM [dbo].[PODetail] WHERE [PODetail].[POID] = @ID GROUP BY [PODetail].[POID]) / (SELECT COUNT([PO].[ID]) FROM [dbo].[PO] WHERE[PO].[VendorID] = @ID GROUP BY [PO].[VendorID]))
                ELSE    0
            END  AS AveragePurchase,
            (SELECT SUM([PO].[FreightCost]) FROM [dbo].[PO] WHERE [PO].[ID] = @ID AND [PO].[VendorID] = @ID GROUP BY [PO].[ID]) AS TotalFreight,
            [Vendor].[IsActive],
            [Vendor].[CreateDT],
            [Vendor].[CreateUserID],
            [Vendor].[ChangeDT],
            [Vendor].[ChangeUserID]
        FROM [dbo].[Vendor]
        WHERE
            [Vendor].[ID] = @ID
        LoadProperty(TotalPurchasesProperty, dr.GetDecimal("TotalPurchases"));
        LoadProperty(TotalFreightProperty, dr.GetDecimal("TotalFreight"));
        LoadProperty(OrderCountProperty, dr.GetInt32("OrderCount"));
        LoadProperty(AveragePurchaseProperty, dr.GetDecimal("AveragePurchase"));
Coordinator
May 1, 2013 at 8:44 PM
Hi Keith,

Your reply repeats your initial question and doesn't explain the object graph you intend to use. This means I'm no closer to understand your question. So I can't help you.

Regards,
Tiago Freitas Leal
Developer
May 1, 2013 at 9:58 PM
tiago wrote:
Hi Keith,

Your reply repeats your initial question and doesn't explain the object graph you intend to use. This means I'm no closer to understand your question. So I can't help you.

Regards,
Tiago Freitas Leal
<br/>
The object graph is unimportant but here it is:

Vendor
-VendorPOs
-VendorItems

There are four columns withing the GetVendor stored procedure which populate the root object (Vendor) that are calculated sub queries and aliased (TotalPurchases, TotalFreight, OrderCount, and AveragePurchases). These are not column names which come from a table and I am having to modify the Fetch code to load these values.

Sorry for the lack of clarity.
Coordinator
May 1, 2013 at 10:08 PM
Edited May 1, 2013 at 10:09 PM
RumbleCow wrote:
The object graph is unimportant but here it is:

Vendor
-VendorPOs
-VendorItems

There are four columns withing the GetVendor stored procedure which populate the root object (Vendor) that are calculated sub queries and aliased (TotalPurchases, TotalFreight, OrderCount, and AveragePurchases). These are not column names which come from a table and I am having to modify the Fetch code to load these values.
Again, please Keith don't talk about columns as I couldn't care less where you get your data from. As you know you can have CslaGenFork generate the query for you, or you can use a view or stored procedure to feed your object with data, that's entirely up to you.

What I care about is objects and properties. So you want to load some properties of the Vendor object. That's not a lot of details. And what seems to be the problem?
Developer
May 1, 2013 at 10:27 PM
tiago wrote:
Again, please Keith don't talk about columns as I couldn't care less where you get your data from. As you know you can have CslaGenFork generate the query for you, or you can use a view or stored procedure to feed your object with data, that's entirely up to you.

What I care about is objects and properties. So you want to load some properties of the Vendor object. That's not a lot of details. And what seems to be the problem?
<br/>

My question is all about populating the properties of an object from a query. The 00.Database 'DB Bind Column' cannot be set and therefore the field in the resultset is not mapped. Is there a way around this or does the generated code need modified after each generation.
Coordinator
May 2, 2013 at 12:08 AM
Edited May 2, 2013 at 12:25 AM
RumbleCow wrote:
My question is all about populating the properties of an object from a query. The 00.Database 'DB Bind Column' cannot be set and therefore the field in the resultset is not mapped. Is there a way around this or does the generated code need modified after each generation.
How come you can't set DB Bind Column?

Where does the query come from? Is it a generated query or a query you wrote?

If it's a generated query, you might find interesting to know that properties of an object may come from different tables. CslaGenFork may or may not write the correct query, but that's not important since you will edit it before saving the stored procedure on the database. If your property data comes from the database, just bind to the correct column/table and never mind about the query.

If it's your own stored procedure, sure you can bind to the result set.

I can see only one reason for not binding a data fed property to the database: when your data doesn't come from the database, for instance a file that is stored on the file system.

If it's a property that is calculated by the object itself, then it isn't database bound per nature and it can't be bound nor should it be bound. That isn't a problem because you don't want to bind it anyway. You can even keep such a property out of CslaGenFork and declare it on the extended file.

Regards,
Tiago Freitas Leal
Coordinator
May 2, 2013 at 12:23 AM
RumbleCow wrote:
(...) does the generated code need modified after each generation.
If you don't like the generated DataPortal_Fetch code, copy it to the extended file and set 00. Generate Options => Use Custom Loading to False. That way you don't have to keep modifying it on every re-generation. The down side is that you loose one of the advantages of a code generator, that is code re-generation. That isn't a path I recommend unless there is no other way to solve the issue. As I said in the previous message, you shouldn't be having this bind to database problem. So your issue can surely be fixed.
Developer
May 3, 2013 at 7:11 PM
tiago wrote:
RumbleCow wrote:
(...) does the generated code need modified after each generation.
If you don't like the generated DataPortal_Fetch code, copy it to the extended file and set 00. Generate Options => Use Custom Loading to False. That way you don't have to keep modifying it on every re-generation. The down side is that you loose one of the advantages of a code generator, that is code re-generation. That isn't a path I recommend unless there is no other way to solve the issue. As I said in the previous message, you shouldn't be having this bind to database problem. So your issue can surely be fixed.
<BR/>

I discovered the 'OnFetchRead' method is getting called and was able to put the code in the non-designer file (see below). Problem solved. Learning something new every day. Thanks for your perseverance.
        partial void OnFetchRead(DataPortalHookArgs args)
        {
            LoadProperty(TotalPurchasesProperty, args.DataReaderArg.GetDecimal("TotalPurchases"));
            LoadProperty(TotalFreightProperty, args.DataReaderArg.GetDecimal("TotalFreight"));
            LoadProperty(OrderCountProperty, args.DataReaderArg.GetInt32("OrderCount"));
            LoadProperty(AveragePurchaseProperty, args.DataReaderArg.GetDecimal("AveragePurchase"));
        }