Multiple or single 'using' statements?

Question

I was wondering if the below way of handling multiple sql's within the same using statement is correct, I have tested this in a project and it works but would it not be better to wrap each sql in it's own using statement? Which option is a better coding practice?

Using cmd As New SqlCommand()

    cmd.Connection = conn
    cmd.CommandType = CommandType.Text

    sql = "Select * From blah"
    cmd.CommandText = sql
    theValue = cmd.ExecuteScalar()

    sql = "Update tbl1 Set a = b"
    cmd.CommandText = sql
    cmd.ExecuteScalar()

    sql = "Update tbl2 Set x = y"
    cmd.CommandText = sql
    cmd.ExecuteNonQuery()

End Using

Thanks

You could just have a single SQL batch there. Not sure why you have 3 separate ones. — Oded, Aug 17 '12 at 15:52
This is just an example in order to convey my question better. — 03Usr, Aug 17 '12 at 15:53
And an incorrect example, calling ExecNonQuery on a SELECT statement. But it does not obscure the issue. — H H, Aug 17 '12 at 15:56
where? ExecuteNonQuery is called on an update statement in the above example. The Select one uses ExecuteScalar. — 03Usr, Aug 17 '12 at 17:29

H H · Accepted Answer · 2012-08-18T06:35:34.570

3

This is a bad practice and could potentially leak something.

I'm not even sure if Command.Close exists but you are missing:

sql = "Select * From blah"
cmd.CommandText = sql
theValue = cmd.ExecuteScalar()

cmd.Close()   ' you need something like this

sql = "Update tbl1 Set a = b"
cmd.CommandText = sql
cmd.ExecuteScalar()

The preferred way is of course not to reuse a Command oject. Create a new one for each action, and 1 Using per IDisposable object.

edited Aug 18 '12 at 06:35

answered Aug 17 '12 at 15:53

H H

263,252
30
330
514

Thanks for the response. But I thought if we use the using statement we did not need to bother with `cmd.Close()`, because a Using block guarantees the disposal of resources when the code is finished with them? – 03Usr Aug 17 '12 at 17:33
1

The Using only assures that the resources of the _last_ Execute are released. Earlier Executes may also result in SQL handles being held, they will be overwritten but may not be freed here. To be sure you'd have to reflector ExecuteXxx – H H Aug 17 '12 at 18:09

score 2 · Answer 2 · answered Aug 17 '12 at 15:52

Personally I would use a different SqlCommand object per execution.

With this simple script its easy to see what is happening, but if your scripts get more complicated and require Sql Parameters, you will have to remember to remove them after each execution (if they're not needed) otherwise an exception would be thrown.

Multiple or single 'using' statements?

2 Answers2