Need Advice

DanEssin posted on Monday, April 23, 2007

I am trying to build an app with 3 "layers" of data
The first is an editableRootList.
The second is each item in that list.
The third is 2 editable child lists that are contined within the EditableRootObject.

I can write a store proc that will retrieve all of the root objects and I can write a stored proc that will retrieve all into (root properties plus child collections) for a specific root object given its PK.

What I can't do is create a single stored proc what would retrieve all of this data a nd automatically populate all the objects at all levels.

So the question is - Is there some reasonable way to do this like for example addint another Fetch overload to the root objects that gets called when they are being added to the roolList:

        private void ExecuteFetch(SqlConnection cn, FilterCriteria criteria)
        {
            using (SqlCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.StoredProcedure;
                cm.CommandText = "spRule_GetActiveRules";
                cm.Parameters.AddWithValue("@Category", criteria.Category);
                using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                {
                    while (dr.Read())
                        this.Add(ProcessingRule.GetProcessingRule(cn, dr));
                }
            }//using
        }

        internal static ProcessingRule GetProcessingRule(SqlConnection cn, SafeDataReader dr)
        {
            ProcessingRule child = new ProcessingRule();
            child.MarkAsChild();
            child.Fetch(cn, dr);
            return child;
        }

        private void Fetch(SqlConnection cn, SafeDataReader dr)
        {
            FetchObject(dr);
            MarkOld();
            using (SqlCommand cm = cn.CreateCommand())
            {
                cm.CommandType = CommandType.StoredProcedure;
                cm.CommandText = "spRule_GetRuleDetails";
                cm.Parameters.AddWithValue("@RuleID", _ruleID);
                using (SafeDataReader dr1 = new SafeDataReader(cm.ExecuteReader()))
                {
                    dr1.NextResult();
                    FetchChildren(dr1);
                }
            }//using
        }

or is this nuts and there's a better way?

Any help would be greatly appreciated,
Dan Essin

RockfordLhotka replied on Monday, April 23, 2007

I'd think you could do two sprocs: one to get the root data, and one to get all the child data (using a join against the root keys).

Obviously the first sproc result can be used to load all the editable root objects.

The second sproc would be called in the ERLB object's DataPortal_Fetch. And here's where things get fun. At this point you have a DataReader with all the child data, and you have an ERLB populated with all the root objects.

Each row in the DataReader includes the key value for the appropriate root object. Using that value, you can find the right root object in the ERLB and call a Fetch() overload where you pass in the DataReader. That root object can then add that DataReader row to its proper child collection.

In the ERLB loops through all the rows, repeating that process, then all the child data would be loaded properly.

This is basically what I did with my DeepData sample to load grandchildren with three total db calls (parent, children, granchildren), and it works pretty well.

DanEssin replied on Tuesday, April 24, 2007

Thanks. I presume that the DeepData sample would also work with the editable versions of the objects?

In terms of the CodeSmith Templates is this the correct mapping?
OrderList          - Editable Root List
OrderInfo          - Editable Switchable
LineItemList       - Editable Child List
LineItemInfo       - Editable Switchable
LineItemDetailList - Editable Child List
LineItemDetail     - Editable Child

Dan

RockfordLhotka wrote:

I'd think you could do two sprocs: one to get the root data, and one to get all the child data (using a join against the root keys).

Obviously the first sproc result can be used to load all the editable root objects.

The second sproc would be called in the ERLB object's DataPortal_Fetch. And here's where things get fun. At this point you have a DataReader with all the child data, and you have an ERLB populated with all the root objects.

Each row in the DataReader includes the key value for the appropriate root object. Using that value, you can find the right root object in the ERLB and call a Fetch() overload where you pass in the DataReader. That root object can then add that DataReader row to its proper child collection.

In the ERLB loops through all the rows, repeating that process, then all the child data would be loaded properly.

This is basically what I did with my DeepData sample to load grandchildren with three total db calls (parent, children, granchildren), and it works pretty well.

DanEssin replied on Tuesday, April 24, 2007

I'm looking at the DeepData sample starting with the bottom and working up.

I understand the LineItemInfo
but I'm confused about the LineItemDetailList.

If you're using a DTO, it loops and gets the data for all the detail items but I can't see where it reads more than one detail row when using a SafeDataReader. LoadChile makes only one call to:
    Add(LineItemDetailInfo.GetItem(data))


I must be missing something.

Can you straighten me out?

Thanks,
Dan

RockfordLhotka wrote:

I'd think you could do two sprocs: one to get the root data, and one to get all the child data (using a join against the root keys).

Obviously the first sproc result can be used to load all the editable root objects.

The second sproc would be called in the ERLB object's DataPortal_Fetch. And here's where things get fun. At this point you have a DataReader with all the child data, and you have an ERLB populated with all the root objects.

Each row in the DataReader includes the key value for the appropriate root object. Using that value, you can find the right root object in the ERLB and call a Fetch() overload where you pass in the DataReader. That root object can then add that DataReader row to its proper child collection.

In the ERLB loops through all the rows, repeating that process, then all the child data would be loaded properly.

This is basically what I did with my DeepData sample to load grandchildren with three total db calls (parent, children, granchildren), and it works pretty well.

RockfordLhotka replied on Tuesday, April 24, 2007

DanEssin replied on Tuesday, April 24, 2007

Got it. I guess I had my face to close to the first tree I found to see the rest of it.

Thanks,
Dan

Rockford Lhotka wrote:

All the looping-through-datareader code is in the DataPortal_Fetch method. Each child and grandchild collection is merely responsible for routing the current row to the correct new object so it can load itself from that row.

 

There are some ways this could be more efficient, but I was going for as much simplicity as I could for this demo.

 

Rocky

 

From: Daniel Essin [mailto:cslanet@lhotka.net]
Sent: Tuesday, April 24, 2007 6:55 PM
To: rocky@lhotka.net
Subject: Re: [CSLA .NET] Need Advice

 

I'm looking at the DeepData sample starting with the bottom and working up.

I understand the LineItemInfo
but I'm confused about the LineItemDetailList.

If you're using a DTO, it loops and gets the data for all the detail items but I can't see where it reads more than one detail row when using a SafeDataReader. LoadChile makes only one call to:
    Add(LineItemDetailInfo.GetItem(data))


I must be missing something.

Can you straighten me out?

Thanks,
Dan

RockfordLhotka wrote:

I'd think you could do two sprocs: one to get the root data, and one to get all the child data (using a join against the root keys).

Obviously the first sproc result can be used to load all the editable root objects.

The second sproc would be called in the ERLB object's DataPortal_Fetch. And here's where things get fun. At this point you have a DataReader with all the child data, and you have an ERLB populated with all the root objects.

Each row in the DataReader includes the key value for the appropriate root object. Using that value, you can find the right root object in the ERLB and call a Fetch() overload where you pass in the DataReader. That root object can then add that DataReader row to its proper child collection.

In the ERLB loops through all the rows, repeating that process, then all the child data would be loaded properly.

This is basically what I did with my DeepData sample to load grandchildren with three total db calls (parent, children, granchildren), and it works pretty well.