0

Does the documentation quotation from this answer: https://stackoverflow.com/a/542691/1011724

When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. When all SQL commands have been successfully completed, LINQ to SQL commits the local transaction and returns.

apply to the .ExecuteCommand() method? In otherwords, can I trust that the following delete is handled in a transaction and will automatically rollback if it fails or do I need to manually tell it to use a transaction and if so how? Should I use TransactionScope?

using(var context = Domain.Instance.GetContext())
{
    context.ExecuteCommand("DELETE FROM MyTable WHERE MyDateField = {0}", myDate)
}
Community
  • 1
  • 1
Dan
  • 45,079
  • 17
  • 88
  • 157
  • The using statement creates an exception handler to the program doesn't exit and continues after the using block. It doesn't guarantee that all the code inside the using statement gets executed. The GetContext can have two different type of failures. 1) Cannot connect to database 2) Fails in getting data from database. Now does it make sense to do a delete if you cannot connect to database, No. Does it make sense to Delete from a Table that doesn't have any rows because an exception occurred while query was being performed, No. I'm not sure it is necessary to perform the Delete in every case. – jdweng Apr 20 '16 at 11:27
  • My question is more from the DB side - will it be transactional? Either the whole delete must occur or nothing must be deleted. So if an error occurs midway through the delete, I need to know that the DB will be rolled back to a state before the delete started. That's what I'm asking. – Dan Apr 20 '16 at 11:59
  • It is better to implement as a stored procedure inside the database. it you had a query that took 1/2 hour to complete and the connection between the client and server disconnected in the middle of the query the delete wouldn't occur. It may be better to do code as a stored procedure in database than in c#. I don't know all requirements to give better answer. – jdweng Apr 20 '16 at 12:31
  • I just want to know if this is in a transaction or not – Dan Apr 20 '16 at 12:33

1 Answers1

2

Every SQL statement, whether or not wrapped in an explicit transaction, occurs transactionally. So, explicit transaction or not, individual statements are always atomic -- they either happen entirely or not at all. In the example above, either all rows that match the criterion are deleted or none of them are -- this is irrespective of what client code does. There is literally no way to get SQL Server to delete the rows partially; even yanking out the power cord will simply mean whatever was already done for the delete will be undone when the server restarts and reads the transaction log.

The only fly in the ointment is that which rows match can vary depending on how the statement locks. The statement logically happens in two phases, the first to determine which rows will be deleted and the second to actually delete them (while under an update lock). If you, say, issued this statement, and while it was running issued an INSERT that inserted a row matching the DELETE criterion, whether the row is in the database or not after the DELETE has finished depends on which transaction isolation level was in effect for the statements. So if you want practical guarantees about "all rows" being deleted, what client code does comes into scope. This goes a little beyond the scope of the original question, though.

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • What if your command sent was multi statements? Does ExecuteCommand apply a transaction? – Terry Mar 01 '17 at 15:41