in

Dot Net Mafia

Group site for developer blogs dealing with (usually) .NET, SharePoint 2013, SharePoint 2010, Office 365, SharePoint Online, and other Microsoft products, as well as some discussion of general programming related concepts.

This Blog

Syndication

Archives

Corey Roth [MVP]

A SharePoint MVP bringing you the latest time saving tips for SharePoint 2013, Office 365 / SharePoint Online and Visual Studio 2013.

It’s easy to configure an External List with Business Connectivity Services (BCS) in SharePoint Foundation 2010

One of my favorite features in SharePoint 2010 is External Lists.  If you haven’t heard by now, the MOSS 2007 Business Data Catalog (BDC) has become Business Connectivity Services (BCS) in SharePoint 2010.  The ever better part is that it is included in SharePoint Foundation and does not require SharePoint Server.  One new concept that we get from this is the External List.  To the end user, these look like regular lists in SharePoint, however they really are reaching out to external systems such as databases and web services.  Whereas the BDC only “officially” supported read operations, the BCS supports full CRUD operations on your external data source. 

The title of the article mentions how easy it is to set up.  As you will see shortly, it really is quite easy using SharePoint Designer.  In fact, if you have a database table, you can have it exposed in SharePoint in under 10 minutes.  If you worked with the BDC before, you know that we had to manipulate huge XML files that represented the application definition.  We relied on third party tools to make this easier, but it was never a clean experience until now.  Before we get started with SharePoint Designer though, first make sure that you have the Business Data Connectivity (likely to be renamed) Service Application started.  To verify this go to Central Administration –> Manage Service Applications.

BCSServiceApplication

You can tell this is SharePoint Foundation since there are only a few Service Applications (SA).  Ok, admittedly it could be server with very few installed, but you get the point.  If you click the Manage button, you can manage any application definitions you have created already.  Although, we’ll skip this since we’ll be using SharePoint Designer.  If the SA is not started for some reason, go to Central Administration –> Services on Server and click Start next to the appropriate service.  Also remember if you are using Windows Server 2008 R2, you will need to have the WCF hotfix installed before any SA works.

Now let’s take a look at our table.  In my case I have a table of products with a few columns.  I want to expose this table to SharePoint and make it editable.

BCSTable

Now open SharePoint Designer 2010 and connect to your SharePoint site.  My server is called sp2010, so I would click open site and then enter http://sp2010.  SharePoint Designer is redesigned and has a new item on the left-hand tab called External Content Types.  This is where we want to go.  Click the External Content Type button in the New section of the ribbon.

BCSNewECT

It will take a minute, but then you will get some details about your new External Content Type.  Here is what mine looks like after I gave it a name of Products.

BCSECTTab

We’re then going to click the link Click here to discover external data sources and define operations.  This is where we pick that we want to pull data from my database table.  We can also use this to connect to a web service or talk to a custom .NET object.

BCSAddDataSource

I’m just going to go with the defaults on the next screen for my data connection.  I gave it my database name of bcs_test as well as my SQL Server name.  You can also configure out which identity is used here to talk to the database.  Remember that you may need to grant permissions on the SQL Server itself for the appropriate user.  It will then iterate your data source and display it to you.  Pick out the table you want, and then right click on it.  This gives you a list of operations you can add as you can see below.

BCSDataSourceAddOperations

As you can see, you can add individual operations such as Read Item, Read List, Create, Update, and Delete.  You can also create an association if you have multiple tables related to each other.  However, I want everything, so I am going to choose Create All Operations.  This starts a wizard.  The most important part for you will be the Parameters configuration.  Here you set what field to show in a picker control as well as what the id is.  Usually, you don’t need to configure much.  Here I am setting that the name field should show up in the picker.

BCSWizardParametersName

It automatically detects my primary key and maps it to the identifier for me.

BCSWizardIdentifier

You can also specify an Office Property to allow you to map things into existing types such as an Outlook Contact or Task.  This lets you use Office to edit things directly in the BCS.  I won’t cover that today though since that is a little bit more involved.  The next step in the wizard allows you to do some filtering.  In my case, I am skipping it. 

When that is finished, go back to the main tab for your External Content Type and you should see something like this.  It has the operations you have chosen as well as what fields are in the type.

BCSTabComplete

Now, we want to save out External Content Type, by pressing the Save (Disk Icon) in the top left corner.  On to the fun part.  Let’s use SharePoint Designer to create an External List by using the Create Lists & Form button in the ribbon.

BCSCreateListsFormButton

Clicking it you will get the following screen asking about your list.  I’m calling my lists Products and I just use default values for the rest.  If you are using server, you can click the Create InfoPath Form checkbox and you will be able to customize the InfoPath form for the list right there.  Since we are using Foundation today, I’ll leave that unchecked.

BCSExternalList

You can also create the list directly through SharePoint as well.  Once the list is created, go to your SharePoint site, navigate to the list, and you should see something that looks like this.

BCSExternalListView

Comparing it to my table from the SQL Server, you can see that it matches.  You can see that it looks a lot like a regular list in SharePoint.  Now, what if I want to change the price on the Plush Bear?  Not a problem.  Click on the item and select edit, change the value in the form and save it.

BCSEditItem

Now what does the data in SQL look like?

BCSSQLEdit

It has the new price of course.  You can also add and delete rows and perform bulk operations, but screenshots really don’t do it justice.  It’s this simple to set up, you should go out and try it for yourself.  One reminder, I will give you is that even though this looks like a list, it doesn’t always quite act like a list.  You won’t find it in SPWeb.Lists for example.  There are a also a number of other things that do and don’t work.  Be on the lookout for a follow-up post on what you can and can’t do with an external list.  This is a great new feature in SharePoint 2010 and I hope you will like it as much as I do.

Comments

 

Brian Bedard said:

Hmm.  and this was Foundation?  I installed Foundation on Win7 and used Designer to try and create an External Content Type and got an error.  I was missing a certain Service Application.  It wasn't installed.  I will try again and get you the exact error message.  I was under the impression the BCS was only part of SP2010 Server but the BDC is available for Foundation users.  Thank you for writing up this post, BTW.

February 2, 2010 5:39 PM
 

CoreyRoth said:

Yes.  It's definitely Foundation on this particular image.  If the Service App isn't in your list, you might try going through the setup wizard in Central Admin again and see if it creates it.  You might also be able to create it manually from the service app page, using the new button in the ribbon.

February 2, 2010 6:20 PM
 

SharePointFrank said:

SharePoint 2010 External List (BCS) for MOSS 2007 / WSS 3.0?

If you like the new SharePoint 2010 external list feature and the new Business Connectivity Services (BCS) and want to make use of something similar in SharePoint 2007 / WSS 3.0 to connect SharePoint lists (no web parts) directly to external data sources without any programming or tools, please take a look at:

www.layer2.de/.../SharePoint-Business-Data-List-Connector.aspx

All list features are available, e.g. grouping, lookups.

You can start buiness workflow actions in SharePoint, if external data changes.

The shareware version is completely free for download and use.

February 17, 2010 10:11 AM
 

CoreyRoth said:

Looks pretty cool, but it looks like Microsoft is making your product unneeded pretty soon.

February 17, 2010 10:22 AM
 

John said:

Hi,

The external list which gets created has workflows option  disabled.Does that mean we cannot use sharepoint native workflows in this list.Any workarounds

Thanking You

John

February 23, 2010 7:23 AM
 

CoreyRoth said:

That seems to be the case.  It doesn't look like we can trigger a workflow off an external list.  There may be a way to get around it, but I'm not sure yet.  I do know that SharePoint workflows don't have to be associated with a list item any more in 2010, so that might be something to leverage.  I don't know if ItemEventReceivers fire for external lists either, but I suspect not.  I need to look into that.

February 24, 2010 8:57 AM
 

Nan said:

External List seems cannot be used to trigger WF. It may because the data for  External List are saved in external data source but not SP.

However, we could create a WF for regular list to access data from  External List. There are couple papers talk about WF with External List.

blogs.msdn.com/.../using-sharepoint-workflows-with-business-connectivity-services-bcs.aspx

March 5, 2010 6:10 PM
 

Raymund said:

Hi, I liked your article as its very informative, I came accross this one as I was looking for a solution to my problem.  

So I retried doing my external list and do this your way according to your post as I might have done somthing wrong along the way, everything seems to be fine but I still have my issue which is if I go the my external list now it gives me this error.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Do you knwo why its doing this?  I hope you could help.

July 4, 2010 9:43 PM
 

David Heffler said:

Do you know if you can export an external list out of sharepoint to load onto another sharepoint server.  For example, I create a list that displays information from an ERP application and I want to share with one of my sister companies that has its own sharepoint site.  

September 1, 2010 1:57 PM
 

cyril said:

thnx for this great article. i followed your instructions to the letter but i am having challenges when it comes to displaying the contents of the database table in the sharepoint list. all i see is the column headings from the external data source without the actual cotents.

Any help?

September 29, 2010 6:50 AM
 

Andy Ogden said:

I'm also having this issue where I receive an empty list with only the column headings.  It seems to be related to the setting of filters, which you haven't covered in this article.

I can set a Limit without issue, however a comparssion or Wildcard filter results in the following error:

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Any ideas?

January 11, 2011 6:22 AM
 

Jagan said:

Why the price field is not displayed in the list view?

February 16, 2011 1:05 PM
 

Peter said:

Do you have a way for a large external list (4'000 items)?

the time as the page is completly loaded is mor than 2 minutes...

Thanks for your Help.

January 10, 2012 7:25 AM
 

Carrick said:

I have 10.000 items in external list, I want to search 1 item in list for deleting or updating, but I don't know how to search, please show me !!!!

February 29, 2012 1:46 AM
 

CoreyRoth said:

@Carrick try going to your search service application and setting up a new content source to use your BCS connection.

April 2, 2012 10:46 PM
 

Alain said:

hello

Can you explain me , what is se difference between

External Content Type

And

Data source

in Sharepoint designer

Thank's

May 16, 2012 2:46 AM
 

CoreyRoth said:

@Alain External content type is where you want to go to set up BCS connections.  Data Source is something different.

May 29, 2012 9:58 PM
 

Riyaz said:

How to make anonymous access to external list?

July 16, 2014 2:16 AM

Leave a Comment

(required)  
(optional)
(required)  
Add

About CoreyRoth

Corey Roth is an independent SharePoint consultant specializing in ECM, Apps, and Search.
2012 dotnetmafia.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems