database timeout error resolution problem

Question

Hi I am having some problems in my app for the database timeout. Due to some network glitches the query takes more than 45 sec to return the resultset which has about 10,000 rows. Most of the time its fast upto 11-12 secs. My app is runs as a scheduled job in the background.

The problem is I need to try three times if i get the timeout exception before writing the error to the error log or exiting. I know i can set the command timeout property to 60 seconds but still i want to try three times before exiting. I have written a method for that but I think there is some flaw in my method.

For now i specifically set the timeout to be 5 seconds as i know well that it takes more than 25 seconds for this stored proc to run. I just want to get that timeout expired exception and write ways to handle it.

My problem is if after 2 times I set the connection timeout to 50 so my method goes and get the correct list but it again goes inside the methodname which i have called inside the catch statement and finally returns nothing. It looks like its getting into recursion or something but i am confused as to where i am wrong.

Any hints or pointers will be helpful in clarifying my concept.

Private Shared timeoutcounter As Integer = 0
Private Shared dbConnectionString As String = ConfigurationManager.AppSettings("DBConn")

Public Shared Function GetPurgeList() As DestList

    Dim AcInfo As DestInfo = Nothing
    Dim tempList As DestList= Nothing
    Dim ConnectionString As String = dbConnectionString
    Dim cn As New SqlConnection(ConnectionString)
    Dim cmd As SqlCommand = Nothing
    Dim dr As SqlDataReader = Nothing

    Try
        cn.Open()
        cmd = New SqlCommand
        With cmd
            .Connection = cn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "usp_abcd"
            .CommandTimeout = 5
            dr = .ExecuteReader
            If dr.HasRows Then
                tempList = New DestList()
                While dr.Read()
                    If dr.GetName(0).ToString() = "errnum" Then
                        ErrorLogger.WriteToErrorLog("Error from stored proc usp_abcd" + CStr(dr("errnum")), dr("errmsg"))
                    Else
                        AcInfo = New DestInfo
...
//fill object
...
                           tempList.Add(AccountInfo)
                    End If
                End While
            End If
        End With

    Catch ex As Exception
       If ex.Message.ToUpper.Contains("Timeout Expired".ToUpper) Then
            timeoutcounter = timeoutcounter + 1
            If timeoutcounter > 2 Then
                timeoutcounter = 0
                ErrorLogger.WriteToErrorLog("Exception from method GetPurgeList timeoutCounter > 3 : " + ex.Message, ex.StackTrace)
            Else
------------>  GetPurgeList() 'it gets here after getting the correct list and I am confused why does it come back here again and then finally return nothing
            End If
        Else
            ErrorLogger.WriteToErrorLog("Exception from method GetPurgeList : " + ex.Message, ex.StackTrace)

        End If

    Finally
        If dr IsNot Nothing Then
            dr.Close()
            dr = Nothing
        End If
        If cmd IsNot Nothing Then
            cmd.Dispose()
            cmd = Nothing
        End If
        If cn IsNot Nothing Then
            cn.Close()
            cn.Dispose()
            cn = Nothing
        End If
    End Try
    Return tempList
End Function

Answer 1

You aren't saving the return value of GetPurgeList when called from the exception. My suggestion would be to drop the recursion entirely. Create a loop and extract the datareader portion into it's own function and simply call that when the sproc succeeds.

What I am showing below is NOT what I recommend, but rather intended to show the general FLOW. I'm not sure if it will work without reopening the connection. If I was doing this for myself, I'd put just about everything in the GetPurgeList into a loop that terminated after X iteration or tempList is not Nothing (it would actually be easier to write it that way, but I hope this makes the IDEA clearer).

Private Shared timeoutcounter As Integer = 0
Private Shared dbConnectionString As String = ConfigurationManager.AppSettings("DBConn")

Private Shared Sub ReadFromDataReader(ByVal dr as DataReader, ByRef tempList as DestList)
        If dr IsNot Nothing AndAlso dr.HasRows Then
            tempList = New DestList()
            While dr.Read()
                Dim AcInfo As DestInfo = Nothing
                If dr.GetName(0).ToString() = "errnum" Then
                    ErrorLogger.WriteToErrorLog("Error from stored proc usp_abcd" + CStr(dr("errnum")), dr("errmsg"))
                Else
                    AcInfo = New DestInfo
...
//fill object
...
                       tempList.Add(AccountInfo)
                End If
            End While
        End If

End Sub

Public Shared Function GetPurgeList() As DestList

Dim tempList As DestList= Nothing
Dim ConnectionString As String = dbConnectionString
Dim cn As New SqlConnection(ConnectionString)
Dim cmd As SqlCommand = Nothing
Dim dr As SqlDataReader = Nothing

try
   cn.Open()
    cmd = New SqlCommand
    With cmd
        .Connection = cn
        .CommandType = CommandType.StoredProcedure
        .CommandText = "usp_abcd"
    End With
Catch ex as exception
  return Nothing ' possibly rethrow error here instead, in any case, do not continue 
End Try


Try
        cmd.CommandTimeout = 5
        dr = cmd.ExecuteReader
        ReadFromDataReader(dr, tempList)

Catch ex As Exception
   If ex.Message.ToUpper.Contains("Timeout Expired".ToUpper) Then
        try
           cmd.CommandTimeout = 15
           dr = cmd.ExecuteReader
           ReadFromDataReader(dr, tempList)
        Catch ex1 as Exception
           If ex.Message.ToUpper.Contains("Timeout Expired".ToUpper) Then
              Try
                cmd.CommandTimeout =25
                dr = cmd.ExecuteReader
                ReadFromDataReader(dr, tempList)

              Catch ex As Exception
                ErrorLogger.WriteToErrorLog("Exception from method GetPurgeList : " + ex.Message, ex.StackTrace)

          End Try
          Else
            ErrorLogger.WriteToErrorLog("Exception from method GetPurgeList : " + ex.Message, ex.StackTrace)


          End If
        End Try
    End If

Finally
    If dr IsNot Nothing Then
        dr.Close()
        dr = Nothing
    End If
    If cmd IsNot Nothing Then
        cmd.Dispose()
        cmd = Nothing
    End If
    If cn IsNot Nothing Then
        cn.Close()
        cn.Dispose()
        cn = Nothing
    End If
End Try
Return tempList
End Function

Answer 2

Change...

GetPurgeList() 'it gets here after getting the correct list and I am confused why does it come back here again and then finally return nothing

Into..

tempList = New DestList()

tempList = GetPurgeList()