CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworktransactionsdbcontext
Muhammad
Muhammad

Reputation: 137

Stored Procedure and Database Context Operation in a Transaction in Entity Framework ASP.NET MVC 5

I have entity framework update operations, between these operations I also have a Stored Procedure (Performing some delete operations). But ef update operations may be failed, in this case, I want to prevent Stored procedure execution. I want to execute a stored procedure only if all ef Update operations successfully completed without error. Here is my code:

 assignment.ActualStatus = model.InquiryAction;
 officeAssignment.UpdateAt = currentDateTime;
 assignment.UpdateBy = userId;
 db.Entry(officeAssignment).State = EntityState.Modified;

                        //delete data for table..
 var succes = db.Database.ExecuteSqlCommand("[dbo].[spUpdateStatus] @inquiryId, @status",
                         new SqlParameter("inquiryId", model.InquiryId),
                         new SqlParameter("status", model.Action));

I know that I can call a stored procedure after the database context.SaveChanges() successfully return 1 but I don't want to use that approach because I have already lots of dependent code. In the above example, I have shown a small part of code.

Upvotes: 0

Views: 1956

Answers (1)

Klaud
Klaud

Reputation: 96

I recommend to roll it all into one SP. Within the SP, you start your transaction, and every step of the way make sure it updates/deletes successfully before moving onto the next query in the SP. If something fails, rollback. Once you get everything done, commit.

Edit to Muhammad's comment: There is an alternative. Wrap the EF code in a using BeginTransaction block, in the transaction block, do a foreach with a try/catch block. In the try, put your db code. On catch, rollback. At the end of the using/end of method, commit. Sample code below (code is NET Core 3.1 btw):

using(IDbContextTransaction transaction = db.Database.BeginTransaction())
{
    foreach (int currentId in IdsEnumerable ?? Enumerable.Empty<int>())
    {
        try
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Id", id),
                // other parameters here
            };

            int rowsAffected = db.Database.ExecuteSqlRaw("EXEC Schema.SPName @Id, etc...", parameters);

            if (rowsAffected > 0)
            {
                totalRowsAffected += rowsAffected;
            }
            else
            {
                transaction.Rollback();
                return false;
            }
        }
        catch(Exception ex)
        {
            transaction.Rollback();
        }
    }

    db.SaveChanges();
    transaction.Commit();
    return totalRowsAffected > 0;
}

Upvotes: 1

Related Questions