SqlConnection, SqlTransaction with using, dispose and close

[Steven Livingstone]

After looking at a number of posting (many with close v dispose etc), I am
interested in whether the following code is the best way to ensure your
transaction behaves as expected and your connection gets closed as well as
connection and transaction both disposed.

using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlTransaction trans = conn.BeginTransaction())
{

try
{
Update1();
Update2();

trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
conn.Close();
}
}
}

 

[Steven Livingstone]

From a post below, i guess this should be improved to :

using (SqlConnection conn = new SqlConnection(connectionstring))
{
using (SqlTransaction trans = conn.BeginTransaction())
{

try
{
Update1();
Update2();

trans.Commit();
}
catch (Exception ex)
{
try {
trans.Rollback();
}
catch (Exception e) {
//,,,
}
}
finally
{
conn.Close();
}
}
}

 

[William Ryan]

The method you definitely gets the job done. The only
thing I'd mention is that you will only rollback the
transaction based on the code in the two posts. You may
want to rethrow the exception so that the caller's handler
takes care of it, b/c if it fails, you probably want some
notification that it failed so you can respond
accordingly...but that's more of any exception handling
issue..as far as the transaction/ADO.NET side goes, looks
good.

Cheers,

Bill

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com

 

[Steven Livingstone]

Thanks Bill - and good luck with the knowdotnet site!

Steven
http://www.stevenlivingstone.com
http://www.venturetogether.org