Exploring Atomicity with DbContextTransaction in .NET and Entity Framework

Hey there, coffee in hand? Great! Let’s roll up our sleeves and get ready for a thrilling dive into the world of transactions!

Now, what’s on the menu for this electrifying journey, you ask? Here are the main acts of the show:

1. Definition of The Transaction and ACID Properties
2. Real-World Scenarios: Implementing DbContextTransaction in Entity Framework

Definition of The Transaction and ACID Properties :

Imagine you’re a big fan of Apple like me, and you are bankrupt, yet determined to acquire the coveted Apple Watch Ultra 2. In such a scenario, you’d initiate a new entry in the database dedicated to Apple watch buyers. This action exemplifies a transaction, an operation performed within the database.

Now, picture this: As you’re meticulously inputting your purchase details for the Apple Ultra Watch 2, you commence payment using two different methods due to your financial situation. However, just as you’re processing the second payment, an unexpected case occurs. What unfolds in such a predicament?

Well, here’s where the concept of Atomicity comes into play. When I refer to “atomicity,” I mean that the transaction must occur in an all-or-nothing fashion. Either both payments are successfully processed, or none of them are. This concept is a vital component of the quartet of fundamental transaction properties: Atomicity, Consistency, Isolation, and Durability, collectively ensuring the integrity and precision of the database.

As mentioned on the Databricks website, ACID is an acronym that refers to the set of 4 key properties that define a transaction: Atomicity, Consistency, Isolation, and Durability. If a database operation has these ACID properties, it can be called an ACID transaction, and data storage systems that apply these operations are called transactional systems. You can find more about this concept in the MongoDB article.

Real-World Scenarios: Implementing DbContextTransaction in .Net and Entity Framework

Let’s engage in the hypothetical scenario once more! Imagine you have a system with a specific requirement: you need to document the purchase transactions that happened in your store after payments are done from the user side, and these transactions must capture essential details in the database. These details include the purchase ID, customer name, timestamp, date, payments made via Visa, payments made in cash, and the product name.

To fulfill this requirement, you’ve established a structured database schema, ensuring that the system operates seamlessly and maintains data integrity.

Now, how did you guarantee that the system accurately associates payments, whether made via Visa or in cash, with their respective purchases? Furthermore, how you will ensure that if any issues arise, none of these payments will be processed/documented in the database without a corresponding purchase record in the system?

Well, we can achieve this using DbContextTransaction which is a class in Entity Framework that represents a transaction within the database context. It allows you to group multiple database operations into a single transaction, ensuring that they are all either committed together or rolled back together if an error occurs.

private void DocumentPurchases(object sender, EventArgs e)
{

    // Start a new database transaction using the CashDbEntities DbContext
    using (DbContextTransaction transaction = StoriedParameter.CashDbEntities.Database.BeginTransaction())
    {
        try
        {
            // Create a new Purchase object and populate its properties
            Purchase newPurchase = new Purchase
            {
                PurchaseID = PurchaseID, // Set the PurchaseID property
                CustomerID = customerId, // Set the CustomerID property
                // Create a new DateTime object based on purchaseTime, with specified year, month, day, hour, minute, and second
                PurchaseTime = new DateTime(purchaseTime.GetTime.Year, purchaseTime.GetTime.Month, purchaseTime.GetTime.Day, purchaseTime.GetTime.Hour, purchaseTime.GetTime.Minute, purchaseTime.GetTime.Second),
                PurchaseDate = purchaseDate.SelectedDate, // Set the PurchaseDate property
                ProductName = productName.Text, // Set the ProductName property
                // Parse the visaAmount.Text to a float it's not empty otherwise set it to 0
                VisaPaymentAmount = string.IsNullOrEmpty(visaAmount.Text) ? 0 : float.Parse(visaAmount.Text),
                // Parse the cashAmount.Text to a float it's not empty otherwise set it to 0
                CashPaymentAmount = string.IsNullOrEmpty(cashAmount.Text) ? 0 : float.Parse(cashAmount.Text)
            };

            // Add the newPurchase to the Purchases DbSet 
            CashDbEntities.Purchases.Add(newPurchase);

            // Check if there is a visa payment amount greater than 0
            if (float.Parse(visaAmount.Text) > 0)
            {
                // Create a new VisaPayment object and populate its properties
                VisaPayment newVisaPayment = new VisaPayment
                {
                    PurchaseID = newPurchase.PurchaseID, // Set the PurchaseID property
                    VisaCardNumber = float.Parse(visaCardNumber.Text), // Parse the visaCardNumber.Text to a float
                    // Set VisaPaymentDetails to null if visaPaymentDetails.Text is empty, otherwise set it to the text value
                    VisaPaymentDetails = string.IsNullOrEmpty(visaPaymentDetails.Text) ? null : visaPaymentDetails.Text
                };

                // Add the newVisaPayment to the VisaPayments DbSet
                CashDbEntities.VisaPayments.Add(newVisaPayment);
            }

            // Check if there is a cash payment amount greater than 0
            if (float.Parse(cashAmount.Text) > 0)
            {
                // Create a new CashPayment object and populate its properties
                CashPayment newCashPayment = new CashPayment
                {
                    PurchaseID = newPurchase.PurchaseID, // Set the PurchaseID property
                    // Set CashPaymentDetails to null if cashPaymentDetails.Text is empty, otherwise set it to the text value
                    CashPaymentDetails = string.IsNullOrEmpty(cashPaymentDetails.Text) ? null : cashPaymentDetails.Text
                };

                // Add the newCashPayment to the CashPayments DbSet
                CashDbEntities.CashPayments.Add(newCashPayment);
            }

            // Save changes to the database
            CashDbEntities.SaveChanges();

            // Commit the database transaction
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // Rollback the transaction if an exception occurs
            transaction.Rollback();
            // Display an error message with the exception's message to the console
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }

}

In short words, these lines of code initialize a database transaction using Entity Framework. Any database operations that follow within theusing the block will be part of this transaction, and if an exception occurs or when the block is exited, the transaction will be either committed or rolled back automatically, ensuring data integrity in the database.

Note that some fields are validated while others are not. I’ve created a separate function that checks the essential fields when the user triggers the “Save Purchase” button. The required fields for validation include PurchaseID, VisaCardNumber, CashPaymentAmount, VisaPaymentAmount, ProductName, PurchaseDate, PurchaseTime, CustomerID, and PurchaseID. The remaining fields are optional, allowing us to include data even if they are empty.

It’s as simple as that, A piece of cake!

See you in the next articles. Stay tuned, just like when you stay tuned for Apple products ;)

Resources

• https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbcontext?view=entity-framework-6.2.0
• https://datascientest.com/en/acid-in-database-management
• https://www.databricks.com/glossary/acid-transactions