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();
lapuccaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wassa_rCommented:
When do you check if the connection is open? Where is your debug break point set at?

Might try this:


                using (SqlConnection connection = new SqlConnection(connectionString), SqlCommand cmd)
                {
                    connection.Open();
                    cmd = new SqlCommand(commandText, connection);
                }
0
Anurag ThakurTechnical ManagerCommented:
normally when you take the approach of using blocks then you dont declare the objects outside the scope means that the objects are defined in the using blocks itself as shown
in my small applications where i am not using my data blocks component i am talking this approach only

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
philipjonathanCommented:
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();
                }
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

lapuccaAuthor Commented:
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();

0
lapuccaAuthor Commented:
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?
0
philipjonathanCommented:
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
0
philipjonathanCommented:
Just to clarify a bit, you need to check for the connection open status inside of using {}, instead of outside of it.
0
brodaseatingCommented:
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

0
brodaseatingCommented:
*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

0
lapuccaAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.