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();
}
}
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 2009
biztalk adapter pack 2.0
enterprise sso
BizTalk 2006 R2
BizTalk Server 2006 R2
BizTalk
biztalk best practices analyzer
biztalk server 2006 r2
biztalk server 2009