Table of Contents

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.