.NET: How to handle timeout in datareader loop
.NET development, Windows Forms, DataViewGrid
I have a winforms project that loads a DataView grid async so that the user can hit cancel (and the form does not get "not responding"). I am getting timeouts sometimes in the While dr.Read ... End While. I can override the timeout default, but isn't there any other way? It would be nice if the timeout could be trapped and let me try again at the same point in the progression of the reader.
My db is SQLServer 2000 sp4.
Here is the code to create the reader. Note that I am setting the option to close the connection when reader is done.
With cmd
'SMCNITT 05/23/2008 tg.74 timeout error on fill of grid
'-timeout override was passed but not used!
If Not CommandTimeout Is Nothing Then
.CommandTimeout = (CInt(CommandTimeout)) 'caller can override command timeout
End If
.CommandText = "spMsgValGridPreviewBatchRS"
.Connection = cnn
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@BatchID", intBatchId)
.Parameters.AddWithValue("@Recordcount", intRecordCount)
.Parameters.Item("@Recordcount").Direction = ParameterDirection.Output
mblnAsyncProcessInProgress = True
result = .BeginExecuteReader(CommandBehavior.CloseConnection)
While Not result.IsCompleted
Threading.Thread.Sleep(100)
'polling for cancel
If mblnCancelModalProcess Then
.Cancel()
Return Nothing
End If
Application.DoEvents()
End While
End With
'create and return the reader
dr = cmd.EndExecuteReader(result)
mblnAsyncProcessInProgress = False
