C# error "The connection was not closed. The connection's current state is open"

Question

I have this code and everything is fine except that when I click the save button, an error is occurred on the connection.Open(); under idpicture = brs.ReadBytes((int)Stream.Length);.

I am a beginner in C# so please bear with me.

private void button6_Click(object sender, EventArgs e)
        {
            if (File.Exists(op.FileName))
            {
                object sel = comboBoxcourse.SelectedValue;

                byte[] idpicture = null;
                FileStream Stream = new FileStream(imgLocation, FileMode.Open, FileAccess.Read);
                BinaryReader brs = new BinaryReader(Stream);
                idpicture = brs.ReadBytes((int)Stream.Length);
                connection.Open();

                if (textBoxidyear.Text == "" || textBoxidnumber.Text == "" || textBoxfirstname.Text == "" || textBoxmiddlename.Text == "" ||
                            textBoxlastname.Text == "" || comboBoxcourse.Text == "")
                {
                    MessageBox.Show("Please complete the form!");
                }
                else
                {
                    cmd = new SqlCommand("SELECT * FROM Sstudent WHERE idnumber ='" + textBoxidnumber.Text + "' ", connection);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    int i = ds.Tables[0].Rows.Count;

                    if (i > 0)
                    {
                        MessageBox.Show("Id Number " + textBoxidnumber.Text + " already exists!");
                    }
                    else
                    {
                        try
                        {
                            //connection.Open();
                            string query = @"INSERT INTO Sstudent(idnumber,idyear,idpicture,firstName,middleName,lastName,gender,course) 
                                        Values(@idnumber,@idyear,@idpicture,@firstName,@middleName,@lastName,@gender,@course)";

                            cmd = new SqlCommand(query, connection);
                            using (SqlConnection xcon = new SqlConnection(@"Server=GRACELIEZEL\SQLEXPRESS;Database=Final_Project;Integrated Security=SSPI;"))
                            {
                                using (SqlCommand xcom = new SqlCommand(query, xcon))
                                {
                                    xcom.CommandType = CommandType.Text;

                                    cmd.Parameters.Add(new SqlParameter("@idnumber", textBoxidnumber.Text));
                                    cmd.Parameters.Add(new SqlParameter("@idyear", textBoxidyear.Text));
                                    cmd.Parameters.Add(new SqlParameter("@idpicture", idpicture));
                                    cmd.Parameters.Add(new SqlParameter("@firstName", textBoxfirstname.Text));
                                    cmd.Parameters.Add(new SqlParameter("@middleName", textBoxmiddlename.Text));
                                    cmd.Parameters.Add(new SqlParameter("@lastName", textBoxlastname.Text));
                                    cmd.Parameters.Add(new SqlParameter("@gender", GetGender()));
                                    cmd.Parameters.Add(new SqlParameter("@course", comboBoxcourse.GetItemText(comboBoxcourse.SelectedItem)));

                                    int N = cmd.ExecuteNonQuery();
                                    connection.Close();

                                    MessageBox.Show(" Saved Successfully...!");
                                    AddStudent p = new AddStudent();
                                    p.Show();
                                    this.Hide();
                                }
                            }

                        }
                        catch (Exception)
                        {
                            throw;
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                }
            }
            else
            {
            MessageBox.Show("Please choose an image.");
            }
        }

Answer 1

It seems you declared your connection variable inside the class so it opened when you first used it (maybe when your first click) and it stays that way. When you click the button after connection opened you are trying to open connection again.

In addition you do not close your connection in some cases like if (i > 0). Your try/finally block is started after that check so close in finally never runs in the case of i is greater than 0.

Better way is to use a local variable and open connection as late as possible and close connection (preferably with using() ) as early as possible. Below is a sample.

using (SqlConnection connection = new SqlConnection(connStr))
{
    connection.Open();
    // Do your work here
}
// Do other work here

One more addition: When you shared connection object and keep it idle for some time, you can get exceptions when you try to use it. For example you opened the connection and waited idle for, let's say, 10 minutes (depends on timeout duration) when you try to use it you will most probably get a TimeOutException. In this case checking State will not help that because it stays as opened until you explicitly change state using Close() or you try to use it in some way. After you get exception State will be updated.

Documentation says (link):

Indicates the state of the SqlConnection during the most recent network operation performed on the connection.

Answer 2

it seems that your connection object is global, and sometimes it seems you try to call connection.Open() ,when there is already open. Try:

if (connection.State == ConnectionState.Closed)
{
  connection.Open();
}

However, have in mind that having global connections objects is not a good practice. Better use local object and even better use it inside a using statement like:

using (var connection= new SqlConnection())
{
      .......//your code here                      
} //when ended disposed will be called which will also close the connection