BizTalk blog: Insert, Update, Delete, Select operation on table in Oracle database using oracleDbBinding

Insert, Update, Delete, Select operation on table in Oracle 11g Express database using the OracleDbBinding from BizTalk Adapter pack 2010.

Create BizTalk Project and generate schema (‘s)
First, we need the XML schemas, which represent our data source. With proper installation of BizTalk Server 2010 Adapter Pack, you will have a metadata browser built into Visual Studio. NET at your disposal. Create a visual studio BizTalk project. Then you can access the metadata browser from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.

Click Add. 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 oracleDBBinding 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.

Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories.

On the root "/" character a range of un-typed generic functions are displayed, which can be exploited. Click on tables and you will see a number of Tables, select Employee.

Click the Delete, Insert, Select and Update statement in available categories and operations. Click the Add button to add the operations.After clicking the Ok button at the bottom, schema (and a single binding file) is added to our associated BizTalk project in Visual Studio.NET.

The binding can be used later, when solution is deployed and ports have to be configured for Oracle 11g Express (i.e. Send Port).

Create message instances
Next step is to generate messages from generated schema, which can be routed from Recieve location in Receive Port to a send port with WCF-OracleDb Adapter (messaging based solution). Generating the messages is easy, by opening first OracleDBBindingHR.Table.EMPLOYEES.xsd in file-editor you can move the desired operation to top, so you can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for TableOperations, save to file as xml (type all files, encoding UTF-8!).

Update

Insert

Delete

Build and deploy the BizTalk project
Only schemas are required for a messaging only scenario with WCF-Oracle Adapter to perform operations on Oracle database. Therefore after assigning a strong name and application name to BizTalk project it can be build and deployed.

Configure the application
After solution has been deployed it is time to configure the ports. FILE ports are easy and focus here will be configuring the WCF-Oracle Send Port. Through BizTalk Management Console one can browse to OracleTableOperations application and right click the application and select Import Bindings … and navigate to file called WcfSendPort_OracleDBBinding_Custom.bindinginfo.xml, select and click Open. You will notice that a Send Port will be created automatically.

If you look at created send port and configuration you will notice that everything is there. Although having all operations in one action mapping is not going to work. If you try to send message with select statement to BizTalk you will get following error:

The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HR_Table_EMPLOYEES_Custom" with URL "oracledb://win-8bpntqktj5m:1521/XE/Dedicated". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.InvalidOperationException: An action mapping was defined but BTS.Operation was not found in the message context.

Port RecvPortOracleOpsIn
Type File
Filter - None

Port SendPortOracleOpsOut
Type File
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#SelectResponse
OR
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#InsertResponse
OR
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#UpdateResponse
OR
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#DeleteResponse

Port RecvPortOracleOpsIn
Type WCF-Custom
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Select

Port WCFSndPort_OracleDBBinding_HR_TABLE_INSERT
Type WCF-Custom
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Insert

Port WCFSndPort_OracleDBBinding_HR_TABLE_INSERT
Type WCF-Custom
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Update

Port WCFSndPort_OracleDBBinding_HR_TABLE_INSERT
Type WCF-Custom
Filter - BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES#Delete

Change Binding properties
enableBizTalkCompatibilityMode = True
useAmbientTransaction = False

Select Instance