Robert Robert - 24 days ago 15
Vb.net Question

How to make SqlDataReader thread safe / convert to thread safe type

I am dealing with a legacy applcation that is written in VB.Net. I have been tasked to use the Paralel task library and thread the application. The bulk of the "work" is in one while loop that centers around a SqlDataReader object. To the credit of the developer it is broken into logical methods and one real Do the work method. The major problem is all of the methods that handle the individual tasks accept SqlDataReader as a parameter. I know that SqlDataReader isn't really thread safe and in the way its being used its not thread safe at all. What I want to do I think is convert the SqlDataReader to a ConcurrentQueue or IEnumerable and then just have the "work" thread and work on the data in that collection:

Using Command As New SqlCommand(StoredProcedure, Connection)
Command.CommandType = CommandType.StoredProcedure
Command.CommandTimeout = 0
Command.Parameters.Add("@Carrier", SqlDbType.VarChar, 50).Value = sCarrier
Using Reader As SqlDataReader = Command.ExecuteReader
If Reader.HasRows = True Then
SetReaderOrdinals(Reader)
Adjustments = New StringBuilder


'TODO this appears to be the bulk of the work in the application
While Reader.Read
Adjustments.Clear()
CommitCount += 1
If Reader.IsDBNull(SomeValue) = False Then
Select Case stuff
Case 1
DoThingForOne(Reader)
Case 2
DoThingForTwo(Reader)
Case 3
DoThingForThree(Reader)
Case 4
DoThingForFour(Reader)
Case 5
DoThingForFive(Reader)
Case 6
DoThingForSix(Reader)
Case Else
'Log something
Exit While
End Select
Else
'We Failed
End If
End While


Inside those methods the rdr is acted upon in those methods ex:

If Rdr.GetString(SomeValue).Trim.Length >= 5 Then

If Rrd.IsDBNull(SomeValue)

Rrd.GetInt32(SomeValue)


What I would like to do is something like:

'I know this isn't how you convert it but I am not sure how you do
Dim rows AS IEnumerable(of MyObject) = Reader

'Create threads and spawn them here

'act upon the collection here in many threads
Parallel.For Each row in rows
'Do row stuff here
If row Not Nothing Then
Select Case stuff
Case 1
DoThingForOne(row)
Case 2
DoThingForTwo(row)
Case 3
DoThingForThree(row)
Case 4
DoThingForFour(row)
Case 5
DoThingForFive(row)
Case 6
DoThingForSix(row)
Case Else
'Log something
Exit For
End Select
Else
'We Failed
End If
End For


I am not sure if this is plausible or the best way to handle this, but it is the first thing that came to mind.

Any Suggestions?

Answer

If you put your database reader code into an iterator function, that will give you the way to convert it to IEnumerable, e.g.

Iterator Function ReadMyObjects() As IEnumerable(Of MyObject)
    Using cn = New SqlConnection("...")
        cn.Open()
        Using cmd = New SqlCommand("...", cn)
            Using rdr = cmd.ExecuteReader()
                If rdr.HasRows Then
                    While rdr.Read()
                        Yield New MyObject() With {
                            .PropA = rdr.GetString(rdr.GetOrdinal("A"))
                        }
                    End While
                End If
            End Using
        End Using
    End Using
End Function

You can then use that in your parallel loop:

Parallel.ForEach(
    ReadMyObjects(),
    Sub(item As MyObject)
        ' do something with item
    End Sub
)
Comments