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");
            }
        }

`.Open()` is a **method** which returns void it is not a property. You cannot assign a value to it. See my answer. — DGibbs, Jul 25 '13 at 09:01

score 20 · Accepted Answer · answered Jul 25 '13 at 08:58

20

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

answered Jul 25 '13 at 08:58

DGibbs

14,316
7
44
83

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. – Marek Jul 25 '13 at 09:08
Where did you call `connection.State == ConnectionState.Closed`? Can you update your question? – DGibbs Jul 25 '13 at 09:11
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. – DGibbs Jul 25 '13 at 09:21
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. – Marek Jul 25 '13 at 09:27
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. – DGibbs Jul 25 '13 at 09:52

score 4 · Answer 2 · answered Jul 25 '13 at 09:01

4

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

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

answered Jul 25 '13 at 09:01

Kamil Budziewski

22,699
14
85
105

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. – Marek Jul 25 '13 at 09:09
2

I'd try `connection.State!=ConnetionState.Open` – Kamil Budziewski Jul 25 '13 at 09:12
1

I tried that too, but didn't pop up the messagebox neither. I updated the question so you can understand better my problém. – Marek Jul 25 '13 at 09:17

Most efficient way to test SQL connection string availibility

2 Answers2

Linked