Creating External content type with CRUD operations using Business Data Connectivity Model in SharePoint 2010

Introduction:

Business Connectivity Services (BCS) is a new service introduced with SharePoint 2010 to allow SharePoint sites to connect to and manipulate external data. SharePoint 2007 had a similar facility in the form of Business Data Catalog (BDC) which made external data available within its site. However, a major problem with BDC was the difficulty in creating solutions as there was no support in the 2007 designer.  Most BDC solutions were simply for accessing external data, manipulating external data sources was extremely difficult.

With SharePoint 2010, BCS ships with out-of-box features such as solutions, services, and tools which make connecting to external data an easy task. Whether you want to retrieve Outlook contacts in a list offline or edit the contents of your document file or share your excel sheet online or reuse data from dynamic InfoPath forms or just update your business presentation, BCS enables deep content sharing, editing and integration in SharePoint 2010 with SharePoint Designer and Visual Studio tools. In this article, we are going to create a model for the BDC service that returns information from a SQL database. You will then create an external list in SharePoint by using this model.

Steps Involved:

1. SQL Database.
2. Create Business Data Connectivity Model.
3. Add LINQ to SQL class.
4. Removing the default Entity from the BDC Model.
5. Adding an Entity to the Model.
6. CRUD Operations.
7. Configure Business Data Connectivity access rights.
8. Creating External List.
9. Testing.

1. SQL Database:

First we need to create a sample database in SQL which is going to be connected to the SharePoint. Simply create a new database in your SQL Server and have it filled with some sample data. In my case, I have created a SQL database, called Customer_Details. In the database Customer_Details I have added one table called Customers. See the details of the table in the following figure.

CustomerID	FirstName	LastName
206570	Vijai	Anand

2. Create Business Data Connectivity Model:

1. Start Visual Studio 2010.
2. Open the New Project dialog box, expand the SharePoint node under the language that you want to use, and then click 2010.
3. In the Templates pane, select Business Data Connectivity Model. Name the project BdcModel, and then click OK.
   
   
   
   
4. The SharePoint Customization Wizard appears. This wizard enables you to select the site that you will use to debug the project and the trust level of the solution.
   
   
   
   
5. Click Finish to accept the default local SharePoint site and default trust level of the solution.

3. Create LINQ to SQL Class:

1. On the Tools menu, click Connect to Database.
2. The Add Connection dialog box opens.
3. Add a connection to the SQL Server Customer_Details sample database. 
4. In Solution Explorer, click the project node.
5. On the Project menu, click Add New Item.
6. In the Installed Templates pane, select the Data node.
   
   
   
   
7. In the Templates pane, select LINQ to SQL Classes.
8. In the Name box, type LINQtoSQL, and then click Add.
9. A .dbml file is added to the project and the Object Relational Designer opens.
10. On the View menu, click Server Explorer.
11. In Server Explorer, expand the node that represents the Customer_Details sample database, and then expand the Tables node.
12. Drag the Customers table onto the Object Relational Designer.
    
    
    

4. Removing the default Entity from the BDC Model:

1. In Solution Explorer, expand the BdcModel1 node, and then double-click the BdcModel1.bdcm file.
2. The Business Data Connectivity model file opens in the BDC designer.
3. In the designer, right-click Entity1, and then click Delete.
4. In Solution Explorer, right-click Entity1.cs, and then click Delete.
5. Right-click Entity1Service.cs and then click Delete.

5. Adding an Entity to the Model:

1. On the View menu, click Toolbox.
2. From the BusinessDataConnectivity tab of the Toolbox, drag an Entity onto the BDC designer.
   
   
   
   
3. The new entity appears on the designer. Visual Studio adds a file to the project named EntityService.cs.
4. On the View menu, click PropertiesWindow.
   
   
   
   
5. In the Properties window, set Name to Customer.
6. On the designer, right-click the Customer, click Add, and then click Identifier.
   
   
   
   
7. A new identifier appears on the entity.
8. In the Properties window, change the name of the identifier to CustomerID.
   
   
   
   
9. In the Type Name drop-down list, select System.Int32.
   
   
   

6. CRUD Operations:

• ReadItem (Specific Finder Method).
• ReadList (Finder Method).
• Create.
• Update.
• Delete.

ReadItem (Specific Finder Method):

1. On the BDC designer, select the Customer entity.
2. On the View menu, click Other Windows, and then click BDC Method Details and it looks like the following one.
   
   
   
   
3. In the BDC Method Details window, from the Add a Method drop-down list, select Create Specific Finder Method.
   
   
   
   Visual Studio adds the following elements to the model. These elements appear in the BDC Method Details window as shown in the following figure.
   
   
   • A method named ReadItem.
   • An input parameter for the method.
   • A return parameter for the method.
   • A type descriptor for each parameter.
   • A method instance for the method.
     
     
      
4. In the BDC Method Details window, click the drop-down list that appears for the Customer Type Descriptor, and then click Edit as shown in the preceding figure.
   The BDC Explorer opens. The BDC Explorer provides a hierarchical view of the model as shown in the following figure.
   
   
   
   
5. In the Properties window, click the drop-down list that appears next to the Type Name property, click the Current Project tab, and then select Customer.
   
   
   
   
6. In the BDC Explorer, right-click the Customer, and then click Add Type Descriptor.
   A new type descriptor named TypeDescriptor1 appears in the BDC Explorer.
   
   
   
   
7. In the Properties window, set the Name property to CustomerID.
8. Click the drop-down list next to the Type Name property, and then select Int32. 
9. Click the drop-down list next to the Identifier property, and then select CustomerID.
   
   
   
   
10. Repeat step 6 to create a type descriptor for each of the following fields. 
     

    Name	Type Name
    FirstName	System.String
    LastName	System.String

11. In the BDC designer, on the Customer entity, double-click the ReadItem method.
    
    
    
    The CustomerService.cs service code file opens in Code Editor.
12. In the CustomerService class, replace the ReadItem method with the following code. This code performs the following tasks:
    
    • Retrieves a record from Customer table of the Customer_Details database.
    • Returns a Customer entity to the BDC service.
    
    

    public static Customer ReadItem(int customerID)
    {
      const string ServerName = @"UKCBEMTSEKIKM01\INSQL01";
      LINQtoSQLDataContext dataContext = new LINQtoSQLDataContext
      ("Data Source=" + ServerName + ";" +
      "Initial Catalog=Customer_Details;Integrated Security=True");
      Customer custometer =(from customers in   dataContext.Customers.AsEnumerable().Take(20)
      where customers.CustomerID == customerID
      select customers).Single();
      return custometer;
    }
    

ReadList (Finder Method), Create, Delete, Update:

1. In the BDC Method Details window, from the Add a Method drop-down list, select Create Finder Method.
   
   
   
   
2. The Type Descriptors of the return parameter have already been defined with the same structure as we just built above. This is because when creating a new method, BDC designer will search the possible Type Descriptors defined in the other methods of this entity and copy them to the newly created methods. 
3. Similarly add the other methods from the Add a Method drop-down list. After adding all the Methods the BDC Explorer looks like the following one.
   
   
   
   In the CustomerService class, replace the code with the following one.
   
   

   using System;

   using System.Collections.Generic;

   using System.Linq;

   using System.Text;

   using BdcModel;

    

   namespace BdcModel.BdcModel1

   {

       public partial class CustomerService

       {

           public static Customer ReadItem(int customerID)

           {

               const string ServerName = @"UKCBEMTSEKIKM01\INSQL01";

               LINQtoSQLDataContext dataContext = new LINQtoSQLDataContext

                     ("Data Source=" + ServerName + ";" +

                      "Initial Catalog=Customer_Details;Integrated Security=True");

               Customer custometer =(from customers in dataContext.Customers.AsEnumerable().Take(20)

                                     where customers.CustomerID == customerID

                                     select customers).Single();

               return custometer;

           }

    

           public static IEnumerable<Customer> ReadList()

           {

               const string ServerName = @"UKCBEMTSEKIKM01\INSQL01";

               LINQtoSQLDataContext dataContext = new LINQtoSQLDataContext

                     ("Data Source=" + ServerName + ";" +

                      "Initial Catalog=Customer_Details;Integrated Security=True");

               IEnumerable<Customer> cust =

           from customers in dataContext.Customers.Take(20)

           select customers;

               return cust;

           }

    

           public static Customer Create(Customer newCustomer)

           {

               const string ServerName = @"UKCBEMTSEKIKM01\INSQL01";

               LINQtoSQLDataContext dataContext = new LINQtoSQLDataContext

                     ("Data Source=" + ServerName + ";" +

                      "Initial Catalog=Customer_Details;Integrated Security=True");

               dataContext.Customers.InsertOnSubmit(newCustomer);

               dataContext.SubmitChanges();

               Customer cust = dataContext.Customers.Single(c => c.CustomerID == newCustomer.CustomerID);

               return cust;

           }

    

           public static void Delete(int customerID)

           {

               const string ServerName = @"UKCBEMTSEKIKM01\INSQL01";

               LINQtoSQLDataContext dataContext = new LINQtoSQLDataContext

                     ("Data Source=" + ServerName + ";" +

                      "Initial Catalog=Customer_Details;Integrated Security=True");

               Customer cust = dataContext.Customers.Single(c => c.CustomerID == customerID);

               dataContext.Customers.DeleteOnSubmit(cust);

               dataContext.SubmitChanges();

    

           }

    

           public static void Update(Customer customer)

           {

               const string ServerName = @"UKCBEMTSEKIKM01\INSQL01";

               LINQtoSQLDataContext dataContext = new LINQtoSQLDataContext

                     ("Data Source=" + ServerName + ";" +

                      "Initial Catalog=Customer_Details;Integrated Security=True");

               Customer cust = dataContext.Customers.Single(c => c.CustomerID == customer.CustomerID);

               cust.CustomerID = customer.CustomerID;

               cust.FirstName = customer.FirstName;

               cust.LastName = customer.LastName;

               dataContext.SubmitChanges();

           }

       }}

4. Build the solution and deploy it.

Note:

For Update method the Type Descriptor Customer should have the following properties.

 

7. 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 ECT.
   
   
   
   
5. And 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.
7. Once you have configured Business Data Connectivity access rights navigate to the site and check the External List –BCS List that we have created.

8. Creating External List:

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 and choose the External Content Type as shown below.
   
   
   
   
6. Click OK.
7. External List is created displaying the items from the SQL data.
   
   
   

9. Testing:

We have created CRUD operations using BDC model and in the BCS list all those operations can be done and it will be reflected in the SQL database.

Creating a new item:

 

Editing an Item:

 

CRUD operations can be performed for the External List BCS list and whatever changes made will be reflected in the SQL database as shown below.

             

Summary:

Thus we have created a model for the BDC service that returns information from a SQL database and performs all CRUD operations. 

Note:

The table that we have created in the SQL database should have Primary Key.