.net transactions

[mehal.ua]

Hi, I'm tyring to use .net transaction, I have code like this :
SqlConnection connection = Company.Instance.NewConnection();
using (connection) {
SqlTransaction transaction = connection.BeginTransaction();
try {
string stringSQL = GetSQLString();
SqlCommand command = new SqlCommand(stringSQL, connection,
transaction);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception) {
transaction.Rollback();
throw;
}
}
How I understand when stringSQL is invalid transaction will rollback
automatically, am I right ? Than, when we going to catch, RollBack()
will thorow an error, am I right ?

 

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

This is a simple enough code piece to try out for yourself to determine
whether or not it will happen. I'm not sure that Rollback will throw if the
transaction is already aborted. However, if it does, you can easily wrap
that call in a try/catch block.

However, if you are using .NET 2.0, you might want to consider using the
System.Transactions namespace. Transaction management can be a tricky thing
to do if you have multiple objects/multiple resources, and it's better to
let this stuff run in the context of a transaction manager. If you aren't
using .NET 2.0, I would seriously consider using COM+ to provide this for
you.

Hope this helps.

 

[Jon Skeet [C# MVP]]

Hi, I'm tyring to use .net transaction, I have code like this :
SqlConnection connection = Company.Instance.NewConnection();
using (connection) {
SqlTransaction transaction = connection.BeginTransaction();
try {
string stringSQL = GetSQLString();
SqlCommand command = new SqlCommand(stringSQL, connection,
transaction);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception) {
transaction.Rollback();
throw;
}
}
How I understand when stringSQL is invalid transaction will rollback
automatically, am I right ? Than, when we going to catch, RollBack()
will thorow an error, am I right ?

A better way of working, IMO, is to do:

using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlCommand command = new SqlCommand(stringSQL, connection,
transaction))
{
command.ExecuteNonQuery();
}
transaction.Commit();
}

The transaction will automatically roll back if it is disposed without
being committed.

 

[Wiebe Tijsma]

A better way of working, IMO, is to do:

using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlCommand command = new SqlCommand(stringSQL, connection,
transaction))
{
command.ExecuteNonQuery();
}
transaction.Commit();
}

The transaction will automatically roll back if it is disposed without
being committed.

Didn't know that... Really elegant way. Bless Anders for "using()" ;-)