Logo
programming4us
programming4us
programming4us
programming4us
Windows XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
 
Windows Server

BizTalk 2010 : ASDK SQL adapter examples (part 2) - Select, Table Valued Function, and Execute Reader

12/2/2012 11:40:21 AM

Example 2—Select, Table Valued Function, and Execute Reader

This second example builds on the first but focuses on the outbound operations. We will extend the scenario by making subsequent calls to other sales order related tables to retrieve data that completes the message with all the required details about the sales order.

For this example we will query other SQL Server objects related to each debatched message from the first example to demonstrate some of the outbound adapter operations, and then build a final canonical message with all the sales order details.

The following database objects are used to enrich the sales order details:

  • A SQL view called Sales.vSalesOrderDetailProduct that returns order line detail including product information. The ASDK SQL adapter supports querying SQL Server tables and views. The limitation with using this approach is we cannot join Views with other Views or Tables from an ASDK SQL adapter call. However, there is a resolution to this problem. The SQL Server View can be modified to accommodate any additional data requirements that we may have.

  • A table-valued function ufnGetContactInformation that returns contact details. Table-valued functions work much like Stored Procedures, from a BizTalk perspective. We are able to provide a strongly typed request and receive a strongly typed response.

  • A dynamic select statement that will be executed by using the ExecuteReader operation to retrieve extra customer details. The ExecuteReader operation is a very flexible operation since it does not have strongly typed result sets and developers do not require access to the table at design time. The challenge with this approach is it requires the BizTalk developer to deal with untyped response messages coming from SQL Server. This may require us to write custom XSLT if we want to parse, or transform these response messages.

To generate the schemas for the outbound operations used in this example, after establishing successful connection, we set Client(Outbound operations) in the Select contract type drop down list. In the Select a category panel we expanded the Views node, select vSalesOrderDetailView and added "Select" to the list of desired operations. Then, we added ufnGetContactInformation function located under the Table Valued Functions node. Lastly, we add ExecuteReader operation located under the "/" root node.

We can now click the OK button to close the wizard and generate the schemas.

First let's discuss the schema for the SELECT operation we want to execute on the vSalesOrderDetailProduct view. To build a SELECT statement, the ASDK SQL adapter requires us to specify desired columns separated by commas in the Columns element under the Select node. The Query element under the same node is where we specify the "WHERE" clause to filter out the result set of the SELECT statement.

The response part of the schema is strongly typed and contains all the columns from the vSalesOrderDetailProduct view. Note the columns are defined with the Min. Occurs. xsd property set to 0. It means the resulting response message from the adapter will contain only the columns listed in the Columns element:

For the table-valued functions the Consume Adapter Service wizard generates a schema that allows us to provide values for the function parameters—note the ContactId element under the ufnGetContactInformation node. The response part of the schema is strongly typed and contains the definition of the table returned by the ufnGetContactInformation function:

To perform the ExecuteReader operation, we need to specify a SELECT statement in the Query element under the ExecuteReader node. The response generated by the adapter for this operation is an array of the DataSet nodes. Each DataSet node in the schema has two <Any> elements, the first will have a schema of the DataSet, and the second will be an ADO.NET diffgram of the data itself:

Now that we have the schemas for the outbound operations, we can create a single map to generate all three request messages at once from the incoming sales order header message. To generate a multi-destination map we double clicked the Transform shape, specified Transform.SalesOrder_to_LookupRequests name for a New Map, and then specified the request and three response messages:

Once we clicked OK the Map designer showed up containing our three schemas packed in one destination schema.

The following image shows the fully configured map used in our sample application:

To set the Query property of the ExecuteReader request message, we use a string concatenate functoid to build a select statement made up of multiple table joins to return address information for a customer. We then link the CustomerID element from the source message to finalize the statement with the WHERE clause:

For the select statement we want to run against the vSalesOrderDetailsProduct view, we need to specify the required columns and a WHERE clause. A string concatenate functoid is used to set the Columns element to *. This will tell the adapter that all columns from the view should be selected. Another string concatenate functoid is used to populate the Query element. We use the SalesOrderID element from the input schema to build the WHERE clause where SalesOrderID =SalesOrderID.

Lastly, we need to specify the input parameter for the ufnGetContactInformation table valued function. This function has only one parameter: ContactID. So we drag the ContactID from the source schema to the ContactID element of the destination schema.

A nice feature of BizTalk is the ability to reuse logical ports within an orchestration. This allows us to bind our logical port to a single physical port in the BizTalk Administration Console, for all three lookups. This reduces the amount of send ports that we have to create and maintain. We can also use a parallel shape in the orchestration to execute all three operations at the same time.

To represent a complete sales order, we have a schema named SalesOrder.xsd. After the parallel shape finishes and all three operations have been run against SQL Server, we can then map their responses and the original sales order header data to the final SalesOrder message:

This is what the LookupResponses_to_SalesOrder map looks like:

As you can see, the preceding map is pretty straightforward with the exception of the ExecuteReaderResponse node. As we previously discussed, the Execute Reader operation has a tradeoff. The tradeoff is that our result set will be untyped, which means we need to perform some custom work to transform this message into a strongly typed message. To achieve this we need to write some XSLT and use an Inline XSLT Call Template scripting functoid to map the diffgram contents:

<xsl:templatename="AddressTemplate">
<xsl:paramname="param1" />
<xsl:iftest="count(//NewTable) > 0">
<xsl:elementname="Addresses">
<xsl:for-eachselect="//NewTable">
<xsl:elementname="Address">
<xsl:iftest="Name">
<xsl:elementname="Name">
<xsl:value-ofselect="Name"/>
</xsl:element>
</xsl:if>
<xsl:if test="AddressLine1">
<xsl:element name="AddressLine1">
<xsl:value-ofselect="AddressLine1"/>
</xsl:element>
</xsl:if>
<xsl:if test="AddressLine2">
<xsl:element name="AddressLine2">
<xsl:value-ofselect="AddressLine2"/>
</xsl:element>
</xsl:if>
<xsl:if test="City">
<xsl:element name="City">
<xsl:value-ofselect="City"/>
</xsl:element>
</xsl:if>
<xsl:if test="StateProvinceCode">
<xsl:element name="StateProvinceCode">
<xsl:value-ofselect="StateProvinceCode"/>
</xsl:element>
</xsl:if>
<xsl:if test="Country">
<xsl:element name="Country">
<xsl:value-ofselect="Country"/>
</xsl:element>
</xsl:if>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:if>
</xsl:template>


					  

That is it for the development phase of the example. The final orchestration with the two maps and all three outbound operations executed in parallel looks like this:

This binding file creates a send port, which is shared by the three operations with the same binding properties as the ones we set when creating the schemas. The important part to review is the SOAP Action header property on the General tab of the WCF-Custom adapter:

<BtsActionMappingxmlns:xsi=http://www.w3.org/2001/XMLSchema-instancexmlns:xsd="http://www.w3.org/2001/XMLSchema">
<OperationName="Select"
Action="ViewOp/Select/Sales/vSalesOrderDetailProduct" />
<OperationName="ufnGetContactInformation"
Action="TableFunction/dbo/ufnGetContactInformation" />
<OperationName="ExecuteReader"
Action="GenericOp/ExecuteReader" />
</BtsActionMapping>


					  

This provides a mapping between the operations and the SOAP actions.

When we enable our solution, after a few seconds we will have new messages containing sales order data. Here is an example of a processed sales order:

<ns0:SalesOrder xmlns:ns0=http://Schemas.SalesOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Header>
<RevisionNumber>28</RevisionNumber>
<OrderDate>2001-07-01T00:00:00Z</OrderDate>
<DueDate>2001-07-13T00:00:00Z</DueDate>
<ShipDate>2001-07-08T00:00:00Z</ShipDate>
<Status>5</Status>
<SalesOrderNumber>SO43660</SalesOrderNumber>
<PurchaseOrderNumber>PO18850127500</PurchaseOrderNumber>
<AccountNumber>10-4020-000117</AccountNumber>
<CreditCardApprovalCode>115213Vi29411</CreditCardApprovalCode>
<SubTotal>1553.1035</SubTotal>
<TaxAmt>124.2483</TaxAmt>
<Freight>38.8276</Freight>
<TotalDue>1716.1794</TotalDue>
<Contact>
<FirstName>Takiko</FirstName>
<LastName>Collins</LastName>
<JobTitle>Purchasing Manager</JobTitle>
<ContactType>Store Contact</ContactType>
<Addresses>
<Address>
<Name>Main Office</Name>
<AddressLine1>6055 Shawnee Industrial Way</AddressLine1>
<City>Suwanee</City>
<StateProvinceCode>GA </StateProvinceCode>
<Country>United States</Country>
</Address>
</Addresses>
</Contact>
</Header>
<Items>
<Item>
<CarrierTrackingNumber>6431-4D57-83</CarrierTrackingNumber>
<OrderQty>1</OrderQty>
<UnitPrice>419.4589</UnitPrice>
<UnitPriceDiscount>0.0000</UnitPriceDiscount>
<LineTotal>419.458900</LineTotal>
<ProductName>Road-650 Red, 44</ProductName>
<ProductNumber>BK-R50R-44</ProductNumber>
<ProductSize>44</ProductSize>
<ProductWeight>18.77</ProductWeight>
<SellEndDate>2003-06-30T00:00:00Z</SellEndDate>
<SellStartDate>2001-07-01T00:00:00Z</SellStartDate>
</Item>
<Item>
<CarrierTrackingNumber>6431-4D57-83</CarrierTrackingNumber>
<OrderQty>1</OrderQty>
<UnitPrice>874.7940</UnitPrice>
<UnitPriceDiscount>0.0000</UnitPriceDiscount>
<LineTotal>874.794000</LineTotal>
<ProductName>Road-450 Red, 52</ProductName>
<ProductNumber>BK-R68R-52</ProductNumber>
<ProductSize>52</ProductSize>
<ProductWeight>17.42</ProductWeight>
<SellEndDate>2002-06-30T00:00:00Z</SellEndDate>
<SellStartDate>2001-07-01T00:00:00Z</SellStartDate>
</Item>
</Items>
</ns0:SalesOrder>
Other -----------------
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Sending email using Outlook
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Exporting data to Microsoft Project
- Microsoft Content Management Server : The ASP.NET Stager Application (part 3) - Staging Attachments
- Microsoft Content Management Server : The ASP.NET Stager Application (part 2) - Staging Channels and Postings
- Microsoft Content Management Server : The ASP.NET Stager Application (part 1) - The DotNetSiteStager Project, Recording Messages to a Log File
- Microsoft Content Management Server : Staging Static Pages - Site Stager in Brief
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 2)
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 1)
- Windows Server 2008 Server Core : Renaming a File with the Ren and Rename Commands, Sorting File Content with the Sort Utility
- Windows Server 2008 Server Core : Moving Files and Renaming Files and Directories with the Move Command, Recovering Lost Files with the Recover Utility
 
 
Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
 
programming4us
Girls
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone