The underlying provider failed on Open Error in Entity Framework Transactions

Error:
The underlying provider failed on Open
MSDTC on server 'XXXXX\\SQLEXPRESS' is unavailable."   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)\r\n   at System.Data.EntityClient.EntityConnection.Open()\r\n   at System.Data.Objects.ObjectContext.EnsureConnection()\r\n   at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave)\r\n   at System.Data.Objects.ObjectContext.SaveChanges()\r\n  

Cause:
The error The underlying provider failed on Open is thrown while performing a transactional update using Entity Framework, if the MSDTC - Distributed Transaction Coordinator service is not started.

Resolution:

Transactions in Entity Framework with DbTransaction.

In many real time scenarios we will have to use transactions to perform Atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

In this post we shall see on how to perform transactions using Entity Framework and DbTransaction. Unlike the TransactionScope which needs the MSDTC - Distributed Transaction Coordinator service to be running, DbTransaction will work even if the service is not running.

In this post we will do 2 operations insert a row in the Bugs table and insert a row in the Comments table in a single transaction.

Transactions in Entity Framework with TranscationScope.

In many real time scenarios we will have to use transactions to perform Atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

In this post we shall see on how to perform transactions using Entity Framework and TransactionScope. To make use of the TransactionScope we need to make sure that the MSDTC - Distributed Transaction Coordinator service is running. If the service is not running then the code will throw the following error.
The underlying provider failed on Open

In this post we will do 2 operations update the Bugs table and insert a row in the Comments table in a single transaction.

TranscationScope Vs DbTransaction

A transaction is an atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

Transactions in the Entity Framework can be performed using 2 ways, using TranscationScope or by using DbTransaction. In this post we shall compare the 2 approaches.

Transactions in Entity Framework

A transaction is an atomic operation, i.e a set so statements should get executed completely or get rollback completely. A typical example of a transactions in the Bank account example, debit X amount from account A and credit the X amount to account B, now if there is an issue between debiting from account A and crediting to account B the whole operation should be cancelled.

Transactions in the Entity Framework can be performed using 2 ways, using TranscationScope or by using DbTransaction. In this post we shall compare the 2 approaches.

ADO.Net Transactions in Oracle Database

ADO.Net Transactions in Oracle Database

While performing database operations there are instances where we need process a set of statements in a single transaction. While connecting to Oracle database transactions can be handled in 2 ways.

      1. Handle transactions in the PL/SQL Package.
      2. Handle transactions using the Connection objects Transaction.

Here we shall see on how the 2^nd approach is used to handle transactions. To process a transaction with the connection object we need to follow the below steps.

1.    Open a connection to the target Oracle database.

2.    Call the BeginTransaction() method of the connection object, this method will return a transaction object, get the Transaction object and assign it to the Command Object.

3.    Now you can call the execute methods of the Command object.

a.    If there are no exceptions then call the Commit() method of the Transaction object

b.    If there are any exceptions while processing any of the commands call the Rollback() method, this will rollback all the statements which were executed since the connection was opened.

4.    Close the connection.

TThe following code executes 3 SQL statements in a single transaction.

OracleConnection objCon = null; 

OracleCommand objCom = null; 

OracleTransaction objTrans = null;

int intQueryOut;

try

{

    objCon = new OracleConnection(GetConnectionString());

    objCom = new OracleCommand(strQuery1, objCon);

    objCon.Open();

    objTrans = objCon.BeginTransaction();

    //

    objCom.Transaction = objTrans;

    objCom.ExecuteNonQuery();

//

objCom.CommandText = "strQuery2";
objCom.ExecuteNonQuery();

//

objCom.CommandText = "strQuery3";
objCom.ExecuteNonQuery();

//
objTrans.Commit();

}

catch

{

    objTrans.Rollback();

    throw;

}

finally

{

    objCon.Close();

}

In the above code block, the queries strQuery1, strQuery2 & strQuery3 execute in a single transaction, if there is a expection while processing any of the queries, then the transactions performed by the other 2 queries will also get rollbacked leaving the database un affected.

That’s it we have seen how to execute a transaction in an Oracle database using the ADO.Net Transaction.