SqlConnection.Close() inside using statement
Solution 1
No need to Close or Dispose
the using
block will take care of that for you.
As stated from MSDN:
The following example creates a SqlConnection, opens it, displays some of its properties. The connection is automatically closed at the end of the using block.
private static void OpenSqlConnection(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
Console.WriteLine("State: {0}", connection.State);
}
}
Solution 2
The using statement ensures that Dispose is called
even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block
; in fact, this is how the using statement is translated by the compiler. MSDN
So ultimately your code line
using (sqlConnection = new SqlConnection(sqlConnectionString_WORK))
will be converted into a normal try finally block
by compiler calling IDisposable object in the finally
Solution 3
According to MSDN documentation for the Close
method:
you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent.
Therefore, calling Dispose
(implicitly so, even, using using
) will cover your bases, as it were.
It's worth noting, too, I think,though not specific to your case, that Close
will always effectively be called when the thing is wrapped in a using
statement - which might not be the case should it be omitted and an exception occur without the proper try
/catch
/finally
handling.
Solution 4
Is it wrong if I don't add sqlConnection.Close(); before disposing it
No, it is not as long as you are using your connection within Using
. When you will leave the using scope, Dispose
will be called for sql connection. which will close the existing connection and free-up all the resources as well.
Solution 5
The using statement is a try finally block and in your case the final block would have a connection.Dispose()
call. So you don't really need a independent connection.Close()
statement there.
The advantage is that this ensures the disposal even in case of an exception since the finally block will always run.
try
{
sqlConnection.Open();
// ....
}
finally
{
if(sqlConnection != null)
sqlConnection.Dispose();
}
Related videos on Youtube
Etrit
Currently working as a Junior Programmer at Asseco-SEE Kosova.
Updated on July 09, 2022Comments
-
Etrit almost 2 years
I'm using this code:
public void InsertMember(Member member) { string INSERT = "INSERT INTO Members (Name, Surname, EntryDate) VALUES (@Name, @Surname, @EntryDate)"; using (sqlConnection = new SqlConnection(sqlConnectionString_WORK)) { sqlConnection.Open(); using (SqlCommand sqlCommand = new SqlCommand(INSERT, sqlConnection)) { sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar).Value = member.Name; sqlCommand.Parameters.Add("@Surname", SqlDbType.VarChar).Value = member.Surname; sqlCommand.Parameters.Add("@EntryDate", SqlDbType.Date).Value = member.EntryDate; sqlCommand.ExecuteNonQuery(); } } }
Is it wrong if I don't add
sqlConnection.Close();
before disposing it? I mean. It's not showing any errors, no problems at all. Is it better to Close it first? If yes, why?-
V4Vendetta over 10 yearsThe using statement will
Dispose
the connection even in case of an exception, so you don't really need aClose
call there
-
-
Matten over 10 yearsIt's about
Close()
, notDispose()
:) -
Darren over 10 yearsIn the example it states: The following example creates a SqlConnection, opens it, displays some of its properties. The connection is automatically closed at the end of the using block.
-
Grant Thomas over 10 years@DarrenDavies Precisely. It's more explicit on the details even further up than that.
-
variable over 9 yearsIs it OK if the code has a connection.Close(); just before your second last curly brace '}'? Will this throw an exception when the using block tries to close a connection that is already closed by code?