UPDATE: I have since moved these articles to my own blog and they can be found here.
Whew. My 9th and final article for TopXML.com on integrating BizTalk Server and WCF is now published. This one looks at the BizTalk Adapter Pack and how to consume the Oracle adapter from inside BizTalk Server. I demonstrated how to insert multiple records at once, call stored procedures, build a database polling solution, and how to call the Adapter Pack from an orchestration using code.
20,500+ words and 178 screenshots later, I definitely learned a lot about WCF (and Oracle) by writing this series of articles. I found the security and attachment topics to be challenging, the transaction topics to be quite interesting, and the BizTalk Adapter Pack to be quite compelling. The WCF integration in BizTalk Server 2006 R2, while not as rich as it could be, is actually pretty darn well built and thought out.
When I started out on this series, I figured that writing about BizTalk/WCF and then the BizTalk Adapter Pack would be somewhat “niche” for the majority of developers, but, hopefully this helps people down the line as they begin to really investigate and evaluate these technologies together.
Richard Seroter's Architecture Musings 
Richard,
Excellent series, the community has certainly benefited from the hardwork!
Cheers, Nick.
Richard, great series of articles!
On your Order and Order Items insert scenario, how would you insert an order id generated by Oracle into the items table (like the order to order items scenario in SQL Server updategram where we use at-identity).
Also, can you select multiple tables you want it to generate the metadata for or do you have to select one at at time?
Thanks,
Thiago Almeida
Hey Thiago,
You mean that the “order id” column is a generated column? Haven’t tried that yet.
As for multiple tables, YES, you can actually choose operations from many tables at once.
Hi Richard,
Yes, I was wondering how it would be possible to insert an order and its order items using only one call to the LOB adapter (in the same transaction) and having the auto generated column be the link between the two tables.
I’ll have to start playing around with these adapters soon!
Thanks, Thiago Almeida
I’ve got a question about the completion of Scenario 3. I’m having trouble creating the enveloping, even after reading your posting about the SQL adapter.
Any advice or examples you know of?
Hi Matt,
I don’t think there was an envelope in Scenario #3. You’re talking about the article this post references, the one using the Oracle adapter, right?
Silly me, this is what happens when I’ve got 4 windows with your blog on them open…
Anyhow, yes… I was asking about the oracle adapter post where you stopped short of making it “enterprise ready.”
If you want to delete these comments, I’ll repost on the correct posting.
I’ll take it as a compliment that you have 4 blog posts open at once 😉
Yes, I see the text that you are referring to. Have you made the envelope schema yet? Did you take the original schema, mark it as an envelope, and then set the body xpath? Then you’d need to create a copy of that schema, rip out everything but the innermost POLLINGSTMTRECORD. Hopefully then, the envelope would debatch and you’d end up with that secondary schema type for each output message.
Very similar to the solution I finally worked out:
1. Set the generated schema to “Envelope=yes”
2. Set the XPath.
3. Set the root node to POLLINGSTMT (neccessary, or the message doesn’t route). I also set the Attribute FormDefault to Unqualified like you did in your SQL example.
4. Set max occurs to 1 on POLLINGSTMTRECORD under POLLINGSTMT
5. DELETE the POLLINGSTMT record (high level, up with the POLLINGSTMT) in the generated schema. This conflicts with the new schema created next.
6. Create a new schema and “include” the generated schema. Note: You must copy the namespace of the original schema.
7. Create a new record in the schame called “POLLINGSTMTRECORD”
8. Set this new record as the root reference and it’s datatype to POLLINGSTMTRECORD.
Now the xml pipeline will debatch successfully.
I’ve had comments on forum posts that I should be careful setting the root reference, but it was the ONLY way I could get the error message that a schema for “POLLINGSTMTRECORD” was not found that aligned this solution with your SQL post.
suggestions / Feedback?
Hi,
Please can you let me know which schemas I should set for
Envelope and Documents schemas for xml dissambler.
Thanks
Murari
Murari, you referring to the debatching of the database content? If so, then the POLLINGSTMT root schema would be marked as an envelope with another schema needed which held the POLLINGSTMTRECORD node which would get debatched. Hopefully similar to DB-debatching posts I’ve done in the past … https://seroter.wordpress.com/2007/01/03/debatching-inbound-messages-from-biztalk-sql-adapter/
Richard,
Can you please answer Thiago’s question on multiple tables inserts within the same transaction using WCF-SQL adapter?
Thanks,
Durga
Durga,
Haven’t tried it yet with taking the auto-generated ID returned from one table and making that a key in the second table while keeping it all under the same transaction.
I searched so many applications but i did not find anything,luckily i got this links It is very useful to me.thanks to richards.
Links are not opening allow to open the site.
Hi there, see the note at the top where I’ve mentioned that these articles are now hosted on this blog (https://seroter.wordpress.com/biztalk-wcf-article-series/).