DRAG DROP
You have an application that accesses a Microsoft SQL Server database.
The database contains a stored procedure named Proc1. Proc1 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).SqlConnection.BeginTransaction Method
Incorrect:
The transaction is not set up by transactionscope here. Begintransaction is used.
correct
0
0
Next code can pass only,the answer is not correct because:
First reason: BeginTransaction() method must be invoked after the connection is opened,
or will be throwed the exception which is explained in the code .
Second reason: “SqlTransaction transaction” variable is not visibled in catch block, because
that variable is in try block , so transaction.Rollback() method can not be
applied in the catch block .
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Transactions;
using System.Data;
namespace TransactionApplication
{
//***********************For next example is required NORTHWIND database ************************************
class Program
{
static void Main(string[] args)
{
string connString = @”Data Source=(local)\sqlexpress; Initial Catalog=NORTHWND;Integrated Security=SSPI;”;
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(“select COUNT(*) from Categories”,connection);
try
{
//Connection must be opened before it is invoked BeginTransaction method , otherwise the next excpetion will be throwed:
// An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll
//Additional information: Invalid operation. The connection is closed.
connection.Open();
int numberOfRecords = (int)command.ExecuteScalar();
SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
transaction.Commit();
}
catch
{
// Here the transaction.Rollback() can not be applied, because “SqlTransaction transaction . . . .” statement is
// inside the try scope and it can not be visibled inside this catch scope.
}
finally
{
command.Dispose();
connection.Dispose();
}
Console.ReadLine();
}
}
}
0
0
I agree. See this example: https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx
0
0