Second Life of a Hungarian SharePoint Geek

February 9, 2011

How to query external lists on the server side using CAML and alter the external data?

Filed under: BCS, CAML, External list, SP 2010 — Tags: , , , — Peter Holpar @ 08:38

In my former post I’ve wrote about how to query SharePoint 2010 BCS external lists using client side code. Now I try to show how to do it on the server side.

For this example I used the external links sample demonstrated in a this post. Although the download for that post contains only a single sample link having SharePoint in its title, you need to create more such links to see the real effect of the code below. I used that code in a server side console application, but after minor modifications it can be used in web context as well.

  1. String siteUrl = "http://sp2010";
  2.  
  3. using (SPSite site = new SPSite(siteUrl))
  4. {
  5.     using (SPWeb web = site.OpenWeb())
  6.     {
  7.  
  8.         SPList list = web.Lists["External Links"];
  9.  
  10.         // query items
  11.         SPQuery query = new SPQuery();
  12.         query.Query =
  13.             @"<Where>
  14.                 <Contains>
  15.                     <FieldRef Name='Title' />
  16.                     <Value Type='Text'>SharePoint</Value>
  17.                 </Contains>
  18.               </Where>
  19.               <OrderBy>
  20.                    <FieldRef Name='Title'/>
  21.               </OrderBy>";
  22.         query.ViewFields =
  23.             @"<ViewFields>
  24.                    <FieldRef Name='Title'/>
  25.               </ViewFields>";
  26.         // RowLimit seems to have no effect
  27.         query.RowLimit = 1;
  28.  
  29.         SPListItemCollection items = list.GetItems(query);
  30.  
  31.         foreach (SPListItem item in items)
  32.         {
  33.             Console.WriteLine("Title: '{0}'", item["Title"]);
  34.         }
  35.  
  36.     }
  37. }

As you can see, the code is just like for a standard SharePoint list. You have to include the fields you use in the code in the ViewFields node of the CAML query as FieldRef. Similar to the client side example, specifying the RowLimit seems to have no effect, that is very sad, but true.

If your model allows that (it is not read-only and has the necessary methods), you can use code to alter (create, update, delete) your external data through the external list as well. In our case, the following code snippet will create a new link in the file system:

  1. newItem["Link"] = new SPFieldUrlValue { Url = "http://www.company.com&quot;, Description = "Link item from code" };
  2. newItem.Update();
  3. list.Update();

In this case we had to fill only the Link field with the link title and URL, as external list item Title field will be determined in our .NET assembly BCS connector class based on the link title (see the original post for details). It means that the fields you have to fill depend on the nature of the connector and the properties of the external system.

Advertisements

2 Comments »

  1. Hii,
    thanx for the post. But its not working in my system. I get following error:
    Error parsing CAML query – child not found.

    My query:
    SPQuery mTempQuery = new SPQuery();
    mTempQuery.Query = “”;
    mTempQuery.ViewFields = @”

    “;

    When I use above query on simple list it works.
    I also wanted to apply join between Sharepoint custom list and external list. can I do that? n How?

    Comment by Swapnill — September 25, 2012 @ 07:40

  2. I am getting the same error when I am trying to get the items from the external list. Please help me

    Comment by vijaymca — November 13, 2013 @ 18:23


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: