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”

12 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

13 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

clip image005 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step4 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step5 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

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

Step7 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step8 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step9 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step10 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step11 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step12 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step15 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step16 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step17 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step18 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step19 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step20 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step21 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step22 thumb Business Connectivity Services and Oracle with BCS Meta Man

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

Step23 thumb Business Connectivity Services and Oracle with BCS Meta Man

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.

Step24 thumb Business Connectivity Services and Oracle with BCS Meta Man

 

<hrayr/>

  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. 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 Bit
    Visual Studio 2010
    SharePoint 2010 64bit
    Oracle 11.x 64bit

    Do you have any idea what might be wrong?

     
  3. 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

    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

    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

    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. I have the same problem with the same configuration! Any idea to solve the problem?

    Thanx

     
  8. Rushi

    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 10g
    any updates?

     
  9. Marc

    Hi, I am getting the same error upon adding the connection: attempt to load oracle client libraries threw badimageformatexception
    I have Windows Server 2008 R2 64 bit.

    Is there any solution for it?

     
  10. Grim Deville

    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. Thank you comments

     
  12. 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. hi i am getting the error when adding the connection.plz give me the solution for this problem.

     
  14. Sean Swords

    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. 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. 32bit application you need to install Oracle Client 11g x86 for BCS Meta Man – please

     
  17. client libraries threw BadImageFormatException. This problem will occur

     
  18. 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

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

     
  20. Merin Nakarmi

    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. KP

    Hi,

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

    Thanks,

    KP

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

     
  23. Irfan

    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 Comment

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

*

* Copy This Password *

* Type Or Paste Password Here *

*