Share via

BizTalk Server 2013: CRUD Operation With WCF-SQL Adapter and Correlation

  • Article

Background

Database operations are usually classified as Create, Read, Update, and Delete (CRUD) Operations. This article will describe how to do a CRUD Operation in a single thread using a correlation set. The sample demonstrates how to do a dependent activity within the same transaction by performing an insert (create) operation on the table followed by an update and deletion of an existing record. This pattern can be beneficial in case you need to do a sequential message flow in SQL Server.

Overview

The example in this article deals with a CRUD Operation in a single Transaction. For simplicity I have used a file folder for the Insert,Update and Delete files, which will be triggered inside Orchestration. In real time scenarios this can be replaced with a client application, which has sequential message flow in SQL server.

Picture 1. Scenario overview.

The BizTalk Adapter Pack

To build a solution with BizTalk Server to operate on a SQL Database it is recommended that you use the WCF-SQL Adapter (binding). The BizTalk Adapter pack contains a database adapter for Oracle- and SQL Server based on WCF. Both are categorized as the database adapters in the adapter pack. While the Oracle eBusiness, Siebel and SAP adapters also belonging to the pack are called Line of Business (LOB) adapters. The SQL adapter or better WCF binding enables you to expose SQL Server database objects as services for instance you can expose database table operation(s) as a web service(s). The Adapter client i.e. BizTalk Server can exchange messages based on the SOAP protocol with a SQL Server database. Basically the metadata of SQL Server database artifacts (tables, functions, procedures, etc.) are described in the structure of a SOAP message by means of a WSDL. Through using the Metadata Wizard, a Visual Studio Plug-in installed during the installation of the Adapter SDK BizTalk (as an adapter client), you can retrieve metadata for operations.

Solution Design and Build Steps

Solution is build with the following steps:

  • Generate a composite schema for Insert ,Update and Delete Operation through Add Generate Item --->Consume Adapter Service --->SQL Binding 

Picture 2. Meta data Wizard a part of the BizTalk Adapter Pack.

The Schema generated will have all the structure of the required Operations (CRUD) along with the WCF binding file. 

Picture 3. Generated schema.

  • BizTalk Orchestration designer include message of each type Of Operation listed in above schema.

**Picture 4.**Created message types.

The sample Orchestration designer, which is being used is shown below.

  Picture 5. CRUD Operation Orchestration.

For simplicity Orchestration has a correlation defined on receive port name and the same receive port has multiple receive location for Insert ,Update and Delete files.

Picture 6. Different receive ports for CRUD to invoke CRUD operations.

While there is single send Port for Insert ,Update and delete Operation with SOAP action Header as below.

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="Delete" Action="TableOp/Delete/dbo/DemoInsert" />
  <Operation Name="Insert" Action="TableOp/Insert/dbo/DemoInsert" />
  <Operation Name="Update" Action="TableOp/Update/dbo/DemoInsert" />
</BtsActionMapping>

The Sample xml  files which is being used for testing is listed below.

Insert File:

<ns0:Insert xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/DemoInsert">
  <ns0:Rows>
    <ns1:DemoInsert xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">
      <ns1:Name>Abhishek</ns1:Name>
      <ns1:Type>BizTalkDemo</ns1:Type>
      <ns1:Value>1</ns1:Value>
    </ns1:DemoInsert>
  </ns0:Rows>
</ns0:Insert>

Once we Publish the insert file ,The data got inserted into SQL table and Orchestration remain in dehydrated state for next command.

Picture 7. SQL Server database tables.

Similarly the Update and Delete statement for SQL Operation are as below

<ns0:Update xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/DemoInsert">
  <ns0:Rows>
    <ns0:RowPair>
      <ns0:After>
        <ns1:Name xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">Abhishek1</ns1:Name>
        <ns1:Type xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">BizTalk</ns1:Type>
        <ns1:Value xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">2</ns1:Value>
      </ns0:After>
      <ns0:Before>
        <ns1:Name xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">Abhishek</ns1:Name>
        <ns1:Type xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">BizTalkDemo</ns1:Type>
        <ns1:Value xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">1</ns1:Value>
      </ns0:Before>
    </ns0:RowPair>
  </ns0:Rows>
</ns0:Update>

And SQL Result is like below with Update data.

While the Delete Statement is like below which will clean up the record.

<ns0:Delete xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/DemoInsert">
  <ns0:Rows>
    <ns1:DemoInsert xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">
      <ns1:Name>Abhishek1</ns1:Name>
      <ns1:Type>BizTalk</ns1:Type>
      <ns1:Value>2</ns1:Value>
    </ns1:DemoInsert>
  </ns0:Rows>
</ns0:Delete>

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki