SharePoint

SharePoint 2010: Business Connectivity Services (BCS) Using WCF

We will learn all of this step-by-step.

Business Connectivity Services (BCS)

Microsoft Business Connectivity Services (BCS), formerly named the Business Data Catalog, enables users to read and write data from an external system a 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 to use a WCF service to show data from an external content source in SharePoint 2010.

So now I will make a WCF service. Here I am showing an Employee database.

Open Visual Studio then seelct Add New Project then select WCF Service Application.

WCF Service Application
Image 1.

Now Remove IService.cs and Service.cs from your solution.

Right-click on your project in Solution Explorer then select Add New Item then select WCF Service then name it is EmployeeService.svc then click Add.

WCF Service
Image 2.

Now again right-click on your project in Solution Explorer then select Add New Item -> Add a Code File then name it EmployeeDataContract then click Add.

code file
Image 3.

Now open EmployeeDataContract.cs and use the following code:

EmployeeDataContract
Image 4.

Now open the IEmployeeService.cs file and use the following code:

IEmployeeService
Image 5.

Now open EmployeeService.cs and use the following code:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Runtime.Serialization;  
  5. using System.ServiceModel;  
  6. using System.Text;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9.   
  10. namespace ManageEmployee_WCFService  
  11. {  
  12.     // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "EmployeeService" in code, svc and config file together.  
  13.     public class EmployeeService : IEmployeeService  
  14.     {  
  15.         SqlConnection con;  
  16.         SqlCommand cmd;  
  17.   
  18.         public EmployeeService()  
  19.         {  
  20.             con = new SqlConnection(@"Data Source=.; Initial Catalog=TestData; Integrated Security=true;");  
  21.         }  
  22.   
  23.         public int AddNewEmployee(EmployeeDataContract EmpObj)  
  24.         {  
  25.             con.Open();  
  26.             cmd = new SqlCommand();  
  27.             cmd.Connection = con;  
  28.             cmd.CommandText = "INSERT INTO EMPLOYEE (Name,Mobile,Email,City) VALUES('" + EmpObj.Name + "', '" + EmpObj.Mobile + "','" + EmpObj.Email + "','" + EmpObj.City + "')";  
  29.             cmd.ExecuteNonQuery();  
  30.             con.Close();  
  31.             return 0;  
  32.         }  
  33.   
  34.         public List<EmployeeDataContract> GetAllEmployees()  
  35.         {  
  36.             con.Open();  
  37.             cmd = new SqlCommand();  
  38.             cmd.Connection = con;  
  39.             cmd.CommandText = "SELECT * FROM EMPLOYEE";  
  40.             SqlDataReader dr = cmd.ExecuteReader();  
  41.             List<EmployeeDataContract> lstEmp = new List<EmployeeDataContract>();  
  42.             while (dr.Read())  
  43.             {  
  44.                 lstEmp.Add(new EmployeeDataContract()  
  45.                     {  
  46.                         EMP_ID = Convert.ToInt32(dr["EMP_ID"]),  
  47.                         Name = dr["Name"].ToString(),  
  48.                         Mobile = dr["Mobile"].ToString(),  
  49.                         Email = dr["Email"].ToString(),  
  50.                         City = dr["City"].ToString()  
  51.                     });  
  52.             }  
  53.             dr.Close();  
  54.             con.Close();  
  55.             return lstEmp;  
  56.         }  
  57.   
  58.         public EmployeeDataContract GetEmployeeDetail(int EmployeeID)  
  59.         {  
  60.             con.Open();  
  61.             cmd = new SqlCommand();  
  62.             cmd.Connection = con;  
  63.             cmd.CommandText = "SELECT * FROM EMPLOYEE WHERE EMP_ID='" + EmployeeID + "'";  
  64.             SqlDataReader dr = cmd.ExecuteReader();  
  65.             EmployeeDataContract empObj = new EmployeeDataContract();  
  66.             while (dr.Read())  
  67.             {  
  68.                 empObj.EMP_ID = Convert.ToInt32(dr["EMP_ID"]);  
  69.                 empObj.Name = dr["Name"].ToString();  
  70.                 empObj.Mobile = dr["Mobile"].ToString();  
  71.                 empObj.Email = dr["Email"].ToString();  
  72.                 empObj.City = dr["City"].ToString();  
  73.             }  
  74.             dr.Close();  
  75.             con.Close();  
  76.             return empObj;  
  77.         }  
  78.   
  79.         public string UpdateEmployee(EmployeeDataContract EmpObj)  
  80.         {  
  81.             con.Open();  
  82.             cmd = new SqlCommand();  
  83.             cmd.Connection = con;  
  84.             cmd.CommandText = "UPDATE EMPLOYEE SET Name='" + EmpObj.Name + "', Mobile='" + EmpObj.Mobile + "', Email='" + EmpObj.Email + "', City='" + EmpObj.City + "' WHERE Emp_ID='" + EmpObj.EMP_ID + "'";  
  85.             cmd.ExecuteNonQuery();  
  86.             con.Close();  
  87.             return "Record Updated";  
  88.         }  
  89.   
  90.         public bool DeleteEmployee(int EmployeeID)  
  91.         {  
  92.             con.Open();  
  93.             cmd = new SqlCommand();  
  94.             cmd.Connection = con;  
  95.             cmd.CommandText = "DELETE FROM EMPLOYEE WHERE Emp_ID='" + EmployeeID + "'";  
  96.             cmd.ExecuteNonQuery();  
  97.             con.Close();  
  98.             return true;  
  99.         }  
  100.     }  
  101. }  
The following is my data table:

Data Table
Image 6.

It is now time to host your WCF service in IIS. So publish your WCF service and save it in your computer anywhere. Now make a new web site in IIS and provide 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 as in the following.

hosted WCF service
Image 7.

Now open your SharePoint 2010 site in SharePoint Designer:

SharePoint Designer
Image 8.

access your hosted WCF service
Image 9.

Select External Content Type here.

External Content Type
Image 10.

Here provide a Name and Display Name and click on "Click here to discover data sources and define operations.".

define operations
Image 11.

Click Add Connection and select WCF Service then click OK.

Add Connection
Image 12.

Here provide a Service Metadata URL and Service Endpoint URL and check "Define Custom Proxy…" then click OK.

Service Metadata URL
Image 13.

Now you can see your all WCF methods here.

Now right-click on every method and define it.

1. AddNewEmployee

AddNewEmployee
Image 14.

operation properties
Image 15.

input parameters
Image 16.

2. Delete Employee

Delete Employee
Image 17.

operation name
Image 18.

map to identifier
Image 19.

3. GetAllEmployees()

getallemployee
Image 20.

operation display name
Image 21.

return parameter configuration
Image 22.

wcf employee name
Image 23.

error and warning
Image 24.

click next
Image 25.

finish
Image 26.

4. UpdateEmployee()

update employee
Image 27.

choose basic setting
Image 28.

configure input parameters
Image 29.

create list and form
Image 30.

Now you can see all your External Content types operations. Now it is time to add a List so in the preceding ribbon click Create Lists and Forms then click "Yes".

select external list
Image 31.

Click OK.

Now open your site and see your List.

list tools
Image 32.

No need to worry. Go to Central Admin and select Application Management -> Manage Service Application -> Business Data Connectivity Service then here set the Meta Data Object permission as shown below:

Meta Data Object permission
Image 33.

Now again view your List:

view your List
Image 34.

From here by clicking on the list you can do all your operations: Add New Item, Delete an Item, Update an Item.

perform your operation
Image 35.

There is a chance when viewing the data that you will get the error message indicating login failed and so on.

To avoid this go to your WCF Service hosted web site Application Pool in IIS and right-click then seelct Advanced Setting then under Process Model select Advanced Setting then select LocalSystem under Built in Account then click "OK".

 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs