Crosstab Query Results and CSLA

CyclingFoodmanPA posted on Friday, October 19, 2007

I am trying to figure out how to create a business object that is based on a crosstab query.  The rows are not a problem but I am trying how to utilize the CSLA architecture for the crosstab query.  All the CSLA objects that are lists inherit from ReadOnlyListBase and have an Info object associated with them (for example, ProjectList and ProjectInfo).  ProjectInfo knows an Id and a Name is going to come to it.  How do you work these when you do not know how many columns are going to come back as the crosstab query can return anywhere from 2 to n columns?  By the way, I am utilizing Itzik Ben-Gan's article "PIVOT on Steroids" (SQL Server Magazine, January 2007) for a crosstab query that is dynamic and it is great.

Anyway, any help or thoughts to lead me in the right direction would be appreciated.

Keith S. Safford

 

KKoteles replied on Wednesday, October 24, 2007

Keith,

I'm not sure you can (nor that you should).  CSLA objects are "static" in design and not dynamic - all the properties are fixed and set so you know what you have.  That is not to say that the end result might not be a "crosstab" based on your database; however, the properties of your object are known (at least the ones I've designed to date).  I have had a situation where a message could belong to one or more lines of business - and they wanted to display that information in a grid.  It is pretty easy to do; however, when they add another line of business on me I am going to have to redesign my object to contain that new line of business.  I'm not sure how else you would do it.

Here is an example of the select statement I use to "denormalize" the database to what I needed for my object:

SELECT
    Message.Active,
    MessageLineofBusiness.LineOfBusiness2,
    MessageLineofBusiness.LineOfBusiness1,
    dMessage.Creator,
    IsNull(Message.Created, GetDate()) AS Created,
    MessageLineofBusiness.LineOfBusiness3,
    MessageLineofBusiness.LineOfBusiness4,
    Message. MessageID,
    Message.MessageBodyText,
    Message.MessageTitleText

FROM Message
LEFT JOIN (SELECT MessageLineofBusiness.MessageID,
            MIN(CASE MessageLineofBusiness.LineofBusinessTypeID
                    WHEN 1
                    THEN IsNull(dMessageLineofBusiness.LineofBusinessActiveIndicator, 'N')
                END) LineOfBusiness1,
            MIN(CASE MessageLineofBusiness.LineofBusinessTypeID
                    WHEN 2
                    THEN IsNull(MessageLineofBusiness.LineofBusinessActiveIndicator, 'N')
                END) LineOfBusiness2,
            MIN(CASE MessageLineofBusiness.LineofBusinessTypeID
                    WHEN 3
                    THEN IsNull(MessageLineofBusiness.LineofBusinessActiveIndicator, 'N')
                END) LineOfBusiness3,
            MIN(CASE MessageLineofBusiness.LineofBusinessTypeID
                    WHEN 4
                    THEN IsNull(MessageLineofBusiness.LineofBusinessActiveIndicator, 'N')
                END) LineOfBusiness4
        FROM MessageLineofBusiness
        GROUP BY MessageLineofBusiness. MessageID) MessageLineofBusiness ON
    Message.MessageID = MessageLineofBusiness.MessageID
WHERE
EXISTS
    (        SELECT TOP 1
                MessageLineofBusiness.MessageID
            FROM MessageLineofBusiness
            WHERE MessageLineofBusiness.MessageID = Message.MessageID AND
                MessageLineofBusiness.LineofBusinessActiveIndicator = 'Y' AND
                (MessageLineofBusiness.LineofBusinessTypeID = 1 OR
                MessageLineofBusiness.LineofBusinessTypeID = 2 OR
                MessageLineofBusiness.LineofBusinessTypeID = 3 OR
                MessageLineofBusiness.LineofBusinessTypeID = 4)
    )
ORDER BY
    Message.MessageCreateDate

Ken