Using TransactionScope across Databases using ADO.NET Entity Framework and ASP.NET MVC

ADO.NET Entity Framework (EF) is a widely used ORM Framework in .NET application development. This framework provides mapped types and methods for performing CRUD operations in your applications and saves additional efforts for writing code for Data Access Layer.

Last week, while discussing a scenario for data access using ADO.NET EF with one of my customer, I was asked to provide a solution to manage transactions across tables of different databases. The scenario was that there were two databases on SQL Server e.g.db1 and db2, both had two separate tables, e.g. Table T1 in db1 and Table T2 in db2. These Databases and Tables were related with each other via business requirements. The application had to insert records in both these tables at the same time. The order of operations was to insert in T1 and then T2. If T2 insert fails, then transaction in T1 should be rolled back.

Fortunately, to handle such requirements, since .NET 2.0 we been provided with TransactionScope class under System.Transaction namespace. This class provides a simple way to make a block of code participate in a transaction without requiring to interact with transaction itself. This class can manage surrounding transactions automatically. The transaction scope is started using the TransactionScope class instance. It is highly recommended to create the TransactionScope object in a using block.

When TransactionScope is instantiated, the transaction manager determines which transaction to participate in. Once this is determined, the scope always participates in that transaction. While creating TransactionScope object, we need to pass the TransactionScopeOption enumeration with the following values:

• Required: A transaction is required by the scope, this uses surrounding transaction if it is already exists, else it creates a new transaction.
• RequiresNew: A new transaction is always created for the scope.
• Suppress: The surrounding transaction is suppressed when creating scope. All operations in the scope are completed without surrounding transaction.

In this application, we will use the following sample Sql Server databases:

Flight Database and FlightBooking Table:

CREATE TABLE [dbo].[FlightBooking] (
    [FlightId]       INT          NOT NULL,
    [FilghtName]     VARCHAR (50) NOT NULL,
    [Number]         NCHAR (10)   NOT NULL,
    [TravellingDate] DATETIME     NOT NULL,
    CONSTRAINT [PK_FlightBooking] PRIMARY KEY CLUSTERED ([FlightId] ASC)
);

Hotel database and Reservation Table:

CREATE TABLE [dbo].[Reservation] (
    [BookingId]   INT          NOT NULL,
    [Name]        VARCHAR (50) NOT NULL,
    [BookingDate] DATETIME     NOT NULL,
    CONSTRAINT [PK_REservation] PRIMARY KEY CLUSTERED ([BookingId] ASC)
);

We will create a new ASP.NET MVC application and will create Models using ADO.NET EF for Flight and Hotel database.

Step 1: Open the free Visual Studio 2013 Community Edition and create a new Empty MVC application, name this as ‘MVC_EF_TransactionScope’. In this application, add Sql Server Database in the App_Data folder. Create Flight and Hotel databases and FlightBooking and Reservation tables in these databases respectively as shown in the above scripts.

Step 2: In the Models folder, add a new ADO.NET Entity Data Model. In the wizard, select Flight Database and select FlightBooking table. Complete the wizard, the application will map with the FlightBooking table. Repeat the procedure for the Hotel database and Reservation table. In the application, we will now have FlightEntities and HotelEntities classes along with FlightBooking and Reservation tables for performing CRUD operations.

Step 3: In the Models folder, add a new class of the name TripReservation. This class will be used to perform Create operation for trip which includes FlightBooking and Reservation. This class will accepts Flight and Hotel information from the UI and store it in Flight and Hotel Database respectively. The code for the class is as follows:

public class TripReservation
{
    public FlightBooking Filght { get; set; }
    public Reservation Hotel { get; set; }
}

Step 4: In the application, add a new folder of the name DataAccessRepository. In this folder, add a new class file and add the following class in it.

using MVC_EF_TransactionScope.Models;
using System.Transactions;

namespace MVC_EF_TransactionScope.DataAceessRepository
{
    public class MakeReservation
    {

        FlightEntities flight;

        HotelEntities hotel;

        public MakeReservation()
        {
            flight = new FlightEntities();
            hotel = new HotelEntities(); 
        }

       //The method for handling transactions 
        public bool ReservTrip(TripReservation trip)
        {
            bool reserved = false;

            //Define the scope for bundling the transaction
            using (var txscope =new TransactionScope(TransactionScopeOption.RequiresNew))
            {
                try
                {
           
                    //The Flight Information
                    flight.FlightBookings.Add(trip.Filght);
                    flight.SaveChanges();
                    //The Hotel Information
                    hotel.Reservations.Add(trip.Hotel);
                    hotel.SaveChanges();

                    reserved = true;
                    //The Transaction will be completed
                    txscope.Complete();
                }
                catch
                {
                }

            }
            return reserved;
        }
    
    }
}

The above class has the following specifications:

1. The class defines instances for FlightEntities and HotelEntities objects which represent the context for ADO.NET EF for performing CRUD operations.

2. The method ReservTrip () accepts TripReservation object. This method defines TransactionScope and bundles the Create operation for Flight and Hotel, within the context of the transaction. The code is recommended to be written within try-catch block. If the SaveChanges () method for both Entities is executed successfully, then the transaction will be completed, else rollback.

Step 5: In the Shared Folder of the application, right click and select Add View. Add two Empty Views of name Success.cshtml and Error.cshtml with the following markup in it.

Success.cshtml

@{
    ViewBag.Title = "Success";
}

<h1>Congratulations!!!!Your Flight and Hotel is booked successfully.</h1>

Error.cshtml

@{
    ViewBag.Title = "Error";
}

<h1>Error, The Trip is not Registered. This may be because either Flight or Hotel Booking is not available.</h1>

Step 6: In the Controllers folder, add a new Empty MVC controller of the name TripController. This controller class will be used to make calls to the MakeReservation class for performing transactions. In this controller, add Create methods as shown in the following code:

using System;
using System.Web.Mvc;
using MVC_EF_TransactionScope.Models;
using MVC_EF_TransactionScope.DataAceessRepository;

namespace MVC_EF_TransactionScope.Controllers
{
    public class TripController : Controller
    {

        MakeReservation reserv;
        public TripController()
        {
            reserv = new MakeReservation(); 
        }
        // GET: Trip
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Create()
        {
            return View(new TripReservation());
        }

        //The Reservation Process
        [HttpPost]
        public ActionResult Create(TripReservation tripinfo)
        {
            try
            {
                tripinfo.Filght.TravellingDate = DateTime.Now;
                tripinfo.Hotel.BookingDate = DateTime.Now;
               var res =  reserv.ReservTrip(tripinfo);

               if (!res)
               {
                   return View("Error");
               }
            }
            catch (Exception)
            {
                return View("Error");
            }
            return View("Success");
        }
    }
}

In this class, the Create HttpGet method returns an instance of the TripReservation () class to generate the Create View. The HttpPost Create method accepts the TripReservation object and passes it to the ReservTrip () method of the MakeReservation class. The ReservTrip method returns a Boolean, if the return value is false or any exception occurs then the Error view will be returned, else the Success view will be returned.

Step 7: To create a UI for entering Flight and Hotel information, scaffold an Empty view with TripReservation model from the HttpGet Create method of the TripController. To add a scaffold, right-click either the project or a folder within the project, and select Add – New Scaffolded Item. Since this is an empty view, we need to design it as following:

@model MVC_EF_TransactionScope.Models.TripReservation

@{
    ViewBag.Title = "Create";
}

<h2 class="text-center">Plan Your Trip</h2>
@using(Html.BeginForm()){


<table class="table table-condensed table-striped table-bordered">
    <tr>
        <td>
            <table class="table table-condensed table-striped table-bordered">
                <tr>
                    <td colspan="2" class="text-center">
                        The Flight Booking Info
                    </td>
                </tr>
                <tr>
                    <td>
                        Flight Id:
                    </td>
                    <td>
                        @Html.EditorFor(m => m.Filght.FlightId)
                    </td>
                </tr>
                <tr>
                    <td>
                        Flight Name:
                    </td>
                    <td>
                        @Html.EditorFor(m => m.Filght.FilghtName)
                    </td>
                </tr>
                <tr>
                    <td>
                        Flight Number:
                    </td>
                    <td>
                        @Html.EditorFor(m => m.Filght.Number)
                    </td>
                </tr>
            </table>
        </td>
        <td>
            <table class="table table-condensed table-striped table-bordered">
                <tr>
                    <td colspan="2" class="text-center">
                        The Hotel Booking Info
                    </td>
                </tr>
                <tr>
                    <td>
                        Booking Id:
                    </td>
                    <td>
                        @Html.EditorFor(m => m.Hotel.BookingId)
                    </td>
                </tr>
                <tr>
                    <td>
                        Customer Name:
                    </td>
                    <td>
                        @Html.EditorFor(m => m.Hotel.Name)
                    </td>
                </tr>

            </table>
        </td>
    </tr>
    <tr>
        <td colspan="2" class="text-center">
            <input type="submit" value="Save Trip" />
        </td>
    </tr>
</table>

}

The above view provides required UI for accepting Flight and Hotel Data from the end-user.

Step 8: To run the application and check transactions, we need to use the Distributed Transaction Coordinator (DTC) Service. This service coordinates transaction that update two or more transactions protected resources e.g. Databases, Message Queues, File Systems, etc. To open the DTC settings follow these steps:

1. Start > Run > Microsoft Management Console (MMC)

2. Open Component Services snap-in from MMC.

3. Double-Click on Computers folder in the Console.

4. Right-Click on My Computers, and select Properties.

5. In the Properties window, select MSDTC tab and check the Use local coordinator Check Box.

6. Click on Ok to close properties window.

7. Double-Click on My Computer. This will show various folder, Double-Click on Distributed Transaction Coordinator folder. This step will show Local DTC.

8. Double-Click on Local DTC to view Transaction List and Transaction Statistics.

9. Double-Click on Transaction-Statistics, the following UI Will be displayed.

The above image shows transaction Statistics with Committed, Aborted Transactions, etc.

Step 9: Run the application and the following result will be displayed:

Enter Flight and Hotel Booking Info and click on the Save Trip Button as show in the following image:

If the data is saved in FlightBooking and Reservation Tables, then the Transaction will be completed and the Success Page will be displayed as shown in the following image:

The Transaction Statistics will be displayed as below:

The Committed Transaction will be increased by one (Note: In my case I have already tested the application for 5 successful transactions so it is increased by 1 and showing 6). In the above image, the Aborted transaction number is 1. We can now see the data entered in the FlightBooking and Reservation tables. Once again run the application, and Change the FlightBooking information but keep the reservation info same as previous values (this means we are creating a Primary Exception condition so that we can test Rollback), as shown in the following image:

Click on the Save Trip button and the Error page will be displayed (since BookingID already exists) as shown below:

Check the Transaction Statistics, the Aborted value will be changed to 2 as shown in the following image:

If you verify the data in FlightBooking and Reservation tables, the newly added record will not be displayed in it. This means that the TransactionScope has managed the Transaction by bundling connection to Flight and Hotel databases in a single scope and monitored the Committed and Aborted Transactions.

Conclusion: As we saw in this simple ASP.NET MVC application using Entity Framework, while performing multiple databases operations for storing related data with dependency across them, it is always recommended to use TransactionScope for managing the transactions.

Download the entire source code of this article (Github)

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn