MySqlDataReader - Invalid Attempt to Read When Reader is Closed

Question

I appreciate this question will have been asked before, however, I have spent the last 5 days trawling SO and the web and haven't yet found a solution to my problem.

I am trying to use C# to retrieve a record from a MySQL Database.

The code compiles fine, however when I test the application and try to bind the data to a DataTable(), the MySqlDataReader throws the following Exception

"Invalid Attempt to Read When Reader Is Closed"

I've not been able to diagnose the issue; as far as I can see, the reader isn't being closed before I try to read from it.

The method is below:

    /// <summary>
    /// Performs a select statement
    /// </summary>
    /// <param name="query">String of the SQL Query</param>
    /// <param name="parameters">List of SQL Parameters</param>
    /// <returns>DataTable</returns>
    public DataTable SelectQuery(string query, List<MySqlParameter> parameters)
    {
        MySqlConnection connection = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand(query, connection);
        foreach(MySqlParameter p in parameters)
        {
            cmd.Parameters.Add(p);
        }

        connection.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        return dt;
    }

The actual database connection is working fine, I've placed a watch on the MySqlConnection and the connection is opening as it should.

I am passing the below query to this method:

string query = "SELECT IDS_USER, USRNM, PSSWRD, USR_SALT FROM factUser WHERE USRNM = ?username";

Which I have tested in MySql Workbench and it returned the correct data.

When I watch the MySqlCommand, the parameters have been added to the command as they should have been.

Any help would be greatly appreciated as I am absolutely stumped!

Answer 1

You're close, but you have to set the ExecuteReader() function to a MySqlDataReader. Also, you should really get in the habit of using using statements especially in SQL related matters as if you do not properly dispose of your connections, you will run out quickly. Using using statements automatically disposes of the item in the using statement.

Your code with the appropriate variable set

MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand(query, connection);
foreach(MySqlParameter p in parameters)
{
    cmd.Parameters.Add(p);
}

connection.Open();
DataTable dt = new DataTable();
MySqlDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
return dt;

With the appropriate using statements

using(MySqlConnection connection = new MySqlConnection(connectionString)){
    using(MySqlCommand cmd = new MySqlCommand(query, connection)){
          foreach(MySqlParameter p in parameters)
          {
              cmd.Parameters.Add(p);
          }

           connection.Open();
           DataTable dt = new DataTable();
           using(MySqlDataReader reader = cmd.ExecuteReader()){
               dt.Load(reader);
               return dt;
           }
       }
  }