ADO.NET rollbacks

[Greg]

If the transaction object is created using a finally statement, will
the transaction automatically be rolled back if an error is thrown, or
do i need to explicitly code the rollback. The code that I am thinking
about using (without an explicit rollback) is below:

Greg.
^^^^^^^^^^^^^^^^^^^^^^^^^BEGIN CODE
SNIP^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^
using (SqlConnection conn = new SqlConnection(_connectionString))
{

conn.Open();
using (SqlTransaction transaction =
conn.BeginTransaction(IsolationLevel.Snapshot))
{
//Delete existing rows
using (SqlCommand cmdDelete = new SqlCommand())
{
cmdDelete.Transaction = transaction;
cmdDelete.Connection = conn;
cmdDelete.CommandType =
CommandType.StoredProcedure;
cmdDelete.CommandText = "sp_deletedstdate";
cmdDelete.Parameters.AddWithValue("@timeZone",
Int32.Parse(uxTimeZones.SelectedValue));

cmdDelete.ExecuteNonQuery();
throw new Exception();
}

//Insert rows from grid
using (SqlCommand cmdInsert = new SqlCommand())
{
cmdInsert.Connection = conn;
cmdInsert.Transaction = transaction;
cmdInsert.CommandType =
CommandType.StoredProcedure;
cmdInsert.CommandText = "sp_insertdstdate";
foreach (GridViewRow row in uxDstGrid.Rows)
{
DateTime from =
((BasicFrame.WebControls.BDPLite)row.FindControl(" uxDstStart")).SelectedDate;
DateTime to =
((BasicFrame.WebControls.BDPLite)row.FindControl(" uxDstEnd")).SelectedDate;
int offSet =
Int32.Parse(((DropDownList)row.FindControl("uxDstO ffset")).SelectedValue);
cmdInsert.Parameters.Clear();
cmdInsert.Parameters.AddWithValue("@timeZone",
Int32.Parse(uxTimeZones.SelectedValue));
cmdInsert.Parameters.AddWithValue("@start",
from);
cmdInsert.Parameters.AddWithValue("@end", to);
cmdInsert.Parameters.AddWithValue("@offset",
offSet);

cmdInsert.ExecuteNonQuery();
}
}
//No errors, so commit
transaction.Commit();
}
}
^^^^^^^^^^^^^^^^^^^^^^^^^END CODE
SNIP^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^

[Nicholas Paldino [.NET/C# MVP]]

Greg,

There is nothing here in a finally statement (although semantically,
there is, given that the using statements compile to try/finally blocks).

In this case though, if any exception is thrown, your transaction will
not commit.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Greg" <sp**********@yahoo.co.ukwrote in message
news:11**********************@57g2000hsv.googlegro ups.com...

If the transaction object is created using a finally statement, will
the transaction automatically be rolled back if an error is thrown, or
do i need to explicitly code the rollback. The code that I am thinking
about using (without an explicit rollback) is below:

Greg.
^^^^^^^^^^^^^^^^^^^^^^^^^BEGIN CODE
SNIP^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^
using (SqlConnection conn = new SqlConnection(_connectionString))
{

conn.Open();
using (SqlTransaction transaction =
conn.BeginTransaction(IsolationLevel.Snapshot))
{
//Delete existing rows
using (SqlCommand cmdDelete = new SqlCommand())
{
cmdDelete.Transaction = transaction;
cmdDelete.Connection = conn;
cmdDelete.CommandType =
CommandType.StoredProcedure;
cmdDelete.CommandText = "sp_deletedstdate";
cmdDelete.Parameters.AddWithValue("@timeZone",
Int32.Parse(uxTimeZones.SelectedValue));

cmdDelete.ExecuteNonQuery();
throw new Exception();
}

//Insert rows from grid
using (SqlCommand cmdInsert = new SqlCommand())
{
cmdInsert.Connection = conn;
cmdInsert.Transaction = transaction;
cmdInsert.CommandType =
CommandType.StoredProcedure;
cmdInsert.CommandText = "sp_insertdstdate";
foreach (GridViewRow row in uxDstGrid.Rows)
{
DateTime from =
((BasicFrame.WebControls.BDPLite)row.FindControl(" uxDstStart")).SelectedDate;
DateTime to =
((BasicFrame.WebControls.BDPLite)row.FindControl(" uxDstEnd")).SelectedDate;
int offSet =
Int32.Parse(((DropDownList)row.FindControl("uxDstO ffset")).SelectedValue);
cmdInsert.Parameters.Clear();
cmdInsert.Parameters.AddWithValue("@timeZone",
Int32.Parse(uxTimeZones.SelectedValue));
cmdInsert.Parameters.AddWithValue("@start",
from);
cmdInsert.Parameters.AddWithValue("@end", to);
cmdInsert.Parameters.AddWithValue("@offset",
offSet);

cmdInsert.ExecuteNonQuery();
}
}
//No errors, so commit
transaction.Commit();
}
}
^^^^^^^^^^^^^^^^^^^^^^^^^END CODE
SNIP^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^

[Greg]

That's great, thanks a lot Nicholas.