© 2011 By: Dov Trietsch. All rights reserved

More CAML and existence.

In “SharePoint List Issues” and “Passing the CAML thru the EY of the NEEDL we saw how to use CAML to return a subset of a list and also how to check the existence of lists, fields, defaults, and values.  

Here is a general function that may be used to get a subset of a list by comparing a “text” type field to a given value. 

The function is pretty smart. It can be used to check existence or to return a collection of items that may be further processed. It handles non existing fields and replaces them with the ubiquitous “Title”, but only once! 

/// Build an SPQuery that returns a selected set of columns from a List
/// titleField must be a "Text" type field
/// When the titleField parameter is empty ("") "Title" is assumed
/// When the title parameter is empty ("") All is assumed
/// When the columnNames parameter is null, the query returns all the fields
/// When the rowLimit parameter is 0, the query return all the items.
/// with a non-zero, the query returns at most rowLimits
///
/// usage: to check if an item titled "Blah" exists in your list, do:
/// colNames = {"Title"}
/// col = GetListItemColumnByTitle(myList, "", "Blah", colNames, 1)
/// Check the col.Count. if > 0 the item exists and is in the collection
private static SPListItemCollection GetListItemColumnByTitle(SPList list, string titleField, string title, string[] columnNames, uint rowLimit)
{
  try
  {
    char QT = Convert.ToChar((int)34);
    SPQuery query = new SPQuery();
    if (title != "")
    {
      string tf = titleField;
      if (titleField == "") tf = "Title";
      tf = CAMLThisName(list, tf, "Title"); 
      StringBuilder titleQuery = new StringBuilder  ("<Where><Eq><FieldRef Name=");
      titleQuery.Append(QT);
      titleQuery.Append(tf);
      titleQuery.Append(QT);
      titleQuery.Append("/><Value Type=");
      titleQuery.Append(QT);
      titleQuery.Append("Text");
      titleQuery.Append(QT);
      titleQuery.Append(">");
      titleQuery.Append(title);
      titleQuery.Append("</Value></Eq></Where>");
      query.Query = titleQuery.ToString();
    }
    if (columnNames.Length != 0)
    {
      StringBuilder sb = new StringBuilder("");
      bool TitleAlreadyIncluded = false;
      foreach (string columnName in columnNames)
      {
        string tst = CAMLThisName(list, columnName, "Title");
        //Allow Title only once
        if (tst != "Title" || !TitleAlreadyIncluded)
        {
          sb.Append("<FieldRef Name=");
          sb.Append(QT);
          sb.Append(tst);
          sb.Append(QT);
          sb.Append("/>");
          if (tst == "Title") TitleAlreadyIncluded = true;
        }
      }
      query.ViewFields = sb.ToString();
    }
    if (rowLimit > 0)
    {
       query.RowLimit = rowLimit;
    }
    SPListItemCollection col = list.GetItems(query);
    return col;
  }
  catch (Exception ex)
  {
    //Console.WriteLine("GetListItemColumnByTitle" + ex.ToString());
    //sw.WriteLine("GetListItemColumnByTitle" + ex.ToString());
    return null;
  }
}

Here I called it for a list in which “Author” (it is the internal name for “Created”) and “Blah” do not exist. The list of column names is: 

string[] columnNames = {"Test Column1", "Title", "Author", "Allow Multiple Ratings", "Blah"}; 

So if I use this call, I get all the items for which “01-STD MIL_some” has the value of 1. the fields returned are:

“Test Column1”, “Title”, and “Allow Multiple Ratings”. Because “Title” was already included and the default for non exixsting is “Title”, it was not replicated for the 2 non-existing fields. 

SPListItemCollection col = GetListItemColumnByTitle(masterList, "01-STD MIL_some", "1", columnNames, 0);

The following call checks if there are any items where “01-STD MIL_some” has the value of “1”. Note that I limited the number of returned items to 1. 

SPListItemCollection col = GetListItemColumnByTitle(masterList, "01-STD MIL_some", "1", columnNames, 1);

The code also uses the CAMLThisName function that checks for an existence of a field and returns its InternalName. This is yet another useful function that I use again and again. 

/// <summary>
/// return a fields internal name (CAMLName) 
/// or the "default" name that you passed.
/// To check existence pass "" or some funny name like "mud in your eye"
/// </summary>

public static string CAMLThisName(SPList list, string name, string def)
{
  String CAMLName = def;
  SPField fld = GetFieldByName(list, name);
  if (fld != null)
  {
     CAMLName = fld.InternalName;
  }
  return CAMLName;
}

That’s all folks?!