C# MySQL ExecuteReader

Question

I have a problem with my C# project. I use a MySQL database and I use the MySQL Connector driver from MySQL Website but I have a problem with the cursor and the connexion. Indeed, Visual Studio says that is impossible to read data from a second procedure because the cursor is already open but I have closed the cursor before the new procedure call.

This is my code:

static public Data loadData()
{
    Data database = new Data();
    myConnexion.Open();
    
    /// <summary>
    ///     Loading of the categories
    /// </summary> 
    MySqlCommand command = new MySqlCommand("getCategory", myConnexion);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    MySqlDataReader cursor = command.ExecuteReader();

    while (cursor.Read())
    {
        int id = Convert.ToInt32(cursor["id"]);
        string categoryName = Convert.ToString(cursor["name"]);

        Category category = new Category(id, categoryName);
        database.addCategory(category);
    }
    cursor.Close();
    
    /// <summary>
    ///     Loading of the projects
    /// </summary>
    command = new MySqlCommand("getProject", myConnexion);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    cursor = command.ExecuteReader();

    while (cursor.Read())
    {
        int idProject = Convert.ToInt32(cursor["id"]);
        string name = Convert.ToString(cursor["name"]);
        int idCategory = Convert.ToInt32(cursor["idCategory"]);

        Category category = database.getCategories()[idCategory];
        Project project = new Project(idProject, name, category);
        Link.addProject(project.getName(), category);
    }
    cursor.Close();

    myConnexion.Close();
    return database;
}

This is the error message from Visual Studio when I launch my program:

Answer 1

You can try converting the DataReader to a using block, which should close and dispose of the datareader.

static public Data loadData()
{
    Data database = new Data();
    myConnexion.Open();

    /// <summary>
    ///     Loading of the categories
    /// </summary> 
    MySqlCommand command = new MySqlCommand("getCategory", myConnexion);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    using (var cursor = command.ExecuteReader())
    {
        while (cursor.Read())
        {
            int id = Convert.ToInt32(cursor["id"]);
            string categoryName = Convert.ToString(cursor["name"]);

            Category category = new Category(id, categoryName);
            database.addCategory(category);
        }
    }



    /// <summary>
    ///     Loading of the projects
    /// </summary>
    command = new MySqlCommand("getProject", myConnexion);
    command.CommandType = System.Data.CommandType.StoredProcedure;
    using(var cursor = command.ExecuteReader())
    {

        while (cursor.Read())
        {
            int idProject = Convert.ToInt32(cursor["id"]);
            string name = Convert.ToString(cursor["name"]);
            int idCategory = Convert.ToInt32(cursor["idCategory"]);

            Category category = database.getCategories()[idCategory];
            Project project = new Project(idProject, name, category);
            Link.addProject(project.getName(), category);
        }
    }

    myConnexion.Close();
    return database;
}