What effects does it have to opena DB conn outside a "using" block?

Question

I tried to search for this on SO but couldn't find it. Maybe I didn't search properly and it is a duplicate of something.

But I would like to ask a question: what is the difference between opening a DB connection inside a using(...) block and outside.

For clarify what I mean, look at the code snippets below.

Snippet where we open a DB connection outside "using" block:

if (_dbConn.State != ConnectionState.Open)
            _dbConn.Open();

        using (var oraclePackage = new OraclePackage())
        { // some DB function here... }

Snippet in which a DB connection is opened inside a "using" block:

using (var oraclePackage = new OraclePackage())
        {
            if (_dbConn.State != ConnectionState.Open)
                _dbConn.Open();

            // some DB functions here 
        }

Would the block where I DO NOT open a connection inside "using" still close it in case of an exception or would it be left open?

possible duplicate of [What is the C# Using block and why should I use it?](http://stackoverflow.com/questions/212198/what-is-the-c-sharp-using-block-and-why-should-i-use-it) — Sinatr, Jul 02 '15 at 09:00
@Sinatr - it doesnt look like duplicate as here question is in what stage connection object reamin if using not applied on connection object and exception raised in code after opening connection — Pranay Rana, Jul 02 '15 at 09:03
@Sinatr - the question you have linked explains what using does. But my question is more of a type where I have a scenario and as Pranay said, I want to know what state the conn object will be in in two scenarios. — Syed, Jul 02 '15 at 09:21
Linked question explains about `IDisposable`, I don't know about oracle, but when I work with databases (localdb, mdb), then I open connection, do query and close connection **as soon as I don't need it**. Pattern with `using` perfectly does that. — Sinatr, Jul 02 '15 at 10:35

score 1 · Answer 1 · answered Jul 02 '15 at 08:58

As you are not applying using on connection object the connection will remain open if exception occurs...so you need finally block which close your connect even there is exception.

so your code will be , as you are not applying using on connection object

try
{
  using (var oraclePackage = new OraclePackage())
    {
        if (_dbConn.State != ConnectionState.Open)
            _dbConn.Open();

        // some DB functions here 
    }
}
finally
{  _dbConn.Close(); }

score 1 · Accepted Answer · answered Jul 02 '15 at 09:05

Would the block where I DO NOT open a connection inside "using" still close it in case of an exception or would it be left open?

In both of those examples, the connection would still be open, until perhaps a finaliser ran, if one ever did. Neither has a using that is "using" dbConn.

The code:

using(someObject)
{
  // More code here.
}

Is equivalent to:

try
{
  // More code here.
}
finally
{
  if(someObject != null)
    ((IDisposable)someObject).Dispose();
}

And the code:

using(var someObject = new SomeClass())
{
  // More code here.
}

Is equivalent to:

var someObject = new SomeClass();
try
{
  // More code here.
}
finally
{
  if(someObject != null)
    ((IDisposable)someObject).Dispose();
}

(Null checks might be optimised out in cases where the object clearly cannot be set to null at any point).

Your code does this with oraclePackage so oraclePackage will be guaranteed to have Dispose() called on it, whether the block of code is left due to an exception or not. You code does not do this with dbConn.

As such, if you don't explicitly call dbConn.Dispose() or dbConn.Close() (which dbConn.Dispose() almost certainly calls into) the connection will not be closed.

If there are no remaining GC roots that can access dbConn then it's likely that eventually the internal object that represents the actual open connection will be finalised, which will lead to the connection being closed, but this is not guaranteed to happen or to happen in a timely manner, and it is quite likely not to work as well with the internal caching mechanism most IDbConnection implementations use.

Ok. Even if I do like this "oraclePackage.Connection = _dbConn;" after the line "if (_dbConn.State != ConnectionState.Open) _dbConn.Open();" in my second snippet?? — Syed, Jul 02 '15 at 09:26
Does `OraclePackage` call `Close()` on its `Connection` when it is disposed? — Jon Hanna, Jul 02 '15 at 09:31
No it doesn't. Then I know it doesn't dispose it. So it would still be left open. — Syed, Jul 02 '15 at 09:37
Yep. You want to have something that closes it, the most obvious way being to put the `dbConn` itself into a `using`. — Jon Hanna, Jul 02 '15 at 09:40

score 0 · Answer 3 · answered Jul 02 '15 at 08:58

Using statement does not handle any exceptions. It is responsible for calling IDisposable on current object (DB connection). This means that no matter if you open connection inside or outside the using block, at the end of the block the connection will be disposed

What effects does it have to opena DB conn outside a "using" block?

3 Answers3