XML Web Service with Datareader Issues

[rikleo2001]

Hello Experts.

I need your help, I am new to XML web service, and I have to complete one task.

Here is current situation.

1. I have web service running to recieve SOAP message from an external service and send message reciepts. This service Recieve XML as object and transforms into XML file and then imports that XML file into SQL Server database and then sends SOAP message reciept. I am not developer of that code, it is huge thousands of lines of code.

Now problem is when XML files recieved from multiple cleint at the same time, then importing of data process dies saying (Data reader already open).
************************************
This is my code behind ASMX file

Public
 
Function ProcessMessage(ByVal MESSAGE As wgsserver.MESSAGE) As <System.Xml.Serialization.XmlElementAttribute("MESSAGE", [Namespace]:="http://ws.abc.com/fieldservices/ext/message-receipt.xsd")> wgsserver.MessageType 
 
Dim returnMsg As wgsserver.MessageType 
 
Try 
 
'log the request message 
 
Dim requestPath As String = Util.logRequestMessage(MESSAGE.Content.WorkOrder.Ref, MESSAGE) 
 
If requestPath Is Nothing Then 
returnMsg = ResponseMessage.getMessageType(
 
False, "logRequestMessage Failed.") 
Util.logResponseMessage(MESSAGE.Content.WorkOrder.Ref, returnMsg)
 
 
Return returnMsg 
 
End If 
 
'validate the request message against svreq.xsd 
 
Dim errorMessage As String = Util.validateRequestXML(requestPath) 
 
If errorMessage = "" Then 
 
'Util.errorLog("No errors in validation") 
 
'if there is no error 
 
If Util.ContainValidSenderAndRecipient(MESSAGE) Then 
 
'if sender and recipient information are correct 
 
'save the data into database. 
 
'return success message-recipient 
 
Dim db As New DataBase 
db.insert_table_MESSAGE(MESSAGE)
 
 
If DataBase._errorMessage <> "" Then 
returnMsg = ResponseMessage.getMessageType(
 
False, DataBase._errorMessage) 
Util.logResponseMessage(MESSAGE.Content.WorkOrder.Ref, returnMsg)
 
 
Return returnMsg 
 
End If 
db.close()
 
returnMsg = ResponseMessage.getMessageType(
 
True, "") 
Util.logResponseMessage(MESSAGE.Content.WorkOrder.Ref, returnMsg)
 
 
Return returnMsg 
 
Else 
 
'if not correct sender or recipient message 
 
'create error log on server 
 
'return failure message 
Util.errorLog("Invalid Sender or Recipient")
 
returnMsg = ResponseMessage.getMessageType(
 
False, "Invalid Sender or Recipient") 
Util.logResponseMessage(MESSAGE.Content.WorkOrder.Ref, returnMsg)
 
 
Return returnMsg 
 
End If 
 
Else 
Util.errorLog("xmlValidation:" & errorMessage)
 
returnMsg = ResponseMessage.getMessageType(
 
False, errorMessage) 
Util.logResponseMessage(MESSAGE.Content.WorkOrder.Ref, returnMsg)
 
 
Return returnMsg 
 
End If 
 
Catch ex As Exception 
Util.errorLog("General: " & ex.Message & vbCrLf & ex.StackTrace)
 
returnMsg = ResponseMessage.getMessageType(
 
False, ex.Message) 
Util.logResponseMessage(MESSAGE.Content.WorkOrder.Ref, returnMsg)
 
 
Return returnMsg 
 
End Try 
 
End Function 

*********************

Now you could see one line which imports the db class (db.insert_table_MESSAGE(MESSAGE)) this actually a general VB class and perform importing process of data using MESSAGE object which is XML file. It works fine, if messages sent one by one, but in case of concurrent session it generates the following error message.

"There is already an open DataReader associated with this Connection which must be closed first."

****************************************
This is the code in Database Class

Public Function insert_table_MESSAGE(ByVal obj_MESSAGE As WGS.wgsserver.MESSAGE) As Integer
 
 
If obj_MESSAGE Is Nothing Then 
 
Return -1 
 
End If 
 
Dim databaseString As String 
 
Dim reader As SqlDataReader 
 
Try 
 
Dim id_EnvelopeType As Integer = insert_table_EnvelopeType(obj_MESSAGE.Envelope) 
 
Dim id_Content As Integer = insert_table_Content(obj_MESSAGE.Content) 
databaseString = "insert into table_MESSAGE (col_Envelope, col_Content, col_MsgType, col_Version) values (" & id_EnvelopeType & ", " & id_Content & ", '" & FSSConversion(obj_MESSAGE.MsgType) & "', '" & FSSConversion(obj_MESSAGE.Version) & "')"
 
 
Dim command As New SqlCommand(databaseString) 
command.Connection = conn
 
command.ExecuteNonQuery()
 
databaseString = "select id from table_MESSAGE where col_Envelope=" & id_EnvelopeType & " and col_Content=" & id_Content & " and col_MsgType='" & FSSConversion(obj_MESSAGE.MsgType) & "' and col_Version='" & FSSConversion(obj_MESSAGE.Version) & "'"
 
 
Dim command2 As New SqlCommand(databaseString) 
command2.Connection = conn
 
reader = command2.ExecuteReader()
 
reader.Read()
 
 
Dim result As Integer = reader.GetInt32(0) 
reader.Close()
 
 
Return result 
 
Catch ex As Exception 
_errorMessage = ex.Message
 
 
If Not reader Is Nothing Then 
reader.Close()
 
 
End If 
Util.errorLog("ERROR INSERT: " & ex.Message & vbCrLf & ex.StackTrace)
 
 
End Try 
 
Return -1 
 
End Function 

*********************************************


I need your expert advice. I need to make that Web Service Working for Multiple Client concurrently.
Any help please...


Thanks in advance.

[dorinbogdan]

Welcome to TheScripts TSDN...

Since the problem is related to ADO.Net I will move this thread to .Net forum.

Dorin.

[dorinbogdan]

One suggestion: check that the conn variable to not be defined as Shared (static).

[rikleo2001]

Sorry about that.

I checked that and it is declared with in class as
"Private Shared conn As SqlConnection"

Is that going to be an issue?

Thanks for your help.

[dorinbogdan]

Just remove the Shared modifier, it should have no negative effect, since it's a private variable.
Then try few tests to make sure that it's working as desired.

Dorin.