In this article I am going to explain BCS in SharePoint 2010.
How we can make an external content type by using BCS. We will learn all of
this step by step.
What is BCS ?
Microsoft Business Connectivity Services (BCS), formerly named
the Business Data Catalog, enables users to read and write data from external
system through WCF service, databases, and Microsoft .NET Framework assemblies.
Business Connectivity Services are a set of services and
features that provide a way to connect SharePoint solutions to sources of
external data and to define external content types that are based on that
external data.
In this article I am showing how we can use WCF service to show
data from an external content source in SharePoint 2010.
So Now I am going to make a WCF service. Here I am showing an
Employee data base.
Open Visual Studio -> Add New Project -> Select WCF
Service Application.
Image 1.
Now Remove IService.cs
and Service.cs from your solution.
Right Click on your solution explorer -> Add New Item ->
Select WCF Service-> Name is as EmployeeService.svc -> Add
Image 2.
Now again Right click on Solution Explorer -> Add New Item
-> Add a Code File -> Name it as EmployeeDataContract
-> Add
Image 3.
Now open EmployeeDataContract.cs
and do below code:
Image 4.
Now open IEmployeeService.cs
file and do the below code:
Image 5.
Now open EmployeeService.cs
and do the below code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ManageEmployee_WCFService
{
// NOTE: You can
use the "Rename" command on the "Refactor" menu to change
the class name "EmployeeService" in code, svc and config file
together.
public class EmployeeService
: IEmployeeService
{
SqlConnection
con;
SqlCommand
cmd;
public
EmployeeService()
{
con = new
SqlConnection(@"Data
Source=.; Initial Catalog=TestData; Integrated Security=true;");
}
public int AddNewEmployee(EmployeeDataContract
EmpObj)
{
con.Open();
cmd = new
SqlCommand();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO EMPLOYEE (Name,Mobile,Email,City)
VALUES('" + EmpObj.Name + "',
'" + EmpObj.Mobile + "','"
+ EmpObj.Email
+ "','" +
EmpObj.City + "')";
cmd.ExecuteNonQuery();
con.Close();
return
0;
}
public List<EmployeeDataContract>
GetAllEmployees()
{
con.Open();
cmd = new
SqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM EMPLOYEE";
SqlDataReader
dr = cmd.ExecuteReader();
List<EmployeeDataContract> lstEmp = new List<EmployeeDataContract>();
while
(dr.Read())
{
lstEmp.Add(new EmployeeDataContract()
{
EMP_ID = Convert.ToInt32(dr["EMP_ID"]),
Name = dr["Name"].ToString(),
Mobile = dr["Mobile"].ToString(),
Email = dr["Email"].ToString(),
City = dr["City"].ToString()
});
}
dr.Close();
con.Close();
return
lstEmp;
}
public EmployeeDataContract GetEmployeeDetail(int EmployeeID)
{
con.Open();
cmd = new
SqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM EMPLOYEE WHERE EMP_ID='"
+ EmployeeID + "'";
SqlDataReader
dr = cmd.ExecuteReader();
EmployeeDataContract
empObj = new EmployeeDataContract();
while
(dr.Read())
{
empObj.EMP_ID = Convert.ToInt32(dr["EMP_ID"]);
empObj.Name = dr["Name"].ToString();
empObj.Mobile = dr["Mobile"].ToString();
empObj.Email = dr["Email"].ToString();
empObj.City = dr["City"].ToString();
}
dr.Close();
con.Close();
return
empObj;
}
public string UpdateEmployee(EmployeeDataContract
EmpObj)
{
con.Open();
cmd = new
SqlCommand();
cmd.Connection = con;
cmd.CommandText = "UPDATE EMPLOYEE SET Name='" +
EmpObj.Name + "', Mobile='" +
EmpObj.Mobile + "', Email='" +
EmpObj.Email + "', City='"
EmpObj.City + "' WHERE Emp_ID='" +
EmpObj.EMP_ID + "'";
cmd.ExecuteNonQuery();
con.Close();
return
"Record Updated";
}
public bool DeleteEmployee(int
EmployeeID)
{
con.Open();
cmd = new
SqlCommand();
cmd.Connection = con;
cmd.CommandText = "DELETE FROM EMPLOYEE WHERE Emp_ID='" +
EmployeeID + "'";
cmd.ExecuteNonQuery();
con.Close();
return
true;
}
}
}
Below
is my Data Table:
Image
6.
Now
time to host your WCF service in IIS. So publish your WCF service and save in
your computer anywhere. Now make a new web site in IIS and give it a port
number and map this IIS website to your WCF publish folder.
Now
access your hosted WCF service with your Full Computer Name like below.
Image
7.
Now
open your SharePoint 2010 site in SharePoint Designer:
Image
8.
Image
9.
Select
External Content Type here.
Image
10.
Here
give Name and display Name and Click on (click here to discover data sources
and define operations.)
Image
11.
Click
Add Connection and select WCF Service -> OK.
Image
12.
Here
Give Service Metadata URL and Service Endpoint URL and check (Define Custom
Proxy…) -> OK.
Image
13.
Now
you can see your all WCF methods here. Now
right click on every method and define it.
1.
AddNewEmployee
Image
14.
Image
15.
Image
16.
2. Delete Employee
Image
17.
Image
18.
Image
19.
3. GetAllEmployees()
Image
20.
Image
21.
Image
22.
Image
23.
Image
24.
Image
25.
Image
26.
4. UpdateEmployee()
Image
27.
Image
28.
Image
29.
Image
30.
Now
you can see all your External Content types Operations. Now time to add List so
in above ribbon click Create Lists &
Forms -> Yes.
Image
31.
Click
Ok.
Now
Open your site and see your List.
Image
32.
No
Need to worry. Go To Central Admin -> Application Management -> Manage
Service Application -> Business Data Connectivity Service -> Here Set
Meta Data Object permission as shown below:
Image
33.
Now
again view your List:
Image
34.
From
here by clicking on list you can perform your all operation is: Add New Item,
Delete an Item, Update an Item.
Image
35.
It may
be a chance while viewing you data you will get an error message ie: login
failed etc..
To
avoid this Go to your WCF Service hosted web site Application Pool in IIS ->
Right Click -> Advanced Setting -> Under Process Model -> Advanced
Setting -> Select LocalSystem under Built in Account -> OK.