SqlConnection, SqlTransaction with using, dispose and close

  • Thread starter Thread starter Steven Livingstone
  • Start date Start date
S

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();
}
}
}
 
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();
}
}
}
 
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
 
Back
Top