Mastering Business Connectivity Services in SharePoint 2010 (Part 2)

Posted by: Mark Kendall , on 1/22/2011, in Category SharePoint
Views: 68489
Abstract: In the first article in this series, Mastering Business Connectivity Services in SharePoint 2010 – Part 1 we created a BCS connector using the Visual Studio Template. In this article, we will create a connector to a database using a web service as the interface to the database. The web service will use LINQ to SQL as the Datacontext.

In the first article in this series, Mastering Business Connectivity Services in SharePoint 2010 – Part 1 we created a BCS connector using the Visual Studio Template. In this article, we will create a connector to a database using a web service as the interface to the database. The web service will use LINQ to SQL as the Datacontext. You will need a SQL Server database to create a customer table, and an IIS server to deploy the Web Service outside of the SharePoint installation-and of course a SharePoint Site to create the interactions and Lists required for this article. Let’s begin:

BCS to Customer Connector

Step 1: create the customer Table-Database Script:

CREATE TABLE [dbo].[Customer_BCS](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nchar](50) NULL,
[CustomerEmail] [nchar](50) NULL,
[CustomerRegion] [nchar](10) NULL,
[CustomerFY08Sales] [bigint] NULL,
[CustomerFY09Sales] [bigint] NULL,
[CustomerID] ASC

Step 2: Create the web service and LINQ to SQL Object:

Customer BCS WebService

Create a web service project in Visual Studio and add a LINQ to SQL object to the projects as shown below: Drag the Customer_BCS table created above from the server Explorer to the LINQ to SQL canvas as shown below:

Note: Here is the link to the source code for all the projects in this Article.

Customer BCS DBML

LINQ to SQL Customer Designer

There are 4 methods in the web service to perform CRUD operations on the customer Table which we will access from our BCS project- Here is the source code for one of those methods: Refer to Article 1 to create the methods required in the example: Mastering Business Connectivity Services in SharePoint 2010 – Part 1

BCS CRUD Operations

One of the keys here lies in the class CustomerSalesInfo as you will pass this object in and out of the webservice form your connector project

Customer Sales info

After you have completed creating the web service, deploy it to an IIS Server that can be located from the BCS Connector project below

Step 3: Create the connector Project in Visual Studio

Web Reference

Set a web reference to the web service as shown above. Then create the methods for the customer

BCS Model Customer

Here is the class for the customer Object:

Customer BCS

The call to the web service and the implementation to the Connector are shown below:

BCS Connector Demo

The key to the code that makes the whole process work is the objects for the LINQ to SQL and the Object for the web service: If you study the above code carefully, I think you will find that it is pretty easy to understand- and more importantly, it is pretty standard across all of the connectors created this way. That is, by using a web service and LINQ to SQL to do the CRUD operations on the Database Tables.

Cust BCS Datacontext

Use the sample code provided to compile, run and debug the code for a better appreciation of what is going on in the different classes.

Deploy the solution to Sharepoint and the connector should be ready to use.

Here is the endpoint that Visual Studio created when I added a web reference to the connector project- you will need to add a similar entry to your SharePoint web.conf:

Sharepoint End Point

Note: Here are some things to consider when deploying the full solution and are were required to make the solution run end-to-end without error:

Web Service:

Place Web Service on non-SharePoint site (for this example)
Add endpoints to web config on Sharepoint site for web service
Change web config on SharePoint to NTLM for above service


Create/Add default forms for new and update methods in SharePoint designer
Add new methods for above to BCS connector


Make Identifier read only on Read list and Read Item in the designer
On BCS Model: Set identifiers as update methods: updater field Preupdater fields to true


No changes- just create table

Step 4: Create External List in Sharepoint

External List in Sharepoint

Refer to article one on how to create the external in SharePoint: of course the data in the list will be different from what you see above, as you can add test items to the database when you create it.

Step 5: Create default forms in SharePoint Designer:

Notice in the form section that I have added 2 now forms to edit and add new customers and will be used by SharePoint as the default forms- if you don’t do this step you will get an error when you try to add or edit the customer list.

Sharepoint Designer

Step 6: Add, Update and Delete Data from the List created above

Add Update Delete List

Notice how the custname changed to Bob Jones 1 when the record was edited in SharePoint:

Uptaded List


Congratulation, you have now created a fairly complex piece of software that can serve as a template to your own connectors to your own data sources.
With SharePoint, though, there are many moving parts that have get used to and it will take some time for you to master all the elements, but this is definitely a good start!

Part 2 of this series showed you how to create powerful custom connectors that are connected to a database via Web Service Calls. I will continue to create articles that will illustrate the powerful features of BCS.

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

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

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!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Mark Kendall is a Software Engineer working in the Oil Industry in Dallas,Texas. He has been a programmer/developer for past 15 years specializing in .NET/C# development. Currently, he is a Sharepoint/Silverlight developer specializing in BCS Services. He has published a book on Dotnetnuke, and written several articles on many .net subjects. Apart from his passion for making money writing software to improve business, Mark enjoys ranting-and-raving about current events and taking long runs on the beach. He blogs at kendallsoft and can be reached at Kendallsoft[attherate]hotmail[dot]com

Page copy protected against web site content infringement 	by Copyscape

Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by phooze on Monday, January 24, 2011 7:04 AM
Mr Mark. I very much enjoyed this article on bcs. Can I use wildcard and =, >, < in bcs external content type? Do you have an example
Comment posted by VinceB on Monday, January 24, 2011 7:09 AM
This is a fantastic example. How do use a stored procedure with input parameters?
Comment posted by Mark Kendall on Monday, January 24, 2011 11:52 AM
1 Create a stored Procedure that accepts a parameter
CREATE PROCEDURE [dbo].[CallBCSCustomer]
   -- Add the parameters for the stored procedure here
   @custid int
   --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.

    -- Insert statements for procedure here
   Select * from Customer_BCS where customerid=@custid
2 Drag stored procedure to LINQ to SQL Canvas
3 Modify Web Service in source code to call stored procedure
        public string[] getACustomer(string strparamCustomerID)
            int tempIntCustomerIDParam = Int32.Parse(strparamCustomerID);
            string[] myCustomerList = new string[6];
        // myCustomerData.CallBCSCustomer is call to stored procedure with parameter
            var returnIndiviualDataItem = (from customer in myCustomerData.CallBCSCustomer(Convert.ToInt32(strparamCustomerID))
                                //.Where(x => x.CustomerID == tempIntCustomerIDParam)
                                           select customer);

            foreach (var cust in returnIndiviualDataItem)
                myCustomerList[0] = cust.CustomerID.ToString();
                myCustomerList[1] = cust.CustomerName.ToString();
                myCustomerList[2] = cust.CustomerEmail.ToString();
                myCustomerList[3] = cust.CustomerRegion.ToString();
                myCustomerList[4] = cust.CustomerFY08Sales.ToString();
                myCustomerList[5] = cust.CustomerFY09Sales.ToString();

            return myCustomerList;
Comment posted by VinceB on Tuesday, January 25, 2011 9:47 AM
Thank you very much for providing an example for the stored proc. I will be able to do it now.
Comment posted by iracan on Tuesday, February 1, 2011 6:11 AM
Nice article Mark. There aren't many artices on bcs and it's helpful to see concepts explained clearly. You can keep smaller images though. If you are explaining a portion of a screen, just give a screenshot of that. Why the whole screenshot?
Comment posted by Marco Antonio Niño Bonfante on Thursday, October 13, 2011 11:53 AM
A little question the web service is has to be in the same server of sharepoint?
Comment posted by susant on Monday, August 13, 2012 4:20 AM
hi Mark,
Nice article, could u post some article on performance point services
Comment posted by Suprotim on Friday, August 17, 2012 2:42 AM
There are some article on Performance Point written by Pravin. Check this list
Comment posted by Subbiah on Monday, September 24, 2012 8:35 AM
Hi Mark, I have created the content type in the same way you have mentioned. I have created the External Lists using these content types through code as well. But I get all the fields as mandatory fields in "Add New" screen. Any idea how to remediate this? Thanks in advance!
Comment posted by aaaadasd on Monday, December 24, 2012 6:06 AM
Comment posted by Gnan on Tuesday, January 8, 2013 5:49 AM
Very nice article
Comment posted by Lauren on Thursday, June 13, 2013 10:37 AM
Hi Mark
This is a one to one mapping between one List and one table.
I have a List, and i want to map it into multiple tables in Oracle db.
What would be the best way to do this?
Comment posted by Jaini on Tuesday, March 18, 2014 5:48 AM
Add endpoints to web config on Sharepoint site for web service
Could anybody explain this.How do I add it?