RSS

How to connect to the Oracle database using Business Connectivity Services (BCS) in SharePoint 2010

27 Aug

In this Step by Step article I will show you how to connect to the oracle database using BCS.

Note: Before we start I will assume that you are already have running and working oracle database.

Prerequisites

Table Name: Orders

Column Name Type Primary Key
Order_ID Number (5) Yes
Order_Name Varchar 2 (50) No
  • Add oracle connection string in tnsnames.ora as following

Go to C: or D: –>App-> Your User Name->product->11.2.0->client_1->Network->Admin.

after going to Admin folder you will find to files (sqlnet.ora,tnsnames.ora)image

Note:if you did not find those files go to sample folder and copy both files and past it in Admin Folder.

  • Now open tnasnames.ora file and add connection string of your oracle DataBase similar to the following syntax

image

Creating a BCS External Content Type

1- Open Visual Studio and create new sharepoint 2010 project as following and select Deploy as a farm solution

image

image

2- After Creating project add new SharePoint Item (SPI) with type Business Data Connectivity Modelimage

3-Now Remove Entity1, Entity1.cs and Entity1Service.cs

image

image

4-Now add new Entity with Orders Name and add identifier with Order_ID name and type System.Int32

image

image

5- Create New class with Order Name

image

6- Open Order.cs and the following code

image

7- Add Oracle.DataAccess.dll reference from the following path

D:\app\yourusername\product\11.2.0\client_1\odp.net\bin\2.x

8- Now it’s time to Create Method for insert,update,delete,retrive.

9-Click on Order Entity on BDC Diagram and add the following Methods.

image

10- now the BDC will be looking to the following diagram

image

11- Now Go to BDC explorer to make sure the Orders identity have the correct Type descriptors for each method as following

image

Note: Make sure to set identifier property  for each Order_ID on each method to Order_ID identifier

image

Note:For Update Method you need to set Pre-Update Field prperty to True for Order_ID in parameter section.

image

12- Open Order.cs and write the following code .


public partial class OrdersService
{
static string connectionString = "Data Source=dotnetfinder;User ID=ahmed; Password=sharepoint";
public static Orders ReadItem(int order_ID)
{

Orders SelectedOrder=new Orders();;
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand("select * from orders where order_id=:order_id", connection);

connection.Open();
cmd.Parameters.Add(":order_id", order_ID);
OracleDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
SelectedOrder.Order_ID = Convert.ToInt32(Reader["Order_ID"]);
SelectedOrder.Order_Name = Reader["Order_Name"].ToString();
}
}
return SelectedOrder;
}

public static IEnumerable<Orders> ReadList()
{

List<Orders> OrderList = new List<Orders>();
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand("select * from orders", connection);
connection.Open();
OracleDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
Orders SelectedOrder = new Orders();
SelectedOrder.Order_ID = Convert.ToInt32(Reader["Order_ID"]);
SelectedOrder.Order_Name = Reader["Order_Name"].ToString();
OrderList.Add(SelectedOrder);
}
}
return OrderList;
}

public static Orders Create(Orders newOrders)
{

using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand("insert into orders (order_id,order_name) values (:order_id,:order_name)", connection);
connection.Open();
cmd.Parameters.Add(":order_id", newOrders.Order_ID);
cmd.Parameters.Add(":order_name", newOrders.Order_Name);

cmd.ExecuteNonQuery();
}
return newOrders;
}

public static void Update(Orders orders, int parameter)
{

using (OracleConnection connection = new OracleConnection(connectionString))
{

OracleCommand cmd = new OracleCommand(string.Format("update orders set order_name=:order_name where order_id={0}",parameter), connection);
connection.Open();

cmd.Parameters.Add(":order_name", orders.Order_Name);

cmd.ExecuteNonQuery();
}
}
}

13-Deploy your solution Now and create New External List and pick up our external content type that we have created in this article

Configure Business Data Connectivity access rights:

1. Go to Central Administration -> Application Management -> Manage Service Applications.

17

2. Click on Business Data Connectivity Service.

3. In the top Ribbon click on Manage.
4. In Service Application Information check the External Content Type Orders

5. In the top Ribbon click the Site Object Permissions.

6. Site Object Permissions wizard will pop up add the account (Group or Users) and assign the permissions.

Creating an External List in the SharePoint Site:

1. Open the SharePoint Site.

2. Go to Site Actions => More Options.
3. On the Create Wizard, from the Installed Templates Select List.

4. In the List Type select External List and click Create.

5. Enter the Name as BCS for OracleDB and choose the External Content Type as shown below.

image

6-Now you can find the external list with data populated from Oracle Database

image

Try now to Create,Update,Delete and Retrieve items and Every Thing should work fine Open-mouthed smile

You can download Source Code from here

 
1 Comment

Posted by on August 27, 2012 in C#, SharePoint, VB.NET

 

Tags: , , , , , , , ,

One response to “How to connect to the Oracle database using Business Connectivity Services (BCS) in SharePoint 2010

  1. Mohammad B.Masadeh

    February 7, 2013 at 1:22 pm

    As usual Mate, its very enough to reach wht i need.
    Thanx a lot

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: