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

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

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

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

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

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

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’:

Complete the Database connectivity Wizard.

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:

The final design will be as shown below:

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:

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

Click on Type Name

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:

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

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’

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

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

You will get a List of methods:

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

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:

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

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

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:

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

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 ’:

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

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:

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

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn