dotnet xml: I'm trying to do some accouting with a .NET web service. I would like to
simply log the request and response times for each web service method
invocation.

The LogRequest function simply INSERTs request data and the LogResponse
UPDATES the request data based on the RequestId value.

The problem here is timing. It appears that the thread pool is processing
these out of order (infrequently). Therefore I do not get the UPDATEs as
expected since the record does not exist for the update to occur. This
happens infrequently, but it does happen as evidenced in sql traces.

The INSERT statement is not that costly and the actual biz logic for the
method runs in between these calls. As such, I didn't expect timing to be an
issue.

Any suggestions?

Here is the sample code I've been working with:

Public Overrides Sub ProcessMessage( _
ByVal message As System.Web.Services.Protocols.SoapMessage)
Try
Select Case message.Stage
Case SoapMessageStage.BeforeDeserialize

Case SoapMessageStage.AfterDeserialize

Dim params As LogRequestParams
params.DBConnStr = _ConnStr
params.RequestTime = System.DateTime.Now()
params.RequestId = Me._RequestId
ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf
AccountLog.LogRequest), params)

Case SoapMessageStage.BeforeSerialize

Case SoapMessageStage.AfterSerialize
If Me._LogResponse Then
Dim params As LogResponseParams
params.DBConnStr = _ConnStr
params.RequestId = Me._RequestId
params.ResponseTime = System.DateTime.Now()
ThreadPool.QueueUserWorkItem( _
New WaitCallback(AddressOf
AccountLog.LogResponse), params)
End If
End Select

[quoted text, click to view]

This is an SQL issue. The update stored procedure you're using for logging
needs to be more robust because it may get called prior to a row's insertion.

I'd recommend checking the @@ROWCOUNT variable in the stored
procedure immediately after the UPDATE statement. It's sufficient to
make the update stored procedure simply return this number of rows
affected, 0 indicating error. When LogResponse receives 0 rows affected,
it should wait for a little while (c.f., WaitHandle) and then try again ... up
until some designated timeout period.

- - - update_log.sql (un-tested)
CREATE PROC update_log
@request_id int,
@response_time datetime
AS

-- Execute the UPDATE statement.
--
UPDATE T_ResponseTimes SET ResponseTime = @response_time
WHERE RequestId = @request_id

-- Return the count of rows affected by the preceding statement.
--
SELECT @@ROWCOUNT

GO
- - -


Derek Harmon