Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
1.50/5 (2 votes)
See more:
Can anybody tell me the difference between below two option for using sqlconnection

and which i should we prefer and why

option 1:

C#
using (SqlConnection conn = new SqlConnection(connString))
{
//logic to retrive data
}


option 2 :

C#
SqlConnection conn = new SqlConnection(connString);

conn.open();

//logic to retrive data


many thanks..
Posted
Updated 10-Jun-12 3:25am
v2
Comments
VJ Reddy 11-Jun-12 1:05am    
Thank you for accepting the solution :-)

The 1st option calls the Dispose method of the object.


For more information you can see the MSDN topic about the using statement.

 
Share this answer
 
v2
Comments
Sandeep Mewara 10-Jun-12 11:30am    
My 5!
Shmuel Zang 11-Jun-12 2:49am    
Thanks.
VJ Reddy 10-Jun-12 12:20pm    
Good answer. 5!
Shmuel Zang 11-Jun-12 3:03am    
Thank you VJ.
Shahin Khorshidnia 10-Jun-12 12:25pm    
Yeah! Good point. +5
The Solution 1 and Solution 3 are good.

I want to add that the using block will be expanded by the compiler to try...finally... block with the creation of a new instance just before try and calling Dispose method in the finally block.

It is nicely explained in this CodeProject article.
Understanding the 'using' statement in C#[^].

I think it may also be helpful
 
Share this answer
 
v2
Comments
Shahin Khorshidnia 10-Jun-12 12:22pm    
Good answer and article.
VJ Reddy 10-Jun-12 12:35pm    
Thank you, Shahin :)
Manas Bhardwaj 10-Jun-12 14:25pm    
Correct +5
VJ Reddy 10-Jun-12 19:37pm    
Thank you, Manas :)
Maciej Los 10-Jun-12 17:20pm    
Good answer, my 5!
In the second one you need to close the connection in the finally block:

C#
SqlConnection conn = new SqlConnection(connString);
 
conn.open();

try
{
   //some code
}
catch(Exception ex)
{
   //Some code
}
finally
{
   conn.Close();
}


But in the first one you don't need to close the connection.
And also it's more clear than the 2st.

And look at this: SQL Server Connection Pooling (ADO.NET)
 
Share this answer
 
v2
Comments
VJ Reddy 10-Jun-12 12:20pm    
Good answer. 5!
Shahin Khorshidnia 10-Jun-12 12:23pm    
Thank you VJ :)
Manas Bhardwaj 10-Jun-12 14:25pm    
Correct +5
Shahin Khorshidnia 11-Jun-12 0:07am    
Thanks you Manas
Maciej Los 10-Jun-12 17:20pm    
Good answer, my 5!
The first one requires that class, instance of which is being created, derives from IDispose, so that when the last scope bracket is closed, it could explicitly call method Dispose() to destroy the object immediately.

In the second example you just give the object to the garbage collector in the standard way, and it will be collected when the time comes.

This difference is often needed when we need to do something immediately after using the object, like to generate a closing tag in HTML, for instance.
 
Share this answer
 
Comments
Sergey Alexandrovich Kryukov 10-Jun-12 23:54pm    
Not accurate -- I voted 3. IDisposable.Dispose has nothing to do with destruction of the object. This is an interface method which can do anything. It is often used to reclaim unmanaged resources, but it cannot be anything else. Not clear what do you mean "when times comes". The key to garbage collection is reachability.
--SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900