Most efficient way to test SQL connection string availibility

Question

I have this code down which I tried to make it Test SQL string connectivity, but I dont know how to handle the part with connection.Open = true would you please help me to solve this out? Thank you so much for your time.

  private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection("Data Source='" + textBox1.Text + "';Initial Catalog='" + textBox2.Text + "';User ID='" + textBox3.Text + "';Password='" + textBox4.Text + "'"))
            {
                try
                {
                    connection.Open();
                    if (connection.Open == true) // if connection.Open was successful
                    {
                        MessageBox.Show("You have been successfully connected to the database!");
                    }
                    else
                    {
                        MessageBox.Show("Connection failed.");
                    }
                }
                catch (SqlException) { }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Chyba v přihlášení: " + ex);
        }
        finally
        {

        }
    }

It says: "Cannot asign 'open' because it is a 'methoud group' " I know that this code might be totaly bad, but I need to handle this somehow and have no idea what is the right way. Thank you.

This is what is not actually working for not-opened connection:

using (SqlConnection connection = new SqlConnection("Data Source='" + textBox1.Text + "';Initial Catalog='" + textBox2.Text + "';User ID='" + textBox3.Text + "';Password='" + textBox4.Text + "'"))
        {

             connection.Open();

            if (connection.State == ConnectionState.Open)
            {

                MessageBox.Show("Spojení s databázi problěhlo úspěšně.");
            }
            connection.Close();
            if (connection.State == ConnectionState.Closed)
            {
                MessageBox.Show("Spojení selhalo");
            }
        }

Cioffi

.Open() is a method which returns void it is not a property. You cannot assign a value to it. See my answer. – Kayleigh

Answer 1

You're using connection.Open = true as if it were a property.

It's a method: connection.Open()

Use the ConnectionState enum to determine if the connection is open or not, eg:

connection.State == ConnectionState.Open

Kayleigh

Hello, thank you for your time, May I ask how would look (if wasn't connected successfully?) I tried connection.State == ConnectionState.Closed or Broken but it didn't pop up the msg box. – Cioffi

Where did you call connection.State == ConnectionState.Closed? Can you update your question? – Kayleigh

Thanks. The second messagebox will obviously not show because you have just called connection.Open() to open the connection, therefore, it's open, not closed. Try calling connection.Close() and then testing the second messagebox. – Kayleigh

I tried but it stopped when the connection.Open fails with bad SqlConnection details and doesnt pop up msg box. THank you so much for your time, I have updated my question, what code I have now. – Cioffi

You can always inspect the connection in the debugger when this happens, you should be able to see it's state there. Alternatively, you could just do if(connection.State != ConnectionState.Open) then it would match all other scenarios. – Kayleigh

Answer 2

You need to check if it's open by this code:

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

Belfort

Hello, thank you for your time, May I ask how would look (if wasn't connected successfully?) I tried connection.State == ConnectionState.Closed or Broken but it didn't pop up the msg box. – Cioffi

I'd try connection.State!=ConnetionState.Open – Belfort

I tried that too, but didn't pop up the messagebox neither. I updated the question so you can understand better my problém. – Cioffi