BeginTransaction causes command to ignore timeout

  • Thread starter Thread starter Leon Mayne
  • Start date Start date
L

Leon Mayne

Hello,
I had a chunk of code that would execute a stored proc to migrate data
between two systems (takes a few minutes) and then performs some data
manipulation on the migrated data. This was working fine when I was
using SqlCommand.CommandTimeout = 0.

After getting the basics to work I wanted to wrap the whole block in a
transaction to roll back everything if something went wrong:

Dim cnMigrate As New SqlConnection(pConnectionString)
Dim trMigrate As SqlTransaction = Nothing
Using cdMigrate As New SqlCommand("uspMigrate", cnMigrate)
With cdMigrate
Try
.CommandTimeout = 0
.CommandType = CommandType.StoredProcedure
.Connection.Open()
trMigrate = .Connection.BeginTransaction("Migrate")
.Transaction = trMigrate
.Parameters.AddWithValue("@MyParam", intMyValue)
Dim daResults As New SqlDataAdapter(cdMigrate)
Dim dsResults As New DataSet()
daResults.Fill(dsResults)
' Code to process post migrate data

' All done, commit the transaction
trMigrate.Commit()

Catch ex As Exception
If .Connection.State <> ConnectionState.Closed AndAlso
trMigrate IsNot Nothing Then
trMigrate.Rollback()
End If
Throw
End Try
End With
End Using

Trouble is, the CommandTimeout property seems to be ignored now, and the
procedure times out after about a minute on .Fill

Do I have to set another timeout for the transaction? If so then where?
 
Leon said:
Hello,
I had a chunk of code that would execute a stored proc to migrate data
between two systems (takes a few minutes) and then performs some data
manipulation on the migrated data. This was working fine when I was
using SqlCommand.CommandTimeout = 0.

After getting the basics to work I wanted to wrap the whole block in a
transaction to roll back everything if something went wrong:

Dim cnMigrate As New SqlConnection(pConnectionString)
Dim trMigrate As SqlTransaction = Nothing
Using cdMigrate As New SqlCommand("uspMigrate", cnMigrate)
With cdMigrate
Try
.CommandTimeout = 0
.CommandType = CommandType.StoredProcedure
.Connection.Open()
trMigrate = .Connection.BeginTransaction("Migrate")
.Transaction = trMigrate
.Parameters.AddWithValue("@MyParam", intMyValue)
Dim daResults As New SqlDataAdapter(cdMigrate)
Dim dsResults As New DataSet()
daResults.Fill(dsResults)
' Code to process post migrate data

' All done, commit the transaction
trMigrate.Commit()

Catch ex As Exception
If .Connection.State <> ConnectionState.Closed AndAlso
trMigrate IsNot Nothing Then
trMigrate.Rollback()
End If
Throw
End Try
End With
End Using

Trouble is, the CommandTimeout property seems to be ignored now, and the
procedure times out after about a minute on .Fill

Do I have to set another timeout for the transaction? If so then where?
Click to expand...

Nevermind, it's because the business layer calls use their own
instantiated connection to the database, which seems to hang if there is
another connection with an open transaction to the same database.
 
Back
Top