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
- 64-bit Oracle Data Access Component (ODAC) from oracle site.Check the following link (http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html)
- Create New Table on Oracle Database with following specification
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)
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
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
2- After Creating project add new SharePoint Item (SPI) with type Business Data Connectivity Model
3-Now Remove Entity1, Entity1.cs and Entity1Service.cs
4-Now add new Entity with Orders Name and add identifier with Order_ID name and type System.Int32
5- Create New class with Order Name
6- Open Order.cs and the following code
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.
10- now the BDC will be looking to the following diagram
11- Now Go to BDC explorer to make sure the Orders identity have the correct Type descriptors for each method as following
Note: Make sure to set identifier property for each Order_ID on each method to Order_ID identifier
Note:For Update Method you need to set Pre-Update Field prperty to True for Order_ID in parameter section.
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.
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.
6-Now you can find the external list with data populated from Oracle Database
Try now to Create,Update,Delete and Retrieve items and Every Thing should work fine 
You can download Source Code from here
DotNet Finder 