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.Comm andTimeout = 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(p ConnectionStrin g)
Dim trMigrate As SqlTransaction = Nothing
Using cdMigrate As New SqlCommand("usp Migrate", cnMigrate)
With cdMigrate
Try
.CommandTimeout = 0
.CommandType = CommandType.Sto redProcedure
.Connection.Ope n()
trMigrate = .Connection.Beg inTransaction(" Migrate")
.Transaction = trMigrate
.Parameters.Add WithValue("@MyP aram", 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.Commi t()
Catch ex As Exception
If .Connection.Sta te <ConnectionStat e.Closed AndAlso
trMigrate IsNot Nothing Then
trMigrate.Rollb ack()
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?
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.Comm andTimeout = 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(p ConnectionStrin g)
Dim trMigrate As SqlTransaction = Nothing
Using cdMigrate As New SqlCommand("usp Migrate", cnMigrate)
With cdMigrate
Try
.CommandTimeout = 0
.CommandType = CommandType.Sto redProcedure
.Connection.Ope n()
trMigrate = .Connection.Beg inTransaction(" Migrate")
.Transaction = trMigrate
.Parameters.Add WithValue("@MyP aram", 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.Commi t()
Catch ex As Exception
If .Connection.Sta te <ConnectionStat e.Closed AndAlso
trMigrate IsNot Nothing Then
trMigrate.Rollb ack()
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?
Comment