Today, we’ll learn to build Business Connectivity Services in SharePoint with .Net Connectivity Assembly using Microsoft Visual Studio 2013 for SharePoint (In-Premises)

Let’s create a database ExternalDatabase in SQL Server with a table Employees. This has following columns:

1. EmpNo – Primary Key, int type, not null
2. EmpName – varchar(50), not null
3. Department – varchar(50), not null
4. Location – varchar(50), null
5. Country – varchar(50), null

 

Let’s move to Visual Studio now.

Following are the steps:

1. Create an Empty SharePoint project mapped with development SharePoint site and deploy as Farm solution option.
2. Once Visual Studio solution is created, add new SharePoint item “Business Data Connectivity Model (Farm Solution)”. Provide name as ExternalEmployeeModel.

3. Double click on ExternalEmployeeModel, it will open .bdcm file in design mode.
4. Delete auto generated Entity and create new Entity named as ExternalEmployeeItem.
5. Now add a new identifier “EmpNo” of type System.Int32
6. Add new class file ExternalEmployeeItem.cs and open
7. Add all properties corresponding to columns in SQL database table. It will look like screen shot below:
8. Now it is a time to add BDC methods. Click ExternalEmployeeModel.bdcm then select BDC Method Details tab at bottom.
9. Select your entity and add a finder method
10. Under parameters, select returnParameter’s type descriptor “ExternalEmployeeList” and edit. It will launch BDC Explorer.
11. Expand “ExternalEmployeeList” in BDC Explorer. Then expand “ExternalEmployeeItem” and add all properties same as the properties added in ExternalEmployeeItem class.
12. In the same way add other methods into this entity. Below screen shot will guide you, how should it look like.

13. Now we’ll set required BCS Custom Properties.

To add this property, click ExternalEmployeeModel in Solution Explorer. In the Properties window, click on (Collection) next to Feature Properties and click the ellipsis (…) button. In the Feature Properties dialog, click Add. Set the Key property to SiteUrl and the Value property to the URL you selected in the SharePoint Configuration Wizard when you created the project. (Note: this should be the same value as the Site URL property in the project properties)

Note: We have to set a BCS Custom Property to allow us to successfully deploy the BCS model. For some reason, the SharePoint Configuration Wizard sets the Site URL project property, but doesn’t create the SiteUrl property in our Feature that is needed for successful deployment.

14. Now we’ll set Title property of BCS Model. Next, we’ll set the Title field for the entity. If this BCS Custom Property is not set, any search results that are returned from this content source will have the name of the BCS Profile Page as the title, and they will all be identical. This makes the search results less valid.

In the BDC Explorer tool window, click ExternalEmployeeItem under Model –> ExternalEmployeeModel –> ExternalEmployeeModel. Click (Collection) next to Custom Properties in the Properties tool window and click the elipsis (…) button.

15. Deploy the project

 

Configuring External Content Type in BCS Service Application

Open up Central Administration on your SharePoint 2013 instance, and navigate to your BCS Service Application Instance. (Central Admin –> Manage Service Applications). If the .NET Connectivity Assembly has been successfully deployed, you will see the ExternalEmployeeItem entity listed in the table as below:

Click the checkbox next to the ExternalEmployeeItem entity in the table, and click the Configure button on the toolbar in the Profile Pages section. In the Configure External Content Type Profile Page Host select the SharePoint site where you want your ECT Profile Pages created. Click OK at the bottom of the popup window when done.

Check the checkbox next to ExternalEmployeeItem in the table and click Create/Upgrade in the Profile Pages section of the toolbar.

A Loading… popup window will appear while the profile pages are created. When completed, an informational dialog will appear. Click OK. Another window will be displayed when the profile page has been successfully created.

Next, check the checkbox next to ExternalEmployeeItem again, and click Set Object Permissions in the Permissions section of the toolbar.

In the Set Object Permissions box, add appropriate permissions for the search service account, content access account, and any app pool accounts for any web applications that will be using, consuming, or displaying data from the ExternalEmployeeItem External Content Type.

Now you can create External list corresponding to this external content type and use CRUD (Create, Read, Update, Delete) operations on external data.

Hope you enjoyed reading it.

Please don't forget to Share, Follow and Like to get updated with latest posts.

It gives me motivation to share more knowledge with you.

About Author

Satyendra Mishra

Project Management Professional (PMP) and Microsoft certified, motivated, energetic and accomplished Project Manager / Architect with 15+ years of work experience in Management, Architecture, Analytics, Development and Maintenance. I have been fortunate to be a part of over 25+ .Net / SharePoint projects delivery with various companies across different industry sectors. This has provided me a valuable insight and experience especially in successful implementation of SharePoint based solutions.

My experience in Web application implementation includes technology strategy and road-map definition, business and technical requirements identification, governance, platform architecture, solution design, configuration, development, quality assurance, training, post-production support, team lead and overall project delivery along with project management.

Satyendra Mishra holds a B.Tech. in Computer Science & Engineering and PG Diploma in Advance Computing from Center for Development and Advance Computing, Pune, India. He is also a certified Project Management Professional (PMP).

I love to share Project Management Tips and Tricks by writing Blogs in the Project Management India Community. I have written around 300+ articles in the Project Management, .Net, SharePoint and related client side technologies.