Mastering Business Connectivity Services in SharePoint 2010 (Part 2)

Posted by: Mark Kendall , on 1/22/2011, in Category SharePoint
Views: 52134
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,
CONSTRAINT [PK_Customer_BCS] PRIMARY KEY CLUSTERED(
[CustomerID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

SharePoint:

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

SharePoint:

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

Database:

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: http://www.dotnetcurry.com/ShowArticle.aspx?ID=632- 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

Summary

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

Give me a +1 if you think it was a good article. Thanks!
Recommended Articles
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


User Feedback
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>
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

    -- Insert statements for procedure here
   Select * from Customer_BCS where customerid=@custid
END
2 Drag stored procedure to LINQ to SQL Canvas
3 Modify Web Service in source code to call stored procedure
[WebMethod]
        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 http://www.dotnetcurry.com/ShowArticle.aspx?ID=740
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
sasddddd
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?

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