How to use DataContext.Translate with multiple result sets?

  • Thread starter Thread starter David Potter
  • Start date Start date
D

David Potter

I have a stored proc that returns multiple results sets and I'm attempting to
use the DataContext Translate method. The first translate works but when I
can't move on to the next resultset as the datacontext has closed the
connection. How do I get around that?

I'm using the following code:


Dim myUserList As New List(Of Stc.BR.WcfContract.UserInformation.UserRow)
Dim myUserPermission As New List(Of UserInfo.UserPermissionRow)

' ... Other Code.

Using myRead As SqlDataReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection), _
myContext As New DataContext(myConnect)


' Read User Table
myUserList.AddRange(myContext.Translate(Of
UserInfo.UserRow)(myRead))


' Move to next table
myRead.NextResult() ' This fails because the connnection
is closed
myUserPermission.AddRange(myContext.Translate(Of
UserInfo.UserPermissionRow)(myRead))
End Using
 
Hi David,

Welcome to Microsoft Newsgroup Support service. My name is Lingzhi Sun
[MSFT]. It's my pleasure to work with you on this case.

The reason for the problem is that once DataContext.Translate method
finishes its work, it closes the corresponding DataReader object. So when
we are calling DataReader.NextResult method, the exception throws. To
retrieve multiple result sets of a stored procedure, we can select another
DataContext.Translate method
(http://msdn.microsoft.com/en-us/library/bb534665.aspx) which returns an
IMultipleResults object
(http://msdn.microsoft.com/en-us/library/system.data.linq.imultipleresults.a
spx). For detail, you can refer to the following code snippet:

=========================
Dim multiSets = myContext.Translate(myRead)
myUserList.AddRange(multiSets.GetResult(Of UserInfo.UserRow)())
myUserPermission.AddRange(multiSets.GetResult(Of
UserInfo.UserPermissionRow)())
=========================

If you have any other questions, please be free to let me know.

Have a nice day!

Regards,
Lingzhi Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top