SharePoint 2010: Programming with Business Data Connectivity Model Using Visual Studio 2010

Posted by: Mahesh Sabnis , on 7/30/2012, in Category SharePoint
Views: 66860
Abstract: In this article, we will be discussing integration between SharePoint 2010 web site and External Data Source using Visual Studio 2010 Business Data Connectivity Model project template

SharePoint as a web application platform is becoming popular amongst various domains of application development. There are several requirements for integrating a SharePoint 2010 Web Site with an external system which consumes data. If a developer has a strong programming background using Visual Studio 2010, the SharePoint 2010 Templates integrated with VS2010 prove useful to provide such an integrated solution.

In today’s scenario, where SharePoint 2010 based applications are used for automation purpose, similar integration requirements are going to popup every now and then. In VS 2010, the ‘Business Data Connectivity Model’ project template allows developers to design solutions for SharePoint 2010 for External Data Source integration.

 

In this article, we will be discussing integration between SharePoint 2010 web site and External Data Source using VS 2010 capabilities. You may be having some doubts about whether to use SharePoint 2010 Designer or Visual Studio 2010 for external content types, so here are some differences between them:

  • SharePoint Designer 2010 directly connects to the SharePoint 2010 site and external content type is directly written in the business data store. Unlike SPS 2010 Designer, VS2010, creates a model file with extension “.bdcm”. This file defines all external content types. This file then gets packaged into the project output assembly and then gets deployed as .WSP file in SharePoint 2010.
  • VS 2010 allows External Content Types (having data provided by .NET assemblies), which makes the code in the assembly as a proxy for External Data communication. Such an approach allows developer to add custom security code, additional business rules for working with more than one data source at a time.

For this application, I am using an already existing site ‘hxxp://win-v04rsve054t/sites/ss’. The SQL Server Database used here is ‘Company’ and the table is as shown below

image

 

Working with External Data Communication using VS 2010

Step 1: Open VS2010 and Create a new Empty SharePoint Project, name it as ‘SPS_ECM_ProductList’.

image002

Make this project as deploy as farm solution as shown below:

image

Step 2: In this project, right click and add a new Business Data Connectivity Model, name it as ‘ProductModel:

image

Once you click OK, a model gets generated as shown below:

image

If you have worked with ADO.NET Entity Framework, you will find a similar type of entity generated. The reason behind this similar type of representation is because the BDCM is based upon ADO.NET EF. The Identifier1 represents the primary or Identity for the Item from the source. The ReadList method is designed to return collection type of data from the source and ReadItem is defined to return a single record from the source.

Step 3: For this demo, we are going to use LINQ to SQL for connecting to SQL Server database and its ProductInfo table. On the project, right click and add a new LINQ to SQL class as shown below and name it as ‘ProductInfoDataModel’:

image006

Complete the Database connectivity Wizard.

image007

Step 4: Once the DataModel is available with us, it’s time to configure the BCDM. Rename ‘Entity1’ to ‘ProductInfo’ and ‘Identity1’ to ‘ProductID’ and set its type to Int32 as shown below:

image008

The final design will be as shown below:

image009

Step 5: Now is the time for us to define operations (methods) for BCDM. Open BDC Explorer and rename ‘ReadList’ to ‘ReadProductList’, Expand ‘ReadProductList’ and rename the returnParameter from ‘Entity1List’ to ‘ProductList’ as shown below:

 

image010

This is an out return type from the method. Now right-click on ‘ProductList’ and select ‘properties’, as shown below:

image011

Click on Type Name

image012

Select ‘ProductInfo’, as the method returns a List of ProductInfo and keep the ‘Is Enumerable’ checkbox checked, which indicates that the collection will be returned from the method.

Expand ProductInfo and you will find ‘Identifier1’ and ‘Message’ properties there, so change them to ‘ProductInfo’ properties. Right-Click on ‘ProductInfo’ and select ‘Add Type Descriptor’ and add ProductID, ProductName,Manifacturer and Price in it and set its data types. The final design will be as shown below:

image013

Step 6: Now repeat similar steps for ‘ReadItem’ method and rename it to ‘ReadProduct’. To define ‘id’ and ‘return’ parameter, copy the ProductInfo from ‘ReadProductList’ and paste in ‘returnParameter’ of ‘ReadProduct’. Just click on ‘Yes’ in the message box that appears

image014

Also copy ‘ProductID’ from the returnParameter of the ReadProductList and paste it in the ‘id’ of the ‘ReadProduct’ method. You will see a similar message as shown above for replacing Type Descriptor, just click ‘Yes’

image015

Step 7: Click on ‘ProductInfo’ in BDCM designer, the method info will be as shown below:

image016

 

Step 8: Now we need to add Create, Update and Delete method. So click on the ‘Add to Method’ at the bottom:

image017

You will get a List of methods:

image018

Add Creator, Updater and Deleter methods and rename them as ‘CreateProductInfo’, ‘UpdateProductInfo’ and ‘DeleteProductInfo’

image019

image020

image021

 

Step 9: Now its time for us to write the code for database connectivity and implement Create, Read, ReadList, Update and Delete methods. Now the question is how to configure the Database ConnectionString to BDCM and access it in code. Go to the BDC Explorer and select ‘LocalSystemInstances’, rename ‘ProductModel’ to ‘productModelDBConnect’, go to the properties and select ‘Custom Properties’ and add a connection string as shown below:

image022

In the project, add a reference to ‘Microsoft.BusinessData’ assembly from the following path:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.BusinessData.dll

Step 9: Open ‘ProductInfoService.cs’ and write the following code which performs CRUD operations:

using System;
using System.Collections.Generic;
using System.Linq;
using SPS_ECM_ProductList;
using Microsoft.BusinessData.SystemSpecific;

namespace SPS_ECM_ProductList.ProductModel
{
    /// <summary>
    /// All the methods for retrieving, updating and deleting data are implemented in this class file.
    /// The samples below show the finder and specific finder method for Entity1.
    /// </summary>
    public class ProductInfoService : IContextProperty
    {

        internal string AccessConnectionString()
        {
            Microsoft.BusinessData.MetadataModel.Collections.INamedPropertyDictionary dictionary =
                this.LobSystemInstance.GetProperties();

            if (dictionary.ContainsKey("CompanyConnStr"))
            {
                return dictionary["CompanyConnStr"].ToString();
            }
            return null;
        }

        /// <summary>
        /// This is a sample specific finder method for Entity1.
        /// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
        /// </summary>
        /// <param name="id"></param>
        /// <returns>Entity1</returns>
        public  ProductInfo ReadProduct(int id)
        {
            ProductInfoDataModelDataContext objContext =
                new ProductInfoDataModelDataContext(AccessConnectionString());
            ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == id);
            return Product;
        }
        /// <summary>
        /// This is a sample finder method for Entity1.
        /// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
        /// </summary>
        /// <returns>IEnumerable of Entities</returns>
        public  IEnumerable<ProductInfo> ReadProductList()
        {
            ProductInfoDataModelDataContext objContext =
                new ProductInfoDataModelDataContext(AccessConnectionString());
            return objContext.ProductInfos.ToList(); 
        }

        public  ProductInfo CreateProductInfo(ProductInfo newProductInfo)
        {
            ProductInfoDataModelDataContext objContext =
                new ProductInfoDataModelDataContext(AccessConnectionString());
            objContext.ProductInfos.InsertOnSubmit(newProductInfo);
            objContext.SubmitChanges();


            ProductInfo Product =
                objContext.ProductInfos.Single(p => p.ProductID == newProductInfo.ProductID);
            return Product;
        }

        public  void UpdateProductInfo(ProductInfo productInfo)
        {
            ProductInfoDataModelDataContext objContext =
                new ProductInfoDataModelDataContext(AccessConnectionString());
            ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == productInfo.ProductID);
            Product.ProductName = productInfo.ProductName;
            Product.Manifacturer = productInfo.Manifacturer;
            Product.Price = productInfo.Price;
            objContext.SubmitChanges();
        }

        public  void DeleteProductInfo(int productID)
        {
            ProductInfoDataModelDataContext objContext =
                new ProductInfoDataModelDataContext(AccessConnectionString());
            ProductInfo Product = objContext.ProductInfos.Single(p => p.ProductID == productID);
            objContext.ProductInfos.DeleteOnSubmit(Product);
            objContext.SubmitChanges();

        }

        public Microsoft.BusinessData.Runtime.IExecutionContext ExecutionContext
        {
            get;
            set;
        }

        public Microsoft.BusinessData.MetadataModel.ILobSystemInstance LobSystemInstance
        {
            get;
            set;
        }

        public Microsoft.BusinessData.MetadataModel.IMethodInstance MethodInstance
        {
            get;
            set;
        }
    }
}

 

The above code shows that an interface ‘IContextProperty’ is implemented which can access the Business Data Connectivity service. The method ‘AccessConnectionString’ reads the ConnectionString from the ‘LocSystemInstance’ properties collection using the Key of the Connection string set in Step 8.

Step 10: Open Feature1.Template.xml and add the following Xml which helps in deploying the application on the web site.

<Properties>
    <Property Key="SiteUrl" Value="hxxp://MyServer/sites/ss"/>
</Properties>

Step 11: Deploy the application and using F5 to run it. This will start the SharePoint web site. To display the ProductInfo List on the Quick launch, click on ‘Site Actions’ on the top-left and select ‘More Options’ as shown below

image023

This action will open the ‘Create’ window. Select ‘Data’ from Filter type and select ‘External List’ as shown below:

image024

Click on the ‘Create’ button. A new Window will open where you need to set the Name of the List and also choose the External Content type to use as the Data source for the list:

image025

This will open the ‘External Content Type Picker’ window. Select ‘ProductModelDBConnect’ and click on ’OK’:

image026

This takes you back to the previous window where you Click on ‘Create’ and a new List will be created on the Quick Launch bar of the site.

Step 13: Now to set the access rights from the SharePoint Site to External Data Source,  add the current login user from the List and set its access rights. You can do this using the ‘Central Administration Web Site’. Select ‘Application Management’ > Service Applications > Manage Service Applications > Business Data Connectivity Service. Here Select ‘ProductInfo’ checkbox and click on ‘Set Objects Permissions ’:

image027

Add the User and set his/her rights as shown below :

image028

This is required because the connection will be made from your Web Site hosted in IIS, to the External Data Source using Business Data Connectivity Web Service. So to authenticate and authorize the user, we need to set the access rights. Click on Ok.

Step 14: Return to the site and click on the ‘ProductInfoList’ (note: Your application in VS2010 must be running). A List Data appears:

image029

Now you can test your Insert, Update and Delete operations.

Note: You must deploy the project assembly created in VS2010 in GAC so that it can be used by Web Site for performing CRUD operations.

Conclusion: Using VS 2010 SharePoint template, it is easy to design more controlled source code for Business Data Connectivity model using sophisticated programming methodology. Here a developer has complete freedom to implement domain specific logic to establish connectivity to an external data system from the SharePoint Web site.

The entire source code of this article can be downloaded over here

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Mayank on Tuesday, July 31, 2012 11:27 PM
Very useful article for programming the BDC. Thanks for sharing it
Comment posted by Rameshwari on Monday, August 27, 2012 6:34 AM
Good Article for learning
Comment posted by Hardik on Tuesday, December 18, 2012 1:10 AM
I have configured the same. Method is too good. But facing issue as:

MethodInstance with Name 'ReadProductList' on Entity (External Content Type) with Name 'ProductInfo' in Namespace 'SPS_ECM_ProductList.ProductModel' failed unexpectedly. The failure occurred in method 'ReadProductList' defined in class 'SPS_ECM_ProductList.ProductModel.ProductInfoService' with the message 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.'.

Correlation ID:61c5467f-15d1-496f-bea1-2c0f9ce5950e
Comment posted by Hardik on Tuesday, December 18, 2012 1:23 AM
I have configured the same. Method is too good. But facing issue as:

MethodInstance with Name 'ReadProductList' on Entity (External Content Type) with Name 'ProductInfo' in Namespace 'SPS_ECM_ProductList.ProductModel' failed unexpectedly. The failure occurred in method 'ReadProductList' defined in class 'SPS_ECM_ProductList.ProductModel.ProductInfoService' with the message 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.'.

Correlation ID:61c5467f-15d1-496f-bea1-2c0f9ce5950e
Comment posted by Zahid Hayat on Thursday, April 11, 2013 7:11 AM
Downloaded the code. Cannot see the BDC model in the explorer as described in the article.
Comment posted by w on Thursday, October 3, 2013 7:21 AM
sddeee
Comment posted by Sushri on Monday, October 28, 2013 1:42 AM
Can we create a external content type using sandbox solution using the above code?
Comment posted by upamal on Monday, May 19, 2014 8:28 PM
hi,
i followed the all instructions and created three external content types. all three were available when i set permission, but when i try to create external list one external content type is missing in the External Content Type Picker’ window. i check the feature, package. farm feature for that external content type is also activated. any idea ?

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel