Vfrdtyky

I have a stored proc which returns two distinct sets of results (header and detail). I then want to take these result sets, and write them independently into two destination tables. The format of each result set is identical to the appropriate destination table, so SqlBulkCopy seemed like the ideal solution. Code is as follows:

Try

    Using connection As SqlConnection = New SqlConnection(My.Settings.MyConnectionString)

        Dim sqlQuery As String = "exec sp_get_order_batch @BatchSize, @NewBatchId"

        connection.Open()



        Using comm As SqlCommand = New SqlCommand(sqlQuery, connection)

            comm.Parameters.Add("@BatchSize", SqlDbType.Int).Value = Me.Config.BatchSize

            comm.Parameters.Add("@NewBatchId", SqlDbType.Int).Value = Me.Config.GetNewBatchNumber()



            Dim rs As SqlDataReader = comm.ExecuteReader

            Dim dtHeader As DataTable = New DataTable

            dtHeader.Load(rs)



            If dtHeader.Rows.Count >= 1 Then

                'We've got some data back - yippee.



                'Write the header data out to the holding table

                Using sqlBulkCopyHeader As New SqlBulkCopy(connection)

                    sqlBulkCopyHeader.DestinationTableName = "dbo.web_order_header"

                    Try

                        sqlBulkCopyHeader.WriteToServer(rs)

                    Catch ex As Exception

                        Me.EventLog1.WriteEntry(ex.Message)

                    End Try

                End Using



                'Grab the line data out as well...

                rs.NextResult()

                Dim dtLines As DataTable = New DataTable

                dtLines.Load(rs)



                'Write the line data out to the holding table

                Using sqlBulkCopyLines As New SqlBulkCopy(connection)

                    sqlBulkCopyLines.DestinationTableName = "dbo.web_order_lines"

                    Try

                        sqlBulkCopyLines.WriteToServer(rs)

                    Catch ex As Exception

                        Me.EventLog1.WriteEntry(ex.Message)

                    End Try

                End Using



            End If



        End Using

    End Using



Catch ex As Exception

    Me.EventLog1.WriteEntry(ex.Message)

End Try

However, what is actually happening is the first call to SqlBulkCopy is actually trying to write the seconf result set into the destination table, rather than the first.

Is there a way to achieve what I want without having to resort to iterating the lines from each dataset and writing them in one at a time??