This discussion is archived
0 Replies Latest reply: Oct 3, 2012 9:57 AM by 642116 RSS

ExecuteNonQuery Blocks

642116 Newbie
Currently Being Moderated
Hello all,

I have the following function in my application that will block on the line for rowsAffected = command.ExecuteNonQuery() if the rows being updated or deleted have a lock on them.

Public Shared Function ExecuteNonQuery(ByVal ConnectionString As String, ByVal factory As DbProviderFactory, ByVal sql As String) As Integer

Dim rowsAffected As Integer = -1

Using conn As DbConnection = factory.CreateConnection
conn.ConnectionString = ConnectionString
conn.Open()
If conn.State.Equals(ConnectionState.Open) Then
Try
Dim command As DbCommand = conn.CreateCommand
command.CommandText = sql
command.CommandType = CommandType.Text
'This blocks; if row or table locks exist, this call will never return.
rowsAffected = command.ExecuteNonQuery()
Catch ex As Exception
Debug.Print(ex.Message)
End Try

End If
End Using

Return rowsAffected
End Function

I would like to be able to detect the row/table locks so that my code does not hang. I have tried using command.CommandTimeout = n expecting the ExecuteNonQuery call would time out after the set time, but it never does.

Any suggestions for improving my implementation so I can prevent my application from hanging up?

Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points