Grandchild heirarchy fails in SafeDataReader call

Grandchild heirarchy fails in SafeDataReader call

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


GregBen posted on Wednesday, July 14, 2010

My DB structure is almost identical to the structure in the DpRepos example of the 3.8 demos.  The root and first child work, but the grandchild errors when getting the resultset.  It fails with this exception:

DataPortal.Fetch failed (ChildDataPortal.Fetch failed on the server). 

The error seems to occur when using the ConnectionManager or the SafeDataReader. I'm hoping someone has encountered this early on in their foray into CSLA.  I might be missing some subtle property setting.  The only difference between the first child and grandchild, is the data for the first child collection comes from the Root query (like the DpRepos example).


Some of the things I’ve tried include

  1. Replace the grandchild query with the root queries (which work).
  2. Verified no nulls are in the table (although I think the SafeDataReader is supposed to handle those OK).
  3. Verified I’m exactly following the DpRepos example.
  4. Works with regular SQLClient objects (shown below)
  5. Rewrote my Root-Child-Grandchild structure in the basic DataPortal format (not like a data repository pattern), with identical results.

Exhibit 1 – doesn’t work, fails here:

AngryUsing dr = New SafeDataReader(dal.Fetch(docketID))

      Private Sub Child_Fetch(ByVal docketID As Integer)

            Using dalFactory = DataAccess.DalFactory.GetManager()
                  Dim dal = dalFactory.GetProvider(Of DataAccess.IImgItemDAL)()

                  AngryUsing dr = New SafeDataReader(dal.Fetch(docketID))
                        While dr.Read()
                              Add(DataPortal.FetchChild(Of ImgItemEdit)(dr))
                        End While
                  End Using
            End Using

      End Sub

Exhibit 2 – this version works.
      Private Sub WorksChild_Fetch(ByVal docketID As Integer)
            Dim connStr As String = "Data Source=srv;Initial Catalog=SCL_DB;Integrated Security=True"
            Dim con As New SqlConnection(connStr)
            Dim sql As String = "select ImgItemID, [DocketID], [ImgName] from [Result].[ImageItem] where DocketID = @id"
            Dim cmd As New SqlCommand(sql, con)
            Dim dr As SqlDataReader

            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@id", docketID)
            con.Open()
            dr = cmd.ExecuteReader()
            While dr.Read
                  Add(DataPortal.FetchChild(Of ImgItemEdit)(dr))
            End While

            con.Close()

      End Sub

RockfordLhotka replied on Thursday, July 15, 2010

You should look at the inner exception (or BusinessException) to see what the real exception is - that'd help determine the problem.

When working with grandchild objects, you almost certainly need to have a second database connection open to load the grandchild data, or you need to enable MARS.

If you don't enable MARS, then you'll need to ensure that the grandchild data access uses a separate connection, and there's an overload on GetManager() that takes a string label for the connection - specifically to allow you to force the opening of a second database connection.

GregBen replied on Thursday, July 15, 2010

Thanks Rocky - I verified the non-DpRepos works when applying a value for `label`.  However, when debugging through the DpRepos version `Demo`, that label is always `default`, while `_ctx.mRefCount` increments, just like mine.  The only difference I see is that my app is a Console (not WPF).  Is the single-thread nature of a console app the issue?  Given your reply, it seems your app uses MARS, since I see no indication that GetManager is altered.  Is MARS somehow configured in Demo's DemoDb.mdf?

I'll read-up on ConnectionManager.GetManager, as I thought one of the benefits was it maintained a pool of connections.  Also, it looks like using the `label` in GetManager invokes DTC, which I'd like to avoid.  So hopefully you can tell me the magic involved in the DpRepos `Demo` solution.

GregBen replied on Thursday, July 15, 2010

Oh yeah, the inner exception is :

There is already an open DataReader associated with this Command which must be closed first.

RockfordLhotka replied on Thursday, July 15, 2010

MARS is configured on the database connection string.

I'm not a database expert, so I read a lot of stuff on the web about MARS. It sounds like there's a potential perf hit when using MARS - some people think it is a big deal, others dismiss it.

I do know that MARS is the simplest solution, since it doesn't force changes in your code. Using a label to make the connection manager give you a second database connection obviously works, but does reduce the composibility of your code...

GregBen replied on Thursday, July 15, 2010

OK - I see how you did this.  Add this to the connection string: MultipleActiveResultSets=True

More info here: http://forums.lhotka.net/forums/p/7780/37126.aspx

Case closed, thanks for the help Rocky! 

Copyright (c) Marimer LLC