BizTalk Adapter Pack 2.0: OracleBinding

In previous post before my holiday to France I tried out BizTalk Adapter Pack 2.0 with SQL Binding. As said back then I also wanted to try out the Oracle Binding. On my Sandbox I installed Oracle 10G Express Database (similar to SQL Express). To download this database you will need to register to Oracle Technology Network (OTN). For Oracle Database 10g Express Edition installation I did the following:

Password voor SYSTEM database e.a. I used: welkom.

Destination Folder: D:\oraclexe\
Port for 'Oracle Database Listener': 1521
Port for 'Oracle Services for Microsoft Transaction Server': 2030
Port for HTTP Listener: 8080

After installation Oracle Database Express Edition browser UI is fired up. I logged in with username system and password welkom.

I then unlocked sample user by following the procedure below:

To unlock the sample user account:

1.Make sure you are still logged on as the database administrator, as described in the previous section.

2.Click the Administration icon, and then click Database Users.

3.Click the HR schema icon to display the user information for HR.

Description of the illustration gs_hr_icon.gif

4.Under Manage Database User, enter the following settings:

•Password and Confirm Password: Enter hr for the password.

•Account Status: Select Unlocked.

•Roles: Ensure that both CONNECT and RESOURCE are enabled.

5.Click Alter User.

After unlocking the sample user account I logged in with HR and am able to view for instance Employees TABLE.

To work with Adapter Pack 2.0 and Oracle you will need Oracle.DataAccess.dll version 2.111.7.0 (check out this tread!). This is included in ODAC 11g Beta 11.1.0.7.10.  I installed this on my Sandbox environment, selecting only Oracle Data Provider for .NET 2.0.11.1.0.7.10 Beta and Oracle Instant Client 11.1.0.7.0.

I checked GAC and Oracle.DataAccess.dll (proper version) was there. Now I started my project I used also for testing BizTalk Adapter Pack (SQL binding) and tried to connect to Oracle.

Right click project and select Add Adapter Service Reference, select oracleDBBinding and click Configure. You will get something like screenshot above. Fill in ServerAddress and ServiceName (you can find these in OracleTNS : //OracleXE/app/oracle/10.2.0/server/NETWORK/ADMIN). Next Security Tab and select credential type UserName and fill in username (unlocked HR) and password.

Click Connect and connection is there. I then selected in category HR, choose Select in Available categories and operations and click on Add.

Finally I clicked OK. I then implemented some code (based on provided sample from Microsoft) to get some data from Employees table based on selection criteria: Last Name.

Code under test button is as follows:

 private void btnTestOracle_Click(object sender, EventArgs e)
        {
            EMPLOYEESRECORDSELECT[] selectRecords = new EMPLOYEESRECORDSELECT[0];
 
            // Initialize WCF client variables
            HR_Table_EMPLOYEESClient aaTableClient = null;
 
            try
            {
 
                // initialize the EMPLOYEES table client
                aaTableClient = new HR_Table_EMPLOYEESClient("OracleDBBinding_HR_Table_EMPLOYEES");
                //Security Credentials
                aaTableClient.ClientCredentials.UserName.UserName = "HR";
                aaTableClient.ClientCredentials.UserName.Password = "welkom";
                //Open Client
                aaTableClient.Open();
                // Select all records and write them to the textbox result
                selectRecords = aaTableClient.Select("*", "LAST_NAME = '" + txtLastName.Text + "'");
                txtResultOracle.Text = "First Name :" + selectRecords[0].FIRST_NAME + "\r\n" + "Email :" + selectRecords[0].EMAIL + "\r\n" + "Phone Number : " + selectRecords[0].PHONE_NUMBER;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                aaTableClient.Close();
            }
 
}

view raw This Gist brought to you by GitHub.

Filling in Last Name King will result in screenshot below:

After Adapter Service Reference is added the app.config is altered, so configuration is done for you.

Also do not forget to include namespaces in your code:

// Include this namespace for Adapter LOB SDK and Oracle exceptions

using microsoft.lobservices.oracledb._2007._03.HR.Table.EMPLOYEES;

using microsoft.lobservices.oracledb._2007._03;

As you can see working with BizTalk Adapter and OracleBinding is fairly easy, but you need to do some configuring in your environment to make it work.

Technorati:BizTalk Server 2009biztalk adapter pack 2.0