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
Exhibit 1 – doesn’t work, fails here:
Using 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)()
Using 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
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.
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.
Oh yeah, the inner exception is :
There is already an open DataReader associated with this Command which must be closed first.
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...
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