Business Connectivity Services and Oracle with BCS Meta Man

 BCS Meta Man, Business Connectivity Services, SharePoint 2010.

In our previous blog posts we have already introduced to you how it is easy to create Business Connectivity Service External Content Types, upscale BDC application definition files and upsize SPD 2010 generated BCS models with BCS Meta Man. All these blog posts were done based off Microsoft SQL server. In this walkthrough we’ll show you how to create a BCS External Content Type, generate C# methods, and make an association for Oracle database tables.

1) Add a New Project

Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type

Give your project a name i.e OracleSecureStoreBCSMetaManDemo and click “OK”

Create OracleSecureStoreBCSMetaManDemo project

2) On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button

Deploy as a farm solution

If prompted, click “Trial” on the licensing dialog

3) Add System.Data.OracleClient reference to your project, which is in the following location,

C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Data.OracleClient.dll.

add System.Data.OracleClient.dll

4) When the BCS Meta Man design surface is in view you will find the BCS Meta Man menu item in the menu bar. Click on BCS Meta Man – > BCS Meta Man Data Source Explorer.

Open BCS Meta Man Data Explorer

5) Click on the “Add Oracle Connection” button of the data source explorer.

Add Oracle Connection

6) In the connection dialog, setup connection to your oracle database.

7) If the connection is OK the treeview will populate with Oracle tables.

Treeview populates with Oracle tables

8) Drag and drop the Departments table to the design surface. In the Entity Creation wizard click on the Next button, select to generate all the methods and then click on the Generate button.

Drag and drop an Oracle table onto the design surface

9) BCS Meta Man will add new files to the project which contain generated C# code. You’ll also see the External Content Type on the design surface.

BCS Meta Man generates lots of code for you

10) At this point we have created a BCS external content type for Departments table. Please, do the same to create External Content Type for the Employees table too.

Department and Employee tables from Oracle

11) View the Visual Studio’s toolbox and choose the association action. Create an association between Departments(Source) and Employees(Destination) External Content Types. It will pop up a dialog for identifier mapping. Select DepartmentId in both dropdown boxes.

Set association fields between content types

12) Click on OK and it will create an association between those two tables. You’ll find that it
automatically adds a new association method to the Employees External Content Type.

BCS Association created

13) Rebuild the project and press CTRL+F5 to deploy the solution.

14) Go to your SharePoint site and add a new External List. Set either Departments or Employees as the External Content Type to use for the list. We want to make sure, that all the generated methods work properly.

15) When the External List is displayed, it calls the BCS Finder method to populate the records.

BCS External List with Oracle data

16) Click on the Items ribbon which is in the top. If Creator method is configured properly then it should be enabled.

Create new item

17) Click on “New Item” and insert a record.

Add record to Oracle

18) You can see the inserted record in the list.

External List updated

19) Let’s check if the specific finder method works ok. Select any record in the list and go to Items ribbon. Click on View Item button.

Test Specific Finder method

20) “View Item” should execute the specific finder method.

View Item

21) And finally, let’s make sure that updater works properly. Select any item in the list, click on Items ribbon and then click on Edit Item button.

Check updater method

22) Update the name of the selected department and click on Save button.

Update data straight to Oracle

23) When the list is re populated, you’ll see the updated record.

External List updated live

Well, we have made sure that all the generated methods(Finder, Specific Finder, Creator, Updater) work properly, but we have an association between Departments and Employees as well. So, let’s make sure it works ok too.

24) Edit the page of your SharePoint site, add add a BCS List and BCS Related List web part. Set Departments as the type for BCS list web part and Employees as the type for BCS related list web part. Connect those two web parts, so BCS List web part should send data to the Related List web part. Select any department in the list web part. This will execute the association method and populate appropriate employees in the related list web part.

Test Oracle association works

 

<hrayr/>

23 comments on “Business Connectivity Services and Oracle with BCS Meta Man

  1. we have now posted a video of this walk through up as well for you to watch:http://www.lightningtools.com/blog/archive/2010/01/28/oracle-and-the-business-connectivity-services.aspx

  2. Alexander Brütt on said:

    When I add an Oracle connection I get the following exception:Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.My System:Windows Server 2008 SP2 64 BitVisual Studio 2010SharePoint 2010 64bitOracle 11.x 64bitDo you have any idea what might be wrong?

  3. casinos gratis on said:

    The BCS List web part should send data to the Related List web part. Select any department in the list web part. This will execute the association method and populate appropriate employees in the related list web part…Thanks…

  4. Patrick Simmons on said:

    I’m having the same issue as Alexander. I’m not able to create the connection – getting error Attempt to load Oracle client libraries threw BadImageFormatException. Oracle 11g x64 client installed and ODBC test is successful. What could my problem be?Thanks

  5. Bast on said:

    I have the same system configuration.In my case I had to add the reference C:WindowsMicrosoft.NETFramework64v2.0.50727System.Data.OracleClient.dll instead of C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Data.OracleClient.dll. This solved the exception

  6. Ratna on said:

    When I add oracle database and selecting the identifiers and click ok. The visual studio 2010 IDE is closing automatically(Crashing). It is never creating External Content Type for me. Any idea on this issue.

  7. Oscar Campo on said:

    I have the same problem with the same configuration! Any idea to solve the problem?Thanx

  8. Rushi on said:

    I have same exception coming.“problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.“I have Oracle 10gany updates?

  9. Marc on said:

    Hi, I am getting the same error upon adding the connection: attempt to load oracle client libraries threw badimageformatexceptionI have Windows Server 2008 R2 64 bit.Is there any solution for it?

  10. Grim Deville on said:

    We get the same as the person above behaviour: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

  11. rüya tabirleri on said:

    Thank you comments

  12. Robbert van Andel on said:

    I fixed the BadImageFormatException error by tweaking the TNS_ADMIN and Path system variables. I have on my development server both 64 and 32 bit versions of the instant client. I set the TNS_ADMIN system variable to point to my 64 bit directory. I set the path to include %TNS_ADMIN%. This will insure that whaterver TNS_ADMIN is set to will also be in the system path. For development, I set a user level system variable for TNS_ADMIN to my 32 bit directory. Now I can connect to Oracle via Visual Studio using the 32 bit driver and via SharePoint using the 64 bit driver.

  13. best casino bonus online on said:

    hi i am getting the error when adding the connection.plz give me the solution for this problem.

  14. Sean Swords on said:

    Same here:‘Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.’.

  15. Nick Swan on said:

    Hi all,Since VisualStudio is 32bit application you need to install Oracle Client 11g x86 for BCS Meta Man – please ensure you select the ‘Administrator’ type of installation:http://www.oracle.com/technetwork/database/enterprise-edition/downloads/111060-win32soft-099267.html

  16. rolex replikate in deutschland on said:

    32bit application you need to install Oracle Client 11g x86 for BCS Meta Man – please

  17. swiss replica watches sale on said:

    client libraries threw BadImageFormatException. This problem will occur

  18. Recruitment Process Outsourcing on said:

    I am so glad this internet thing works and your article really helped me. Might take you up on that home advice you gave. Perhaps a guest appearance would be good.

  19. Cathy on said:

    This is really helpful!!! But how do I deploy to the server if I list my local machine here?

  20. Merin Nakarmi on said:

    To connect with Visual Studio, you will require Oracle Client 32 bit and to connect with SharePoint you will require Oracle Client 64 bit.

  21. Hi,I created the external content types successfully but cannot create an associatiom. Any ideas why this is happening ?Thanks,KP

  22. web designing vizag on said:

    I really loved reading your blog. It was very well authored and easy to understand.

  23. Irfan on said:

    Hello,After creating External Content Type and deploying the solution, I can see the External Content type under BCS service in SharePoint. But, when I try to create an External list, the new External Content Type is not available to pick. It does not show up in the external Content type list. Any help would greatly be appreciated. Thanks,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

HTML tags are not allowed.