How to Select, Insert, Update and Delete in BizTalk using Oracle Adapter

Select, Insert, Update and Delete statements can be executed using the BizTalk Oracle adapter by generating the Oracle Adapter metadata for a specific table. Right click the solution and click add generated items and click “Add adapter metadata”. A schema along with multi-part message types and port will be created along with an Orchestration file. For creating Oracle adapter metadata see Generating MetaData From Oracle Adapter.

Multi-part messages for Selecting (Query, QueryResponse), Update(Update, UpdateResponse), Insert(Insert, InsertResponse) and Delete(Remove, RemoveResponse) will be created with a message part named parameter for each message.

You can generate requests for each Request messages by changing the Root Reference property of the schema to the request message you want. Generate the instance and load the xml into the XMLDocument type variable with the SQL Statement in the message.

Bulk Insertion is an advantage using Oracle adapter and if you have several records to be inserted or updated you can create their messages and send it to the oracle adapter. In the response message the rows affected is returned.

For Select, Update and Delete you have to fill the Filter tag. For filters we supply the where clause just as in any other SQL Statement. For e.g. If we want to select particular records we give “columnname1 = value and columnname2 = value”.

• Select and Remove Type multi-part message only requires the filter parameter to be filled.

 

• For Update we supply the values of the columns that have to be changed in the request message along with the filter parameter.

 

 

• Insert type messages only require records to be inserted without filters.

 
Create a new port with solicit request-response type. Select the existing port type which was auto-generated with the metadata created.

 

codeproject
technorati tags : BizTalk