0 Replies Latest reply: Oct 3, 2012 11:57 AM by 642116 RSS

    ExecuteNonQuery Blocks

    642116
      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,