如何:使用 Business Connectivity Services 连接到 Oracle 数据库

上次修改时间: 2010年5月26日

适用范围: SharePoint Server 2010

Microsoft Business Connectivity Services (BCS) 可从以下各类数据库连接到外部数据:

  • Microsoft SQL Server

  • Oracle

  • OLE DB

  • ODBC

如何:基于 SQL Server 表创建外部内容类型这一主题说明使用 Business Connectivity Services 显示来自 SQL Server 数据库的外部数据的基本方式。在使用其他数据库(如 Oracle、OLE DB 和 ODBC)时,必须遵循下列方法之一(因为 Microsoft SharePoint Designer 2010 不支持这些数据库):

  1. 从头开始创建 Business Connectivity Services 模型。有关信息,请参阅创作 BDC 模型。有关编写或修改 Oracle 数据库的模型时的注意事项,请参阅下一节。

  2. 创建 Web 服务或 .NET 连接程序集,为数据库中公开的外部数据提供接口。有关详细信息,请参阅Creating Web and WCF Services for Business Connectivity Services如何:创建 .NET 连接程序集

创建 BDC 模型以连接到 Oracle 数据库

按照创作 BDC 模型中的过程执行操作以从头开始创建模型。

在编写或修改 BDC 模型以连接到 Oracle 时,应记住以下注意事项:

  • Oracle SQL 语法要求您按以下方式指定查询中的参数:为参数添加冒号 (:) 而非 @ 符号前缀。确保在元数据的 SQL 语句中正确设置它们。

  • 如果 Oracle 连接需要连接字符串中的显式用户 ID 和密码参数,则:

    1. 使用 Oracle 凭据在 Secure Store Service 中设置应用程序定义。

    2. 使用 RdbCredentials 的 AuthenticationMode。

    3. 在将 RdbCredentials 用作验证模式时,无法使用 RdbConnection User ID 和 RdbConnection Password 属性,因为这些值是由 Secure Store Service 提供的。如果指定这些值,则会将其忽略。必须使用安全存储来提供 Oracle 凭据。

  • 必须在服务器场中的所有计算机上安装 Oracle 客户端,并且必须将 TNS net 服务配置为从 SharePoint 连接到 Oracle。这对于服务器上的外部列表、Web 部件和配置文件页是必需的。由于应用程序(如搜索)只在应用程序服务器上运行,因此可以在应用程序服务器上安装 Oracle 客户端。搜索将只从应用程序服务器连接。简而言之,必须在从中连接到 Oracle 的服务器场的计算机上安装 Oracle 客户端。出于同一原因,还必须在富客户端计算机上安装 Oracle 客户端。

以下示例演示如何为使用 Secure Store Service 进行连接的 Oracle 数据库设置 LobSystemInstance 属性。将 YOUR_ORACLE_NET_SERVICE_NAME_HERE 替换为 TNS net 服务名称,并将 SECURESTORE_ORACLE_APP_ID_HERE 替换为在 Secure Store Service 中设置的企业应用程序定义的名称。

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Model xmlns="https://schemas.microsoft.com/windows/2007/BusinessDataCatalog" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="https://schemas.microsoft.com/windows/2007/BusinessDataCatalog
 BDCMetadata.xsd" Name="OracleHRDB">
  <LobSystems>
  <LobSystem Type="Database" Name="OracleHR" DefaultDisplayName="Oracle 2">
    <Properties>
      <Property Name="WildcardCharacter" Type="System.String">%</Property>
    </Properties>
    <LobSystemInstances>
      <LobSystemInstance Name="Oracle HR Instance">
        <Properties>
        <Property Name="AuthenticationMode" Type="System.String">RdbCredentials
        </Property>
        <Property Name="DatabaseAccessProvider" Type="System.String">Oracle
        </Property>
        <Property Name="RdbConnection Data Source" Type="System.String">
          YOUR_ORACLE_NET_SERVICE_NAME_HERE</Property>
        <Property Name="SsoApplicationId" Type="System.String">
          SECURESTORE_ORACLE_APP_ID_HERE</Property>
        <!-- Server ship 
        <Property Name="SsoProviderImplementation" 
          Type="System.String">
            Microsoft.Office.SecureStoreService.Server.SecureStoreProvider,
            Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral,
            PublicKeyToken=71e9bce111e9429c</Property> --> 

         <!-- Client Ship -->
        <Property Name="SsoProviderImplementation" 
          Type="System.String">
          Microsoft.Office.BusinessData.Infrastructure.SecureStore.LocalSecureStoreProvider, 
          Microsoft.Office.BusinessData, Version=14.0.0.0, Culture=neutral, 
          PublicKeyToken=71e9bce111e9429c</Property> 
       </Properties>
      </LobSystemInstance>
    </LobSystemInstances>
    <Entities>
      <Entity EstimatedInstanceCount="10000" Name="Employee" 
        DefaultDisplayName="Employee" Namespace="HR.OracleModel" Version="1.0.0.0">
        <Properties>
          <Property Name="Title" Type="System.String">EName</Property>
        </Properties>
        <Identifiers>
          <Identifier TypeName="System.String" Name="EmployeeName" />
        </Identifiers>
        <Methods>
          <Method Name="EmployeeFinder">
            <Properties>
              <Property Name="RdbCommandText" Type="System.String">
                SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 
                FROM SCOTT.EMP 
                WHERE ENAME LIKE :Name 
                ORDER BY EMPNO</Property>
              <Property Name="RdbCommandType" Type="System.Data.CommandType, 
                System.Data, Version=2.0.0.0, Culture=neutral, 
                PublicKeyToken=b77a5c561934e089">
                Text</Property>
            </Properties>
            <FilterDescriptors>
              <FilterDescriptor Type="Wildcard" Name="EmployeeName" />
            </FilterDescriptors>
            <Parameters>
              <Parameter Direction="In" Name=":Name">
                <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName"
                  AssociatedFilter="EmployeeName" Name="EmployeeName" >
                  <DefaultValues>
                    <DefaultValue MethodInstanceName="IdEnumeratorInstance"
                      Type="System.String">%</DefaultValue>
                    <DefaultValue MethodInstanceName="EmployeeFinderInstance" 
                      Type="System.String">%</DefaultValue>
                  </DefaultValues>
                </TypeDescriptor>
              </Parameter>
              <Parameter Direction="Return" Name="Employees">
                <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, 
                  Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
                  IsCollection="true" Name="Employees">
                  <TypeDescriptors>
                    <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, 
                      Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
                      Name="Employee">
                      <TypeDescriptors>
                        <TypeDescriptor TypeName="System.Decimal" Name="EMPNO" />
                        <TypeDescriptor TypeName="System.String" ReadOnly ="true" 
                          IdentifierName="EmployeeName" Name="ENAME" />
                        <TypeDescriptor TypeName="System.String" Name="JOB" />
                        <TypeDescriptor TypeName="System.Decimal" Name="MGR" />                       
                        <TypeDescriptor TypeName="System.DateTime" Name="HIREDATE" />
                        <TypeDescriptor TypeName="System.Decimal" Name="SAL" />
                        <TypeDescriptor TypeName="System.Decimal" Name="COMM" />
                        <TypeDescriptor TypeName="System.Decimal" Name="DEPTNO" />
                      </TypeDescriptors>
                    </TypeDescriptor>
                  </TypeDescriptors>
                </TypeDescriptor>
              </Parameter>
            </Parameters>
            <MethodInstances>
              <MethodInstance Type="Finder" ReturnParameterName="Employees" 
                ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0" 
                Name="EmployeeFinderInstance" >
                 <AccessControlList>
                    <AccessControlEntry Principal="redmond\domain users">
                      <Right BdcRight="Execute"/>
                      <Right BdcRight="Edit"/>
                      <Right BdcRight="SetPermissions"/>
                      <Right BdcRight="SelectableInClients"/>
                    </AccessControlEntry>
                  </AccessControlList>
                 </MethodInstance>
              <MethodInstance Type="SpecificFinder" ReturnParameterName="Employees" 
                ReturnTypeDescriptorName="Employee" ReturnTypeDescriptorLevel="1" 
                Name="EmployeeSpecificFinderInstance" >
                <AccessControlList>
                    <AccessControlEntry Principal="redmond\domain users">
                      <Right BdcRight="Execute"/>
                      <Right BdcRight="Edit"/>
                      <Right BdcRight="SetPermissions"/>
                      <Right BdcRight="SelectableInClients"/>
                    </AccessControlEntry>
                  </AccessControlList>
                 </MethodInstance>
              <MethodInstance Type="IdEnumerator" ReturnParameterName="Employees" 
                ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0" 
                Name="IdEnumeratorInstance" >
                 <AccessControlList>
                    <AccessControlEntry Principal="redmond\domain users">
                      <Right BdcRight="Execute"/>
                      <Right BdcRight="Edit"/>
                      <Right BdcRight="SetPermissions"/>
                      <Right BdcRight="SelectableInClients"/>
                    </AccessControlEntry>
                  </AccessControlList>
                 </MethodInstance>
            </MethodInstances>
          </Method>
          <Method Name="Update" DefaultDisplayName="EmployeeUpdater">
            <Properties>
              <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, 
                Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                Text</Property>
              <Property Name="RdbCommandText" Type="System.String">
                UPDATE SCOTT.EMP 
                SET EMPNO=:EmpNo,JOB=:Job,MGR=:Mgr,HIREDATE=:HireDate,
                SAL=:Sal,COMM=:Comm,DEPTNO=:DeptNo 
                WHERE ENAME=:Name</Property>
            </Properties>
            <AccessControlList>
              <AccessControlEntry Principal="redmond\domain users">
                <Right BdcRight="Edit" />
                <Right BdcRight="Execute" />
                <Right BdcRight="SetPermissions" />
                <Right BdcRight="SelectableInClients" />
              </AccessControlEntry>              
            </AccessControlList>
            <Parameters>
              <Parameter Direction="In" Name=":EmpNo">
                <TypeDescriptor TypeName="System.Decimal" UpdaterField="true" 
                  Name="EMPNO" />
              </Parameter>
              <Parameter Direction="In" Name=":Name">
                <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" 
                  Name="EmployeeName" >
                </TypeDescriptor>
              </Parameter>
              <Parameter Direction="In" Name=":Job">
                <TypeDescriptor TypeName="System.String" UpdaterField="true" 
                  Name="JOB" />
              </Parameter>
              <Parameter Direction="In" Name=":Mgr">
                <TypeDescriptor TypeName="System.Decimal" UpdaterField="true" 
                  Name="MGR" />
              </Parameter>
              <Parameter Direction="In" Name=":HireDate">
                <TypeDescriptor TypeName="System.Nullable`1[[System.DateTime, mscorlib, 
                  Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" 
                  UpdaterField="true" Name="HIREDATE" />
              </Parameter>
              <Parameter Direction="In" Name=":Sal">
                <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib, 
                  Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" 
                  UpdaterField="true" Name="SAL" />
              </Parameter>
              <Parameter Direction="In" Name=":Comm">
                <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib, 
                  Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" 
                  UpdaterField="true" Name="COMM">
                  <Properties>
                    <Property Name="Decimal Digits" Type="System.Int32">9</Property>
                  </Properties>
                </TypeDescriptor>
              </Parameter>
              <Parameter Direction="In" Name=":Deptno">
                <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib, 
                  Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" 
                  UpdaterField="true" Name="DEPTNO">
                  <Properties>
                    <Property Name="Decimal Digits" Type="System.Int32">9</Property>
                  </Properties>
                </TypeDescriptor>
              </Parameter>
            </Parameters>
              <MethodInstances>
                <MethodInstance Type="Updater" Name="Update" 
                  DefaultDisplayName="SQLAllTypes Update">
                  <AccessControlList>
                    <AccessControlEntry Principal="redmond\domain users">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                   </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>
          <Method Name="Delete" DefaultDisplayName="EmployeeDelete">
            <Properties>
              <Property Name="RdbCommandText" Type="System.String">
                DELETE FROM SCOTT.EMP WHERE ENAME = :Name</Property>
              <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, 
                Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                Text</Property>
             </Properties>
            <AccessControlList>
              <AccessControlEntry Principal="redmond\domain users">
                <Right BdcRight="Edit" />
                <Right BdcRight="Execute" />
                <Right BdcRight="SetPermissions" />
                <Right BdcRight="SelectableInClients" />
              </AccessControlEntry>
              </AccessControlList>
            <Parameters>
               <Parameter Direction="In" Name=":Name">
                <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName" 
                  Name="EmployeeName" >
                </TypeDescriptor>
              </Parameter>
            </Parameters>
            <MethodInstances>
              <MethodInstance Type="Deleter" Name="Delete" DefaultDisplayName="Employee 
                Delete">
                <AccessControlList>
                  <AccessControlEntry Principal="redmond\domain users">
                    <Right BdcRight="Edit" />
                    <Right BdcRight="Execute" />
                    <Right BdcRight="SetPermissions" />
                    <Right BdcRight="SelectableInClients" />
                  </AccessControlEntry>
                 </AccessControlList>
              </MethodInstance>
            </MethodInstances>
          </Method>
        </Methods>
      </Entity>
    </Entities>
  </LobSystem>
  </LobSystems>
</Model>