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.


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.


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.


Image 3.

Now open EmployeeDataContract.cs and use the following code:


Image 4.

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


Image 5.

Now open EmployeeService.cs and use the following 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;
        }
    }
}

The following is my 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.


Image 7.

Now open your SharePoint 2010 site in SharePoint Designer:


Image 8.


Image 9.

Select External Content Type here.


Image 10.

Here provide a 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 then click OK.


Image 12.

Here provide a Service Metadata URL and Service Endpoint URL and check "Define Custom Proxy…" then click 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 it is time to add a List so in the preceding ribbon click Create Lists and Forms then click "Yes".


Image 31.

Click OK.

Now open your site and see your List.


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:


Image 33.

Now again 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.


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".