Execute multiple stored procedures in VB.NET

Question

I'm creating a WinService in VB.NET to get some data from a Table, do some things with these data and then upload new data into this Table. What I need is something like this:

Dim conn As New MySqlConnection(my_connString)
conn.Open()

Dim cmd As New MySqlCommand("my_Stored_Procedure_1", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim reader As MySqlDataReader = cmd.ExecuteReader()

While reader.Read()
    Try
        ' SP to SELECT Data from DB table '
        Dim columnData As String
        columnData = reader("ColumnName")
        columnData_2 = reader("ColumnName_2")

        ' (...) Do something with this Data '

        Try
            ' SP to UPDATE Data into the same DB table '
            'cmd.Dispose() '
            cmd = New MySqlCommand("my_Stored_Procedure_2", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.ExecuteReader()

            ' (...) Do something else '

        Catch ex As Exception
            Console.WriteLine("ERROR: " & ex.Message)
        End Try
    Catch ex As Exception
        Console.WriteLine("ERROR: " & ex.Message)
    End Try
End While

reader.Close()
conn.Close()

The problem is that this doesn't work. It says There is already an open DataReader associated with this Connection which must be closed first. So I tried to create different SQL commands, close and re-open the connection, and create different connections like suggested here but all of them without success. This class seems to be useful but that's a lot of code for a simple (?) task. I've read a lot of similar questions but I didn't found what I need yet.

How can I handle this issue? Some help would be nice.

Answer 1

This sure looks like a duplicate of the question you linked to, but the answer there doesn't provide a lot of detail on how to fix the error. As the error says, you can only have one open reader per connection, so you need to use a different connection for the update. You say you have tried that, but perhaps your attempt was incorrect. As suggested in the linked question, you should also use Using statements for resource management.

So, you probably want something like this (untested, of course!):

Try
    Using conn1 As New MySqlConnection(my_connString),
          conn2 As New MySqlConnection(my_connString)

        conn1.Open()
        conn2.Open()

        Using cmd1 As New MySqlCommand("my_Stored_Procedure_1", conn1)
            cmd1.CommandType = CommandType.StoredProcedure

            Using reader1 As MySqlDataReader = cmd1.ExecuteReader()
                While reader1.Read()
                    ' SP to SELECT Data from DB table '
                    Dim columnData As String
                    columnData = reader1("ColumnName")
                    columnData_2 = reader1("ColumnName_2")

                    ' (...) Do something with this Data '

                    ' SP to UPDATE Data into the same DB table '
                    Using cmd2 As New MySqlCommand("my_Stored_Procedure_2", conn2)
                        cmd2.CommandType = CommandType.StoredProcedure
                        Using reader2 As MySqlDataReader = cmd2.ExecuteReader()
                            ' (...) Do something else '
                        End Using ' reader2
                    End Using ' cmd2
                End While
            End Using ' reader1
        End Using ' cmd1
    End Using ' conn1, conn2
Catch ex As Exception
    Console.WriteLine("ERROR: " & ex.Message)
End Try

As you can see from the levels of nesting, there is quite a lot going on here in terms of the resource scopes, so you may want to refactor this into multiple methods. You could also use a data adapter to populate a DataTable for the results of my_Stored_Procedure_1, instead of using a DataReader, and then just need a single connection (assuming the data isn't too large for that).