Building a SPQuery ViewFields string

Aug 1

Posted by

If you’re querying SharePoint content using a CAML query from code it’s a good habit to always populate the SPQuery instance’s ViewFields property. Otherwise the returned SPListItem instances might not contain any data for certain fields and throw an exception when you try to access those fields.

Specifying ViewFields involves creating a string of CAML FieldRef elements. For instance if we want our query to return items that contain data for the Title, Created and ID field we use this: 

   1: <FieldRef Name='Title'/><FieldRef Name='Created'/><FieldRef Name='ID'/>

As you can see there’s some overhead of boilerplate markup involved. I’ve written a small piece of code that I always use to make my life a little easier. Today I happened to post this code in a reply I wrote on the MSDN forums and also decided to submit it as Community Content to the official SPQuery docs on MSDN. Then I thought I might as well share it with you here. So here it is: 

   1: public static string BuildViewFieldsXml(params string[] fieldNames)
   2: {
   3:     const string TEMPLATE = @"<FieldRef Name='{0:S}'/>";
   4:     StringBuilder sb = new StringBuilder();
   5:     foreach (string fieldName in fieldNames)
   6:     {
   7:         sb.AppendFormat(TEMPLATE, fieldName);
   8:     }
   9:     return sb.ToString();
  10: }
  11:  
  12: // Use it like this:
  13: SPQuery query = new SPQuery();
  14: query.ViewFields = BuildViewFieldsXml("Title", "Created", "ID");
  15:  
  16: // Note that you can specify a variable amount of string parameters, i.e.
  17: query.ViewFields = BuildViewFieldsXml("Title", "Created", "ID", "Author", "Gender");

Yeah, you’re right. This piece of code isn’t exactly rocket science. But you might appreciate it anyway 🙂

Posted on August 1, 2008, in .net, developing, Handy, sharepoint and tagged , , . Bookmark the permalink. 8 Comments.

  1. Dotan | August 4, 2008 at 3:15 pm

    Thanks Leon! Just what I was looking for…

    Reply
  2. Valentin Rossi | August 5, 2008 at 11:25 pm

    Thanks. Code snippets like this one are very helpful.

    Reply
  3. Satish Reddy | October 9, 2008 at 8:57 pm

    Hi,

    This is my code

    public DataTable GetSiteDeatils()
    {

    String strSiteURL = “http:///Configuration/Pages/default.aspx”;

    SPSite oSPSite = new SPSite(strSiteURL);
    SPWeb oSPWeb;

    oSPWeb = oSPSite.OpenWeb();

    SPQuery oSPQuery = new SPQuery();

    string[] fields ={“Title”,”ListName” };
    oSPQuery.ViewFields = BuildViewFieldsXml(fields);

    SPListItemCollection oSPListItemCollection = oSPWeb.Lists[“ListDetails”].GetItems(oSPQuery);
    DataTable dt = oSPListItemCollection.GetDataTable();

    return dt;

    }

    public static string BuildViewFieldsXml(params string[] fieldNames)
    {

    const string TEMPLATE = @””;
    StringBuilder sb = new StringBuilder();

    foreach (string fieldName in fieldNames)
    {
    sb.AppendFormat(TEMPLATE, fieldName);
    }
    return sb.ToString();
    }

    Its returning Title and Listname in datatable.But, its additionaly including ID and Created Columns .

    How to elininate ID and Created Columns..

    Regards
    Satish

    Reply
  4. Leon Zandman | October 9, 2008 at 9:48 pm

    @Satish: A query using SPQuery always returns SPListItem instances with the “ID” and “Created” fields filled. This is by design. The ViewFields property only allows you to return additional fields. So I’m afraid you cannot prevent those fields from ending up in your DataTable.

    Also I notice your sample code doesn’t dispose the SPWeb and SPSite instances properly. And did you notice you can call my BuildViewFieldsXml() function like this:

    oSPQuery.ViewFields = BuildViewFieldsXml(“Title”, “ListName”);

    You don’t have to create that separate array, like you do.

    Reply
  5. Satish Reddy | October 10, 2008 at 8:40 pm

    Thank you much,I am disposing SPWeb and SPSite objects in finally block,thats is my sample code .Even I digged in other blogs also,at the end I came to know ID and Created columns can’t be eliminated in SPQuery.

    Once again Thank so much for ur response..

    Reply
  6. Raghu Iyer | April 6, 2009 at 6:42 pm

    Hi Leon,

    Thanks for your inputs on IAlertNotifyHandler, i have been trying to resolve this issue from long time now.

    Will give this a shot and let you know how it goes.

    Thanks
    Raghu Iyer

    Reply
  7. Beekermd03 | August 5, 2009 at 8:01 pm

    Satish:
    To remove the ID and Created columns from the GetItems Query, remove them from the DataTable object. In yourcase, dt.Columns.Remove(“ID”) and dt.Columns.Remove(“Created”).

    Hope this helps someone!

    Reply
  8. Bespoke Website Development | March 4, 2010 at 6:50 pm

    I’ve been doing SPContext.Current.Web.Lists[“meeting”] style queries, this helps me now!

    Reply

Leave a comment