Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-11-18
10
Medium Priority
?
592 Views
Last Modified: 2012-06-21
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();
0
Comment
Question by:lapucca
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 3

Assisted Solution

by:wassa_r
wassa_r earned 400 total points
ID: 22990799
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
 
LVL 26

Accepted Solution

by:
Anurag Thakur earned 800 total points
ID: 22990826
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
 
LVL 18

Expert Comment

by:philipjonathan
ID: 22990842
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:lapucca
ID: 22990934
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
 

Author Comment

by:lapucca
ID: 22990938
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
 
LVL 18

Expert Comment

by:philipjonathan
ID: 22991090
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
 
LVL 18

Assisted Solution

by:philipjonathan
philipjonathan earned 800 total points
ID: 22991097
Just to clarify a bit, you need to check for the connection open status inside of using {}, instead of outside of it.
0
 
LVL 1

Expert Comment

by:brodaseating
ID: 22996230
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
 
LVL 1

Expert Comment

by:brodaseating
ID: 22996306
*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
 

Author Comment

by:lapucca
ID: 22997280
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Screencast - Getting to Know the Pipeline
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question