Delete a file from database and file system

Question

I have a table that references files in a shared location on our network (stores the file path in the database).

I have a button that needs to delete the record from the database and the file off the File System:

foreach (var report in reports)
{
      string filePath = report.ReportPath;

      if (File.Exists(filePath));
      {
         File.Delete(filePath);
      }                      

      context.ReportGenerations.DeleteObject(report);
      context.SaveChanges();
}

An exception could be thrown when deleting the file or deleting the database record and if this happens I would like neither of the operations to complete.

Is there an easy way to ensure both operations are carried out successfully?

Answer 1

You have to do 2 two things

• Wrap the whole process inside a database transaction.

• Delete file from database before doing it from file system

If the process fails deleting from database, physical file won't be removed as you haven't reached file system delete logic.

If the process fails deleting from file system you rollback transaction and database operation is reverted.

DbTransaction transaction = null;
foreach (var report in reports)
{
    try
    {
        transaction = context.Connection.BeginTransaction();

        context.ReportGenerations.DeleteObject(report);
        context.SaveChanges();

        string filePath = report.ReportPath;
        if (File.Exists(filePath));
        {
            File.Delete(filePath);
        }
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
    }
}        

---

While I believe this is the safer approach you can implement wihout getting really complex I agree that there is no synchronous approach that guarantee 100% of efficacy. To be sure that no orphan item remains, you'll have to implement a background clean up process. You'll have to analyze if such an extra complexity is justified or not according your scenario.

Answer 2

Delete the file from database and file system in a transaction:

using (TransactionScope scope = new TransactionScope(TransactionScope.Required, 
 new TransactionOptions 
     { IsolationLevel = IsolationLEvel.ReadCommitted}))
{
   try 
   {
       // Delete file from database
       // Delete physical file 
       // commit
   }
   catch (Exception ex)
   {
       // no commit, so database deletion will be rolled back
   }       
}

If the deletion of the file on a physical drive fails for some reason, the database deletion will be rolled back too.

If the deletion in the database failed, the file won't be deleted physically.

Commit is only executed if both physical delete and database delete has succeeeded.

So whatever exception might occur; you end up in a consistent state.