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 Feature SharePoint Foundation Standard Edition Enterprise Edition
External List

Yes

Yes

Yes

External Data Column

Yes

Yes

Yes

BDC Service

Yes

Yes

Yes

Connector Framework

Yes

Yes

Yes

Secure Store Service

No

Yes

Yes

External Data Search

No

Yes

Yes

Profile Pages

No

Yes

Yes

Business Data Web Parts

No

No

Yes

Rich Client Integration

No

No

Yes

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
  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.

SharePoint 2010 Visual Studio Replaceable Parameters

This is one of the best features in the Visual Studio 2010 Tools for SharePoint 2010! You can use these replaceable parameters in your project files for SharePoint values. Visual Studio will replace them with actual values when packaging the solution.

This is very useful, especially in Elements file or .ascx page files.

Some Example Usages

For example, this is how I use for my delegate control:

<Control Id="AdditionalPageHead"
           ControlAssembly="$SharePoint.Project.AssemblyFullName$"
           ControlClass="$SharePoint.Type.15c18d58-bb1f-458e-b932-2223ca1f276a.FullName$" />

When packaging, the $SharePoint.Project.AssemblyFullName$ will get replaced to:

AssemblyName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=publickeytoken

And $SharePoint.Type.<guid>.FullName$ will get replaced to the appropriate class.

If you want to use the token the replace your class name, you need to add the Guid (System.Runtime.InteropServices) attribute:

Guid Attribute

When you add Event Receiver to your feature, VS2010 does exactly the same. After adding event receiver, navigate to your feature properties to find the tokens used:

Feature Properties

And if you add an Application Page or User Control, you can see it too:

Application Page

How to find if my token has been replaced properly?

If you want to check whether your tokens had been replaced properly:

1) Package your solution – Right click your Project | Select Package

2) In the Solution Explorer, click Show All Files

3) Expand pkgobj | Debug or Release | TokenReplaceFolder

pkgobj Folder

4) Open the appropriate files to find whether your tokens has been replaced

Download Document Set as zip

One of the cool new feature in SharePoint 2010 is Document Sets. They can easily replace Folders in SharePoint 2010. Document Sets help you organize related documents into a ‘set’ and also provide a user interface (home page) to manage them. You can also use metadata against the document set which makes it even more useful!

Document Set

But one of the most asked feature is missing from the SharePoint UI – Downloading the whole document set, along with its files as a zip file!

But if you look into the SharePoint 2010 SDK, there is code that extends the Document Set Ribbon tab to download the document set!

It turns out downloading the document set can be done using only Server Object Model (SharePoint API) . Microsoft uses the same for some of the document set functions like sending the document set to content organizers.

Unfortunately, this option is not available in the Client Object Model and in Sandboxed environment. The reasons are quite obvious:

1) Client Object model includes only SharePoint Foundation 2010 features. Document Set is a SharePoint Server 2010 feature.

2) Microsoft.Office.DocumentManagement.dll should be referenced to work with Document Sets which is not sandboxed.

You can download the full solution along with the source code from here: http://bit.ly/docsetdownload (The SDK does not have the full working project/solution)

Once deployed, you will get Download Copy option inside your document set:

Document Set download

Using the SharePoint 2010 Modal Dialog

SharePoint 2010 introduces the new dialog framework which helps users stay in context of the page without navigating away from the page. Yes, the modal dialogs that pop up:

image

The JavaScript client object model provides the SP.UI.ModalDialog class to work with the dialog framework.

In order to work with the dialog framework, we need to first create the dialog options:

var options = SP.UI.$create_DialogOptions();
options.width = 500;
options.height = 250;
options.url = "/_layouts/StandardsPortal/ChangePassword.aspx";
options.dialogReturnValueCallback = Function.createDelegate(
                    null, portal_modalDialogClosedCallback);

As you can see from the above code, we set options on width, height and what is the URL the modal dialog should load. In this case, an Application Page. Notice that we also initialize the callback.

Once the options are set, you can now show the modal dialog:

SP.UI.ModalDialog.showModalDialog(options);

Now warp this code into a function:

function portal_openModalDialog() {
    var options = SP.UI.$create_DialogOptions();
    options.width = 500;
    options.height = 250;
    options.url = "/_layouts/StandardsPortal/ChangePassword.aspx";
    options.dialogReturnValueCallback = Function.createDelegate(
                        null, portal_modalDialogClosedCallback);
    SP.UI.ModalDialog.showModalDialog(options);
}

You can now call this JavaScript function from your custom action or from web part or from your custom page or from your custom Ribbon button. Below is the custom action XML:

<CustomAction
   Id="{F93B1F84-1DBE-4C10-82E3-2CA47346359E}"
   Title="Change Password"
   Description="Change your password"
   Sequence="1000"
   Location="Microsoft.SharePoint.StandardMenu"
   GroupId="PersonalActions"
   ImageUrl="~sitecollection/_layouts/images/menulistsettings.gif">
   <UrlAction Url="javascript:portal_openModalDialog();"/>
</CustomAction>

Notice how we invoke the JavaScript dialog:

<UrlAction Url="javascript:portal_openModalDialog();"/>    

Below is the dialog callback code (very simple actually):

function portal_modalDialogClosedCallback(result, value) {
    if (value == '1') {
        this.statusId = SP.UI
            .Status
            .addStatus("Password Changed",
               "Your password has been changed. Use it next time when you log in.", 
                true);
        SP.UI.Status.setStatusPriColor(this.statusId, "Green");
    }

    if (value == '0') {
        this.statusId = SP.UI
            .Status
            .addStatus("Password Change Failed",
                "Your password has <b>not</b> changed. Please try again.", 
                true);
        SP.UI.Status.setStatusPriColor(this.statusId, "Green");
    }

    setTimeout(RemoveStatus, 6000);
}

function RemoveStatus() {
    SP.UI.Status.removeStatus(this.statusId);
}

As you can see, I pass a single value back and now I can check and show status based on that value. You can also check for the dialog result using SP.UI.DialogResult.OK and SP.UI.DialogResult.cancel properties:

 function CloseCallback(result, value) { 
        if(result === SP.UI.DialogResult.OK) { 
            alert("OK was clicked!"); 
        } 
        if(result === SP.UI.DialogResult.cancel) { 
            alert("Cancel was clicked!");         
        } 
 }

Showing a status is very simple using the SP.UI.Status.addStatus method.

Now, after deploying when you click on the Change Password custom action

image

You get the modal popup dialog opening the Application page URL set in the dialog options:

image

To wire the Modal Dialog ‘OK’ and ‘Cancel’ code, we write the appropriate code in the ‘Change Password’ button click & ‘Cancel’ button click:

this.Page.Response.Clear(); 
this.Page.Response.Write("
<script type=\"text/javascript\">window.frameElement.commonModalDialogClose(1, 1);</script>"); 
this.Page.Response.End();

If successful, you will see the following status:

image

If failed, you will see the following status:

image

SharePoint 2010 Client OM: Type Casting Field instances to Field Type instances

If you are using Client Object Model to create Fields, you will be working with the Field class:

private static void AddFieldsToProjectsList()
{
    String fldDescriptionXml = String.Format(FieldXml,
                                            "<guid>",
                                            "Text",
                                            "Description",
                                            "Description",
                                            "TRUE",
                                            "TRUE",
                                            "TRUE",
                                            "TRUE");
            
    String fldDueDateXml = String.Format(FieldXml,
                "<guid>",
                "DateTime",
                "DueDate",
                "Due Date",
                "TRUE",
                "TRUE",
                "TRUE",
                "TRUE");


    using (ClientContext spContext = new ClientContext(_siteUrl))
    {
        spWeb = spContext.Web;                

        List lstProjects = spWeb.Lists.GetByTitle("Projects");

        Field fldDescription = lstProjects
            .Fields
            .AddFieldAsXml(fldDescriptionXml, 
                            true, 
                            AddFieldOptions.AddToDefaultContentType);               
 
        Field fldDueDate = lstProjects
            .Fields
            .AddFieldAsXml(fldDueDateXml, 
                            true, 
                            AddFieldOptions.AddToDefaultContentType);
                
        spContext.ExecuteQuery();
    }
}

As you can see in the above code sample, I am adding a DateTime field. So, what if I have to change the field’s DisplayFormat to Date only?

If you try to cast the Field object to FieldDateTime object, you will get an error:

Unable to cast object of type ‘Microsoft.SharePoint.Client.Field’ to type ‘Microsoft.SharePoint.Client.<field-type>’

image

So, how do we cast then?

You should use SPContext.CastTo method to cast a specified client object to its derived type.

Below is the right way to cast a Field object to FieldDateTime object:

FieldDateTime fldDateField = spContext.CastTo<FieldDateTime>(fldDueDate);
fldDateField.DisplayFormat = DateTimeFieldFormatType.DateOnly;
fldDateField.UpdateAndPushChanges(true);

SharePoint 2010 EcmaScript(JavaScript) IntelliSense in VS2010

If you are using JavaScript Client OM for building SharePoint 2010 solutions, especially manipulating the Ribbon, building modal dialog boxes etc., , you might be wondering how I can get IntelliSense working for my JavaScript code.

 

It is actually very simple. At the top of your .js file, you need to add the following:

 

/// <reference name="MicrosoftAjax.js" />
/// <reference path="file://C:/Program Files/Common Files/Microsoft Shared/Web Server Extensions/14/TEMPLATE/LAYOUTS/SP.core.debug.js" />
/// <reference path="file://C:/Program Files/Common Files/Microsoft Shared/Web Server Extensions/14/TEMPLATE/LAYOUTS/SP.debug.js" />

 

Depending on your needs, the .js files may change, but you have to include MicrosoftAjax.js.

 

After saving the .js file, press Ctrl + Shift + J  to update the JavaScript IntelliSense.

 

Tip: Sometimes, you might have to press Ctrl + Shift + J for each .js file referenced.

 

After this, I now get the intellisense:

 

JavaScript IntelliSense

 

You can find more options here: http://msdn.microsoft.com/en-us/library/ff798328.aspx

SharePoint 2010:Starting Site Workflows Programmatically

So you have created a very cool site workflow in SharePoint Designer 2010, published and you want to start programmatically.

 

Below is the code snippet to start a site workflow. Credit goes to MSDN user Paul Turner :

 

static void Main(string[] args)
{
    using (SPSite spSite = new SPSite("http://mysite"))
    {
        using (SPWeb spWeb = spSite.RootWeb)
        {
            SPWorkflowAssociation workflowAssociation

                                                 = spWeb

                                                   .WorkflowAssociations

                                                   .GetAssociationByName("MySiteWorkflow",

                                                                          CultureInfo.InvariantCulture);
            if (workflowAssociation != null)
            {
                SPWorkflow siteWorkflow

                                  = spSite

                                    .WorkflowManager

                                    .StartWorkflow(spSite,

                                    workflowAssociation,

                                    "<Data><SalesOrderId>71774</SalesOrderId></Data>",

                                   SPWorkflowRunOptions.Asynchronous);
            }
        }
    }

    Console.WriteLine("Workflow Started");

    Console.Read();
}

 

The <Data><SalesOrderId>71774</SalesOrderId></Data> is the initiation form data. Replace SalesOrderId with yours.

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:

 

image

 

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:

 

image

 

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

 

image

 

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’:

 

image

 

In the Ribbon, select Set as Title:

 

image

 

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

PowerPivot for SharePoint 2010

 

If you are planning to install PowerPivot for SharePoint 2010, then there are two ways to install:

 

1) Installing to an existing SharePoint 2010 farm

 

2) Installing to a new SharePoint 2010 farm

 

I always find (myself and) more people fall in the first category where there already exists SharePoint 2010 farm.

 

Some things to note 

 

1) PowerPivot strictly requires SQL Server 2008 R2

2) PowerPivot works with x86 Office client, which  means, you don’t have to install PowerPivot x64 just because SharePoint 2010 and SQL Server 2008 R2 is x64

 

Installation Docs

 

MSDN has excellent posts on how to install PowerPivot. If you follow the MSDN documentation word by word, I can assure you that you will succeed without any pains installing and configuring PowerPivot for SharePoint. (other than the only issue which is discussed later in this blog post):

 

1) How to: Install PowerPivot for SharePoint on a New SharePoint Server

 

2) How to: Install PowerPivot for SharePoint on an Existing SharePoint Server

 

Cannot load or locate Microsoft.AnalysisServices.SharePoint.Integration.DLL assembly

After installation, you can get an error which says SQL Installation was successful but could not load or locate Microsoft.AnalysisServices.SharePoint.Integration.DLL assembly

 

The only solution is as follows:

 

1) Navigate to the folder: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

2) Copy the Microsoft.AnalysisServices.SharePoint.Integration.DLL to another folder

3) Uninstall SQL Server 2008 R2 PowerPivot Instance

4) Copy the Microsoft.AnalysisServices.SharePoint.Integration.DLL to GAC

5) Install SQL Server 2008 R2 for PowerPivot

 

I do know this is a known issue and there will be a fix/update soon.

Co-authoring in Office Word 2010

So, you have heard of this wonderful thing called co-authoring in Word/PowerPoint/Excel 2010 and very eager to try it out?

 

If you have SharePoint 2010 + Office 2010 installed, then you are all setup to try out co-authoring right away! Yes, no extra setup needed! It should also work with SharePoint Foundation 2010.

 

Before going forward, let me remind you that co-authoring in Word and PowerPoint is only supported in the rich clients which means that it is not supported in the web applications. I haven’t worked with Excel, so not sure what is the story with Excel 2010.

 

There is only one requirement needed: You need to disable check-out for the document library. This is because, if you enable check-out, then the document gets locked by the user and hence you wont be able to co-author:

 

image

 

Co-authoring Demo

So, here is my first user Dan opening a document from the SharePoint 2010 document library. Another user Kim also opens the same document from the document library. Now, after 30 seconds (that seems to be the refresh time for each change), you should be able to see the user icon appear at the bottom bar of the Word 2010:

 

image

 

image

 

Presence/OCS integration is right out of the box!

 

So, if Kim adds/changes the document, Dan is going to those changes through. You should see something like this:

 

image

 

Once updated, it will show the updated content

 

image

 

If the other user is editing the existing content, you will get notified too:

 

image

 

If users want to chat, they can do so instantly!

 

image

 

Backstage also shows the currently editing people:

 

image

 

When all the editing is done, you can save the document back to the SharePoint document library.

FBA in SharePoint 2010 requires claims mode

If you have already set up Forms Based Authentication(FBA) in SharePoint 2010 public beta, you would have noticed not much of a change from SharePoint 2007. As you are aware, SharePoint 2010 includes new authentication model called Claims Based authentication. You can read more about this here. Since the SharePoint 2010 RTM, FBA now requires claims mode set up for the web application. Your membership provider and role provider now acts as the Identity Provider.

 

So, if you go to your web application settings, and choose to edit the Authentication Providers, you will notice the following:

 

image

 

Notice that there is a help on how to enable Forms Based Authentication in claims mode. You can click on it to read more about how to set up FBA.

 

Here is a simple PowerShell script which changes the web app’s authentication mode to claims based:

 

$webApp = Get-SPWebApplication “/">http://<web-app>/

$webApp.UseClaimsAuthentication = "True";

$webApp.Update();

$webApp.ProvisionGlobally();

 

Once you have executed the above PowerShell script, if you go to your web app’s authentication provider settings, you should see:

 

image

 

Clicking on Default, you will be able to select your identity provider for the claims based authentication:

 

image

 

If you are creating a new web app, you can initially choose which authentication mode you want:

 

image

 

You can refer more to this blog post by Steve on setting up FBA in SP2010: http://bit.ly/a5WhlE (or) this MSDN article: http://bit.ly/bUYvjC

Come to the AUS and NZ SharePoint 2010 Launch Event

As you are aware, SharePoint 2010 is launching globally on May 12th. If you are in New Zealand and want to celebrate the launch of SharePoint 2010, feel free to join us!  We are organising a special User Group Event following the USA official launch of Office and SharePoint 2010! We intend to make this not only a National NZ event, joining together all our SharePoint User Groups around New Zealand, but also join the Australian User Groups in their Virtual event.

 

Come and join us for preso's, pizza and party - plus hands on labs where possible....and no doubt off to a bar later....or for those who want to - join us in a Virtual Meeting!

 

If you are interested, please complete the survey at the link below - and we will update you with information on venues, agenda and so forth! We also have some cool giveaway prizes!

 

You can input your interest here: http://www.dot.net.nz/Lists/SharePoint2010Survey/overview.aspx

 

You can now sign up to be part of this unique event by registering on the following URL

 

Wellington Location:

The event will start at 6PM in Wellington on May 13th (Thursday) to coincide with the global SP2010 launch and will be held at Gen-i "North Tower" 68 Jervois Quay, Wellington, New Zealand. If you are attending the event please meet around 5.45pm at the North Tower lobby and you will be escorted to the venue on the Second Floor.

Location Map : 68 Jervois Quay - Wellington

 

And don’t forget we also have the New Zealand SharePoint Conference & Australia SharePoint Conference on its way, so don’t forget to register for the conference too!

 

New Zealand Community SharePoint Conference 2010

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) :

 

Feature Deployment Error \

 

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

 

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:

 

image

 

Now your solution will deploy fine!


Creative Commons License
Chaks' Corner Blog by Chakkaradeep Chandran is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.
Based on a work at www.chakkaradeep.com.
Permissions beyond the scope of this license may be available at http://www.chakkaradeep.com.