c# - Will a transaction always commit (making sure file is imported only once)? -

the question ask needs introduction. have application imports small files , e-mails own sql server database folder every 5 minutes. of great importance that

1. each file must imported
2. each file must imported once

now part 1 not big deal. part 2 raises concerns. if file cannot deleted after being imported (for example due lack of access rights), , next time application looks @ folder file imported again , again, until fixes access rights problem.

thus wondering if following approach work safely, without data loss:

1. start transaction
2. import file db
3. delete file folder
4. commit transaction (or rollback if there error)

this point arrived title of question: will last commit step succeed if prior steps did not throw exception? safe delete file before commit, without risking losing file? if sql server shut down during transaction? or shall delete record database if file deletion failed?

the "transaction" speak of ensuring data committed database. add durability ensure file can't imported more once, have enumerable number of options, let's consider following:

1. when importing file, add file name table in unique constraint sql insert fail , entire "transaction" rolled back. of course work if file names in fact unique. if file name isn't unique, see if can find enough information is. times there additional domain information can put (e.g. invoice #, customer #, etc.) make unique , insert still fail.
2. keep listing of files have been processed , don't process them again. xml file, or in memory, it's still risky because if user's drop new file in there same name - want import one. think option 1 best bet here.

one thing need realize though have 2 separate technologies you're managing. deletion of file, or should attempt, doesn't have database transaction unless make it. so, let's had flow this:

using (sqlconnection c ...) using (sqlcommand cmd ...) {     sqltransaction t = c.begintransaction();     try     {         // update database          // try delete file          t.commit();     }     catch (exception ex)     {         // know failed         // can catch more specific exceptions , respond here     } } 

if t.commit() isn't reached, database will not updated.