Entity Framework Transactions in Foreach Loop

.net c# entity-framework-6 transactions

Question

I am trying to save data from a Windows Forms DataGridView using Entity framework. For some reason the records do not save to the database. I want to loop through the DataGridView and add each row into a Transaction object and save all the transactions (rows) to the database at once.

The information I found on transactions was from MSDN. While searching for a solution to the problem I came across this SO post that has a comment that reads "I'm taking an exception to trapping the exception like this. It causes the database operation to fail silently." This seems to be what is happening in my code. I do not get any errors, it just doesn't save anything to the database.

How can I save all the transactions atomically and rollback if something fails? How can I reorganize things to correctly capture an error? If the comment is correct, what is wrong with the code from MSDN?

Here is my code:

// Store the valid transactions
using ( var dbContextTransaction = context.Database.BeginTransaction() )
{
    try
    {
        Transaction oTransaction = new Transaction();

        foreach ( DataGridViewRow row in dgvInventoryTransactions.Rows )
        {
            if ( !row.IsNewRow )
            {
                // Get the product
                int iSelectedProduct = int.Parse( row.Cells[0].Value.ToString() );
                oTransaction.Product = context.Products.First( p => p.ID == iSelectedProduct );

                // Quantities
                oTransaction.FullQuantity = int.Parse( row.Cells[1].Value.ToString() );
                oTransaction.PartialQuantity = int.Parse( row.Cells[2].Value.ToString() );
                oTransaction.CalculatedQuantity = (double)oTransaction.FullQuantity + ( (double)oTransaction.PartialQuantity / (double)oTransaction.Product.Pieces );

                // Check to see if a truck has been selected
                if ( !string.IsNullOrEmpty( row.Cells[3].FormattedValue as String ) )
                {
                    // A truck has been selected
                    int iSelectedVehicle;
                    if ( int.TryParse( row.Cells[3].FormattedValue.ToString(), out iSelectedVehicle ) )
                    {
                        oTransaction.Vehicle = context.Vehicles.Where( v => v.VehicleNumber == iSelectedVehicle ).First();
                    }

                }

                // Check to see if the "Source" field contains data
                if ( !string.IsNullOrEmpty( row.Cells[4].FormattedValue as String ) )
                {
                    oTransaction.SourceNumber = row.Cells[4].Value.ToString();
                }

                // Check to see if the comments field contains data
                if ( !string.IsNullOrEmpty( row.Cells[5].FormattedValue as String ) )
                {
                    oTransaction.Comments = row.Cells[5].Value.ToString();
                }

                // Get the transaction type
                int value = int.Parse( cmboTransactionType.SelectedValue.ToString() );
                oTransaction.TransactionType = context.TransactionTypes.Where( t => t.ID == value ).First();

                // Get the current user
                var user = context.Employees.First( u => u.ID == Globals.User ); // One lookup...
                oTransaction.CreatedBy = user;
                oTransaction.LastUpdatedBy = user;                                     

                // Add the dates
                oTransaction.TransactionDate = dtpTransactionDate.Value;
                oTransaction.CreateDate = DateTime.Now;
                oTransaction.LastUpdatedDate = DateTime.Now;

                // Save the transaction
                context.SaveChanges();


            }

        }

        // Commit the changes to the database
        dbContextTransaction.Commit();

    }
    catch ( Exception )
    {
        dbContextTransaction.Rollback();
        MessageBox.Show( "Records were NOT saved successfully.", "Save Unsuccessful", MessageBoxButtons.OK, MessageBoxIcon.Information );
    }

}
1
2
8/26/2018 11:03:23 PM

Accepted Answer

You have not inserted oTransaction or connected it to some existing object. EF does not know that this object exists. Try the AddObject method to add the transaction. This registers it with EF.

Also, you can delete the Rollback call. It rolls back when there is no commit. Also move the message box out of the using block to quickly end the transaction.

0
1/21/2016 4:05:14 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow