Integrating Oracle into SharePoint 2010 Using Business Data Connectivity Model

Article

Introduction:

Business Connectivity Services (BCS) is a new service introduced with SharePoint 2010 to allow SharePoint sites to connect to and manipulate external data. SharePoint 2007 had a similar facility in the form of Business Data Catalog (BDC) which made external data available within it's site. However, a major problem with BDC was the difficulty in creating solutions as there was no support in the 2007 designer. Most BDC solutions were simply for accessing external data; manipulating external data sources was extremely difficult.

With SharePoint 2010, BCS ships with out-of-box features such as solutions, services, and tools which make connecting to external data an easy task. Whether you want to retrieve Outlook contacts in a list offline or edit the contents of your document file or share your excel sheet online or reuse data from dynamic InfoPath forms or just update your business presentation, BCS enables deep content sharing, editing and integration in SharePoint 2010 with SharePoint Designer and Visual Studio tools. In this article, we are going to create a model for the BDC service that returns information from an Oracle database. You will then create an external list in SharePoint by using this model.

Steps Involved:

Oracle Database.
Create Business Data Connectivity Model.
Modify the default Entity from the BDC Model.
Read Operations.
Configure Business Data Connectivity access rights.
Creating External List.
Testing.

1. Oracle Database:

Table Name: hr.employeedetails

Note:

In the above table EMPLOYEE_ID is the Primary Key.

2. Create Business Data Connectivity Model:

Start Visual Studio 2010.
Open the New Project dialog box, expand the SharePoint node under the language that you want to use, and then click 2010.
In the Templates pane, select Business Data Connectivity Model. Name the project BdcModelUsingOracle, and then click OK.
The SharePoint Customization Wizard appears. This wizard enables you to select the site that you will use to debug the project and the trust level of the solution.
Click Finish to accept the default local SharePoint site and default trust level of the solution.

3. Modify the default Entity from the BDC Model:

In Solution Explorer, expand the BdcModel1 node, you could find Entity1.cs and Entity1Service.cs.
Rename Entity1.cs as Employee.cs.
Click Yes in the above pop up.
Replace the code in the Employee.cs as shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BdcModelUsingOracle.BdcModel1
{
    public partial class Employee
    {
        public int Employee_ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }}
The Business Data Connectivity model file opens in the BDC designer.
In the designer, right-click Entity1, and then click Properties.
Set the Name property to Employee.
Right-click Identifier1 and then click properties.
Set the Name property to Employee_ID and Type Name to System.Int32.
Delete the default methods ReadList and ReadItem.
The BDC designer will look as shown below.
Solution Explorer will look as shown below.

4. Read Operations:

ReadItem (Specific Finder Method).
ReadList (Finder Method).

ReadItem (Specific Finder Method):

On the BDC designer, select the Employee entity.
On the View menu, click Other Windows, and then click BDC Method Details and it will look as shown below.
In the BDC Method Details window, from the Add a Method drop-down list, select Create Specific Finder Method.

Visual Studio adds the following elements to the model. These elements appear in the BDC Method Details window as shown in the figure below.

• A method named ReadItem.
• An input parameter for the method.
• A return parameter for the method.
• A type descriptor for each parameter.
• A method instance for the method.
In the BDC Method Details window, click the drop-down list that appears for the Employee Type Descriptor, and then click Edit as shown in the figure above.

The BDC Explorer opens. The BDC Explorer provides a hierarchical view of the model as shown in the figure below.
In the Properties window, click the drop-down list that appears next to the Type Name property, click the Current Project tab, and then select Employee.
In the BDC Explorer, right-click the Employee, and then click Add Type Descriptor.

A new type descriptor named TypeDescriptor1 appears in the BDC Explorer.
In the Properties window, set the Name property to Employee_ID.
Click the drop-down list next to the Type Name property, and then select Int32.
Click the drop-down list next to the Identifier property, and then select Employee_ID.
Repeat step 6 to create a type descriptor for each of the following fields.
In the BDC designer, on the Employee entity, double-click the ReadItem method.

The EmployeeService.cs service code file opens in Code Editor.
Add the reference System.Data.OracleClient.dll.
In the EmployeeService class, replace the ReadItem method with the following code. This code performs the following tasks:

• Retrieves a record from Employee table.
• Returns an Employee entity to the BDC service.

public static Employee ReadItem(int employee_ID)
        {
            Employee employees = new Employee();
            string connectionString = "Data Source=orcl;Persist Security Info=True;" +
                   "User ID=system;Password=password-1;Unicode=True";
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                string sql = "SELECT * FROM hr.employeedetails where employee_id=" + employee_ID;
                command.CommandText = sql;
                OracleDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    employees.Employee_ID = employee_ID;
                    employees.FirstName = Convert.ToString(reader["FirstName"]);
                    employees.LastName = Convert.ToString(reader["LastName"]);
                }
            }
            return employees;
        }

ReadList (Finder Method):

In the BDC Method Details window, from the Add a Method drop-down list, select Create Finder Method.
The Type Descriptors of the return parameter have already been defined with the same structure as we just built above. This is because when creating a new method, BDC designer will search the possible Type Descriptors defined in the other methods of this entity and copy them to the newly created methods.

In the EmployeeService class, replace the code with the code shown below.

public static IEnumerable<Employee> ReadList()
        {
            List<Employee> employees = new List<Employee>();
            string connectionString = "Data Source=orcl;Persist Security Info=True;" +
                   "User ID=system;Password=password-1;Unicode=True";
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                OracleCommand command = connection.CreateCommand();
              string sql = "SELECT * FROM hr.employeedetails";
                command.CommandText = sql;
                OracleDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Employee employee = new Employee();
                    employee.Employee_ID = Convert.ToInt32(reader["Employee_ID"]);
                    employee.FirstName = Convert.ToString(reader["FirstName"]);
                    employee.LastName = Convert.ToString(reader["LastName"]);
                    employees.Add(employee);
                }
            }

            return employees;
        }
Build the solution and deploy it.

5. Configure Business Data Connectivity access rights:

Go to Central Administration -> Application Management -> Manage Service Applications.
Click on Business Data Connectivity Service.
In the top Ribbon click on Manage.
In Service Application Information check the External Content Type Employee.
And in the top Ribbon click the Site Object Permissions.
Site Object Permissions wizard will pop up add the account (Group or Users) and assign the permissions.

6. Creating External List:

Open the SharePoint Site.
Go to Site Actions => More Options.
On the Create Wizard, from the Installed Templates Select List.
In the List Type select External List and click Create.
Enter the Name as BCS for Oracle and choose the External Content Type as shown below.
Click OK.
External List is created displaying the items from the Oracle data.

7. Testing:

We have created Read operations using BDC model and if any update is done in the Oracle database it will be reflected in the external list that we have created in the SharePoint 2010. Open the Oracle SQL * Plus. I am going to update and insert data as shown below.