Using?

[tshad]

Trying to figure out why to use "using".

I am at a loss to see why this:

using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
// BeginTransaction() Requires Open Connection
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}

is better than

SqlConnection connection =
new SqlConnection(connectionString)
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
// BeginTransaction() Requires Open Connection
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}

What does the "using" statement do for you?

Thanks,

Tom

 

[Jon Skeet [C# MVP]]

What does the "using" statement do for you?

It means you can actually get rid of your call to connection.Close()
and the finally block in your first piece of code.

I would personally use "using" statements for the SqlCommand and the
SqlTransaction, however. SqlTransaction will abort if you call Dispose
without committing, so you don't need your catch block either. In other
words, the whole code can become:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();

using (SqlTransaction transaction = connection.BeginTransaction())
{
transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
}

Note that your original code would have thrown a NullReferenceException
had you been unable to open the database connection or begin the
transaction.

 

[Peter Duniho]

Trying to figure out why to use "using".

[...]
What does the "using" statement do for you?

Well, in the first example you posted, you don't actually need the
"finally" clause. So the "using" statement actually allows you to leave
it out.

Also, if CreateCommand() throws an exception (out-of-memory, for example)
you'll catch it in the "using" example but not the other (same thing is
true for any code you put after the creation of the SqlConnection but
before the try/catch block.

Another benefit is that no matter what happens to the "connection"
variable, the original SqlConnection will always be closed (i.e. if you
overwrite the "connection" variable with a new value, even null, "using"
will still do the right thing with the originally provided instance).

Basically, "using" provides a clean, reliable, robust way to deal with
disposing things that need to be disposed. You can implement the
functionality other ways, but as is typically the case when you've got a
language- or framework-provided mechanism for doing something, your code
is almost always easier to guarantee to be correct if you use the built-in
mechanism rather than coding it yourself.

Pete

 

[Fredo]

A: It saves you a few lines of code.
B: It calls Dispose(). In the case of a SqlConnection object, Dipose() calls
Close(), but it also performs some other cleanup. I don't know that you'd
necessarily see leaks, but best to always call Dispose() on objects that
implement IDisposable().

In your first example, your call to connection.Close() isn't necessary
because the Dispose() of the connection object will do that for you. In your
second example, you're not disposing of a disposable object. Tsk, tsk...

Generally speaking, "using" is good when you have a large block of code with
mutliple exit points. That is, let's say you have multiple return statements
in the block. You could handle it with a try/finally and manually calling
Dispose(), but it's just easier to use the "using" statement. With "using",
you don't need to worry about handling the disposal. You know that when
execution leaves the block that your object will get disposed.

Generally speaking, I try to use the "using" statement any time I use a
disposable object whose scope is entirely within a method (obviously, since
it calls Dispose() when you exit the block scope and thus the object won't
survive a method exit if it's declared in a "using" statement). It basically
keeps me from having to worry about return statements and calling Dispose().
It's particularly useful in terms of code maintenance since it's quite easy
for someone to come in later and add a new "return" statement somewhere in
the code and forget to clean up the disposable object. If you have a "using"
statement, that won't cause any problems.

 

[tshad]

It means you can actually get rid of your call to connection.Close()
and the finally block in your first piece of code.

I would personally use "using" statements for the SqlCommand and the
SqlTransaction, however. SqlTransaction will abort if you call Dispose
without committing, so you don't need your catch block either. In other
words, the whole code can become:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();

using (SqlTransaction transaction = connection.BeginTransaction())
{
transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
}

Note that your original code would have thrown a NullReferenceException
had you been unable to open the database connection or begin the
transaction.

This was just an example. But in my actual code, I also want to log the
error and send an error message back to my user (this is going to be a
Windows Service). I don't see where in you setup I can do that without the
try/catch.

Also, in my actual code I also want to use the same connection to run
multiple commands. So using your example, how would I deal with the
SqlCommand.

I would probably do something like:

string errorMessage = "";

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
cmd = new SqlCommand("proc" + ptn.TableName + "_insert",
conn);
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

return errorMessage;

I assume, as you said, I don't need the finally clause to close connection.

You said the Dspose would abort the tansaction? Even if I don't actually
call Dispose()?

By abort the transaction I assume that you mean rollback?

Why would that be? Would it be do to closing the connection with a commit
that Sql Server would decide to roll it back?

I assume that the Using statement would close the statement at the end of
the Using clause.

Not the end of the "using" clause?

Thanks,

Tom

 

[Fredo]

This was just an example. But in my actual code, I also want to log the
error and send an error message back to my user (this is going to be a
Windows Service). I don't see where in you setup I can do that without
the try/catch.

In that case, you would need try/catch

Also, in my actual code I also want to use the same connection to run
multiple commands. So using your example, how would I deal with the
SqlCommand.

I would probably do something like:

string errorMessage = "";

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
cmd = new SqlCommand("proc" + ptn.TableName + "_insert",
conn);

change that to:

using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn);

cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

return errorMessage;

I assume, as you said, I don't need the finally clause to close
connection.

You said the Dspose would abort the tansaction? Even if I don't actually
call Dispose()?

"using" implicitly calls dispose when the object defined in the using
statement goes out of scope.

So, if I do something like this:

using (MyObject myOb = new MyObject())
{
... do something here ...
}
myOb.Dispose()

The myOb.Dispose() call would fail. First of all, myOb is out of scope.
Second of all, myOb was disposed when the closing brace "}" was executed.

By abort the transaction I assume that you mean rollback?

Correct.

Why would that be? Would it be do to closing the connection with a commit
that Sql Server would decide to roll it back?

It's simply the way SqlTransaction.Dispose() works. If the transaction is
disposed without your code excplicitly calling Commit(), it will rollback,
on the assumption that something failed. This makes perfect sense in a using
block since, if you get an unhandled exception during execution, the
exception will take you outside of the using block, thus calling Dispose()
on the transaction and properly rolling it back instead of committing it.

I assume that the Using statement would close the statement at the end of
the Using clause.

Not the end of the "using" clause?

Not sure what you mean here. It's kind of like this:

using (MyObject myOb = new MyObject())
{
... do something here ...
} <--- Dispose() called here, more or less

Thanks,

Tom

 

[Fredo]

In the part where I wrote:

using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn);

what I really meant was:

using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
}

Sorry, just wanted to clarify.

 

[Jon Skeet [C# MVP]]

This was just an example. But in my actual code, I also want to log the
error and send an error message back to my user (this is going to be a
Windows Service). I don't see where in you setup I can do that without the
try/catch.

<snip>

Fredo has responded to the other points.

I suspect it's unlikely that this code appears in the top level of your
service. You're likely to need to catch and log errors which occur
elsewhere, right?

Just catch them near the top of your call stack, and log them there,
rather than logging them in the data access layer. The same information
will still be available, and it means having far fewer catch blocks
which do very little.

 

[tshad]

<snip>

Fredo has responded to the other points.

I suspect it's unlikely that this code appears in the top level of your
service. You're likely to need to catch and log errors which occur
elsewhere, right?

Just catch them near the top of your call stack, and log them there,
rather than logging them in the data access layer. The same information
will still be available, and it means having far fewer catch blocks
which do very little.

Makes sense.

Thanks,

Tom

 

[tshad]

In the part where I wrote:

using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn);

what I really meant was:

using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
}

Sorry, just wanted to clarify.

So then I would have:

string errorMessage = "";

using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
foreach(...)
{
using (SqlCommand cmd = new SqlCommand("proc" + ptn.TableName +
"_insert", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
...
dbReader = cmd.ExecuteReader();
...
}
}
}
catch(Exception exc)
{
errorMessage = exc.Message;
}
}

return errorMessage;


So at then end of the "using" block of code for the SqlCommand - dispose is
called and cmd is closed and in the using block for SqlConnection - the
SqlConnection is closed at the end.

Thanks,

Tom