You use Microsoft Visual Studio 2010 and Microsoft .NET Framework 4 to create an application. The
application connects to a Microsoft SQL Server database. You write the following code segment that
executes two commands against the database within a transaction. (Line numbers are included for
reference only.)
01 using (SqlConnection connection = new SqlConnection(cnnStr)) {
02 connection.Open();
03 SqlTransaction sqlTran = connection.BeginTransaction();
04 SqlCommand command = connection.CreateCommand();
05 command.Transaction = sqlTran;
06 try {
07 command.CommandText = “INSERT INTO Production.ScrapReason(Name) VALUES(‘Wrong
size’)”;
08 command.ExecuteNonQuery();
09 command.CommandText = “INSERT INTO Production.ScrapReason(Name) VALUES(‘Wrong
color’)”;
10 command.ExecuteNonQuery();
11
12 }
You need to log error information if the transaction fails to commit or roll back. Which code segment
should you insert at line 11?
A.
sqlTran.Commit();
}
catch (Exception ex) {
sqlTran.Rollback();
Trace.WriteLine(ex.Message);
}
B.
sqlTran.Commit();
}
catch (Exception ex) {
Trace.WriteLine(ex.Message);
try {
sqlTran.Rollback();
}
catch (Exception exRollback) {
Trace.WriteLine(exRollback.Message);
}}
C.
catch (Exception ex){
Trace.WriteLine(ex.Message);
try{
sqlTran.Rollback();
}
catch (Exception exRollback){
Trace.WriteLine(exRollback.Message);
}}
finaly {
sqltran.commit( );
}
D.
catch (Exception ex) {
sqlTran.Rollback();
Trace.WriteLine(ex.Message);
}
finaly {
try {
sqltran.commit( );
}
catch (Exception exRollback) {
Trace.WriteLine(excommit.Message);
}}
Explanation:
A would work, but B is better since we are checking for possible errors during the rollback. C & D
would try to do a rollback before a commit?
The finally block is only executed when the application ends, which may not be the appropriate place
to put this logic. Normally, a finally block would contain code to close a Database connection. The
finally block executes even if an exception arises.