Using Javascript to access an external list in SharePoint 2010.

In today's post let's take a look at leveraging SharePoint's ECMAScript Client Object Model to access an external list data. In this post we're going to cover:

• Using javascript and the client object model to build a products search application that returns products from an external list.
• Host the application in side of a content editor webpart.

Let's begin by taking a look at the Products external list.

Products External List

Figure 1 shows a list of products external list which is view from the AdventureWorks.Products sql table. Using external content types and lists provides you with the capabilities to connect and manipulate data from external systems such as a web service, sql server database or from a .NET assembly. Leveraging SharePoint Designer 2010 or Visual Studio 2010 provides the tools that's need to craete external content types and lists to be consume in SharePoint or in other applications.  For more information about BCS, external content types and lists check out Microsoft Business Connectivity Services. Now that we have a list of products to work with, next we will leverage SharePoint's client object model to extract and display products based on user's search requests.

Figure 1

Leveraging SharePoint's Client Object Model to access external list data.

Figure 2 and 3 shows will show you the end results. Users will type in the first letter or two, click on the search button and the application returns a list of products based on the user's search request.

Figure 2

Figure 3

To begin we need to add a content editor webpart to host the application.. Add the web part to the home page and then we need to edit html source for the web part. Figure 4 shows the location of the edit inside of the ribbon. Inside of the HTML editor copy and paste the javascript source code thats located as a file to download at the end of this blog post.


Figure 4

Notice in the source code the CAML query and the 'Include' statement inside of the clientContext.load method. Both are required to access data from an external list. See Figure 5

Figure 5

Inside of the CAML query the Method tag provides the operation name used to query the Products list.. The Name attribute is where you specify the name of the operation. Note that the value of the Name attribute should be a valid operation that exists in the external content type. See Figure 6 shows you list of operations that the external content type supports.

Figure 6

The ViewFields tag  requires you to include all fields that anoperation returns. Remember confuring your external content type and in the wizard the their step Return Parameters ? In this step you can specify the fields that you want to return for an operation.  Figure 7 shows the button to edit a selected operation and Figure 8 shows the Return Parameter step where you can configure the operation's return fields. Finally in the clientContext.load method the 'Include' statement makes the fields available to use in the onQuerySucceeded function.

Figure 7

Figure 8

Let's recap

• Using BCS provides you the capabilities to expose external data using external content types and lists.
• Leveraging the Client Object Model you can access external list data and expose it to the client.
• The Method tag  specifies the CURD operations to use whne querying an external list. This gives you the ability to implement select, update and delete CAML querites against external lists.
• Make sure you include all fields that anoperation returns inside of the ViewFields tags. Do the same for the 'Include' statement inside of the clientContext.load method as well.

For a copy of the AdventureWorks database check out this link: Microsoft SQL Server Database Product Samples.


Sample Source Code: