How to insert, update or delete after DataReader.Read?

Question

I have a project that needs to update database when I read from DataReader, it says that the DataReader is already opened on this connection that must be closed first.

Here is my code:

Try

    conn = fOpenConnection()
    cmd = conn.CreateCommand
    queryjadi = "select *..."
    cmd = New MySqlCommand(queryjadi, conn)
    dbReader = cmd.ExecuteReader

    While dbReader.Read
        For Each lvi In lsvBOM.Items
            If dbReader("kode") = lvi.Text.ToString Then
                **'here i want to update my database**
            End If
        Next
    End While

    dbReader.Close()

    queryjadi = "SELECT * ... "

    cmd = New MySqlCommand(queryjadi, conn)
    dbReader = cmd.ExecuteReader

    For Each lvi In lsvBOM.Items
        status = False
        While dbReader.Read
            If dbReader("kode") = lvi.Text Then
                status = True
            End If
        End While
        If status = False Then
            'untuk insert
            **'here i want to insert into database**
        End If
    Next

   MessageBox.Show("Data sudah disimpan", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

Catch sqlEx As MySqlException

    fSQLError(sqlEx.Number, sqlEx.Message)

Catch ex As Exception

    MessageBox.Show(ex.Message, "Kesalahan", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)

Finally
    dbReader.Close()
    cmd.Dispose()
    conn.Close()

End Try

I know that I can't access database when I read from DataReader, how to fix it?

Answer 1

Normally, a connection only supports one concurrent operation; so if you want to use a single connection, a one approach is to buffer the data first (into a list or table of some kind), then iterate over the list in memory, using the connection to perform updates.

Depending on the database vendor/version, you might be able to enable "MARS" (Multiple Active Result Sets) - this is a technology that enables multiple commands on the same connection. For example, on SQL Server this is activated by changing the connection string to include:

MultipleActiveResultSets=True

A third option is to use two connections; one for the read operation, and one for the updates - however, note that this might hit issues with blocking (and possibly also deadlocking) between the connections.

However, the fourth and most preferred option is: don't pull data over the network just to do an update. If possible, write a batch-based operation in SQL that performs the change directly without ever leaving the database, and just execute that SQL script.