asked on

Why can't I open connection when I use "using" code?

If I use the following code, in debug mode, I don't get error but the connection state stays "closed" even thought connection.Ope() is executed no problem.
SqlConnection connection;
SqlCommand cmd;
using (connection = new SqlConnection(connectionString))
{
connection.Open();
cmd = new SqlCommand(commandText, connection);
}

When i use the following code then there is no problem with connection not opened. Why is that so?
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(commandText, connection);
connection.Open();

SOLUTION

wassa_r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Anurag Thakur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

philipjonathan

I agree with wassa_r that it could be a matter of how you check the connection open status. Try to execute the SqlCommand and see if exception is generated (if command is executed when connection is not yet opened, an exception is generated). Just try to execute a select query ...

SqlConnection connection;
SqlCommand cmd;
using (connection = new SqlConnection(connectionString))
{
connection.Open();
cmd = new SqlCommand(commandText, connection);
cmd.ExecuteScalar();
}

lapucca

ASKER

I'm checking connection outside of using {}. I expanded the connection property and looked at the "status" and it shows "Closed". I check on it becuase I was getting Exception error message when I execute my SQLDATAREADER , it complained that the connection was not initilized.
SqlDataReader rdr = cmd.ExecuteReader();

lapucca

ASKER

I forgot to mention that these code are in a C# class that is in test folder. I'm developing on VS2008 MCV platform. Would this be causing this error?

philipjonathan

Well, I guess that's the purpose of "using", isn't it?
Once the code goes beyond its {}, then the variable in using clause is Disposed, or Closed in this case.

Check this out:
http://msdn.microsoft.com/en-us/library/yh598w02.aspx

SOLUTION

philipjonathan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brodaseating

Hi lapucca,

Try opening the connection after you set your SqlCommand; thats they way I do it and never had a problem.

Cheers

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = "SELECT ID, Name FROM Customers";
  
  conn.Open();
  
  cmd.Excecute...
  conn.Close();
}

Open in new window

brodaseating

*Note. dont need to call conn.Close(); since the Dispose() method will be called after the last "}". Also check for an open conn before trying to Open() again.

Thanks

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

Open in new window

lapucca

ASKER

Duhhhhhhhhhhhh.... Thanks for everyone's help on this. It's my first time using the "using" for data connection. Understand now the connection is disposed outside of {} is the key for me. Thanks to everyone's input.

Why can't I open connection when I use &quot;using&quot; code?

Why can't I open connection when I use "using" code?