CODEWITHSUNDEEP
asp.net-mvcentity-framework
Anis Alibegić
Anis Alibegić

Reputation: 3230

ASP.NET MVC - EF Stored procedure and file upload. No roll-back

I have a problem when I'm using insert stored procedures and uploading a file in ASP.NET MVC.

I have three parts in my try-catch block:

  1. Generate the file name.
  2. Executing the insert stored procedure.
  3. Saving the file.

Generate a file name, insert that file name into the database and save a file with that name.

Problem:

If executing the insert stored procedure succeeds and file save fails, the inserted row still remains resulting in the event without the photo.

If I switch the order, first saving the file and then inserting into the database, the problem still remains. If file save succeeds and insert fails, there is a photo without the event.

Sample code (just an illustration).

try
{
    //Generate a new file name.
    var newFileName = Guid.NewGuid().ToString();

    //Execute stored procedore.
    Database.Insert_Event(newFileName, title, description, dateTime);

    //Save event photo.
    eventPhoto.SaveAs(newFileName);
}
catch (DataException exception)
{
    //Exception message when executing insert SP.
}
catch (IOException exception)
{
    //Exception message when saving file.
}

Question:

How to revert the database insert when inserting using stored procedure if the file save fails or how to revert file save if the insert fails?

Upvotes: 0

Views: 386

Answers (1)

Abdul Samad
Abdul Samad

Reputation: 441

The best approach would be to first save the file and then insert into the database.

So what I have done below is we save the file to our new path (newFileName). If the file isn't saved the exception will be caught in our catch statement. Next, if everything goes smoothly we check if the file exists at our new path, we execute the Stored Procedure.

While executing the Stored Procedure if we face an error it will be caught in our catch statement and there will no data inserted to the database and we will delete the file from our new path leaving no trace of it.

This will cover both scenarios of your question.

    //Generate a new file name.
    var newFileName = Guid.NewGuid().ToString();
    try
    {    
        //Save event photo.
        eventPhoto.SaveAs(newFileName);

        //checks if file exists at the new path
        if(File.Exists(newFileName)){
            //Execute stored procedore.
            Database.Insert_Event(newFileName, title, description, dateTime);
        }
    }
    catch (DataException exception)
    {
        //Exception message when executing insert SP.
        // Remove the saved file
        if(File.Exists(newFileName)){
            File.Delete(newFileName)
        }
    }
    catch (IOException exception)
    {
        //Exception message when saving file.
    }

Upvotes: 2

Related Questions