How to invoke store procedure under BizTalk orchestration flow through WCF-SQL
Some how we might need to interactive with database in the orchestration flow (under BizTalk) for some reasons: write the data back after some specific action or business rule. The story will guide the way step by step.
The the very beginning of starting operating our “Message” inside the orchestration design, we need create message variables and declare which schema that the message should apply to. So create XSD files for our target store procedure.
- Fill-in database setting.
2&3. Find the target procedure(s) and added its.
4. Press OK then system will generate schema xsd for us.
The generated files will appears under solutions as above. Then right click the mouse of the _xxxxxxProdcedure.dbo.xsd then “Generate Instance”:
<?xml version="1.0"?>
<ns0:SP_XXXXXX_Update_AS2_MDN_Time xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
<ns0:MSG_KEY>Example
</ns0:MSG_KEY>
</ns0:SP_XXXXX_Update_AS2_MDN_Time>So we could have example that how to passing XML format to WCF-SQL that with parameter: “MSG_KEY” in our orchestration design view as below.
Inside the orchestration design, we have to prepare the XML format “Message” that looks like example that following definition of XSD file(s). — (1).
UPDATE_MDN_XML = new System.Xml.XmlDocument();// create empty xml format content with no parameter for default
UPDATE_MDN_XML.LoadXml("<ns0:SP_XXXXX_Update_AS2_MDN_Time xmlns:ns0=\"http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo\"><ns0:MSG_KEY></ns0:MSG_KEY></ns0:SP_XXXXX_Update_AS2_MDN_Time>");// MSG_UpdateMDNTime with message type _xxxxxProcedure.dbo.xsd
MSG_UpdateMDNTime = UPDATE_MDN_XML;
Then we just assigned back the store procedure parameter to the “Message” — (2).
// .....
// do some others business logic....
MSG_TARGET(EdiIntAS.MessageId) = SSID;// fill-in the parameter
xpath(MSG_UpdateMDNTime, "/*[local-name()='SP_XXXXX_Update_AS2_MDN_Time' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']/*[local-name()='MSG_KEY' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo']") = SSID;
Passing the “Message” to send port. So the store procedure will be invoked with expect. — (3)
Final step, create Static-One-Way Send Port and configure the settings as below:
Please remember to biding the “Outbound logical Ports” to the “Send Ports” we have created above.
All set and here we go:)
