Transaction behavior when connection is lost

[Guest]

How do I ensure that all database operations are rolled back when database
connectivity is lost during the middle of a transaction?

using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
Insert1
Insert2
//// connection is lost here /////
Insert3
transaction.Commit();
}
catch
{
//Roll back the transaction.
transaction.Rollback();
}
}

Is this set at the database and/or is it different for Sql Server vs Oracle?

 

[marc.l.caron]

That should be a property of the connection to Commit or Rollback on
Disconnect. I believe (could be wrong here) that all major RDBMS's
have some property that can be set to indicate transaction behavior on
disconnect. May or may not be implemented in the .NET wrapper class
though.

 

[Mary Chipman [MSFT]]

If the connection is dropped, then SQL Server will roll back any
uncommitted transactions. Best practice is to implement explicit
transactions in stored procedures in T-SQL, not in client code. That
gives you more control and boosts performance.

-Mary