SQL Server Is SqlConnection.Close() need inside a Using statement?

ykejflvf  于 2023-06-28  发布在  其他
关注(0)|答案(7)|浏览(136)

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();
        }
    }
}

It's not showing any errors or problems at all. Is it wrong if I don't add sqlConnection.Close(); before disposing of it? If yes, why?

vkc1a9a2

vkc1a9a21#

There is no need to Close (or Dispose ) here; the using block will take care of that for you.

As stated from Microsoft Learn :
The following example creates a SqlConnection , opens it, [and] 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);
    } 
}
aydmsdu9

aydmsdu92#

According to the Microsoft Learn 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.

brvekthn

brvekthn3#

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.

yftpprvb

yftpprvb4#

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.

oo7oh9g9

oo7oh9g95#

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();
}
brvekthn

brvekthn6#

You are using a Using which will Dispose() the object for you.

If you take the connection outside of the Using statement, then yes - you would need to close the connection when finished.

hs1ihplo

hs1ihplo7#

No, it is not wrong. The sqlConnection will close the connection after it will pass using block and call Dispose method. SqlConnection.Dispose() equal to SqlConnection.Close() method.

From MSDN: If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent.

相关问题