title: MySql, passing datareader, closing connections
body: <p>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</p>
  
  <pre><code>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 #" &amp; ex.Number &amp; " 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 
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>