calling sub multiple times from same thread

Question

I have a single threaded application that needs to call a sub multiple times passing it different arguments each time. The only problem is that the sub opens a data reader and reads data from a database (using the passed argument). That works fine, but the read operation takes a few seconds, so the second call to the sub throws an error "there is already an open datareader associated with this connection".

Without redesigning my whole app, is there something I'm missing where I need to do something with the datareader to allow multiple reads to happen simultaneously?

Thanks.

For example:

For x as integer = 0 to 10
 dim id as integer = my_sub(x)
Next X

Private function my_sub(x)

  dim return_value as integer = 0

  my_reader = sqldatareader
  db.cmd.CommandText = "select id from database where id = " & x
  my_reader = db.cmd.ExecuteReader
  my_reader.Read()
  If my_reader.HasRows Then
     return_value = my_reader(0)
  End If
  my_reader.Close()

  Return return_value

End Sub

It sounds like the datareader is not being closed in the code, but without seeing it we can't say for certain. — Andrew Morton, Oct 29 '19 at 16:29
It is being closed in the sub. The sub just takes a few seconds to run. So the second call to the sub is sent before it has a chance to finish reading and close itself. — John, Oct 29 '19 at 16:31
Without seeing the code, your question might be closed as a duplicate of [Exception: There is already an open DataReader associated with this Connection which must be closed first](https://stackoverflow.com/questions/5440168/exception-there-is-already-an-open-datareader-associated-with-this-connection-w). — Andrew Morton, Oct 29 '19 at 16:35
It should be noted this code has a huge security hole and is open to SQL injection — Hogan, Oct 29 '19 at 16:40
Andrew Morton's link has the answer to your issue. Your Command, and presumably your Connection, are not scoped to the `my_sub()`. You should be instantiating your connection and command objects as they are needed, and then dispose of them. Yours are already instantiated (and the connection open), wrapped in variable `db`. This is what is causing the problem. — HardCode, Oct 29 '19 at 16:40
Does this answer your question? [Exception: There is already an open DataReader associated with this Connection which must be closed first](https://stackoverflow.com/questions/5440168/exception-there-is-already-an-open-datareader-associated-with-this-connection-w) — STLDev, Oct 29 '19 at 16:48

score 2 · Accepted Answer · answered Oct 29 '19 at 16:48

User HardCode provided a great explanation in their comment:

Your Command, and presumably your Connection, are not scoped to the my_sub(). You should be instantiating your connection and command objects as they are needed, and then dispose of them. Yours are already instantiated (and the connection open), wrapped in variable db. This is what is causing the problem.

And the corresponding code would be something like

Private Function MySub(x As Integer) As Integer

    Dim returnValue As Integer = 0

    Using conn As New SqlConnection(yourConnectionString)
        Dim sql = "SELECT [id] FROM [database] WHERE [id] = @x"

        Using cmd As New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@x", .SqlDbType = SqlDbType.Int, .Value = x})

            conn.Open()
            Dim rdr = cmd.ExecuteReader()

            If rdr.HasRows Then
                rdr.Read()
                returnValue = rdr.GetInt32(0)
            End If

            rdr.Close()
            conn.Close()

        End Using
    End Using

    Return returnValue

End Function

Yeah, I see now that I need a separate (and new) connection each time I call the sub. So the sub needs to contain the new instance of the connection and that I can't use the existing connection that gets created outside the sub. Thanks for the help. It works now. — John, Oct 29 '19 at 16:53
@John Something called [Connection Pooling](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling?redirectedfrom=MSDN) is used to keep the construction of new connections efficient, if you were wondering about that aspect of it. — Andrew Morton, Oct 29 '19 at 18:42

calling sub multiple times from same thread

1 Answers1