Article Series on BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns

Written by

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.

Series Summary
BizTalk and WCF: Part I, Operation Patterns	Get the source code!
BizTalk and WCF: Part II, Security Patterns
BizTalk and WCF: Part III, Transaction Patterns
BizTalk and WCF: Part IV, Attachment Patterns
BizTalk and WCF: Part V, Publishing Operations Patterns	Get the source code!
BizTalk and WCF: Part VI, Publishing Advanced Service Patterns
BizTalk and WCF: Part VII, About the BizTalk Adapter Pack	Get the source code!
BizTalk and WCF: Part VIII, BizTalk Adapter Pack Service Model Patterns
BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns

Technorati Tags: BizTalk, WCF

Comments

19 responses to “Article Series on BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns”

May 8, 2008

Nick Heppleston

Richard,
Excellent series, the community has certainly benefited from the hardwork!

Cheers, Nick.

Reply
May 8, 2008

Thiago Almeida

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

Reply
May 8, 2008

Richard Seroter

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.

Reply
May 12, 2008

Thiago Almeida

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

Reply
May 15, 2008

BizTalk WCF Adapter Pack Articles on TopXML.com « Connected Thoughts

[…] If you work with BizTalk and are interested in WCF the articles are a *must read*. His post “Article Series on BizTalk and WCF: Part IX, BizTalk Adapter Pack BizTalk Patterns” has the links to each […]

Reply
August 5, 2008

Article Series – BizTalk and 2006 – Riaan's Blog

[…] https://seroter.wordpress.com/2008/05/08/article-series-on-biztalk-and-wcf-part-ix-biztalk-adapter-pa… […]

Reply
August 17, 2008

Excellent BizTalk/WCF Resources by « Miguel’s BizTalk Blog

[…] https://seroter.wordpress.com/2008/05/08/article-series-on-biztalk-and-wcf-part-ix-biztalk-adapter-pa… […]

Reply
February 10, 2009

Matt Holt

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?

Reply
February 10, 2009

Richard Seroter

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?

Reply
February 11, 2009

Matt Holt

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.

Reply
1. February 11, 2009
  
  Richard Seroter
  
  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.
  
  Reply
February 11, 2009

Matt Holt

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?

Reply
1. January 21, 2010
  
  murari
  
  Hi,
  
  Please can you let me know which schemas I should set for
  Envelope and Documents schemas for xml dissambler.
  
  Thanks
  Murari
  
  Reply
January 22, 2010

Richard Seroter

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/

Reply
March 8, 2010

Durga

Richard,

Can you please answer Thiago’s question on multiple tables inserts within the same transaction using WCF-SQL adapter?

Thanks,
Durga

Reply
March 9, 2010

Richard Seroter

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.

Reply
July 6, 2011

chinna

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.

Reply
July 14, 2011

Chinna

Links are not opening allow to open the site.

Reply
1. July 17, 2011
  
  Richard Seroter
  
  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/).
  
  Reply