DRAG DROP
You have an application that accesses a Microsoft SQL Server database.
The database contains a stored procedure named Proc1. Procl accesses several rows of
data across multiple tables.
You need to ensure that after Proc1 executes, the database is left in a consistent state.
While Proc1 executes, no other operation can modify data already read or changed by
Proc1. (Develop the solution by selecting and ordering the required code snippets.
You may not need all of the code snippets.)
Answer: See the explanation.
Explanation:
Box 1:Box 2:
Box 3:
Box 4: transaction.Commit();
Box 5:Box 6: transaction.Rollback();
Box 7: } finally {
Box 8:Note:
* Box 1: Start with the sqlconnection
* Box 2: Open the SQL transaction (RepeatableRead)
/ IsolationLevel
Specifies the isolation level of a transaction.
/ RepeatableRead
Volatile data can be read but not modified during the transaction. New data can be added
during the transaction.
/ ReadCommitted
Volatile data cannot be read during the transaction, but can be modified.
/ ReadUncommitted
Volatile data can be read and modified during the transaction.
Box 3: Try the query
Box 4: commit the transaction
Box 5: Catch the exception (a failed transaction)
Box 6: Rollback the transaction
Box 7: Final cleanup
Box 8: Clean up (close command and connection).
Reference: SqlConnection.BeginTransaction Method
Incorrect:
The transaction is not set up by transactionscope here. Begintransaction is used.
There is a problem here. Box2 throws exception since Connection was not opened and thus in the Closed state.
An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll
Additional information: Invalid operation. The connection is closed.
7,1,5,10,8,9,3,4
Looks like the correct answer to me. Just to add a couple of ref:
https://msdn.microsoft.com/en-gb/library/ms173763.aspx
https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx
http://stackoverflow.com/questions/224689/transactions-in-net
Connection must be opened before it`s invoked BeginTransaction() method . The answer is not right.
I agree with Elvis. Few errors in this code:
– the connection must be opened before the BeginTransaction() (otherwise you got the same exception as al adeeb);
– the command must take the transaction as a parameters (otherwise you got a exception saying that the Transaction property of the command has not been initialized).
Here’s an example of a code that works with one of my database; you can adapt it to your need (just change the connection string and the command to execute).
var connectionStringManual = @”data source=(localdb)\MSSQLLocalDB;initial catalog=SchoolDB;integrated security=True;MultipleActiveResultSets=True;”;
SqlConnection connection = new SqlConnection(connectionStringManual);
SqlTransaction transaction = null;
SqlCommand command = null;
try
{
connection.Open();
transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
command = new SqlCommand(“UPDATE dbo.Student SET LastName = ‘Liiu’ WHERE LastName = ‘Liu'”, connection, transaction);
var rowsAffected = command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction?.Rollback();
}
finally
{
command?.Dispose();
connection.Dispose();
}
And to do things properly, this code should be used within “using” blocks:
using (var connectionUsing = new SqlConnection(connectionStringManual))
{
connectionUsing.Open();
using (var transactionUsing = connectionUsing.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
using (var commandUsing = new SqlCommand(“UPDATE dbo.Student SET LastName = ‘Liu’ WHERE LastName = ‘Liiu'”, connectionUsing, transactionUsing))
{
var rowsAffected = commandUsing.ExecuteNonQuery();
transactionUsing.Commit();
}
}
-> rollbacks are automatically handled. We just have to manually commit.