New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Exclusive offer: get 50% off this eBook here
SOA Patterns with BizTalk Server 2009

SOA Patterns with BizTalk Server 2009 — Save 50%

Implement SOA strategies for Microsoft BizTalk Server solutions with this book and eBook

$35.99    $18.00
by Richard Seroter | April 2009 | .NET BPEL Microsoft SOA Web Services

In this article by Richard Seroter, you will learn what's WCF SQL Adapter, how to go about executing composite transactions, polling for data, and using SQL Server Query notifications.

Do not go where the path may lead; go instead where there is no path and leave a trail.
-Ralph Waldo Emerson

Many of the patterns and capabilities shown in this article are compatible with the last few versions of the BizTalk Server product. So what's new in BizTalk Server 2009?`

BizTalk Server 2009 is the sixth formal release of the BizTalk Server product. This upcoming release has a heavy focus on platform modernization through new support for Windows Server 2008, Visual Studio.NET 2008, SQL Server 2008, and the .NET Framework 3.5. This will surely help developers who have already moved to these platforms in their day-to-day activities but have been forced to maintain separate environments solely for BizTalk development efforts.

Lets get started.

What is the WCF SQL Adapter?

The BizTalk Adapter Pack 2.0 now contains five system and data adapters including SAP, Siebel, Oracle databases, Oracle applications, and SQL Server. What are these adapters and how are they different than the adapters available for previous version of BizTalk?

Up until recently, BizTalk adapters were built using a commonly defined BizTalk Adapter Framework. This framework prescribed interfaces and APIs for adapter developers in order to elicit a common look and feel for the users of the adapters. Moving forward, adapter developers are encouraged by Microsoft to use the new WCF LOB Adapter SDK. As you can guess from the name, this new adapter framework, which can be considered an evolution of the BizTalk Adapter Framework, is based on WCF technologies.

All of the adapters in the BizTalk Adapter Pack 2.0 are built upon the WCF LOB Adapter SDK. What this means is that all of the adapters are built as reusable, metadata-rich components that are surfaced to users as WCF bindings. So much like you have a wsHttp or netTcp binding, now you have a sqlBinding or sapBinding. As you would expect from a WCF binding, there is a rich set of configuration attributes for these adapters and they are no longer tightly coupled to BizTalk itself. Microsoft has made connection a commodity, and no longer do organizations have to spend tens of thousands of dollars to connect to line of business systems like SAP through expensive, BizTalk-only adapters.

This latest version of the BizTalk Adapter Pack now includes a SQL Server adapter, which replaces the legacy BizTalk-only SQL Server adapter. What do we get from this SQL Server adapter that makes it so much better than the old one?

Feature

Classic SQL Adapter

WCF SQL Adapter

Execute create-read-update-delete statements on tables and views; execute stored procedures and generic T-SQL statements

Partial (send operations only support stored procedures and updategrams)

Yes

Database polling via FOR XML

Yes

Yes

Database polling via  traditional tabular results

No

Yes

Proactive database push via SQL Query Notification

No

Yes

Expansive adapter configuration which impacts connection management and transaction behavior

No

Yes

Support for composite transactions which allow aggregation of operations across tables or procedures into a single atomic transaction

No

Yes

Rich metadata browsing and retrieval for finding and selecting database operations

No

Yes

Support for the latest data types (e.g. XML) and SQL Server 2008 platform

No

Yes

Reusable outside of BizTalk applications by WCF or basic HTTP clients

No

Yes

Adapter extension and configuration through out of the box WCF components or custom WCF behaviors

No

Yes

Dynamic WSDL generation which always reflects current state of the system instead of fixed contract which always requires explicit updates

No

Yes

SOA Patterns with BizTalk Server 2009 Implement SOA strategies for Microsoft BizTalk Server solutions with this book and eBook
Published: April 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Needless to say, the WCF SQL Server Adapter offers significant benefit over the previous SQL Server adapter and introduces a range of new capabilities that are well overdue in the realm of database interaction. In this section, we will look at a few use cases and patterns for this adapter, which best demonstrate the capabilities it provides.

Solution set up

Before we get started building a series of different scenarios, we need to prepare our environment with the database tables and schemas that will be reused  over and over again. All of our demonstrations in this article deal with batches of biologic materials that we use in our drug making process. These batches of material go through different stages before being ready for shipment.

To get started, I created a SQL Server 2008 database named BizTalkSOA, which houses our relevant tables. In all, there are four tables that we will use  throughout this article:

  • BatchMaster : This table holds the complete batch description as well as its current state. Note that this table's last column uses an XML data type.

    New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

  • BatchDistribution : This table stores information used by distributors who pick up completed batches.

    New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

  • BatchDistributionPickup: This table stores the details of where a batch is physically located.

    New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

  • DistributionVehicles : This table holds information about the fleet of vehicles used to transport batches.

    New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Next, we need a new BizTalk project to hold our enterprise schema that represents a batch of materials. While messages about material batches may arrive to (or depart from) BizTalk Server in diverse formats, this canonical schema is what BizTalk should use internally to represent this enterprise object.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Now we're ready to start building solutions on the SQL Server WCF adapter

Executing composite transactions

In a normalized database environment, a single canonical data entity often has its data spread across more than one underlying database table. Therefore it's critical to be able to insert data into all relevant tables from within a single transaction. Otherwise, we end up with an inconsistent state across our tables. Ignoring the database-centric ways to ease this burden (such as table views, stored procedures), let's consider how we would update multiple tables at once from BizTalk Server 2009. Specifically, we want to take a material batch canonical message and insert its data into multiple tables. After that, we need to execute a scalar operation against the database in order to find out if any shipping trucks are regionally available for transporting this batch.

First, we need the XML schemas which represent our data source. By installing the WCF LOB Adapter SDK, we get a nice metadata browser built into Visual Studio. NET. We access this from a BizTalk project by right-clicking the project, choosing  Add then Generated Items and selecting Consume Adapter Service.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

This action opens a new window that provides us the interface to connect to,browse, and select database objects for service interaction. The very first thing we need to do here is choose the sqlBinding  as the service binding, and then configure a connection string. The simplest  working connection string consists of an Initial Catalog value for the database,  and a valid Server name entry. Note that the adapter now uses a connection  string in the form of a URI instead of the traditional Data Source=;Initial  Catalog=;User Id=;Password= style.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Once a satisfactory connection string is defined, we click the Connect button to establish our active connection to the target database. If the connection is successful, we see the category browser stuffed with a set of database object categories.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

If you recall from our use case summary earlier, we need to get schemas for two tables (BatchDistribution and BatchDistributionPickup) as well as a generic scalar query. If we click on the root "/" character, we see a range of untyped generic functions that we may exploit. I've added the ExecuteScalar operation to my desired list of schemas.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Keeping this window open, we next want to add Insert operations for the two target tables. This leaves us with three operations selected in the window.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

After clicking the Ok button at the bottom, we see a series of schemas (and a single binding file) added to our associated BizTalk project in Visual Studio.NET. A pair of type schemas are created to represent underlying data types, but the three most important schemas are shown here. There are two schemas for table operations, and one for the generic scalar query.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

The WCF SQL Server Adapter supports the nesting of multiple (cross) table interactions within a single schema, which in turn get executed inside a single transaction. The schema must have two root nodes: one named Request and another named RequestResponse. The namespace of the schema must be in the format of http://[PROJECT_ NAME].[COMPOSITE_SCHEMA_NAME], which means that my schema below has a namespace of http://Seroter.BizTalkSOA.Chapter9.BizTalkSql. CompositeBatchInsert_XML. In order to reuse our adapter-generated schemas, we must use the Import option and reference both the BatchDistribution and BatchDistributionPickup schemas.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Once the import references are established, we must create records underneath both the Request and RequestResponse nodes and access the Data Structure Type property to find our imported types.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Note that the physical order of the nodes maintains significance to the adapter. In the schema above, the structure of the Insertnodes implies that the BatchDistributioninsert occurs before the BatchDistributionPickupinsertion. Keep this in mind when dealing with tables that have foreign key relationships or other dependencies on execution order.

With our schemas in place, we must now forge ahead with the orchestration, which processes these messages. To execute the "insert" part of the process, we need a total of three orchestration messages: one corresponding to the inbound "master" message, and two to handle the communication with the adapter.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

As you might expect, we need a BizTalk Map in order to get from the "master" schema to the adapter's request message schema. I have used Scripting functoids in this map to create random IDs needed by the destination database.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Because you have multiple destination nodes with the same name, you will need to set the BizTalk map grid property Ignore Namespaces for Links to No. Otherwise, your map will not successfully compile due to namespace confusion

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

At this point, we can complete our orchestration flow and deploy the BizTalk project. In summary, our orchestration accepts in the Material Batch master message, transforms the message using the map we just created, and then sends that message to a request/response orchestration port. Finally, just for logging purposes, I send the adapter result message back out of the orchestration.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

To save ourselves some work, we should import the adapter-generated binding file into our BizTalk application, which results in a new bi-directional send port being created in the BizTalk Administration Console. Now as you might expect, our generated send port has no knowledge of the fact that we are looking to do a composite transaction. If we look at the SOAP actions of the send port, we see entries for inserting into either of two tables, and an entry for the scalar operation.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

We need to add an additional entry here, which reflects our composite operation. The new entry is:

<Operation Name="InsertComposite" Action="CompositeOperation" />

The operation Name must match the orchestration's logical port name, while the Action value must always be equal to CompositeOperation in order for the adapter to correctly process it. The only other item to confirm in this send port is that the binding property useAmbientTransaction is set to True. The CompositeOperation action is a known keyword for the adapter and signifies that multiple operations are about to be executed within a single transaction. An ambienttransaction is the one used by the code being executed, so the useAmbientTransaction property ensures that we are wrapping all operations inside the common transaction BizTalk has established.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

After physical ports are created to receive the initial file and send the logged result, we start the application and send in a material batch master instance. Sure enough, we can see new database entries inserted with matching DistributionID values. The XML emitted by the orchestration shows the expected empty result nodes.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

What about adding the "scalar query" aspect to the existing orchestration? To accomplish this, we first add two more orchestration messages that correlate to the existing auto-generated scalar request and response schemas. Next, we need to inflate the query message. We could build this message via code, but I chose to use a BizTalk map. The left side message is irrelevant since I don't use any data from it and only care about a single Concatenate functoid in the middle design surface. I utilized this functoid to construct the scalar query. In this very simple case, I hard-coded the query parameters, but clearly a real-life scenario would require us to look up the site where the batch was produced and transmit that location to the functoid.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

This orchestration now includes flow steps to create the scalar query message, send it to the adapter, and log the response. Because the scalar operation is known in the binding file and is thus part of the existing send port's SOAP action, I made sure to use the correct orchestration port operation name (i.e. ExecuteScalar).

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

After building, deploying, and binding this orchestration to the necessary ports, I sent another batch message into BizTalk Server and observed the scalar output. Sure enough, I have two records in my database associated with California, and the scalar result reflects that.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

SOA Patterns with BizTalk Server 2009 Implement SOA strategies for Microsoft BizTalk Server solutions with this book and eBook
Published: April 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Polling for data

The original BizTalk Server SQL Server adapter supported polling of databases, but we now have a much more full-featured experience than before. For example, in the classic SQL Server adapter, the server-side stored procedures had to be authored with the BizTalk consumer in mind. A FOR XMLclause was required in the procedure in order to force the results into a BizTalk-friendly XML format. To see how that experience has been changed, let's look at how to poll our BatchMaster table and yank the relevant records into BizTalk Server.

Once again we return to the Consume Adapter Service window. If you remember, we access this by right-clicking our project and choosing to Add a Generated Item. However, instead of simply setting the target database and server, we must now classify some additional parameters. First we must set the InboundID as part of the connection properties. This makes the connection string unique to each poller and is a requirement of the adapter for polling scenarios.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

On the Binding Properties tab, we have three critical properties. First, we need to tell the adapter we are doing TypedPolling (versus untyped polling or classic FOR XML based polling). Next, we set the PolledDataAvailableStatement which the adapter uses to figure out if there are any records to pull. This statement should return a numeric count. My statement is:

SELECT COUNT(*) FROM dbo.BatchMaster WHERE ManufStage = 'Final'
AND Status = 'Pending'

Finally, we distinguish the actual PollingStatement which the adapter uses to return data to BizTalk Server. My polling statement returns all the rows from the tableif we've reached the final manufacturing stage. However, to prevent polling the same records over and over again, we want to flag those previously polled records as Read. What's great is that we can combine this with our polling statement and execute this update within the same transaction.

SELECT * FROM dbo.BatchMaster WHERE (ManufStage = 'Final') 
AND (Status = 'Pending');
UPDATE dbo.BatchMaster SET Status = 'Read'
WHERE ManufStage = 'Final' AND Status = 'Pending'

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Following the entry of all these connection settings, we return to the Consume Adapter Service window and connect to our database. This time, we must switch the Select contract type from Client (outbound operations) to Service (inbound operations). This is because instead of treating the adapter as a service (and BizTalk as a client), the roles are reversed. Once that switch is made, we see new operations available. Choose the TypedPolling option and select Add.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

If you want to confirm that your polling statement is correct, you can highlight the TypedPolling operation and click the Properties button. This shows you the dyanamically-generated WSDL for the operation, including the data contract returned

Once this Consume Adapter Service window is closed, we end up with a new schema and binding file. The schema shows the typed result set emitted by the polling query. If you recall, our table has a column (ProtocolSteps) using the XML data type, and the wizard successfully reads it and interprets it as a string type.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

After deploying our new schema, we import the auto-generated binding into our BizTalk application and observe that a new receive port and receive location now exist. The receive location is configured with all of the connection and polling settings we defined in the Consume Adapter Service window during metadata browsing. I've also created a send port which simply subscribes to the message distributed by this receive location. Once we turn the receive location on, and confirm that our table holds data in a pollablestate, we should see the polling result message sent to disk.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

 

Using SQL Server Query notification

While database polling is certainly a sufficient way to learn about database changes, it is inherently fraught with inefficiency. In order to guarantee a timely processing of data, I have to set an aggressive polling window, which in turn means that there will be a measurable number of polling operations that return no results. In essence, the database acts as a passive store, which is constantly being harassed with "do you have anything yet" messages. Isn't there a better way?

Enter Query Notification which is a capability now supported by the WCF SQL Server adapter in concert with SQL Server 2008. Query notification is a means for the database server (through the use of SQL Server Service Broker) to communicate state changes to subscribers as they occur. So instead of asking for changes, the database tells you when a particular condition has been detected.

Yet again, we head to the Consume Adapter Service wizard to generate schemas. Like the previous demonstration, we have more to add to our initial connection than just the database and server names. On the Binding Properties tab, we set the Inbound Operation Type to Notification and set the Notification Statement which expresses our interest in a particular condition. My statement is:

SELECT BatchID FROM dbo.BatchMaster WHERE (ManufStage = 'Final') 
AND (Status = 'Pending')

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

I'm telling the database that I only want insert/update/delete messages when data matching the above condition is impacted. Once our connection properties are adequate, we connect to the target data source and again switch our "client type" from a Client to a Service. Now we choose Notification as a service operation and complete this wizard.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

This wizard produces a single schema and a binding file. The schema is almost alarmingly simplistic and contains no context about the data source that changed.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

We are meant to absorb this message into an orchestration, and having context about which receive port it came from, query our database for the corresponding messages. For instance, we set up a notification for batches in a final stage of manufacturing.When a batch reaches that stage, this notification message is sent out which simply says data has changed. Knowing that this message arrived from the receive port using a particular notification query, we send a message to the database requesting the records which triggered the notification. The Query Notification process simply tells us "something happened" but it's still our responsibility to ascertain the impact

That said, we need to generate a schema that lets us "select" from the master batch table so that we are able to extract the impacted records. After once again visiting the Consume Adapter Service wizard and choosing the Select operation on the BatchMaster table, we end up with yet another binding file, and a strongly-typed query schema

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Let's tie all these pieces together now. We need a new orchestration, which accepts the query notification message. Using the distinguished fields on that message, we can determine if we've received a change notification for a message meeting our criteria.

 

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

If a change message is encountered, we want to query the BatchMaster table and pull back those records of interest. I used a BizTalk map to construct the table query message. Like an earlier demonstration, I disregard the source message and use a single Concatenate functoid to produce the where portion of my query (with Columns defaulted to "*"). Note that we want to prevent the retrieval of records that we've read before. Similar to our previously polling demonstration, we will embed an updatestatement in this request so that immediately after the relevant records are read, they are flagged to prevent a duplicate read.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

The orchestration flow should now be completed. After receiving the notification, checking the type of change, building a query message, and calling the adapter, we send the response out for logging purposes

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

After building and deploying the solution, we import our new binding files into the BizTalk application. As a result, we should have a new receive port and location for query notification, and a new send port for database querying.

Once the orchestration is bound and the ports started, we need to change our database table content in a way that will trigger our notification event. If I take a record in a previous manufacturing state and switch it to Final, SQL Server sends a notification and my orchestration reads, marks, and distributes the data.

New SOA Capabilities in BizTalk Server 2009: WCF SQL Server Adapter

Query notification is a powerful mechanism for making our SQL Server database event driven instead of simply being a passive repository. This opens up a range of possibility on our service bus for establishing more real-time connections and lookups between systems.

While it's great that BizTalk Server can auto-generate schemas for SQL Server artifacts, don't discount the importance of canonical schemas that decouple your processes from the potentially fickle back end data structures. Consider using canonical schemas for your data entities and only map to the auto-generated formats at the edges of the bus.

Summary

In this article, we've seen a new way to generate and consume services through the WCF SQL Server Adapter. This new adapter offers us significantly more control over access to our back end database while providing a cleaner metadata browsing and development experience. The reusability of this adapter is compelling and means that a developer well-versed in BizTalk development can seamlessly transfer that data connectivity knowledge to a standard WCF application.


If you have read this article you may be interested to view :


About the Author :


Richard Seroter

Richard Seroter is a solutions architect for an industry-leading biotechnology company, a Microsoft MVP for BizTalk Server, and a Microsoft Connected Systems Advisor. He has spent the majority of his career consulting with customers as they planned and implemented their enterprise software solutions. Richard worked first for two global IT consulting firms, which gave him exposure to a diverse range of industries, technologies, and business challenges. Richard then joined Microsoft as a SOA/BPM technology specialist where his sole objective was to educate and collaborate with customers as they considered, designed, and architected BizTalk solutions. One of those customers liked him enough to bring him onboard full time as an architect after they committed to using BizTalk Server as their enterprise service bus. Once the BizTalk environment was successfully established, Richard transitioned into a solutions architect role where he now helps identify enterprise best practices and applies good architectural principles to a wide set of IT initiatives.

Richard maintains a semi-popular blog of his exploits, pitfalls, and musings with BizTalk Server and enterprise architecture at http://seroter.wordpress.com.

The authors have provided a website with further information about the book here: http://appliedarchitecturepatterns.com/

 

Books From Packt

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008 – Installation, Migration, and Configuration

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

 

 

 

 

Your rating: None Average: 5 (1 vote)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
e
y
d
b
P
h
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software