Creating External Lists Programmatically

Creating External Lists using SharePoint Designer 2010 or SharePoint 2010 UI is just a breeze. But what if you want to create them programmatically?

There are two different ways you could do so:

Using Object Model

Using the SPWeb.Lists.Add method we can create an external list:

SPListDataSource ds = new SPListDataSource();
ds.SetProperty(SPListDataSource.BDCProperties.LobSystemInstance, "Demo Customers");
ds.SetProperty(SPListDataSource.BDCProperties.EntityNamespace, "http://intranet");
ds.SetProperty(SPListDataSource.BDCProperties.Entity, "Demo Customers");
ds.SetProperty(SPListDataSource.BDCProperties.SpecificFinder, "CustomerRead Item");

using (SPSite site = new SPSite("http://intranet"))
{
    using (SPWeb web = site.RootWeb)
    {
        web.Lists.Add("Demo Customers", "Demo Customers", "Lists/DemoCustomers", ds);
    }
}

Using the SPListDataSource.BDCProperties, we can set the properties of the external system.

You can now wrap this code in a feature receiver.

Using List Instance

Creating external lists using the List Instance wizard in Visual Studio 2010 is not available, but you can still create an external instance using list instance.

Using the list instance wizard, add a list instance to your project. Select any list to instantiate from.

Now, edit the list instance’s Elements.xml and change it to reflect the creation of an external list:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance Title="CustomersListInstance"
                OnQuickLaunch="TRUE"
                TemplateType="600"
                FeatureId="00bfea71-de22-43b2-a848-c05709900100"
                Url="Lists/CustomersListInstance"
                Description="External Customers List">
    <DataSource>
      <Property Name="LobSystemInstance" Value="Demo Customers" />
      <Property Name="EntityNamespace" Value="http://intranet" />
      <Property Name="Entity" Value="Demo Customers" />
      <Property Name="SpecificFinder" Value="CustomerRead Item" />
    </DataSource>
  </ListInstance>
</Elements>

Things to note:

1) TemplateType – The external list template type Id is 600

2) DataSource – Same as how we used the SPListDataSource.BDCProperties when creating an external list using object model

What is Included in BCS – BCS Feature Comparison Table

Below is a simple table to find what BCS features are baked into the SharePoint 2010 platform editions:

BCS Limitations

Even though BCS is great and can do wonders when you want SharePoint 2010 to interact with your external systems or other LOB applications, it has its own limitations. Understanding these limitations will help you build good BCS solutions.

Below are some/all of the BCS/External Lists limitations:

1. Workflows cannot be associated with external lists
• However, you can use the external data columns and manipulate the external lists in a workflow
2. No RSS feed support for external lists
3. No REST based access for external lists
• The http://site/_vti_bin/ListData.svc does not support external lists
4. LINQ to SharePoint spmetal.exe does not support external lists
5. Cannot configure alerts for external lists
6. Cannot export external list items to Excel (Export to Excel feature)
7. Item-Level permissions is not available for external lists
8. Versioning cannot be configured on external lists
9. Item History is not available on external lists
10. Datasheet view cannot be used in external lists
• XSLT is supported though
11. You cannot create a site column of type ‘External Data Column’
• External data columns can be created only as list based columns and cannot be consumed in site level content types
12. No Write support for BLOB
• You cannot write back to BLOB fields using BCS unless you write your own method
• You can access BLOB columns by defining a StreamAccessor method and presenting the external data via the BCS Data List web part by checking the Display stream fields property
13. Ratings feature is not supported for external lists

Some of the limitations are quite obvious as the data does not reside in SharePoint and it is not SharePoint’s responsibility on how the data behaves. SharePoint is here to just present the external data. That said, I do think some are not limitations, but Microsoft just didn’t have time to implement, like:

1. RSS feed support
2. External data site columns
3. Export to Excel

If I have missed any limitations, please feel free to comment and I will add them to the list.

BCS: Showing a friendly display name on the External Item Picker control

Associations in BCS are a great way to relate two external content types based on a foreign key. It is actually very easy to create an association using SharePoint Designer 2010 – no code required!

You can read more on the following blogs posts to know more about associations:

1) The Notion of Associations and the External Item Picker

2) Tooling Associations in SharePoint Designer 2010

If you want to know quickly how can Associations help me, consider the picture below:

The Sales Order table has a column CustomerID which is actually a foreign key in the Customers table.

If you create Sales Order external list, you will notice that you get a text field for entering the CustomerID, which isn’t that helpful:

But if you create an association, then you get an external item picker where you can filter/select the customer:

If you read the blog post which I pointed out earlier, it says:

“……it’s possible to show a friendly display name on the external item picker control upon customer selection to show something more meaningful that the number that identifies the customer in the external system, for instance the customer name, or whatever field that is tagged as the title field in SPD.”

But how do you ‘tag as the title field in SPD’ ?

Well, it turns out it is very easy and simple!

Set Field as Title

In your external content type Summary View, select the field you want to ‘tag as the title’:

In the Ribbon, select Set as Title:

Now, you will be able to see that field, whatever is set as Title in the external item picker instead of the default identifier field

SharePoint 2010: 'The default web application could not be determined' Error

When building SharePoint 2010 applications using Visual Studio 2010: This shouldn't happen at any instance, but in an unlikely event, you may end up with this error (probably when building a custom BDC .NET assembly) :

\

Error occurred in deployment ‘Add Solution’: The default web application could not be determined. Set the SiteUrl property in feature BdcModelProject1_Feature1 to the URL of the desired site and retry activation.

Parameter name: properties

This occurs when the feature’s manifest is missing its SiteUrl property which determines where the feature will be deployed. Here is a simple workaround:

1) Open your Feature Designer

2) Click on the Manifest tab

3) Expand Edit Options and click on Overwrite generated XML and edit manifest in the XML editor. This will allow you, the developer, to handle the manifest file instead of VS2010 handling it.

4) Add the SiteUrl property:

<Property Key=”SiteUrl” Value=”http://<your-site-name>” />

5) The final manifest will look something like this:

Now your solution will deploy fine!

Part 2: BCS Connectivity Errors

Now that you have configured BCS, you can create external content types and external lists. You can refer to my blog post – SharPoint 2010: BCS Walkthrough – to quickly learn how to create an external content type and an external list.

BDC Access Denied Error

Now that your external list is created, you will certainly like to view the list in SharePoint. When you visit the external list, don’t be surprised if you see the error below:

This is a very common error that many of you might face. This error is due to the current user not having enough permissions to access the BDC entity.

So, browse to your BDC Service Application page: Central Administration | Application Management | Manage service applications | Business Data Connectivity

You should be able to locate your BDC model. For our example, its the External Customers. In the dropdown ECB menu, select on Set Permissions:

In the Set Permissions dialog window, you can now choose your user(s)/group(s) who need access to this BDC entity. For our example, I am choosing Administrator user.

You can also set the type of permission you want to grant. For our example, I have granted all of the permissions available:

1) Edit

2) Execute

3) Selectable In Clients

4) Set Permissions

Query against the database error

Now if you refresh your external list page, you might get this error or similar error:

This means that the user has access to the BDC entity, but there is something wrong when the model is trying to fetch the external data. In this case, it is from the database. Our External Customers entity model connects to SQL Server database to retrieve the customers. So, this error clearly tells that there is some problem while fetching that data.

One other useful thing to do when you get this error is to check the Windows Event Viewer logs. BCS logs errors to Windows Event Viewer logs. Here is the cause for our error:

It is very clear from the logs that the user Administrator is not having access to the Customers database. So, its an easy fix – After granting the user Administrator rights to Customers database, here we are with all the customers!

Part 1: Configuring BCS in SharePoint 2010

There has been a lot of blog posts floating around (including in this blog) on how to consume external data into SharePoint 2010 using the new Business Connectivity Services(BCS) feature set built using the new Business Data Connectivity(BDC) service application. But less has been told on how to configure BCS for both intranet & internet scenarios.

For the next few weeks will see how we can configure BCS, the various BCS authentication schemes and the double hop issue. This blog post today will take you through the basics of configuring BCS.

Configure BCS via Farm Configuration Wizard

After SharePoint 2010 is successfully installed and the farm created, you should be redirected to your central administration page where SharePoint will configure your farm with the available services. You can either accept to configure all the services or select the services you want for your farm.

If you had chosen to configure all services (which is the default option), you already have BDC service application configured for you by SharePoint. Once configured, you should be able to see the BDC in your service application’s list.

1. Browse to the central administration site
2. Click on Application Management | Service Applications | Manage service applications
3. You should be able to locate the Business Connectivity Services

Configure BCS by creating a new BCS Service Application

If you had not chosen BCS in the farm configuration wizard, then you probably don’t have BCS configured. However, you can create a BCS service application and proxy in a few steps.

1) Browse to the central administration site

2) Click on Application Management | Service Applications | Manage service applications

3) In the ribbon, click on New | Business Data Connectivity

4) Specify the settings for your Business Data Connectivity service application

• Make sure you enter the Service Application Name, Application Pool and right Managed Account

5) Once created successfully, you should see your new BDC service application

Configure Service Application Associations

Every web application in SharePoint 2010 will be associated to its own set of service applications. You can choose to associate your web application with all of the service applications or choose from the existing list.

If you had configured BCS using the SharePoint Configuration Wizard, your web application will be already associated with the default BDC service application and you can skip this step.

If you had configured BCS manually by creating a new BDC service application, the chances are that the new BDC service application is already associated with all of your web applications. But it is always better to check once. Follow the steps below to do so:

1) Browse to the central administration site

2) Click on Application Management | Service Applications | Configure service application associations

3) Click on your web application

4) If you already see My BDC Service in the default list, then its already associated, else:

• Choose custom from the drop down
• Select the new BDC service application and also you can set it as default

The BDC service application is now configured and you are ready to rock the BCS world of magic!

Building and Deploying BDC Models in SharePoint Foundation 2010

** Note: The problem and solution discussed below applies for SharePoint 2010 public beta. Future versions may avoid the need to copy this DLL from SharePoint Server. **

One of the cool things about SharePoint Foundation 2010 (the ‘free’ version) is that it supports Business Connectivity Services – which means you can build Business Data Connectivity(BDC) models and deploy to your site.

The Problem

If you have already tried building one (custom .net assembly), you will encounter the following error:

Failed to load receiver assembly "Microsoft.Office.SharePoint.ClientExtensions, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"

The Solution

The solution is very simple – If you have SharePoint Server 2010 beta installed, copy the Microsoft.Office.SharePoint.ClientExtensions.dll from C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI to the following locations where SharePoint Foundation 2010 is installed:

1) GAC

2) C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI

Now,  deploying your BDC model (custom .net assembly) from visual studio should work :)

BDC .NET Assembly Connector: Tame SharePoint Search to search your .NET BDC Entity

So, now you have built your BDC .NET Assembly Connector and even added write operations to it. The next big thing you would think is – Can I tell SharePoint to search this BDC model?

Of course, you can! But there are few things you need to do in your BDC model in order for SharePoint to crawl your BDC model.

The BDC model exposes operations or methods through which SharePoint is going to interact with the external data sources. In our previous example, we had 3 operations:

1. ReadItem – Read Operation returning a single entity
2. ReadList – Read operation returning a collection of entities
3. Update – Write operation updating a single entity

So, when you want SharePoint to crawl the BDC model, we need to tell SharePoint which operation to use in order to gather the data.

The RootFinder Method

With the BDC models, we can set properties on operations, entities, LOB instances etc., that let SharePoint identify certain extra capabilities. If a finder method has its RootFinder property set, then SharePoint will know that it has to enumerate the entities to crawl.

In our sample, the ReadList method is going to return all the entities and thus we can set the RootFinder property on this method.

Switch to your BDC Explorer, and click on ReadList. In the properties, click on Custom Properties button and add the RootFinder property as in the screenshot below:

Name – RootFinder

Type – Select System.String from the dropdown

Value – x

Switch to the designer pane and click on the ReadList method:

In the BDC Method Details pane below, click on ReadList instance under Instances under ReadList method:

In the properties, click on Custom Properties button and add the RootFinder property as in the screenshot below:

Name – RootFinder

Type – Select System.String from the dropdown

Value – x

Remember, since we want the ReadList method to be the RootFinder, we are doing this. If you have any other method, you can set this property for that method instead of the ReadList method.

Setting the ShowInSearchUI Property for the Lob System Instance

Not only we need to set the RootFinder property for the specific finder method, we also need to set the ShowInSearchUI property on the LobSystemInstance to true. This will tell SharePoint that this particular LobSystemInstance should be used in Search UI. Unfortunately, currently this can only be done via code. Below is the code to set this property on the BooksListBdcModelInstance. Just create a simple console application and execute the following code. You need to add the BDC Admin Object Model references which are as follows:

NOTE: The following is only applicable for the current Beta 2 release which is publically available. Things might change in the later builds.

1. Microsoft.BusinessData from C:\Windows\assembly\GAC_MSIL\Microsoft.BusinessData\14.0.0.0__71e9bce111e9429c\
2. Microsoft.SharePoint.BusinessData.Administration.Client from C:\Windows\assembly\GAC_64\Microsoft.SharePoint.BusinessData.Administration.Client\14.0.0.0__71e9bce111e9429c\
3. microsoft.office.sharepoint.clientextensions.intl from C:\Windows\assembly\GAC_MSIL\microsoft.office.sharepoint.clientextensions.intl\14.0.0.0__71e9bce111e9429c\

static void Main(string[] args)
{
    AdministrationMetadataCatalog catalog = 
        AdministrationMetadataCatalog.GetCatalog("http://demo2010a");
    Model bdcModel=catalog.GetModel("BooksListBdcModel");
    LobSystemCollection lobs = bdcModel.AllReferencedLobSystems;

    LobSystem flatFileBDCModel = (from l in lobs
                                  where l.Name == "BooksListBdcModel"
                                  select l).FirstOrDefault();
    if (flatFileBDCModel != null)
    {
        LobSystemInstance flatFileBDCModelInstance 
            = (from ls in flatFileBDCModel.LobSystemInstances
               where ls.Name == "BooksListBdcModelInstance"
               select ls).FirstOrDefault();

        if (flatFileBDCModelInstance != null)
        {
            Console.WriteLine("Updating ShowInSearchUI property for " + flatFileBDCModelInstance.Name);
            flatFileBDCModelInstance.Properties.Add("ShowInSearchUI", "true");                    
            //flatFileBDCModelInstance.Properties.Remove("ShowInSearchUI");                    
            flatFileBDCModelInstance.Update();

            flatFileBDCModel.Update();
        }               
    }

    bdcModel.Update();            

    Console.ReadKey();
}

We are using the BDC Admin Object Model and:

1. Connect to the SharePoint Site – change the site from http://demo2010a to your SharePoint site
2. Get the Catalog – which is BooksListBdcModel
3. Get the Lob System – which is again BooksListBdcModel
4. Get the Lob System Instance – which is BooksListBdcModelnstance
5. Set the property to true

Configure SharePoint Search

Creating the BDC Content Source

Go to the Search Administration in Central Admin – Central Administration | Application Management | Manage service applications | Search Service Application and create a new Content Source

Select the Content Source Type as Line of Business Data

Select to crawl selected external data sources and select BooksListBdcModelInstance

Select to start a full crawl and click OK. This will start a full crawl of the content source.

Wait till the full crawl is over.

Updating the Scopes

Now go to your scope where you want to include this content source and create a new rule to include this content source:

Click OK.

Wait for the scope to be updated. This might from few seconds to few minutes depending on the content (It took 18 minutes for me!) or You could start the update manually from the Search Administration page. Look for Scopes updates status in the System Status.

Perform Search

Once the content source is crawled and scopes updates, perform the search from the site. For our books entity, enter any ISBN number, say for example 0470099410 in the search text box and hit the search button.

You should see the search results showing up!

(Clicking on it will not take you to the list item though)

BDC .NET Assembly Connector: Updating a BCS Entity

In my previous post, I showed you how to build a simple BDC model using the .NET Assembly Connector. SharePoint 2010 not only allows to perform read operations from external data sources, but also supports write operations to external data sources. This allows us to update or delete an entity (based on the Identifier). In this post, we will extend our previous BooksList BDC Model to include write operations.

To make the update process simpler, I have slightly modified the previous code. I have now introduced a static List that holds the list of all books and build the entity in ReadList and ReadItem methods from the static List.

The Changes

The new books list:

static List<string> books = new List<string>

{

    "0470099410,Book1,Author1,$25.50,Publisher1",

    "0470099411,Book2,Author2,$35.50,Publisher2",

    "0470099412,Book3,Author3,$45.50,Publisher3",

    "0470099413,Book4,Author4,$55.50,Publisher4",

    "0470099414,Book5,Author5,$65.50,Publisher5"

};       

The updated ReadItem method:

public static BookEntity ReadItem(string id)

{

    string book = books.FirstOrDefault(s => s.Contains(id));

    if (book != null)

    {

        string[] b = book.Split(',');

        return new BookEntity

        {

            ISBN = b[0],

            Name = b[1],

            Author = b[2],

            Price = b[3],

            Publisher = b[4]

        };

    }

    return null;

}

The updated ReadList method:

public static IEnumerable<BookEntity> ReadList()

{

    //TODO: This is just a sample. Replace this simple sample with valid code.

    List<BookEntity> booksList = new List<BookEntity>();

    BookEntity bookEntity;

    foreach (string book in books)

    {

        string[] b = book.Split(',');

        bookEntity = new BookEntity()

        {

            ISBN=b[0],

            Name=b[1],

            Author=b[2],

            Price=b[3],

            Publisher=b[4]

        };

        booksList.Add(bookEntity);

    }

    return booksList;

}       

Current Operations in the external list

If you browse to your list and open the ECB menu, you can see there is only View option available:

Adding the Update Method

In your BDC diagram pane, click on the entity to reveal the BDC Method Details pane.

At the bottom you can see the option to Add a Method. Click on that to reveal the available options:

Click on Create Updater Method. This will add the Update operation to the BDC model.

And the diagram pane is also updated to hold the new Update method:

Update Operation Parameters

By default, the update method adds the entity as the only parameter of type In which means that this will be coming into the .NET Assembly Connector from SharePoint list. But for our demo, we have set the ISBN property as the Identifier and this is going to be the key field in terms of finding the required book. So, we should have another parameter of type In which is of type String and will be the ISBN property.

Click on Add a Parameter and select Create Parameter in the Update method in the BDC Method Details pane. This will create a new parameter.

Switch to BDC Explorer and you can see the new parameter added:

Click on the new parameterTypeDescriptor. In the Properties pane, change:

1. Name to ISBN 
2. Set the Pre-Updater Field to True
3. Select the Identifier as ISBN
   

We have now set that this parameter of name ISBN is the Identifier and a pre-updater field which essentially tells BDC to preserve the field’s previous value when the entity is being updated.

In the diagram pane, double click on Update method to open the code behind

Adding Update Code

Below is my update code:

public static void Update(BookEntity bookEntity, string parameter)

{

    string book = books.FirstOrDefault(s => s.Contains(parameter));

    if (book != null)

    {

        int index = books.IndexOf(book);

        books.RemoveAt(index);

        books.Insert(index,

                     String.Format("{0},{1},{2},{3},{4}",

                                    bookEntity.ISBN,

                                    bookEntity.Name,

                                    bookEntity.Author,

                                    bookEntity.Price,

                                    bookEntity.Publisher));

    }

} 

We are only updating the List and when BDC calls ReadList operation for refreshing, it will pick up the updated books List, iterate and build the entity.

Build and deploy your solution.

Edit Item

Now you can see that you have the option of Edit Item

Click on Edit Item and you will be presented with the dialog window to edit the current item :)

Change the Author from Author1 to New Author and click Save

You should see the updated value for that entity!

P.S: If you encounter any errors clicking Edit Item, please re-create the External List. I am not sure the cause of the problem, but will investigate and update on this issue in this post as soon as I find the cause.

You can download the source code here

Building a BDC Model using the .NET Assembly Connector

My previous post on BCS Walkthrough showed how to make use of the SQL Server Connector, connect to a database,and bring the data back to SharePoint 2010. This used the SQL Server as its data source. If you want to bring the data back from a WCF service, there is the out of the box WCF Service Connector.

What if your external data source is not SQL Server or a web service, and is some custom .NET assembly or a flat file? This is where the .NET Assembly Connectors come into picture. Developers can build & design their custom model & entities using Visual Studio 2010, thereby help the BCS to interact with the custom data sources. The .NET Assembly Connector is nothing but a .NET assembly that acts as a proxy to interact with the external data source.

Lets explore this .NET Assembly Connector with a simple example.

I would also recommend you to watch the BCS Overview Demo videos if you are not familiar with BCS.

External Books List

So, I have this external books list that I want to bring into my SharePoint 2010 site. The list has the following properties associated with it:

1. ISBN
2. Name
3. Author
4. Price
5. Publisher

BDC Model Project

Open VS2010 and select the Business Data Connectivity Model project template

Once the project is created, you will be presented with the simple entity called Entity1with two read operations:

1. ReadList
2. ReadItem

The names suggest what they do. ReadList operation is going to return you the full list (list of Entity1) and the ReadItem is going to read a single item (Entity1). You can see the same in the BDC Model Designer:

If you explore the Solution Explorer, you will see the class files and the feature associated with this BDC project

The Entity1 class corresponds to the Entity1 entity and has its properties defined. The Entity1Service class implements the operations, that is ReadList and ReadItem.

Modifying the Entity1 Class

We can now modify the Entity1 class to BookEntity and declare the properties. Rename the Entity1.cs to BookEntity.cs

The ISBN will be our Identifier or the primary key with which a book is identified.

Modifying the Entity1Service Class

Rename the Entity1Service.cs to BookEntityService.cs.

Create a simple books array. For the simplicity of this post we are using Objects. This data can also come from a flat file.

Modify the ReadItem to return the book matching the id, which is the ISBN property:

Modify the ReadList to return the list of all books:

Update the BDC Model Designer

Now that we have modified the code to do what we want, it is time to modify the BDC Designer model. Double click on BdcModel1.bdcm in the Solution Explorer to open the designer. Switch to BDC Explorer.

Go ahead and rename the BdcModel1 to BooksListBdcModel and the BdcModel1 in the LobSystemInstances to BooksListBdcModelInstance

Rename the Entity1 to BookEntity matching our entity name.

In the BDC Model, rename Identifier1 to ISBN

Switch to BDC Explorer and expand ReadItem operation and change the Identifier1 to ISBN under @id

Rename the Entity1 in the @returnParameter to BookEntity and delete the Message type descriptor node. Then rename the Identifier1 to ISBN

Right click on BookEntity node and select Add Type Descriptor. It will add a new type descriptor node:

Rename it to Name.

Do the same and add Author, Price and Publisher. Below is how it will look after adding these properties. Make sure you explore the Properties window and check the type name to be System.String

Expand ReadList operation.You can see that the Entity1 is listed again as the return parameter.

You don’t have to create them again, instead we can copy from the ReadItem operation and paste the BookEntity here. In the ReadItem, right click on BookEntity and select to Copy

Right click on Entity1List and select Paste

You can now safely delete the Entity1 node. Rename the Entity1List to BookEntityList

Below is our modified BDC Model

For more details on these functions, switch to the diagram pane and click on the BookEntity and this should reveal the Method Details Window:

You can see that @id parameter is a parameter that is going to come into the .NET assembly (which is the proxy and is BookEntityService class) and @returnParameter is of return type that this .NET assembly operation is going to return. So now it makes clear that ReadItem is accepting @id parameter which corresponds to the ISBN property (type descriptor) and returns a @returnParameter which is of type BookEntity. The ReadList returns @returnParameter which is of type BookEntityList which is a collection of type BookEntity.

Based on your needs, you can add new methods, define parameters and implement them.

Build and deploy your solution.

Verifying the BDC Model

Browse to your Central Admin and open Application Management | Manage service applications | Business Data Connectivity

You should see the BDC model we just built and deployed

Click on BookEntity to explore the fields and various other properties

Create the External List

Browse to your Site. Click on Site Actions | View All Site Content and then Create

Choose External List from the Content and Data category and click Create

Give a name to your list and choose the BooksListBdcModelInstance as your external content type. You can use the picker to pick

Click Create to create the list.

And here is our list pulling external data from our .NET assembly!

You can download the sample here.

Workflow Support for External Lists

If you are already playing with SharePoint 2010 and BCS, you would have noticed that there is no option to associate workflows for external lists. Many users have asked me whether these external list support workflows, so here is the answer from Tom Rizzo, SharePoint Senior Director, at the MSDN forums:

You can associate a workflow as a custom action for your External List in SPD.  You probably (and I'll have to double check but I'm 99% sure) can't have a workflow fire by default against an external list since the workflow engine won't see things changing in the backend system.

Now, workflow is pluggable in 2010 so you might be able to plug in your own event provider into our workflow engine and watch the backend yourself.  I haven't tried to do that yet so I don't know if it's feasible but it's something to take a look at if you want automatic workflow start but definitely clicking a button on the ribbon on an external list and triggering a workflow is supported.

Rather than creating an external list, you may want to instead create a SharePoint list with external columns so you get more SharePoint native features.
Hope this helps.

Tom

SharePoint 2010: Business Connectivity Services Walkthrough

Business Connectivity Services (BCS) in SharePoint 2010 provides new ways to connect and integrate with external data in SharePoint. Business Connectivity services also allows users to create external content types, external lists based on the external data. This opens up a new dimension on how you view external data in SharePoint. To get to know more about BCS, you can have a look at the Business Connectivity Services poster - http://bit.ly/bcs_poster

Lets get straight to work!

Here is a simple Customers table that you would like to bring to SharePoint.

Ideally you want to create a Customers List in SharePoint which can bring this Customers data into SharePoint and be in sync with the external data.

SharePoint Designer to the Rescue

If you are thinking ‘What, SharePoint Designer? Are you crazy!’ – Well, my dear friend, you are in for a surprise with what SharePoint Designer 2010 can offer you!

The first and foremost thing you would notice is the Office Ribbon integration:

No more folder views and is replaced by the Navigation pane:

With SharePoint Designer 2010, creating External Content Types is very simple!

Click on the External Content Types in the navigation pane. This will open the External Content Types tab.

Select New External Content Type from the ribbon.

This will create the new external content type

Go ahead and change the Name and Display Name to External Customers:

To create external connections and operations, click on click here to discover external data source an…

Now you can add connections:

SharePoint Designer allows to create external data sources connected to:

1) SQL Server

2) .NET Type

3) WCF Services

Lets select SQL Server as our external data source is in the SQL Server

Enter your server details. The Database Name will be Customers.

Now, I can see the Customers database and the Customers table that I am looking for:

Right click and create the operations. Create All Operations will create the necessary Create, Read, Update, Delete operations.

Go through the Wizard and complete it.

And here are the operations created:

Now, you can create External Lists pretty easily from the Ribbon:

Fill in your List details:

And our external list is created!

No pain, no hassles, pretty simple step-by-step procedure!

Integrating with Office Outlook

To go one step, further, you might actually want to use these Customers in your Outlook so that you can store them as contacts. Integrating external data to Office is just few clicks away with SharePoint Designer!

In the SharePoint Designer, Choose the Office Item Type as Contact for the external content type.

This will enable Outlook Contacts integration with the external content type.

Rest is to map the appropriate data source fields with Office properties.

Double click on Read Item and map the fields:

Select the appropriate Office field from the Office Property for each of the data source element.

Once mapped, save the changes in SharePoint Designer.

Open the external list in the browser.

Under the List tab in the Ribbon, click on the Connect To Outlook button. This will send the external list information to Outlook. As we have configured the external content type as Contact Office Item, they would appear as Outlook Contacts.

A new Outlook add-in will be installed:

Below is a screenshot of the external data as Outlook Contacts:

Sync to SharePoint Workspace

You can also sync this external list with SharePoint Workspace and take the data offline!

Here is a screenshot of the external list in SharePoint Workspace