We have a nasty problem with database updates that I can't seem to get a handle on. We're not doing anything odd or out of the ordinary...at least to me.
* create a SqlConnection object (connect to db)
* create a SqlCommand object using the connection. In my case we are executing a stored procedure, but it could be any SQL.
* set the CommandTimeout property to some very high value (600 seconds)
* create a SqlTransaction object and assign it to the Transaction property of the SqlCommand object.
* execute the command: SqlCommand.ExecuteNonQuery( )
* check return code and make sure everything is OK.
* if ok, commit the transaction: SqlTransaction.Commit( )
Here's where the problem is: The SqlTransaction.Commit( ) throws SqlException with a "Timeout Expired" message. At this point, in my particular case I crash the program and investigate. What I find is that even though the Commit threw an exception, it actually committed the data to the database! The main problem is that I can't find a way to tell the SqlTransaction object to wait x seconds before giving up, basically a timeout threshold. This property is available on the SqlConnection object and the SqlCommand object but neither govern how long to wait for a commit to take place. The first will only control how long to wait before giving up on a connection attempt. The second controls how long to wait before bailing on the actual update itself. This is a significant problem in our environment and is causing substantial grief among developers.