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
If conn.State.Equals(ConnectionState.Open) Then
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
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?