I know this is been asked in one for or another but I really can't find the best explanation. I have a MySql DataReader function which is supposed to get one row from DB and return it "as is" because I want to process the data somewhere else
Private Function GetRow(ByVal sql As String) As MySqlDataReader
Dim cmd As New MySqlCommand
Try
cmd.Connection = New MySqlConnection() With {.ConnectionString = connString}
cmd.CommandText = sql
cmd.CommandType = CommandType.Text
cmd.Connection.Open()
Using dr As MySqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.HasRows Then
dr.Read()
End If
Return dr
End Using
Catch ex As MySqlException
MsgBox(("Error #" & ex.Number & " while reading from DB has occurred: ") + vbCrLf + ex.Message, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Error!")
Finally
cmd.Connection.Close()
If cmd IsNot Nothing Then cmd.Dispose()
End Try
Return Nothing
End Function
Each time I used the function, connection remains opened (pooling disabled) so I started to use "using" statement for MySqlDataReader, along with "finally" dispose the connection itself. But now the function always returns DR with nothing, probably "using" dispose the Read() results.
Now the question is how can I pass the Read() entire row "as is"? Why should I still use Reader (if so complicated) if I can use a TableAdapter and pass the DataTable even for 1 row expected results?
