Asynchronous http handler dealing with .csv files in asp.net

I have developed a live scoring application which is based on the long polling approach, or Comet as they also call it. I have used ASP.NET 4.0 running on IIS 6 (windows 2003 - only two CPUs, which does not help me much with the availability of threads in the pool).

The data is coming in in the form of .csv files that are pasted to the source folder on the web server, which I then import with the use of Microsoft JET 4.0 OleDb Provider, and display utilizing different methods, depending on the part of the application.

The engine of the long polling part relies on the IHttpAsyncHandler. Since it is a live scoring application, the user visits the website, gets a regular response with current data, and on body load sends a new request via jquery ajax to the asynchronous http handler.

This handler then stores the request in the queue, and returns (normally) the thread back to the thread pool. Once this happens, I create a manual reset event, and hold the operation, while a file system watcher object is created and sent to look for changes in the csv data source folder.

Once it fires an onChange event I set the manual reset event and the async operation is permitted to resume with getting the new, refreshed csv files and to respond to the client with fresh data.

This would all be nice if I was not getting errors all the time. In general, very very general way, the application is working, but I have a problem which I cannot quite pin point.

Namely, I am not sure whether the problem is with access to the csv files as they might be locked by the process which brings them in to the server (ftp transfer from the sport venue). Or is it maybe my (ab)use of the IHttpAsyncHandler, or maybe it is just that I don't have enough CPUs and threads (which I find hard to believe, as I have only around 3000 unique visitors every day. I don't know the hour by hour numbers).

Is it possible that a IIS 6 windows 2003 with two CPUs cannot upholad this sort of an application?

here's the errors that I keep getting:

Event Type: Error Event Source: ASP.NET 4.0.30319.0 Event Category: None Event ID: 1325 Date: 20/04/2011 Time: 15:33:14 User: N/A Computer: xxx Description:An unhandled exception occurred and the process was terminated.

Application ID: /LM/W3SVC/1/ROOT Process ID: 5264 Exception: System.Data.OleDb.OleDbException Message: Unspecified error

StackTrace: at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Broker.brCSV.readCSV(String fileName) at SwatchTiming.AsynchOperation.StartAsyncTask(Object workItemState) at System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(Object state) at System.Threading.ExecutionContext.runTryCode(Object userData) at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

And also:

Event Type: Error Event Source: .NET Runtime 4.0 Error Reporting Event Category: None Event ID: 5000 Date: 20/04/2011 Time: 15:33:14 User: N/AComputer: xxx Description:EventType clr20r3, P1 w3wp.exe, P2 6.0.3790.3959, P3 45d6968e, P4 system.data, P5 4.0.0.0, P6 4ba1e064, P7 1ea3, P8 87, P9 system.data.oledb.oledbexception, P10 NIL. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.Data:0000: 63 00 6c 00 72 00 32 00

c.l.r.2.0008: 30 00 72 00 33 00 2c 00 0.r.3.,.0010: 20 00 77 00 33 00 77 00 .w.3.w.0018: 70 00 2e 00 65 00 78 00

p...e.x.0020: 65 00 2c 00 20 00 36 00 e.,. .6.0028: 2e 00 30 00 2e 00 33 00 ..0...3.0030: 37 00 39 00 30 00 2e 00 7.9.0...0038: 33 00 39 00 35 00 39 00 3.9.5.9.0040: 2c 00 20 00 34 00 35 00 ,. .4.5.0048: 64 00 36 00 39 00 36 00 d.6.9.6.0050: 38 00 65 00 2c 00 20 00 8.e.,. .0058: 73 00 79 00 73 00 74 00 s.y.s.t.0060: 65 00 6d 00 2e 00 64 00 e.m...d.0068: 61 00 74 00 61 00 2c 00 a.t.a.,.0070: 20 00 34 00 2e 00 30 00 .4...0.0078: 2e 00 30 00 2e 00 30 00

..0...0.0080: 2c 00 20 00 34 00 62 00 ,. .4.b.0088: 61 00 31 00 65 00 30 00 a.1.e.0.0090: 36 00 34 00 2c 00 20 00 6.4.,. .0098: 31 00 65 00 61 00 33 00 1.e.a.3.00a0: 2c 00 20 00 38 00 37 00 ,. .8.7.00a8: 2c 00 20 00 73 00 79 00 ,. .s.y.00b0: 73 00 74 00 65 00 6d 00 s.t.e.m.00b8: 2e 00 64 00 61 00 74 00 ..d.a.t.00c0: 61 00 2e 00 6f 00 6c 00 a...o.l.00c8: 65 00 64 00 62 00 2e 00 e.d.b...00d0: 6f 00 6c 00 65 00 64 00 o.l.e.d.00d8: 62 00 65 00 78 00 63 00 b.e.x.c.00e0: 65 00 70 00 74 00 69 00 e.p.t.i.00e8: 6f 00 6e 00 20 00 4e 00 o.n. .N.00f0: 49 00 4c 00 0d 00 0a 00 I.L.....

and...

Event Type: Error Event Source: .NET Runtime Event Category: None Event ID: 1026 Date: 20/04/2011 Time: 15:34:26 User: N/A Computer: xxx Description:Application: w3wp.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception.Exception Info: System.Data.OleDb.OleDbException

Stack: at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Broker.brCSV.readCSV(System.String) at [ProjectNamespace].AsynchOperation.StartAsyncTask(System.Object) at System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(System.Object) at System.Threading.ExecutionContext.runTryCode(System.Object) at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode, CleanupCode, System.Object) at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object) at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

A few more pieces of info for the end. I have tried to fine tune the IIS 6 but it did not seem to help out too much.

So does anybody have an idea what the heck is going on, and why my site is crashing every five minutes?

EDIT: Here's my code in the handler, hope this helps

The BeginProcessRequest is as follows (other than that I am only setting the IsReusable to false):

Public Function BeginProcessRequest( _
    ByVal context As System.Web.HttpContext, _
    ByVal cb As System.AsyncCallback, _
    ByVal extraData As Object) _
    As System.IAsyncResult _
    Implements System.Web.IHttpAsyncHandler.BeginProcessRequest

    Dim asynch As New AsynchOperation(cb, context, extraData)
    asynch.StartAsyncWork() 

    Return asynch
End Function

and then the AsynchOperation class which implements IAsyncResult:

Class AsynchOperation
Implements IAsyncResult
Private _completed As Boolean
Private _state As [Object]
Private _callback As AsyncCallback
Private _context As HttpContext
Private mre As New ManualResetEvent(False)
Dim br As New Broker.brCSV
Dim brLiveGames As New Broker.brLiveGames

ReadOnly Property IsCompleted() As Boolean _
        Implements IAsyncResult.IsCompleted
    Get
        Return _completed
    End Get
End Property

ReadOnly Property AsyncWaitHandle() As WaitHandle _
        Implements IAsyncResult.AsyncWaitHandle
    Get
        Return Nothing
    End Get
End Property

ReadOnly Property AsyncState() As [Object] _
        Implements IAsyncResult.AsyncState
    Get
        Return _state
    End Get
End Property

ReadOnly Property CompletedSynchronously() As Boolean _
        Implements IAsyncResult.CompletedSynchronously
    Get
        Return False
    End Get
End Property

Public Sub New(ByVal callback As AsyncCallback, _
        ByVal context As HttpContext, _
        ByVal state As [Object])
    _callback = callback
    _context = context
    _state = state
    _completed = False
End Sub

Public Sub StartAsyncWork()
    ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf StartAsyncTask), Nothing)
End Sub

Private Sub StartAsyncTask(ByVal workItemState As [Object])


    Dim fsw As New FileSystemWatcher("D:\ClientRoot\Swatchtiming\bv\ReadData\")
    fsw.NotifyFilter = NotifyFilters.LastWrite
    AddHandler fsw.Changed, AddressOf OnChanged
    fsw.EnableRaisingEvents = True
    fsw.IncludeSubdirectories = False

    Dim aTimer As New System.Timers.Timer
    AddHandler aTimer.Elapsed, AddressOf OnTimerChanged
    aTimer.Interval = 60000
    aTimer.Enabled = True
    mre.WaitOne()

    Dim rdr As OleDbDataReader
    Dim i As Integer = 0
    Dim eventName As String = ""
    Dim dsFiles As DataSet = brLiveGames.getFileNameWithEventTitle()
    Dim teamClass As String = "TeamA"
    Dim serveIndicator As String = ""
    Dim serveImage As String = ""
    Dim serveSpeed As String = ""
    Dim fileName As String = ""
    Dim fileNumber As String = ""
    Dim matchID As String = ""
    Dim venueLocation As String = ""
    Dim set1, set2, set3 As String

    For i = 0 To Convert.ToInt16(dsFiles.Tables(0).Rows.Count) - 1
        If eventName <> dsFiles.Tables(0).Rows(i).Item("EventTitle") Then
            eventName = dsFiles.Tables(0).Rows(i).Item("EventTitle")
            _context.Response.Write("<div class='eventTitle'>" & eventName.ToString() & " <span class='bracketLink'>- <a href='Brackets.aspx?Brackets=" & dsFiles.Tables(0).Rows(0).Item("BracketsFile") & "'>View brackets</a></span></div>")
        End If
        rdr = br.readCSV(dsFiles.Tables(0).Rows(i).Item("fileName"))

        _context.Response.Write("<div class='matchView'>")
        While (rdr.Read)
            matchID = rdr.Item("Current_Match_Index")
            If venueLocation <> "" Then
                venueLocation = ""
            Else
                venueLocation = br.getVenueLocation(matchID)
                _context.Response.Write("<div class='matchTitle'>" + venueLocation + "</div>")
            End If
            set1 = IIf(IsDBNull(rdr.Item("SET_1")), "&nbsp;", rdr.Item("SET_1"))
            set2 = IIf(IsDBNull(rdr.Item("SET_2")), "&nbsp;", rdr.Item("SET_2"))
            set3 = IIf(IsDBNull(rdr.Item("SET_3")), "&nbsp;", rdr.Item("SET_3"))
            _context.Response.Write("<div class='" & teamClass & "'>")
            If teamClass <> "TeamB" Then
                teamClass = "TeamB"
            Else
                teamClass = "TeamA"
            End If
            serveIndicator = IIf(IsDBNull(rdr.Item("Service_Indicator")), "", rdr.Item("Service_Indicator"))
            If serveIndicator = "" Then
                serveImage = "<img src='images/css/serveIndicatorNone.png' alt='#' width='14' height='14' />"
            Else
                serveImage = "<img src='images/css/serveIndicator.png' alt='#' width='14' height='14' />"
            End If
            serveSpeed = IIf(IsDBNull(rdr.Item("Serve_Speed")), "&nbsp;", "Serve: " & rdr.Item("Serve_Speed") & " km/h")
            _context.Response.Write("<div class='flag'><img src='images/flags/" & rdr.Item("NOC") & ".jpg' alt='" & rdr.Item("NOC") & "' width='22' height='14' /></div><div class='NOC'>" & rdr.Item("NOC") & "</div><div class='serveIndicator'>" & serveImage & "</div><div class='teamName'>" & rdr.Item("Short_Team_Name") & "</div><div class='set1'>" & set1 & "</div><div class='set2'>" & set2 & "</div><div class='set3'>" & set3 & "</div><div class='serveSpeed'>" & serveSpeed & "</div>")
            _context.Response.Write("</div>")
        End While
        _context.Response.Write("</div>")
        rdr.Close()
    Next
    fsw.Dispose()
    dsFiles.Dispose()
    _context.Response.End()

    _completed = True
    _callback(Me)

End Sub

Private Sub OnChanged(ByVal sender As Object, ByVal e As FileSystemEventArgs)
    mre.Set()
End Sub

Private Sub OnTimerChanged(ByVal sender As Object, ByVal e As ElapsedEventArgs)
    mre.Set()
End Sub

End Class

Edit #2: The code for the Broker.brCSV.readCSV(fileName)

Public Function readCSV(ByVal fileName As String) As OleDbDataReader
    Dim rdr As OleDbDataReader = Nothing
    Dim folderName = ("FolderName")
    Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folderName & ";Extended Properties='text;HDR=Yes;FMT=Delimited';Mode=3"

    Dim cn As New OleDb.OleDbConnection(cnString)
    Dim cm As New OleDb.OleDbCommand("Select * from " & fileName, cn)
    cm.Connection.Open()
    rdr = cm.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

    Return rdr
End Function

Please observe the ending of the connection string, specifically the Mode parameter. The msdn states that this is how you specify the file access permissions, but it could be that I did not interpret the instructions in the right way... Namely, mode=3 is supposed to specify the file access as read/write but I'm not sure if it works.

EDIT #3: The new Broker.brCSV.readCSV() throws an InvalidOperationException

As per the suggestions of the kind helper Smudge202 I have altered the code of the Broker.brCSV.readCSV method as follows:

Public Function readCSV(ByVal fileName As String) As OleDbDataReader
    Dim folderName = ("Folder Name")
    Dim cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folderName & ";Extended Properties='text;HDR=Yes;FMT=Delimited';Mode=3"

    Using cn As New OleDb.OleDbConnection(cnString)
        Using cm As New OleDb.OleDbCommand("Select * from " & fileName, cn)
            cm.Connection.Open()
            Dim rdr As OleDbDataReader = cm.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
            Return rdr
        End Using
    End Using

End Function

However, when tested this code caused the following errors:

Event Type: Error Event Source: ASP.NET 4.0.30319.0 Event Category: None Event ID: 1325 Date: 22/04/2011 Time: 08:46:33 User: N/A Computer: EUW0002184 Description: An unhandled exception occurred and the process was terminated.

Application ID: /LM/W3SVC/1/ROOT

Process ID: 6408

Ex开发者_如何学运维ception: System.InvalidOperationException

Message: Invalid attempt to call Read when reader is closed.

StackTrace: at System.Data.OleDb.OleDbDataReader.Read() at SwatchTiming.AsynchOperation.StartAsyncTask(Object workItemState) at System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(Object state) at System.Threading.ExecutionContext.runTryCode(Object userData) at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

as well as:

Event Type: Error Event Source: .NET Runtime Event Category: None Event ID: 1026 Date: 22/04/2011 Time: 08:47:53 User: N/A Computer: EUW0002184 Description: Application: w3wp.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception. Exception Info: System.InvalidOperationException Stack: at System.Data.OleDb.OleDbDataReader.Read() at SwatchTiming.AsynchOperation.StartAsyncTask(System.Object) at System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(System.Object) at System.Threading.ExecutionContext.runTryCode(System.Object) at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode, CleanupCode, System.Object) at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object) at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

So, when the handler attempts to use the new readCSV method I get these errors... Any further suggestions? :) Thank you Smudge, and thanks everyone else!

---

In addition to one or two comments I've added so far.... =)

As you've noticed in your 3rd edit, once a reader has been disposed, you are unable to fetch the data from it.

The idea is that you create and open your connection. Fetch the data as early as possible when the connection is open, and then dispose any resources that were used once you have the data.

In your case, you are opening and closing the connection within the readCSV function, then passing the closed reader back to "StartAsyncTask". What you could do perhaps, is slightly refactor... Instead of using the OleDbDataReader you could use an OleDbDataAdapter. Using the adapter you can call the Fill method to populate a dataset.

Once a dataset has been populated, it is in memory. You can close the adapter, close the connection, dispose of both ('using' statements) and pass the dataset back to your StartAsync method?

Comment on here if you need any examples of this.

Good luck!

EDIT:

A quick note on the multithreading work you're doing...

Regarding the IIS settings, if you are running your website on more than one process be aware that at some stage you will likely have 2 processes (or more) sat awaiting the file system watcher. When the FSW detects a change it will notify both of your processes; in an unpredictable order, but likely in quick succession, which in turn will cause two seperate threads to start reading the files. You may encounter issues at this stage when two threads request the Jet Provider to open the same file(s) at the same time. Ensure you have plenty of exception catching logic in here to help.

You might even need to consider the use of mutexes if this is the case for you, to allow one process at a time to process results, but I rarely like to encourage those.

On the note of thread exceptions, take a look at this article which I believe still holds true in IIS7.5/.Net 4. Be very careful with your worker threads, exceptions can take down the website if not caught.