When I have a return within the code of Using(connection) does the connection and db objects gets dismissed properly

Question

I'm not sure if my question is clear so here's a code sample:

    public static bool isRecordExist(int ID)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                int flag = int.Parse(command.ExecuteScalar);

                if (flag)
                    return false;
                else
                    return true;
            }
        }
    }

So, now I understand that I don't need to close or dismiss any Sql objects when I have the 'using' keyword, because it does that automatically as soon as you get our of it's brackets but now the we reach to the 'return' part. will it dismiss and close the objects properly or do I need to save this value and make my check and 'return' outside the 'using' code ?

Why not just `return !flag;` ? And even better, use int.TryParse() — Joel Coehoorn, Aug 18 '11 at 21:43
this is my Query and I couldn't let it False or True .. "IF EXISTS (SELECT ID FROM Cruises WHERE ID = @cruiseId) select 1 else select 0" — Mazen Elkashef, Aug 18 '11 at 21:54
@IKashef. If you want True/False change your query to "IF EXISTS (SELECT ID FROM Cruises WHERE ID = @cruiseId) select convert(bit,1) else select convert(bit,0)" — Carter Medlin, Aug 18 '11 at 21:59

score 4 · Answer 1 · edited May 23 '17 at 12:04

4

Yes it closes automatically. Exiting a using block calls .Dispose() on the object in question which for a SqlConnection will close the connection and any open resources.

Does End Using close an open SQL Connection

edited May 23 '17 at 12:04

Community

1
1

answered Aug 18 '11 at 21:38

Carter Medlin

11,857
5
62
68

I'm not sure what you're trying to say .. do you mean that disregarding the return. anytime I have using() I need to close the connection ?!! – Mazen Elkashef Aug 18 '11 at 21:41
@Ikashef no, he said the opposite of that – Joel Coehoorn Aug 18 '11 at 21:44
1

@lkashef The using statements in your code above will dispose of your SQL objects. The usings are still closed even after the return is hit. – Dylan Hayes Aug 18 '11 at 21:52
yh sorry I didn't get that you're confirming what I was saying. but I just wanted to know, as return gets me out of loops, function. I though it might get me out of the using() and the connection ends up open! .. but you guys confirmed that it will still close thanks. +1 – Mazen Elkashef Aug 18 '11 at 21:56

score 2 · Accepted Answer · answered Aug 18 '11 at 21:45

2

Yes, the connection will be closed.

Note that this can cause problems. Say, for example, that you want to return a DataReader from the function. As soon as you return the datareader, the connection that your reader depends on is closed by the using block and you can't read any records. In those situations, I use either a delegate or an iterator block to get around the problem, depending on what I'm doing.

answered Aug 18 '11 at 21:45

Joel Coehoorn

399,467
113
570
794

Oh okay :D .. I understand what you're saying, but If my method is just checking whether this record exists or not, I mean there is no more db work will depend on the result, then I'm on the safe side, right ? – Mazen Elkashef Aug 18 '11 at 21:52

score 2 · Answer 3 · answered Aug 18 '11 at 21:55

Yes, the object will be disposed properly. If you take a look at the IL generated from the method, you will see a try/finally block in the appropriate spots of your using { ... } statements. Exiting the method from any part inside of a using { ... } block will always follow the try/finally dispose pattern.

I also would recommend stacking your using statements like this:

using (SqlConnection connection = new SqlConnection(ConnectionString))
using (SqlCommand command = new SqlCommand(commandText, connection))
{
   //some work
}

It generally makes the code more readable, especially if you are using 4 or 5 of them.

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

Yes, because it is syntactic sugar for try/finally without the catch.

So when you do this:

using (SqlConnection connection = new SqlConnection ...) {
   code code code then return;}

You get (approximately) this behind the scenes:

SqlConnection connection; 
connection = null;
try
{
    connection = new SqlConnection ...;
    code code code then  return; 
}
finally
{
   if (connection != null) connection.dispose();
}

And by the definition of finally, it is always called, no matter how you break out of the try block (usually you think 'exception' but also including return).

When I have a return within the code of Using(connection) does the connection and db objects gets dismissed properly

4 Answers4

Yes, because it is syntactic sugar for try/finally without the catch.