Asp.net C# & MySQL, (The Read method cannot be called when another read operation is pending.)

Question

Problems: 1: The Read method cannot be called when another read operation is pending. 2: There is already an open DataReader associated with this Connection which must be closed first.

I have used Asp.net c# and MySql to develop my project. When I did publish it, I faced a problem. The problem is when more than one user open the website, the MySql connection stop working. There is a sample of my code.

 public partial class ConTest : System.Web.UI.Page
    {
   MySqlConnection con = new MySqlConnection("Server= mysql-85112-0.cloudclusters.net; port=11923; Database=Test; Uid=xxxx; Pwd=xxxx;");
        MySqlCommand cmd = new MySqlCommand();
        MySqlDataReader dr;
        protected void Page_Load(object sender, EventArgs e)
        {

            cmd.Connection = con;

            Database.con.Close();
            Database.con.Open();
            cmd.CommandText = "select s_name from students_info";
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Label1.Text = dr[0].ToString();
            }
            Database.con.Close();


        }
    }

Does this answer your question? [Using MySQLConnection in C# does not close properly](https://stackoverflow.com/questions/5567097/using-mysqlconnection-in-c-sharp-does-not-close-properly) — devlin carnate, Aug 10 '22 at 22:39
Also, [here](https://stackoverflow.com/questions/59427365/what-is-the-best-way-to-close-a-mysql-connection-in-an-asp-net-application) — devlin carnate, Aug 10 '22 at 22:41

score 0 · Answer 1 · answered Aug 11 '22 at 14:30

Don't share MySqlConnection objects. Create a new one when you need it, and close it automatically by using a using declaration:

protected void Page_Load(object sender, EventArgs e)
{
    using var connection = new MySqlConnection("Server= mysql-85112-0.cloudclusters.net; port=11923; Database=Test; Uid=xxxx; Pwd=xxxx;");
    using var command = new MySqlCommand("select s_name from students_info", connection);

    connection.Open();
    using var dr = command.ExecuteReader();
    if (dr.Read())
    {
        Label1.Text = dr.GetString(0);
    }
}

Asp.net C# & MySQL, (The Read method cannot be called when another read operation is pending.)

1 Answers1