Sharepoint 2010 development using bcs
One of the big features for SharePoint 2010 (and for building OBAs) is the Business Connectivity Services (BCS). I’ve written a few posts on this, but wanted to follow up on these early posts with some additional detail on how to create a solution leveraging web services. I see many customers trying to tie together SharePoint with external systems using a service-oriented design.
In this post, I’ll walk through how you can leverage a web service using the BCS (specifically employing the BDC Model project template in VS 2010) to create a read-only relationship with an external data source. This will be the first post in a series that will eventually cover the broad CRUD spectrum. So, let’s get down to it.
The high-level process you will want to go through is as follows:
- Create the data source;
- Create a service that interacts with your data source;
- Create the external content type using VS 2010; and
- Use the external content type in SP 2010 to create a web-service enabled external list.
Creating the Data Source
In this example, the data source is a SQL Server db with a single table with five records—see figure below. The number of records are less important than the structure of the columns.
You can choose to use something similar or create something different. If you use the code in this blog-post and you’re new to this, then create a db table with a similar structure. The following shows the data types in this simple table.
You create the new data source in SQL Server 2008 by right-clicking the Databases node and selecting New Database. Design your columns as per the above and then save the db when complete.
Creating the Web Service
With your db created, you now want to create a web service to interact with that db. We’ll create an ASP.NET service in this example to again keep things simple. (Note that you could equally apply much of the code to a WCF service.)
Open Visual Studio 2010 and create a new ASP.NET service application. You’ll want to add a new data source as an entity model. You can do this by click Data and then Add Data Source and then walking through the new data source wizard. I called my new data model CustomerSale—see the figure below to understand how Visual Studio interprets the data types.
You’ll need an in-memory object, so right-click your project and click Add and then select Class. Call your class CustomerSalesInfo.cs (or <name>.vb) and then click OK. You want to map the properties of your object to that of the data source. Here is the code for that object. You’ll note that I didn’t map the data types exactly. I do some in-code translation of data types.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace CustomerServiceBCSApp
{
public class CustomerSalesInfo
{
public string custID { get; set; }
public string custName { get; set; }
public string custEmail { get; set; }
public string custRegion { get; set; }
public string custFY08Sales { get; set; }
public string custFY09Sales { get; set; }
}
}
Now you can write some code in your core service class. As this blog post is about read only, it will cover two web methods: one for reading the entire list and another for reading one item in a list. You’ll note that you require these two methods to create an external content type using BCS. In the code below, note that I’m creating a data context for my entity model called myCustomerData and there’s a class-level list collection I’m also creating called myCustomerList. The list collection is what I use to build out and populate my in-memory object (which I’ll return in the getAllCustomers() method. In fact, in the getAllCustomers() method I simply use a LINQ statement to query and get all of the data in the data context, and then I iterate through the members of the var returnListOfData object to creater my in-memory object. You may choose another method here (maybe even foregoing the in-memory object) to optimize your code. I pass no parameters to the getAllCustomers method as this is my, so to speak, “SELECT *” statement.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
namespace CustomerServiceBCSApp
{
[WebService(Namespace = "https://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
public class Service1 : System.Web.Services.WebService
{
CustomersEntities myCustomerData = new CustomersEntities();
List<CustomerSalesInfo> myCustomerList = new List<CustomerSalesInfo>();
[WebMethod]
public List<CustomerSalesInfo> getAllCustomers()
{
var returnListOfData = (from customer in myCustomerData.CustomerSales
select customer).ToArray();
foreach (var cust in returnListOfData)
{
CustomerSalesInfo tempCustomer = new CustomerSalesInfo();
tempCustomer.custID = cust.CustomerID.ToString();
tempCustomer.custName = cust.CustomerName.ToString();
tempCustomer.custEmail = cust.CustomerEmail.ToString();
tempCustomer.custRegion = cust.CustomerRegion.ToString();
tempCustomer.custFY08Sales = cust.CustomerFY08Sales.ToString();
tempCustomer.custFY09Sales = cust.CustomerFY09Sales.ToString();
myCustomerList.Add(tempCustomer);
};
return myCustomerList;
}
[WebMethod]
public string[] getACustomer(string strparamCustomerID)
{
int tempIntCustomerIDParam = Int32.Parse(strparamCustomerID);
string[] myCustomerList = new string[6];
var returnIndiviualDataItem = (from customer in myCustomerData.CustomerSales
.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;
}
}
}
The second method takes a string parameter (a customer ID) and then builds a LINQ query based on that parameter. In this instance, I wanted to use a string array that I can easily parse on the other side. I do prefer to leverage generics wherever possible because I like the flexibility of dynamic collections as opposed to hard-coded lengths, but for this sample I though it interesting enough to show both examples.
Once you’ve gotten this far, you can test out the web service to make sure it runs. Test it from VS 2010 and then deploy it to IIS. To do this, create a new folder in your wwwroot folder (I called mine SPConnections) and then publish your service to that directory.
To publish the service, right-click your web service project select Publish and complete the options in the Publish Web dialog. Specifically, change the Publish Method to File System and then point the Target Location to the folder you just created under wwwroot.
Click Publish to publish the service to that directory. Now, any time you make changes you can simply click Publish and your service definition and code will be updated.
Since you’ve published your service to the file system, you’ll now want to create a web application in IIS that points to this service. To do this, open IIS and right-click the Sites node and select Add Web Site. Provide a name for the site, map the physical path to the folder under wwwroot (where you published your service to), give the site a unique port (e.g. 1111), click the Connect As button to provide the appropriate permissions for the service. (Note that you may have issues accessing the SQL Server if you have not configured the App Pool correctly—e.g. you can access using Local System or Network Service as opposed to the native Application Pool Identity.) Enable Windows Forms authentication by clicking Authentication and then clicking Windows and then Enable.
To test the service from IIS, click the Content tab and then right-click Service1.asmx and select Browse. You should be able to execute both methods—one to get all of the customers in your db and the other (by passing a customer ID that exists within your db) to get a specific customer.
Creating the External Content Type (ECT)
You’ll create the ECT using the new BDC Model project. To find this, click File, New, Project, and then in the SharePoint 2010 folder select Business Data Connectivity Model. Provide a name for your project and click OK. 
Using this template is a little different from SharePoint Designer (SPD) 2010, which also enables you to build out ECTs. My personal feeling is that SPD is a little easier to create the ECTs, but then if you want to have more complex projects that perhaps leverage other parts of the .NET Framework then using the VS project is better. Note that VS also deploys the ECT (and all associated code) as a feature as opposed to an XML file to the Metadata Store (as is the case with SPD 2010).
Okay, now you have your project set up you need to edit the Entity1 object to map to your external data source entity. You’ll note that I’ve kept a lot of the default namespaces and objects in this example. I’ll change these in future posts and keep you apprised as to the places you’ll need to look out for when trying to update/change these. To follow is what your Entity1 should now look like.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace WroxTestBDC.BdcModel1
{
public partial class Entity1
{
public string Identifier1 { get; set; }
public string custName { get; set; }
public string custEmail { get; set; }
public string custRegion { get; set; }
public string custFY08Sales { get; set; }
public string custFY09Sales { get; set; }
}
}
As a general rule of thumb, as soon as you’ve made the change to your Entity1 object go immediately to your .bdcm (the ECT model) and amend the entity structure there as well, or your deploy will fail. Also note that the way in which you edit your methods should actually correspond to the code-behind. For example, the structure of my ReadItem method (which is where I’ll call my getACustomer web method) passes the Identifier1 parameter and returns an object from my database via the web method. Similarly, the ReadList method will return a list of objects, and it defines this return object through the Entity1List object in the taxonomy below.
Now, note again I’m only doing a read-only transaction—mostly because this is a good jumping off point but also because these two methods are created by default for you.
Okay, before we jump to the code behind add a reference to the web service. To do this, right-click the BCS project and select Add Service Reference. Add the URL to the service (copy and paste the URL from your IIS browse) in the Service field, provide a name for your service and click OK.
At this point, you are now ready to implement the service (i.e. the two web methods you created) in the VS project.
Navigate to the Entity1Service.cs file and make sure your code looks like the following code snippet. You can see that I create an instance of the service and then call the appropriate method on that service to return the code from my external data source. Note that in the case of the ReadItem method, it takes a string parameter (which would get activated when you click on a record in the external list). I’ve changed the default IEnumerable return object to use my preferred List<> object (which is essentially a list collection of the Entity1 object).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WroxTestBDC.CustomerSalesWS;
using System.Xml.Serialization;
using System.Xml;
using System.Xml.Linq;
namespace WroxTestBDC.BdcModel1
{
public class Entity1Service
{
public static Entity1 ReadItem(string id)
{
CustomerSalesWS.Service1SoapClient myWSProxy = new CustomerSalesWS.Service1SoapClient();
string[] returnedData = new string[6];
returnedData = myWSProxy.getACustomer(id).ToArray();
Entity1 entity1 = new Entity1();
entity1.Identifier1 = id;
entity1.custName = returnedData[1];
entity1.custEmail = returnedData[2];
entity1.custRegion = returnedData[3];
entity1.custFY08Sales = returnedData[4];
entity1.custFY09Sales = returnedData[5];
return entity1;
}
public static List<Entity1> ReadList()
{
CustomerSalesWS.Service1SoapClient myWSProxy = new CustomerSalesWS.Service1SoapClient();
var salesData = myWSProxy.getAllCustomers();
List<Entity1> mySalesInfoList = new List<Entity1>();
foreach (var item in salesData)
{
Entity1 tempEntity = new Entity1();
tempEntity.Identifier1 = item.custID.ToString();
tempEntity.custName = item.custName.ToString();
tempEntity.custEmail = item.custEmail.ToString();
tempEntity.custRegion = item.custRegion.ToString();
tempEntity.custFY08Sales = item.custFY08Sales.ToString();
tempEntity.custFY09Sales = item.custFY09Sales.ToString();
mySalesInfoList.Add(tempEntity);
}
return mySalesInfoList;
}
}
}
At this point, you can build and deploy the ECT by right-clicking the project and selecting Deploy.
Before you go to SharePoint, one thing you’ll need to make sure you do is to copy the property settings for your system.serviceModel in your app.config file to your SharePoint’s web.config file—else you will get nasty web configuration errors.
<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="Service1Soap" closeTimeout="00:01:00" openTimeout="00:01:00"
receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
useDefaultWebProxy="true">
<readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
maxBytesPerRead="4096" maxNameTableCharCount="16384" />
<security mode="None">
<transport clientCredentialType="None" proxyCredentialType="None"
realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
</binding>
</basicHttpBinding>
</bindings>
<client>
<endpoint address="https://localhost:289/Service1.asmx" binding="basicHttpBinding"
bindingConfiguration="Service1Soap" contract="CustomerSalesWS.Service1Soap"
name="Service1Soap" />
</client>
</system.serviceModel>
After this, you are now ready to create the external list in SharePoint 2010 using your newly create web-service enabled ECT.
Creating the External List
You can now create the external list in SharePoint. To do this, click the All Site Content link and click Create. Then in the Create options, click List and click External List. Click Create.
When the External List page opens, provide a name for the list and then in the External Content Type field click the right-most icon to browse for external content types that have been deployed to SharePoint. Click Create when complete.
At this point, your external list will have been created. The two read operations will enable you to have the full list of your records in the Customers database (via web service) rendered in the list—see below.
And if you click on one of the records it will display the individual record—see below.
And there you have it. You now have a web-service enabled external list.
In future posts, I will show you how to add new web methods to handle update, create and delete and then add the corresponding code in the BDC project you just created.
But for now, enjoy your holiday dinners!
Steve