SP 2010: Custom RSS provider for your Business Connectivity Services (BCS) connected External Lists

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

If you've been working with BCS and external lists you may have noticed that there's no RSS feeds enabled for those lists.

So to get around this problem I've created a custom RSS feed generator that essentially provides you with an RSS/ATOM feed for you so you can render it in your browser or any feed-reader of choice.

Please note that this is a beta with a few things that needs fixing before it should be used, I'd like your feedback on it so I can complete it for you all to utilize.

External Lists and RSS

If you go to your SharePoint 2010 installation where you've got an external list you will see that you're lacking the RSS functionality on that specific list.

Example:

The RSS feed is by default disabled, and out of the box there's no way to enable it for this specific type of list.

Some people gave me the brilliant idea of using REST and the ListData.svc service to pull out the data from the lists. But as you know (or will know from now on) is that REST and External Lists is a no go. You can't utilize the ListData.svc service to pick out information from your external lists, unfortunately.

A custom RSS generator for your external list

I've created an RSS generator feature for your external lists, which (when enabled) will give you two new buttons in your External List ribbon menu called "Custom RSS".
The first button will take you to the Feed, and the second button is there to enable you to toggle the settings for the feed (i.e. what fields to show in the feed etc). The second button is only available to administrators of that list. Pretty sweet.

Custom RSS Ribbon tab with an RSS Button:

Custom RSS Feed Settings dialog:
(The RSS fields title, author, pubDate and description can be mapped to any field in your External List)

Custom RSS Feed rendered in the browser:
(Rendered based on settings done in the Settings-dialog)

Feed opened in the RSS reader in Outlook 2010

Download and Install the RSS generator

Alright, so this is not a complete feature as of yet - there's some glitches that is to be fixed and tuned, but I would love your feedback if you try it out. Please use the contact form and submit any feedback or features you'd like to see in this RSS gadget.

You can download the current version (not for production) here:
[BETA]Zimmergren.SP2010.BCS.RSS.wsp

Published: Jun-26-10 | 4 Comments | 0 Links to this post

SP 2010: BCS problem with AuthenticationMode and RevertToSelf

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

I bumped into an issue the other week where I was creating a new BCS External Content Type and was hoping to hook up some new connections on a couple of demo farms I've created.

While most of these things were working like a charm, I bumped into an odd error message that basically told me that the elevation of privileges isn't enabled on my BCS Service Application.

Problem, a short story told even shorter

You might receive an error message like this when trying to hook up a new External Data Source depending on your configuration:

The metadata object that has Name 'yourName' has a Property with name 'AuthenticationMode' and value 'RevertToSelf'. This value indicates that the runtime should revert to the identity of the application pool, but reverting to the application pool must be explicitly enabled for the service application by a farm administrator.

Solution

While digging around for a couple of minutes I couldn't really find a suitable UI option for changing this behavior, so I dug into the power of PowerShell instead - which probably saved me quite some troubleshooting.

First you need to check the name of your Business Data Connectivity Service Application - a quick way is to check the UI, or you could use a PowerShell Cmdlet or use the API. I'm just using the UI for easy access:

Next, launch a new SharePoint 2010 Management Shell console and type in the following lines of code. Note that the name of the Service App below, is a copy of the name you took above:

$bcsServiceApp = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}

$bcsServiceApp.RevertToSelfAllowed = $true;

$bcsServiceApp.Update();

This should now execute nicely (if you've got the appropriate permissions) and you should be able to see the RevertToSelfAllowed property be changed to true:

If you now would try to hook up your connections again, you should hopefully not see this message again.

Quick fix, done. Enjoy.

del.icio.us Tags: BCS,Business Connectivity Services,SP2010,SharePoint 2010,SharePoint

Published: Jun-09-10 | 2 Comments | 0 Links to this post

Access denied by Business Data Connectivity - Solution

Auhtor: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Lately I've been rolling around parts of Sweden and doing training, seminars and workshops on SharePoint 2010 with a bunch of companies and people.

One thing that I've been showing off, which I'm totally in love with by the way, is the BCS (Business Connectivity Services) functionality.

I've been getting a few questions from people who have been trying this out, but stumbled onto some problems with it in terms of permissions when they tried it out themselves.

Problem - Access Denied by Business Data Connectivity

When you've created your external list and try to access it (even as the same user, Administrator, that created it) you might get the following error:

Access denied by Business Data Connectivity

If you bump into this, please don't freak out - just keep reading..

Solution - Permissions on the BCS Entity

To resolve this so called problem, follow along here:

Go to Central Administration -> Application Management -> Manage Service Applications -> Business Data Connectivity Service* -> [Your Entity] -> Set Permissions

* or whatever name you've chosen for your BCS Service application

Configure the actual permissions

In my case below, I've just told SharePoint that my Farm Administrator should be able to do all actions (Edit, Execute, Selectable in Clients and Set Permissions)

Please note: You might want to use different permissions in your environments - the permissions set in this blog post is just to demonstrate how you effectively change/add permissions for your BCS Entities to get up and running.

Check your external list

You should now be able to access the external list and see the desired result.

Keep rocking folks!

Cheers

Published: May-08-10 | 15 Comments | 0 Links to this post

SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 3 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

In my previous article in the series, I talked about how easy it is to fetch information using the standard SharePoint server API-approach. In this article I will talk about how you can access data in your BCS data source, by utilizing the Client Object Model.

Client Object Model code to read from the external list

Just like I described in my previous article, you now have the awesome ability to work with data in your External Lists (connected to a data source using BCS) - namely, you can use the standard SharePoint APIs.

Since I've shown you how you can do this using the Server Object Model, I thought I could take another spin at it and show you the code for doing basically the same with the Client Object Model.

The underlying data

As with my previous article, I'm still using the same data source as I set up in my first article - the "ProductList" table in my SQL Server database called "Zimmergren_DB"

As seen in the SQL Server Management Studio:

Let's fetch the data using a Windows Forms application that utilizes the Client Object Model!

I've designed a Windows Forms application to utilize the .NET Client Object model (in contrary to using the Silverlight client object model or JavaScript client object model).

It looks like this:

When you click the fancy button called "Get External Data", it will use the Client Object Model to fetch the records from the external list (from the SQL server) and display them in a DataGridView. Nothing fancy.

The code!

With no further delays or chit-chat, here's the simple code!

    // Define the Client Context (as defined in your textbox)
    SP.ClientContext context = new SP.ClientContext(tbSite.Text);
    SP.Web site = context.Web;

    var ProductList = site.Lists.GetByTitle(tbList.Text);

    SP.CamlQuery camlQueryAwesomeness = new SP.CamlQuery();

    IQueryable<SP.ListItem> productItems =
                                      ProductList.GetItems(camlQueryAwesomeness);

    IEnumerable<SP.ListItem> externalList =
                                      context.LoadQuery(productItems);

    // This is where we actually execute the request against the server!
    context.ExecuteQuery();

    // Retrieve the products from the product list using some fancy LINQ
    var productListData = from product in externalList
      select new
      {
          // We're never pointing to the field at the 0-index
          // because it's used by the BDC Identity itself. Hence our elements start at 1.
          ProductID = product.FieldValues.ElementAt(1).Value.ToString(),
          ProductName = product.FieldValues.ElementAt(2).Value.ToString(),
          ProductDescription = product.FieldValues.ElementAt(3).Value.ToString()
      };

    // Simply clear the rows and columns of the GridView
    gvProducts.Rows.Clear();
    gvProducts.Columns.Clear();

    // Add the columns we need (ProductID, Name, Description)
    gvProducts.Columns.Add("ProductID", "ProductID");
    gvProducts.Columns.Add("Name", "Product Name");
    gvProducts.Columns.Add("Description", "Product Description");
    foreach (var product in productListData)
    {
        // For each product in the list, add a new row to the GridView
        gvProducts.Rows.Add(
                                 product.ProductID,
                                 product.ProductName,
                                 product.ProductDescription
                                 );
    }

References and recommended reading

Summary & Download

At this point, I've showed you three short articles in which I describe how you can set up a Business Connectivity Services data source - then utilize the Server OM or Client OM to fetch information from that external data storage.

Worth to note is that this is still a Beta product, which of course means that it may differ in functionality and performance in comparison with the final (RTM) version of SharePoint 2010.

Download the complete Visual Studio 2010 project here: [ Zimmergren.SP2010.ClientOM.BCS.zip ]

Published: Jan-21-10 | 7 Comments | 0 Links to this post

SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 2 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

In my previous article I talked about how you can set up a simple BCS configuration to fetch and work with external data in SharePoint 2010. In this article I will talk about how you can utilize the SharePoint 2010 object model to work with that external data, directly from the SharePoint API. It's all really simple!

Working with data in an External List using the SharePoint Object Model

The code in this sample doesn't really differ from the way you fetch information from any other list in SharePoint (2007 or 2010). This - of course - is very welcomed news, as we do not need to learn any new frameworks or tools to work with the data in our external lists. It simply works as any other SPList, basically.

Retrieving external data, made simple:

When fetching items from an external list, you can simply do that by utilizing the good-old SPList object. We do not need to work with any other types of namespaces or frameworks in order to do this.

In my SQL Server I've got a table called "ProductList".
This list is filled with the following data:

Fetching some items from the external list, and displaying them in a console app:

// Product List is my external list, that is working with data in the SQL Server!
SPList list = web.Lists["Product List"];

SPQuery q = new SPQuery();
q.Query =
"<Where><IsNotNull><FieldRef Name='ProductID' /></IsNotNull></Where>";
q.RowLimit = 100;

SPListItemCollection col = list.GetItems(q);

foreach (SPListItem item in col)
Console.WriteLine(item["Name"].ToString());

This will render the following result (fetched from the database):

The things you see in the console windows is fetched straight from the SQL Server (using a BCS connection through the External List).

Writing data to the External List (hence, writing to the SQL Server)

Seriously, this is way too easy as well...

// Get the external list
SPList list = web.Lists["Product List"];

// Use the traditional approach to create SPListItems and hook it up with the list
SPListItem item = list.Items.Add();
item["Name"] = "Sample Product Wohoo";
item["Description"] = "Sample Description Wohoo";
item.Update();

Upon running this code in your SharePoint application, it will create the SPListItem object and add a Name and Description. When you hit .Update() it will push this data through the data source connection, to your SQL server.

Here's what the updated data looks like:

We're running a Beta-product!

As you can imagine, there's a ton of new cool things to work with in SharePoint 2010 - where the BCS is one. This article discuss the very basics of how you can retrieve information from these lists using the normal API-approach.

At the time of this writing (during Public Beta) there isn't any measures on performance and what impact it has on the server in comparison to alternative ways to fetch and work with the data.

As time goes on, there will be probably be some new information on this - I'll keep you posted when I know more.

Summary

As you can see, working with external data from the SharePoint API isn't very hard to do. What you need to make sure is to have an external list set up somewhere (see this article for how you can do that) and then you can simply use the normal SPList object from the SharePoint object model to work with the external list and it's external data from the SQL server (in my case).

So if you haven't already: Get on the SharePoint 2010 wagon and enjoy the ride!

Published: Jan-19-10 | 4 Comments | 0 Links to this post

SP 2010: Getting started with Business Connectivity Services (BCS) in SharePoint 2010

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 1 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

BCS in SharePoint 2010 is an awesome refinement of the Business Data Catalog from MOSS 2007. With BCS - or Business Connectivity Services - you get the possibility to connect external data and work with it from SharePoint.

In this article I will not cover the basics of what BCS is all about (MSDN/TechNet does this very well) - I will rather give you a walkthrough of how you can setup a BCS connection to an external database, and then work with this information directly from a SharePoint list - without the user actually knowing anything about the connection to the database.

BCS Poster: Business Connectivity Services Poster
BCS Team Blog: http://blogs.msdn.com/bcs/

A sample SQL database

I'll just show you how my sample database is set up - simply create a new database in your SQL Server and have it filled with some example data. In my case, this is the data in my SQL database, called Zimmergren_DB:

In this sample database, I've added a table called ProductList which in theory will represent some products in this database, like this:

I'm filling the database with some sample data, so we will be familiar with this data when we later watch this information from SharePoint:

Alright - we have some sample data in our SQL Server. Nothing fancy, just some very simple data. Great, let's get going with the fun stuff!

Creating an external content type

The most effective and easy way to set up a simple BCS connection, is to use SharePoint Designer 2010. You heard me, we can now get up and running with BCS by using SPD instead of modeling complex ADF files and things like that.

In order to do this, we need to create a new External Content Type!

Here's how do create our External Content Type and hook it up with our database, step by step:

Open the site you want to work with using SharePoint Designer 2010
Select "External Content Types" in the left hand navigation:

Loading this page might take some time, be patient!
Click to create a new External Content Type like this:
Click the link that reads: "Click here to discover external data sources and define operations":
Click "Add Connection"
Select "SQL Server" as your Data Source Type:
Enter the details about your connection to your SQL Server:
When the connection is made, your Data Source Explorer will be filled with the database you have specified. Now choose the table you want to work with, and right-click and select "Create All Operations":

You'll be presented with a wizard-like dialog where you can specify the operations, elements and other properties for your BCS connection.
Click "Next" to get to the Parameters page
Select the field that you want to act as an Identifier. In my case I've selected my ProductID just to get on with it:
Click "Finish"
You'll be presented with a list of operations that your External Content Type can do, like this:

That's it. A few points, a few clicks - and you're done. Let's create an external list (using the Browser to show how simple it is..) and hook up our external content type with it!

Creating an external list

There's a few ways to create an external list in SharePoint 2010. We will create it using the Browser UI to show you how simple it can be.

Open your site and choose Site Actions - More Options…
Select the External List template, and click Create
Enter a name for your list, e.g. Product List
You'll see a field in this list called External Content Type, click the browse-button beside it:

What is really awesome here, is that you're now presented with a dialog where you simply can choose the data source for this list. That means, you'll select the data source you've created (mine is called Zimmergren_DB). Then your list will automatically work against the SQL database, but still have the look and feel of a SharePoint 2010 list.
Select your data source and click OK:
Now simply click the button called Create:

Would you look at that! You're now working with external data, from your (what looks to be) normal SharePoint list! This is brilliant!

You now have the ability to create new items, update existing items, delete items and do all your normal CRUD-operations (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.

Proof of concept - Adding a new product

Let's just for the fun of it add a new product called "Awesome Product 1.0" like the following screenshot:

Now go to your SQL Server and see the changes take effect immediately. The data is NOT stored in SharePoint, it's stored in your SQL Database.

This is what my table now looks like in the SQL Server, after adding a new item in the SharePoint list:

Summary

With a few points, followed by a few clicks - you've set up your external data connection. Basically it's that simple.

Of course there's a lot of things to consider when doing these configurations - and you might not want to auto-generate the CRUD-operations, but rather create them one by one and specify more fine-grained permissions etc.

This is merely a sample to show you how easy it is to actually get up and running with the SharePoint 2010 Business Connectivity Services (BCS) and work with external data!

Enjoy

Published: Jan-18-10 | 15 Comments | 0 Links to this post