In a "Using" Block Is a SQLconnection Closed on Return or Exception

in a using block is a SqlConnection closed on return or exception?

1. Yes
2. Yes.

Either way, when the using block is exited (either by successful completion or by error) it is closed.

Although I think it would be better to organize like this because it's a lot easier to see what is going to happen, even for the new maintenance programmer who will support it later:

using (SqlConnection connection = new SqlConnection(connectionString)) 
{    
    int employeeID = findEmployeeID();    
    try    
    {
        connection.Open();
        SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
        command.CommandTimeout = 5;

        command.ExecuteNonQuery();    
    } 
    catch (Exception) 
    { 
        /*Handle error*/ 
    }
}

When does SqlCommand close when used in another function?

A SqlConnection is closed when you call Dispose. Exiting the using block does that. The connection will be closed.

That's all there is to it. It will not magically stay open because ADO.NET does not know and cannot find out that you returned the connection object.

Move the connection out of the GetSqlCommand method. Pass the connection object into that method. GetSqlCommand has no business in creating a connection. It should take a connection, use it, but not close it.

Close SqlConnection in the Finally when using Using

using (var conn = new SqlConnection(_dbconnstr)) 
{
    //code
}

is expaded to:

SqlConnection conn = new SqlConnection(_dbconnstr);
try
{
    //code
}
finally
{
    conn.Dispose();
}

So you should handle errors but you can forget about closing connection.

VB.NET Does SqlConnection get closed automatically in a Try/Catch if exception is thrown?

No. That's why you'll want to declare the connection variable before the try/catch and add a finally to it to ensure you have a place where the connection can be closed and disposed:

 Dim con As New SqlClientConnection( . . .)

 Try
      ' DB Operations (create, read, update, delete) here
      con.open()

 Catch SqlClientException (ex)

     ' Deal with exception
 Finally
      ' Code here will always run, whether or not the try code succeeded
      ' or if you wound up in a catch block. You can safely call .close()
      ' or (as shown below) .dispose() because if the connection is already
      ' closed/disposed, the call doesn't do anything.


      ' Dispose is what you want - it will not only close the
      ' connection, but it will release the .NET reference to
      ' the remote resource, freeing up that resource (the database
      ' in this case) to serve other clients.

      con.Dispose()

 End Try

If I use SqlCommand in a method as a parameter, does it block a SqlConnection dispose?

I want to thank you all for your responses! After doing a lot of research and modifications, I implemented @Jack A's and @Jhon Busto's recomendations. But you where right John it didn't solve the connection pool problem, it turns out that the real problem was the timer, I didn't notice that it was constantly executing Example1 and Example2 but not every second, it was every 50ms or less, so I presume that it created a lot of connections in the pool. I was changing the Timer.Interval property of the timer but I didn't know this:

If Enabled and AutoReset are both set to false, and the timer has previously been enabled, setting the Interval property causes the Elapsed event to be raised once, as if the Enabled property had been set to true. To set the interval without raising the event, you can temporarily set the Enabled property to true, set the Interval property to the desired time interval, and then immediately set the Enabled property back to false.

Source: https://docs.microsoft.com/en-us/dotnet/api/system.timers.timer.interval?view=netframework-4.7.2

So, if I needed to change the Timer.Interval I followed Microsoft's documentation, I tested everything again and it worked. Be careful using Timers! hehe :)

Do I need to manually close a .NET SqlConnection, if it throws an exception inside a `using` statement?

No, it is still disposed if it is within the using

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.

Source : MSDN.

Leaving .net SqlConnection open indefinitely vs a using block

As others have mentioned, the connection may be being closed on the server side or by a idle timeout, check the server side logs for details.

For your 2nd part of your question, there are performance counters that tracks connection pool information. This can be viewed via code (see linked page for an example) setting the process id of your running program and displaying the value of things like NumberOfPooledConnections and NumberOfNonPooledConnections to see if the connection pool is actually being used by the process.

By turning on the off by default counters you could also monitor NumberOfFreeConnections which would tell you if there is a free open connection for your program to pick up and use to get that immediate response without the overhead of opening a connection you are asking for.

Do I need to close connection when using a 'using' statement

NO need as object will automatically disposed when we use using blocks. Go through this

http://msdn.microsoft.com/en-us/library/yh598w02.aspx

What is the C# Using block and why should I use it?

---

Related Topics