none
SqlTransaction.Rollback closes the SqlConnection RRS feed

  • Question

  • I have a console app that imports a number of tables into a sql database using the SqlBulkCopy class.  To ensure no interference with other uses during the import, the app places the database in single user mode first.  I then begin a transaction with SqlConnection.BeginTransaction, delete rows from several tables, and import new data using the bulkcopy class.  If all is sucessful, I commit the transaction and place the database back into multi user mode.

    I have a rollback statement in a catch block.  What I'm seeing is that when a rollback does occur do to some error, the SqlConnection state is closed.  This prevents me from placing the database back into multi user mode.  When I check the running processes on the sql server, I can see that there is a rollback operation running on the database, however I am unable to reopen the connection at that time.  Is there a way to get around this.  I would have thought that the rollback method would have blocked until the rollback was completed, but that doesn't seem to be the case.  I am ending up with a database that does get rolled back, but is still in single user mode.

    Friday, September 2, 2011 8:51 PM

Answers

  • I found the problem.  The rollback was timing out with an exception.  The solution was to set the connection timeout property in the connection string to something other than the default of 15 seconds.
    Tuesday, September 6, 2011 4:25 PM

All replies