1

I've been looking for a good guide to using Business Connectivity Services for a while now and have not run into one that details how to implement and use this for a full solution. Most of them that I have come across help get it set up for a certain 3rd party application to sit on top of but not how to use it out of the box.

Does anybody have a good guide for setting this up and using it? Particularly to connect to an Oracle instance. We are looking at designing a portal that will pull information from an Oracle database and display that to a user.

The version of Sharepoint is not really an issue as we are looking at 2010 and 2013 for this solution.

Any help would be appreciated as I have been researching for quite some time and have not found a good guide for this yet.

|improve this question
1

Unfortunately SharePoint Designer doesn't support connecting to Oracle database. So you may follow one of the following approaches:

1.Manually create a BDC Model for Connecting to Oracle Databases from the beginning How to: Connect to an Oracle Database Using Business Connectivity Services 2.Create a Web service to provide an interface to the external data exposed in the database 3.Use Visual Studio's BDC Model project and write the code for Oracle connection. Please look at this blog post Business Data Connectivity Model – Finder Method I would recommend you to try out the product provided by the company where I work for, and name of that product is BCS Meta Man . It will allow you to generate both Database model and C# code automatically as soon as you drag and drop database tables to the design surface.

It supports Oracle and ODBC connection.

Please look at these blog post and video:

Business Connectivity Services and Oracle with BCS Meta Man

Oracle and the Business Connectivity Services

SharePoint 2010 Secure Store Service and Oracle

Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010

Business Connectivity Service Associations using Oracle Stored Procedures in SharePoint 2010

BCS filters and Oracle Stored Procedures with Parameters in SharePoint 2010

Please take a look at this sample BDC Model for Oracle database:

<?xml version="1.0" encoding="utf-8"?>
<Model Name="BCSMetaMan1Model" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  <LobSystems>
    <LobSystem Name="BCSMetaMan1" Type="Database">
      <LobSystemInstances>
        <LobSystemInstance Name="BCSMetaMan1Instance">
          <Properties>
            <Property Name="ShowInSearchUI" Type="System.String"></Property>
            <Property Name="DatabaseAccessProvider" Type="System.String">Oracle</Property>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <Property Name="RdbConnection Data Source" Type="System.String">auriga</Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String"></Property>
            <Property Name="RdbConnection User ID" Type="System.String">hr</Property>
            <Property Name="RdbConnection Password" Type="System.String">hr</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity Name="REGIONS" Namespace="BCSMetaMan1" Version="1.0.0.0">
          <Properties>
            <Property Name="OriginalTableName" Type="System.String">"HR"."REGIONS"</Property>
            <Property Name="IsCustomCode" Type="System.Boolean">false</Property>
            <Property Name="Title" Type="System.String">REGION_NAME</Property>
          </Properties>
          <Identifiers>
            <Identifier Name="REGION_ID" TypeName="System.Decimal" />
          </Identifiers>
          <Methods>
            <Method Name="GetAllREGIONSEntitys">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">REGIONS</Property>
                <Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
                <Property Name="RdbCommandText" Type="System.String">select "REGION_ID", "REGION_NAME" from "HR"."REGIONS"</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">HR</Property>
              </Properties>
              <Parameters>
                <Parameter Name="returnparameter" Direction="Return">
                  <TypeDescriptor Name="REGIONSList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="REGIONS" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" ReadOnly="true" />
                          <TypeDescriptor Name="REGION_NAME" TypeName="System.String" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetAllREGIONSEntitys" Default="true" Type="Finder" ReturnParameterName="returnparameter" />
              </MethodInstances>
            </Method>
            <Method Name="GetSingleREGIONSEntityByID">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">REGIONS</Property>
                <Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
                <Property Name="RdbCommandText" Type="System.String">select "REGION_ID", "REGION_NAME" from "HR"."REGIONS" where "REGION_ID"=:REGION_ID</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">HR</Property>
              </Properties>
              <Parameters>
                <Parameter Name=":REGION_ID" Direction="In">
                  <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" />
                </Parameter>
                <Parameter Name="returnparameter" Direction="Return">
                  <TypeDescriptor Name="REGIONSList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="REGIONS" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" ReadOnly="true" />
                          <TypeDescriptor Name="REGION_NAME" TypeName="System.String" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetSingleREGIONSEntityByID" Default="true" Type="SpecificFinder" ReturnParameterName="returnparameter" ReturnTypeDescriptorPath="REGIONSList[0]" ReturnTypeDescriptorLevel="1" />
              </MethodInstances>
            </Method>
            <Method Name="GetAllREGIONSEntityIDs">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">REGIONS</Property>
                <Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
                <Property Name="RdbCommandText" Type="System.String">select "REGION_ID" from "HR"."REGIONS"</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">HR</Property>
              </Properties>
              <Parameters>
                <Parameter Name="returnparameter" Direction="Return">
                  <TypeDescriptor Name="REGIONSIDs" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="REGIONSSimpleElement" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetAllREGIONSEntityIDs" Default="true" Type="IdEnumerator" ReturnParameterName="returnparameter" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>

Link to source

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.