I have a table that has data which needs to be sent to a service via an API. I have created a Windows Service which starts a timer. Once the timer has elapsed it runs the SUB ProcessRequests(). Here I use EF to get any rows of fresh request. I then have a For Next loop which composes a json string from the various file in a row and fires an api call. All this works up to this point.
I also get a response back from the api and I need to update the corresponding row with this data as well as update the status field and the raisedOn field. Unfortunately the dc.SaveChanges eventually causes an exception and the exception message is a timeout or connection issue. The initial call to get fresh rows had no issues with connectivity and did not time out. I'm thinking that I may have a rowlock issue or something. Any help appreciated.
My code is:
Sub ProcessRequests()
' Define the various objects we're going to use
Dim newServiceNowEvent As ServiceNowEvent = Nothing
Dim de As New AutoTicketDBEntities
Try
' Open a connnection to the database and get any Request that are not COMPLETED
Dim EventQuery = (From r In de.Ticket_Requests
Order By r.Requested
Where r.snEventRaised <> "Completed"
Select r.ID, r.Prefix, r.UniqueID, r.TicketText, r.UserUniqueID, r.UserSysID, r.snEventExceptionText)
' Iterate through any new records
For Each EventRequest In EventQuery
Try
' Create a new instance of the OpsViewEventParser class (EventDetails) and parse the content of the TicketText field
Dim EventDetails As New OpsViewEventParser(EventRequest.TicketText)
' If there has been a parsing error, the Summary property of TicketDetails will contain "Parse Error",
' so we'll throw an exception. The Message property will contain details of the exception encountered
If EventDetails.Summary = "Parse Error" Then
Console.WriteLine("There was a Parsing Error")
Throw New Exception(EventDetails.Summary & " - " & EventDetails.Message)
End If
'Parsing was successful
' Create a new instance of the ServiceNowEvent class and populate its properties from EventDetails
newServiceNowEvent = New ServiceNowEvent
With newServiceNowEvent
.source = EventDetails.Source
.event_class = EventDetails.EventType
.node = EventDetails.Host
.metric_name = EventDetails.SubSource
.type = EventDetails.SubSource
.message_key = ""
.severity = EventDetails.Priority
.description = EventDetails.SmartDescription
.additional_info = EventDetails.CreateOpsViewJson
.time_of_event = EventDetails.EventTime
' Raise the ServiceNow Event
.RaiseServiceNowEvent()
End With
If newServiceNowEvent.SNReturnStatus.StartsWith("Error") Then
Throw New Exception(newServiceNowEvent.SNReturnMessage)
End If
' No Errors so Update the Ticket_Requests table with the snEventStatus (Completed) and a timestamp
' We also save the json we posted to the SN API
Dim dc As New AutoTicketDBEntities
Try
Dim EventToUpdate = (From i In dc.Ticket_Requests
Where i.ID = EventRequest.ID
Select i).FirstOrDefault()
If EventToUpdate IsNot Nothing Then
EventToUpdate.snEventRaised = "COMPLETED"
EventToUpdate.snEventCreated = Now()
EventToUpdate.snEventJSONString = newServiceNowEvent.GeneratedJSONstring.Replace("'", "''")
EventToUpdate.snEventSmartTeam = EventDetails.Team.ToUpper
EventToUpdate.snEventStatus = "EventCreated"
dc.SaveChanges() '####### THIS IS WHERE THE CODE TIMES-OUT ##########
End If
Catch ex As Exception
' Write details of the exception to the Application Log
EventLog.WriteEntry("AutoEvent ServiceNow Service", "The ServiceNow Event Service failed. The Exception reported was " & ex.Message, EventLogEntryType.Error)
Finally
dc.Dispose()
End Try
Catch ex1 As Exception
Dim dc As New AutoTicketDBEntities
Try
Dim EventToUpdate = dc.Ticket_Requests.SingleOrDefault(Function(i) i.ID = EventRequest.ID)
If EventToUpdate IsNot Nothing Then
EventToUpdate.snEventRaised = "PROCESSING"
EventToUpdate.snEventExceptionText = ex1.Message
EventToUpdate.snEventJSONString = newServiceNowEvent.GeneratedJSONstring.Replace("'", "''")
EventToUpdate.snEventFailCount += 1
de.SaveChanges()
End If
Catch ex As Exception
' Write details of the exception to the Application Log
EventLog.WriteEntry("AutoEvent ServiceNow Service", "The ServiceNow Event Service failed. The Exception reported was " & ex.Message, EventLogEntryType.Error)
Finally
dc.Dispose()
End Try
EventLog.WriteEvent("AutoEvent ServiceNow Service", "The ServiceNow Auto Event Service encountered an error raising an incident but failed to update the database with details of the error. The Exception whilst attempting to raise the incident was " & ex1.Message, EventLogEntryType.Error)
End Try
Next
Catch ex As Exception
' Write details of the exception to the Application Log
EventLog.WriteEntry("AutoEvent ServiceNow Service", "The ServiceNow Event Service failed. The Exception reported was " & ex.Message, EventLogEntryType.Error)
Finally
de.Dispose()
End Try
End Sub ' END ProcessRequest
Fixed the issue by making the first query a list and disposing of the connection then looping though the list. Main change is
Dim EventQuery As List(Of Ticket_Requests)
Try
' Open a connnection to the database and get any Request that are not COMPLETED
EventQuery = (From r In de.Ticket_Requests
Order By r.Requested
Where r.snEventRaised <> "Completed"
Select r).ToList()
Console.WriteLine("We have " & EventQuery.Count & " events to process.")
' Iterate through any new records
Catch ex As Exception
' Write details of the exception to the Application Log
Console.WriteLine("AutoEvent ServiceNow Service - The ServiceNow Event Service failed. The Exception reported was " & ex.Message)
Console.WriteLine(ex.InnerException.Message)
Finally
de.Dispose()
End Try
'Then continue with the Loop code....
Hope this helps others.