Persisting a SqlDataReader in a SqlFunction enumerator

I have built a Table Valued SqlFunction which streams out filtered results from a table. The way that I have implemented this is by using a custom class which implements the IEnumerator interface.

The class internally stores a SqlDataReader and a SqlConnection as private member variables. The Class initializer (ie: the New function) creates a SqlCommand which is executed with ExecuteReader into the SqlDataReader and returns.

The IEnumerator.MoveNext method loops through the SqlDataReader until it finds a result which matches the heuristic filter, and the IEnumerator.Current method returns the current result from the SqlDataReader.

Unfortunately as soon as the initializer returns the SqlDataReader is automatically closed, and I can't figure out why. I've debugged through this and at the end of the Initializer the SqlDataReader is definately open, and as soon as first call to MoveNext is run it is closed.

Can anyone offer any suggestions on how I can fix this.

In the interim i've had to load all of the filtered results into a temporary ArrayList in the Initializer, which defeats the purpose of streaming out the results.

"You can use the context connection in the initialization method ..., but not in the method that fills rows (the method pointed to by the FillRowMethodName attribute property)." (copy/pasted from Managed Data Access Inside SQL Server with ADO.NET and SQLCLR), so you cannot loop through the SqlDataReader inside FillRow method.

Solutions are:

Implement the filtering using T-SQL (if possible)|||I'm not using the FillRow method to access the SqlDataReader; it is just passed a copy of the object (it's a UDT in this case) which the IEnumerator.Current method returns. I am looping through the SqlDataReader in the IEnumerator.MoveNext method.

My code looks something like this (with large chunks of logic cut out):

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
Imports System.Collections
Imports System.Security
Imports System.Net

<Assembly: AllowPartiallyTrustedCallers()>

Public Class MyListIterator
Implements IEnumerator

Private oIncludes(0) As MySQLType
Private oExcludes(0) As MySQLType
Private oColumnMeta As SqlMetaData
Private oConn As SqlConnection
Private oDR As SqlDataReader
Private bMoreRecords As Boolean = False
Private iChannelAccountID As Integer

Public Sub New(ByVal ChannelAccountID As Integer)
iChannelAccountID = ChannelAccountID
InitReader()
End Sub

Private Sub InitReader()
oConn = New SqlConnection("context connection=true")
Debug("Starting InitReader")
Dim oCommand As SqlCommand
Dim oMyCode As MySQLType

' Set up the filter arrays (oIncludes and oExcludes) here
' ....
' end filter array setup

' now we'll just loop through all of the My codes and return those that match
oCommand = New SqlCommand("SELECT MyCode FROM MyCodes", oConn)
Dim oColumnMeta As New SqlMetaData("MyCode", SqlDbType.Udt, GetType(MySQLType))

oConn.Open()
oDR = oCommand.ExecuteReader()
bMoreRecords = True
' dont' need to get the first item
' the initial position of the result set is defined as "before the beginning"
End Sub

Public ReadOnly Property Current() As Object Implements System.Collections.IEnumerator.Current
Get
If bMoreRecords Then
Return oDR(0)
Else
Return Nothing
End If
End Get
End Property

Public Function MoveNext() As Boolean Implements System.Collections.IEnumerator.MoveNext
If bMoreRecords AndAlso Not oDR.IsClosed Then
' find the next included code
Do While oDR.Read
If CType(oDR(0), MySQLType).IsIncluded(oIncludes, oExcludes) Then Return True
Loop
End If
bMoreRecords = False
Return False ' no more records
End Function

Public Sub Reset() Implements System.Collections.IEnumerator.Reset
bMoreRecords = False
If Not oDR Is Nothing AndAlso oDR.IsClosed Then oDR.Close()
If Not oConn Is Nothing Then oConn.Close()
InitReader()
End Sub

End Class

Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(TableDefinition:="MyCode MySQLType", _
IsPrecise:=True, _
IsDeterministic:=False, _
DataAccess:=DataAccessKind.Read, _
FillRowMethodName:="FillMyListRow")> _
Public Shared Function GetMyList(ByVal ChannelAccountID As Integer) As IEnumerator
Return New MyListIterator(ChannelAccountID)
End Function

Public Shared Sub FillMyListRow(ByVal oMyInCode As Object, <Out()> ByRef oMyOutCode As MySQLType)
If Not oMyInCode Is Nothing Then
oMyOutCode = CType(oMyInCode, MySQLType)
Else
oMyOutCode = MySQLType.Null
End If
End Sub
End Class|||

Further to my last post, I can't use TSQL to create the filters because the logic is too complex (it relies on heirarchical data and complex type rules).

I have created the function as a SQLCLR stored procedure successfully however I was hoping to use a SqlFunction so that I could perform joins (eg: SELECT [whatever] FROM [mytable] WHERE [myfield] IN MyFunction(@.ChannelAccountID)).

One alternative is that I figure out how to join select results with the results returned from a stored procedure, however i haven't figured out how to do that without executing the stored procedure into a temporary table (and even that is simply what I guess that you could do; i haven't actually tried at this point).

I have also implemented a scalar valued CLR function which checks the filters, however this is unoptimal because I need to build the filter arrays again for each and every row in the table (about 20,000 rows). This performs too slow for my liking.

Cheers

Beric Holt

http://buzzrick.true.geek.nz