Earlier this week I participated in a discussion thread on LinkedIn regarding the usage of the WCF SQL Adapter in BizTalk Server and I wanted to summarize some of my thoughts and the recommended best practices regarding this particular scenario.
Basic Requirement
Using the WCF SQL Adapter in BizTalk, we need to issue a select statement against multiple tables in SQL Server through the use of INNER JOINs.
Background
The WCF SQL Adapter supports composite operations for Insert, Update and Delete statements, but not for select statements. The recommended practice for all but the most simple, single-table T-SQL statements is to wrap the query logic in a stored procedure and invoke the stored procedure via the adapter. Some of the advantages of this approach include:
- encapsulating the database logic at the database level keeping good separation between BizTalk code and SQL Code
- enabling good ALM practices by giving us a stored procedure that can be tested, versioned and promoted at the database level
- improving query performance through optimized execution plans
- allowing the enforcement of security on the execution of the stored procedure
- providing a strongly typed interface to program against in BizTalk
Stored procedures are the way to go and I recommend using them 99.999% of the time. If, for whatever reason, we find ourselves in a situation where we cannot use stored procedures, we have a couple of options at our disposal. I only recommend using them as a last resort and in the following order:
- Create a view to encapsulate the data that the WCF Adapter must consume. By using a view, we can compile the data into a single object that can be queried by the WCF SQL Adapter and return a strongly typed dataset. The benefits of using a view are similar to the stored procedure primarily because we are able to utilize the functionality within the SQL Server database.
- Use the WCF SQL Adapter ExecuteReader operation.
MSDN Library Reference: http://msdn.microsoft.com/en-us/library/dd788494(v=BTS.70).aspx
I want to be perfectly clear when I say that the ExecuteReader can get us the results that we need, however, we need to generate a custom mapping to produce the desired result set. Using the ExecuteReader, we can submit just about any T-SQL statement that we like or even dynamically generate the SQL statement on the fly. If you enjoy writing custom XSLT stylesheets and XPath comes easy to you, then this is a viable, albeit, more tedious option. If the sight of the “xsl:” prefix makes you squeamish, stop now and go convince your DBA that stored procedures are indeed the way to go. If, however, you have endured the migraine headaches that come from debugging XSLT stylesheets and you are not frightened, let’s move on.
Scenario
We need to select data from the MvcMusicStore database to return a catalog of items. Unfortunately, we work with some extraordinarily mean DBAs who detest the brilliance of SQL Server stored procedures and like creating views even less. In this case, we need to drop back to the ExecuteReader option and write a bit more code.
Step 1: Craft our SQL Query
SELECT TOP 10 ar.Name AS 'ArtistName', a.Title, a.Price, g.Name AS 'GenreName'
FROM album a
INNER JOIN Artist ar ON a.ArtistId = ar.ArtistId
INNER JOIN Genre g ON a.GenreId = g.GenreId
Step 2: Add the Generic Schema operations
Add the ExecuteReader schemas to our project using the Add -> Add Generated Items -> Consume Adapter Service Wizard inside the BizTalk project.
Note: If you have never used the wizard, make a connection to the database, select “Client Outbound Operations” as the contract type, select the root item in the left pane “/” and then select “ExecuteReader” from the list of operations in the right pane.
Step 3: Plug our query into the ExecuteReader Query node
<ns0:ExecuteReader xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/" xmlns:ns3="http://schemas.datacontract.org/2004/07/System.Data">
<ns0:Query>SELECT TOP 10 ar.Name AS ‘ArtistName’, a.Title, a.Price, g.Name AS ‘GenreName’ FROM album a INNER JOIN Artist ar ON a.ArtistId = ar.ArtistId INNER JOIN Genre g ON a.GenreId = g.GenreId</ns0:Query>
</ns0:ExecuteReader>
Note: The query cannot contain any line breaks. It must be in one continuous string or the SQL Adapter will throw an exception when the request message is submitted.
I recommend storing the SQL Query in a static class in a C# Utility assembly rather than putting it inside a BizTalk orchestration variable or map. It may seem like overkill, but when it comes time to find, debug, modify or deploy this code, it is far easier to contain queries in one place where you can find them rather than deep inside BizTalk orchestrations. We can also write unit tests and see when the query changes through the use of a simple diff in your source control provider of choice.
Step 4: Submit the query
In order to figure out what we are dealing with, we need to submit the query to SQL Server and receive our generic dataset result. The first time through, I recommend turning on Message Tracking to get a copy of the response message:
<ExecuteReaderResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/">
<ExecuteReaderResult>
<DataSet xmlns="http://schemas.datacontract.org/2004/07/System.Data">
<xs:schema id="NewDataSet" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element msdata:IsDataSet="true" name="NewDataSet">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="NewTable">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="ArtistName" type="xs:string" />
<xs:element minOccurs="0" name="Title" type="xs:string" />
<xs:element minOccurs="0" name="Price" type="xs:decimal" />
<xs:element minOccurs="0" name="GenreName" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<NewTable>
<ArtistName>AC/DC</ArtistName>
<Title>For Those About To Rock We Salute You</Title>
<Price>8.99</Price>
<GenreName>Rock</GenreName>
</NewTable>
<NewTable>
<ArtistName>AC/DC</ArtistName>
<Title>Let There Be Rock</Title>
<Price>8.99</Price>
<GenreName>Rock</GenreName>
</NewTable>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
</ExecuteReaderResult>
</ExecuteReaderResponse>
Congratulations. We got the data out of the database, but we have to parse through a bit of a mess with all of the generic dataset elements to get to the actual results that we want.
Step 5: Build an XSLT stylesheet
With the power of XSLT at our fingertips, we can craft a simple stylesheet to extract the data that we need. This transformation will generate a canonical message and ensure no SQL Server specific schema elements leak into our BizTalk code, like so:
<xsl:template match="/" name="Main">
<xsl:apply-templates select="/\*[local-name()=’ExecuteReaderResponse’]/\*[local-name()=’ExecuteReaderResult’]/\*[local-name()=’DataSet’]/\*[local-name()=’diffgram’]/*[local-name()=’NewDataSet’]" />
</xsl:template>
<xsl:template match="*">
<MusicCatalog xmlns="http://MVCMusicStore.MusicCatalog">
<xsl:for-each select="NewTable">
<CatalogItem>
<ArtistName>
<xsl:value-of select="ArtistName"/>
</ArtistName>
<Title>
<xsl:value-of select="Title"/>
</Title>
<Price>
<xsl:value-of select="Price"/>
</Price>
<GenreName>
<xsl:value-of select="GenreName"/>
</GenreName>
</CatalogItem>
</xsl:for-each>
</MusicCatalog>
</xsl:template>
Step 6: Test the XSLT
One of the reasons I prefer using an XSLT stylesheet in this situation is that it gives me the ability to easily test the transform by assigning the XSL to an XML document and debugging inside Visual Studio. This is much faster and provides us the opportunity to pull the transformation outside of BizTalk entirely if we had to fulfill those creepy unknown requirements that tend to sneak up on us 6 months into a project.
- Save the ExecuteReaderResponse XML to a file.
- Inside Visual Studio, open the XML File and set the StyleSheet property to the XSLT Stylesheet we just created.
- Set the Output property for the XML file to a location where you want the result of the transformation saved.
- Click anywhere inside the XML document and then from the top menu, select XML -> Start XSLT without Debugging
Running the transformation on our ExecuteReaderResponse document gives us the following message which we can now consume easily:
<?xml version="1.0" encoding="utf-8"?>
<MusicCatalog xmlns="http://MVCMusicStore.MusicCatalog">
<CatalogItem>
<ArtistName>AC/DC</ArtistName>
<Title>For Those About To Rock We Salute You</Title>
<Price>8.99</Price>
<GenreName>Rock</GenreName>
</CatalogItem>
<CatalogItem>
<ArtistName>AC/DC</ArtistName>
<Title>Let There Be Rock</Title>
<Price>8.99</Price>
<GenreName>Rock</GenreName>
</CatalogItem>
<CatalogItem>
<ArtistName>Lenny Kravitz</ArtistName>
<Title>Greatest Hits</Title>
<Price>8.99</Price>
<GenreName>Rock</GenreName>
</CatalogItem>
</MusicCatalog>
This may be stating the obvious, but it is critical that we have a MusicCatalog Schema in our project in order to set the Source and Destination properties of our BizTalk map.
Step 7: Plug the XSLT into a BizTalk map
- Create a new BizTalk map using the ExecuteReaderResponse schema as the source and the MusicCatalog schema as the destination.
- Click anywhere in the middle of the map and set the Custom XSLT Path in the property pane to our custom XSLT.
- Set the TestMap Input and Output properties and test the BizTalk map to ensure we get the same results as we did in the previous step.
When this code is deployed, the map should be placed in the Inbound Maps section of the WCF SQL Send Port configuration window in the BizTalk Administration console.
Conclusion
There you have it. Given a strict requirement not to use Stored Procedures or Views, we can issue a select statement against multiple SQL tables in the same transaction and parse the generic results into a meaningful message.