While working on a BTS 2006 solution - I decided to use the SQL Adapter to call a stored Proc to update data.
While the SQL Adapter wizard is OK, there's no real reason to use it. I usually delete everything it creates, apart from the Schema for namespace samples. The Namespaces you specify through the wizard is there mainly for the SQL Adapter to figure out where the bits are for it to process, and where return results should be inserted into......
In alot of solutions I build, I usually have a single generic SQL Update Orchestration, not an Orch, Schema + Port for each type of SQL action required.
The trick to all this is how the SQL Adapter handles the messages sent to it. More details is found in the SQLXML documentation.
The paper back version:
Let's say I have two tables and a Stored Proc that I want to use within the SAME DB (if I want to talk to different DB's then we'd need to create a separate message for the different DB's to update due to the fact that the physical SQL Port (whether it be 'dynamic' or physical) e.g. SQL://ServerName/DB.......
Table A: PacMan Players
Fields - Name, email
Table B: PacMan Scores
Fields - email, score
Stored Proc: UpdateScores
Params: email, score, gametime
If these three were in the same DB here's the message(s) that you'd need to send to the SQL Adapter (could even be via CBR and not ALWAYS an Orch).
e.g. a sample message for stored procs.
<sqlRequest xmlns='http://micksdemos.sql'>
<Updates>
<UpdateScores email='jackiechan@j.com' score='54' gametime='1200' />
<AnotherStoredProc p1='2' p2='aaa' p3='....' />
</Updates>
<Results>
<!-- **** Set to be ANY element here, with 'skip' processing set via the schema **** -->
</Results>
</sqlResults>
e.g. a sample message for tables (further details on this message structure can be obtained from SQLXML Documents)
<sqlRequest xmlns='http://micksdemos.sql'>
<sync>
<after>
<PacManScores email='jackiechan@j.com' score='22000' />
<PacManPlayers Name='mick' email='mick@b.com' />
...
</after>
</sync>
</sqlResults>
Now the interesting thing upon the results being returned for the called Stored Procs
We sent down batches of 400 updates to be performed via the stored proc method, and the results were supprising!!!!
We got a message back via one of the several Two-Way SQL Ports defined (each talking to a different database, being activated via CBR)
The return results was a Multi-part message with 400 parts!!!!!! In this case I was waiting for the return message within an Orchestration and then carrying on (mainly for BAM purposes to capture timings, average call times etc)
Do you know how hard it was to find an appropriate message type?????? If I made a multi-part message type with 5 parts it's not 400. If I made one with 400 parts (each part was a type of ANY) then I'm sure we'd have a batch in the future with 401 updates...boom! blows up.
So my challenge was to find the appropriate message type for this return message.....needless to say "I'm still looking"
I tried
(1) XLANGMessage - not serializable and bts wont compile in the IDE. This is the most logical cause then I could just go through the parts grabbing each result message.
(2) XLANGPart - long shot, individual part of a message, but also if a Message if declared as ANY type then this is the .NET Message Type that represents it behind the scenes.
(3) ANY - Compiled and run, error when the results message is returned, as the ANY type is still dealing with a single part message
(4) XMLDocument - yeah right! Sort of the one that you cover your eyes, run the test and peep through your fingers looking at the screen to see if it worked....or more like *hoped' it worked :)
Solution: Create a simple Custom Pipeline Component to Consolidate the Return parts
The Orchestration is fine to go on continuing processing.
The thing that stumped me is that I send in a Batch within a Single XML Document, why dont I get that as a response??
I could imagine when sending a single update this problem never occurs. (and it hasnt in the past)
Here's the custom pipeline component - this one's in VB.NET as per the client's coding standards on this.
(I use the VirtualStream found in the SDK) - this is not production ready code. Further stress testing needed.
Here's a snippet showing the execute method (BTSHelper.VirtualStream - is the VirtualStream class from the BTS 2006 SDK)
#Region "IComponent Members"
Public Function Execute(ByVal pContext As IPipelineContext, ByVal pInMsg As IBaseMessage) As _
IBaseMessage Implements IComponent.Execute
Try
Dim msgReturn As IBaseMessage = InternalMyExecute(pContext, pInMsg)
Return (msgReturn)
Catch ex As Exception
Throw ex
End Try
End Function
Private Function InternalMyExecute(ByVal pc As IPipelineContext, ByVal inMsg As IBaseMessage) As IBaseMessage
Dim outMsg As IBaseMessage = Nothing
Dim outPt As IBaseMessagePart = Nothing
Dim outStream As BTSHelper.VirtualStream = Nothing
Dim sw As StreamWriter = Nothing
Try
If (inMsg.PartCount > 1) Then 'combine all the parts into one - painful return results from SQL.
outMsg = pc.GetMessageFactory().CreateMessage()
outMsg.Context = inMsg.Context
outPt = pc.GetMessageFactory().CreateMessagePart()
outStream = New BTSHelper.VirtualStream()
sw = New StreamWriter(outStream)
sw.Write("<{0}>", _documentRootElement)
For i As Integer = 0 To inMsg.PartCount - 1
Dim sptName As String = String.Empty
Dim s As String = GetMessagePartAsString(inMsg.GetPartByIndex(i, sptName))
sw.Write(s)
Next
sw.Write("</{0}>", _documentRootElement)
sw.Flush()
' we DONT want to close the stream i.e. sw.close()
outStream.Seek(0, SeekOrigin.Begin)
outPt.Data = outStream
outMsg.AddPart("Body", outPt, True)
Return (outMsg)
Else 'single part
Return (inMsg)
End If
Catch ex As Exception
inMsg.SetErrorInfo(ex) ' the inMessage is the one that gets reported on in BizTalk within the pipeline
EventLog.WriteEntry(_EVENTLOG_SOURCE, "SQL Combiner Exception Internal Execute- "
+ ControlChars.CrLf + ControlChars.CrLf + ex.Message, EventLogEntryType.Error)
Throw ex
Finally
End Try
End Function
Private Function GetMessagePartAsString(ByVal pt As IBaseMessagePart) As String
Dim xdoc As XmlDocument = Nothing
Try
xdoc.Load(pt.GetOriginalDataStream())
Return (xdoc.DocumentElement.OuterXml)
Catch ex As Exception
Throw ex
Finally
xdoc = Nothing
GC.Collect()
End Try
End Function
Public Sub CopyStream(ByVal src As Stream, ByVal dst As Stream)
Try
&nb