Data access ebook FactoryImplementation Unittest fails on OrderEdit

StefanCop posted on Monday, August 15, 2011

I think there's a mistake in the FactoryImplementation of the ebook sample (v 0.5): Test.Library.Net.OrderEditTest.OrderEditGetOrderEditTest throws the exception that the DataReader runs still a command. Looking at the source code I think the exception is right ;-)

In class OrderLineItemDal

public OrderLineItems FetchList(int orderId)
{
...
using (var dr = cm.ExecuteReader())
{
    ...
    while (dr.Read())
    {
      ...
      LoadProperty(child, OrderLineItem.PersonsProperty, olpd.FetchList(childId));
      result.Add(child);
    }
  }

The call to FetchList starts a nested command.

What solution would you prefer (aside the multiple result set, which I cannot use on my DB)?

After I fetched the basic data of my items (using DataReader Block) I could loop on these items an then invoke the FetchList.
I could work with DTOs: again first fetch the DTO of this level and the invoke FetchList for the children/grandchildren. Having a List<DTO> supports one command after the other.

Thanks

Stefan

JonnyBee replied on Monday, August 15, 2011

I'm sure the EBook sample is meant for SqlServer 2005/2008 which support MARS.

So for your situation:

1. Restructure code so that you will only need 1 connection and 1 active resultset.

2. Open more connections to have one connection per active resultset.

Most recommended would be to use MARS but as you say, this is not available for your DB.
Just out of curiosity, which DB are you using?

StefanCop replied on Monday, August 15, 2011

Actually, this EBook sample should work with SqlCe (there's another Dal implemetation using EF on SqlServer), if I've wll understood the EBook.

The DB in question is Sybase's Anywhere (the "small" one). In fact, I use the System.Data.Common classes.

Restructuring code seems the way to go. Using multiple connections has a potential problem, if the caller has started a transaction but DTC not enabled.

Rocky mentioned in the EBook Data Acces part different models and variations.

For Factory approach, there's

- Factory Implemetation (the sample above)

- Factory Invocation, which is said to be the most complex.

But if I use ADO.NET (and with another DB than SqlServer this seems the safest approch) I will use either directly IDataReader or a List of DTOs.

Using directly IDataReader seems at first the easier approach, but coming to more complex object graphs the overhead of an intermediate List of DTOs helps to structure code such that using 1 resultset at a time. From business analysis I expect graphs up to 4 levels (not counting collections), and 1-3 branches per level.

So, I wonder which model would be the best for our application. Has anyone experiences with raw ADO.NET and Factory model?