none
BCS connectivity with oracle without Meta Man in SharePoint 2010 ? RRS feed

  • Question

  • Hi,

    I have to fetch information from Oracle database without using the Meta man or using any other third party tool?

    Please describe me step by step ?

     

      

    Friday, March 8, 2013 8:56 AM

Answers

  • Hi,

    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:

    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>

    Hope this makes sense.

    Dmitry

    Twitter Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog | Мой Блог

    Tuesday, March 12, 2013 6:40 AM

All replies

  • 1> You have to write a WCF service for getting the data from Oracle database. Refer the below article for sample WCF service to be used in SharePoint.

    http://msdn.microsoft.com/en-us/library/gg318615(v=office.14).aspx

    2> Then you have to consume that WCF service in BCS.Use the following approach depending upon your requirement.

    SharePoint Designer Approach:

    http://malikhan.wordpress.com/2010/01/11/business-connectivity-services-bcs-using-wcf-sharepoint-designer-2010/

    Visual Studio approach:

    http://msdn.microsoft.com/en-us/library/office/ff953200(v=office.14).aspx

    Friday, March 8, 2013 11:05 AM
  • Can you Pleas describe how to create the WCF for getting the records from Oracle like creating the Connection with oracle?
    Monday, March 11, 2013 10:47 AM
  • Hi,

    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:

    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>

    Hope this makes sense.

    Dmitry

    Twitter Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog | Мой Блог

    Tuesday, March 12, 2013 6:40 AM