retryExecuteSP: Try iRetry += 1 Dim cmd As New SqlCommand("spLB_updateThreadInfo", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("threadIinfo", threadInfo) conn.Open() cmd.ExecuteNonQuery() Return True Catch ex As Exception If iRetry < 6 Then Threading.Thread.Sleep(1000) GoTo retryExecuteSP End If Return False Finally conn.Close() End Try End Function
- you only need to retry when there is something like 'not connected ..', 'time out' ...yes, I will add in more specific checking when I make live
you don't close connection .... but you reopen it in second attempt , even for timeout it wasn't necessaryBut I thought this would be closed by the finally block which is always executed
here you have overview, but don't you want to rollback everything if your last statement erroredIt's actually a simple update that the sp performs which should either work or not, and I can add some error trapping in the sp itself, it's mainly the potential connectivity issue that worries me.
what if you get an error 'unable to allocate space', or 'FK violation' -> you retry and after 6 times you return false .... and how do you know what error?Yes, I will add some kind of logging to catch the full exception details in case of 6 times failing.
0) will block (won't it?) Why not start a timer and get it to fire off the retry?
On your main code, when calling the method do:
storedProcExecuted = false;
storedProcExecuted = callYourStoredProcMethod(y
This way your method will retry until it executes and returns true.
It's pretty rough but functional and easy.