Using BusinessListBase with multi-table Object

Using BusinessListBase with multi-table Object

Old forum URL: forums.lhotka.net/forums/t/8786.aspx


strangerd posted on Friday, April 09, 2010

Hi

I'm required to design an object which uses a couple of tables.

My parent table is table 1

Table 2, 3 and 4 should be the child. (If you run a query on these joined tables, you already get multiple records)

Is it possible to create objects that work in this fashion? If so, does anyone have examples on how this can be done, as the CSLA book has only basic examples where the object is dealing only with children of itself, not a joined object comprising of multiple tables.

I seem to get it right to a point. But I'm using LINQ and when you get to the fetch_child and so forth, you have to bring in a LINQ table, where as with my situation, I have to use mutiple.

Thank you

RockfordLhotka replied on Friday, April 09, 2010

There are really two options.

One is to use a join in your query so you get back one row of data per child. That complicates the query of course, but is usually the most efficient solution because it minimizes database queries.

Two is to do multiple queries to the database. You may be able to get the Table 2 data in one query (for all child objects), and then in each Child_Fetch() you'd do two more queries to get the data from the other two tables. Obviously this is less efficient because it requires more database queries, but it avoids the (potentially) complex join of the first option.

strangerd replied on Friday, April 09, 2010

Hi

Thanks for the reply. Do you have any examples on the Editable root collection and Editable child collection, like in your book on page 180?

Do you have an example also on your second option?

Thank you

RockfordLhotka replied on Friday, April 09, 2010

The DeepData sample might help, though it is old. I'm pretty sure I have a parent-child-grandchild sample in the code for the Core 3.8 video series.

Ultimately this isn't really a CSLA question though. It is a question about how to efficiently get at data from your database. The techniques vary depending on your database and its API. SQL Server supports some nice features, such as multiple result sets and MARS - though they come with tradeoffs in terms of performance and complexity.

Multiple result sets are something I use a lot, but they are only really useful for parent-child relationships. If the parent is a list, or you have grandchild data, this isn't a useful technique.

MARS allows you to do multiple overlapping queries using the same connection. I've heard MARS has bad perf impact, but don't really know (I'm not a database expert). The alternative is to use multiple connections, and that's typically what I do - if for no other reason that it is easy. The ConnectionManager (and similar manager types) in Csla.Data supports this by allowing you to specify a label on the GetManager() call - use a label for your secondary connection to avoid conflicting with the default connection used to get the parent data.

strangerd replied on Monday, April 12, 2010

Hi

The DeepData example is not what I am looking for. I am just looking for an example piece of code for the Rootlist and child classes, where mutiple tables are involved. I just need it as a start.

I can't be expected to pay $300 for just one example piece of code.

Is there any other way you can help me out?

Thank you

RockfordLhotka replied on Monday, April 12, 2010

I still don't understand what you are asking. I answered your original question in my first answer. It isn't hard, and it is all about data access, not objects.

private void DataPortal_Fetch(...)
{
  // query to get root data
  // load root properties
  // query to get list of child data
  Children = DataPortal.FetchChild<Children>(childData);
}

in list

private void Child_Fetch(ChildDataListType childData)
{
  // foreach through childData
  // call Add() on each new child created with
  // DataPortal.FetchChild
}

in child

private void Child_Fetch(ChildDataType childData)
{
  // load properties from childData
  // query Table 3
  // load properties from Table 3
  // query Table 4
  // load properties from Table 4
}

I'm obviously missing the point of your question, because this is really not hard.

strangerd replied on Monday, April 12, 2010

Hi

All I want to do is populate a grid with a list of records which I get directly from the object (comprising of multiple tables which return multiple records when you query them).

I have the couple of tables. But all the tables are on the same level. In terms of root and child.

I just want to return a list of the objects by using businesslistbase. Currently BusinessBase only allows for the single retrieval of data.

I am new to this, so my conclusion was that I would need to use the BusinessListBase if i want to do deferred operations.

Thank you

RockfordLhotka replied on Thursday, April 15, 2010

That is true, if you are just getting a list of child objects, then your root object will be a BLB. But the idea is the same as in my previous post - just a little easier:

private void DataPortal_Fetch()
{
  // open the database
  // do a query to get the data (joining your tables I assume)
  // foreach through the resulting data
  // call Add() on each new child created with
  // DataPortal.FetchChild
}

in child

private void Child_Fetch(ChildDataType childData)
{
  // load properties from childData
}

This is exactly what I show in the book and in many examples - you are just doing a join to get the data from several tables instead of one table - that's simple database stuff.

Copyright (c) Marimer LLC