SharePoint InputFormTextBox Validation

RequiredFieldValidator can not work with SharePoint InputFormTextBox. You have to client side JavaScript validation instead. There's an out-of-box function RTE_GetRichEditTextOnly come handy to retrieve the value from InputFormTextBox when doing JavaScript validation:

function ValidateInputFormTextbox() {
    var inputText = RTE_GetRichEditTextOnly("<%= inputControl.ClientID %>");
    if (inputText)
        return true;
    else
        return false;
}

Populate and Update SharePoint Choice Field

SPFieldChoice and SPFieldMultiChoice are two type of SharePoint Choice field. The first one is single selection and displayed as dropdown menu or the radio buttons; the latter is multiple selections and displayed as a list of check boxes. To populate the choice items in a custom webpart, we can simply go through the Choices collection in the choice field:

    // Populate Dropdown by SharePoint Choices (single selection from dropdown menu or radio buttons)
    private void PopulateSPChoiceDropDown(DropDownList dropdown, SPList list, string fieldName)
    {
        if (dropdown == null || list == null || list.ItemCount == 0)
            return;
            
        dropdown.Items.Clear();
        if (list != null && list.Fields.ContainsField(fieldName))
        {
            SPFieldChoice spChoices = list.Fields[fieldName] as SPFieldChoice;
            if (spChoices != null && spChoices.Choices != null)
            {
                for (int i = 0; i < spChoices.Choices.Count; i++)
                {
                    dropdown.Items.Add(new ListItem(spChoices.Choices[i], spChoices.Choices[i]));
                }
            }
        }
    }

    // Populate CheckBoxList by SharePoint Choices (muliple selction)
    private void PopulateSPChoiceCheckBoxList(CheckBoxList checkboxList, SPList list, string fieldName)
    {
        if (checkboxList == null || list == null || list.ItemCount == 0)
            return;
            
        checkboxList.Items.Clear();
        if (list != null && list.Fields.ContainsField(fieldName))
        {
            SPFieldMultiChoice spChoices = list.Fields[fieldName] as SPFieldMultiChoice;
            if (spChoices != null && spChoices.Choices != null)
            {
                for (int i = 0; i < spChoices.Choices.Count; i++)
                {
                    checkboxList.Items.Add(new ListItem(spChoices.Choices[i], spChoices.Choices[i]));
                }
            }
        }
    }

As a matter of fact, we can merge above two methods into one:

    // Populate List Control such as Dropdown and CheckBoxList by SharePoint Choices
    private void PopulateSPChoiceToListControl(ListControl listControl, SPList list, string fieldName)
    {
        if (listControl == null || list == null || list.ItemCount == 0)
            return;
      
        listControl.Items.Clear();
        if (list != null && list.Fields.ContainsField(fieldName))
        {
            SPFieldMultiChoice spChoices = list.Fields[fieldName] as SPFieldMultiChoice;
            if (spChoices != null && spChoices.Choices != null)
            {
                for (int i = 0; i < spChoices.Choices.Count; i++)
                {
                    listControl.Items.Add(new ListItem(spChoices.Choices[i], spChoices.Choices[i]));
                }
            }
        }
    }

The reason we can merge them together is because SPFieldChoice is inherited from SPFieldMultiChoice, and both DropDownList and CheckBoxList are implementation of abstract ListControl class. To update the SPField:

        SPFieldMultiChoiceValue values = new SPFieldMultiChoiceValue();
        foreach (ListItem listItem in checkList.Items)
        {
            if (listItem.Selected)
            {
                values.Add(listItem.Value);
            }
        }
        item["MultipleChoiceField"] = values;
        item["SingleChoiceField"] = dropdown.SelectedValue;
        item.Update();

SharePoint Batch Delete

It would be inefficient to delete many List items one by one when the List is huge. SharePoint provides a batch update mechanism using CAML. Following code snippet illustrates such usage where all records in a SharePoint List that have not been updated for more than 1 year will be deleted:

    void DeleteOldListItems(SPSite site, string webUrl, string listName)
    {
        StringBuilder sbDelete = new StringBuilder();
        sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
        // CAML query for batch deleting
        string batchDeleteCaml = 
"<Method><SetList Scope=\"Request\">{0}</SetList><SetVar Name=\"ID\">{1}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>";
        // Query items not touched for more than a year; same as <Value Type="DateTime"><Today OffsetDays="-365" /></Value>
        string filterQueryCaml = @"<Where><Geq><FieldRef Name='Modified'/><Value Type='DateTime' IncludeTimeValue='TRUE'>" + 
            SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddYears(-1)) + "</Value></Geq></Where>";
        using (SPWeb web = site.OpenWeb(webUrl))
        {
            SPList list = web.Lists[listName];
            SPQuery filterQuery = new SPQuery();
            filterQuery.Query = filterQueryCaml;
            SPListItemCollection filterItems = list.GetItems(filterQuery);
            foreach (SPListItem item in filterItems)
            {
                sbDelete.Append(string.Format(batchDeleteCaml, list.ID.ToString(), item.ID.ToString()));
            }
            sbDelete.Append("</Batch>");
            web.ProcessBatchData(sbDelete.ToString());
        }
    }

SharePoint SPList.ItemCount vs SPList.Items.Count

You can get item counts of a SharePoint list by SPList.ItemCount or SPList.Items.Count property. But these two numbers can be different. The inconsistency is due to the SharePoint content security. SPList.ItemCount shows the exact item number, but SPList.Items only returns the list items that current user has access to them.

For example, if a list has 10 items, and only 5 of 10 are accessible to you, then SPList.ItemCount = 10 and SPList.Items.Count = 5 in your security context.

In addition, SPList.ItemCount is more efficient than SPList.Items.Count, because SPList.ItemCount is just a property of the list, but SPList.Items.Count will result in loading all list items (with permissions) into memory.

Get SPUsers From SharePoint PeoplePicker Control

Code below shows how to retrieve SPUsers from the SharePoint PeoplePicker control when PeoplePicker control is set to only allow users (SelectionSet="User"):

<SharePoint:PeopleEditor id="peoplePicker" runat="server" AllowEmpty="true" ValidatorEnabled="true" MultiSelect="true" SelectionSet="Group"/>

public static List<SPUser> GetPeopleEditorUsers(PeopleEditor picker)
{
    List<SPUser> users = new List<SPUser>();
    try
    {
        foreach (PickerEntity entity in picker.ResolvedEntities)
        {
            if ((string)entity.EntityData["PrincipalType"] == "User")
            {
                int userID = Int32.Parse(entity.EntityData["SPUserID"].ToString());
                SPUser user = SPContext.Current.Web.SiteUsers.GetByID(userID);
                if (user != null)
                {
                    users.Add(user);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Log error
    }
    return users;
}

The code needs to be revised a bit when groups are included in the PeoplePicker (SelectionSet="SPGroup,DL,SecGroup"):

public static List<SPUser> GetPeopleEditorGroupUsers(PeopleEditor picker)
{
    List<SPUser> users = new List<SPUser>(); 
    try
    {
        foreach (PickerEntity entity in picker.ResolvedEntities)
        {
            if ((string)entity.EntityData["PrincipalType"] == "SharePointGroup")
            {
                int groupID = int.Parse((string)entity.EntityData["SPGroupID"]);
                SPGroup group = SPContext.Current.Web.SiteGroups.GetByID(groupID);
                foreach (SPUser user in group.Users)
                {
                    users.Add(user);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Log error
    }
    return users;
}

The difference between SPWeb.Groups and SPWeb.SiteGroups:

• SPWeb.Groups: Groups explicitly assigned persmissions in a site collection.
• SPWeb.SiteGroups: All valid groups available in the site collection (superset of SPWeb.Groups). You can add a new group to SPWeb.SiteGroups, but not the SPWeb.Groups.

The difference between SPWeb.AllUsers, SPWeb.SiteUsers and SPWeb.Users:

• SPWeb.Users: Users explicitly assigned permissions in a site collection.
• SPWeb.SiteUsers: All valid users available in a site collection (superset of SPWeb.Users).
• SPWeb.AllUsers: All users who are either members of the site or who have browsed to the site as authenticated members of a domain group in the site (superset of SPWeb.SiteUsers).

32 Character Limit Of SharePoint List's Internal Field Name

I came cross a weird problem and found this issue. A DateTime field with name of "KSNF WebPart Update Time" is used in a custom Feature, and I can get or set its value without any problem using SharePoint API:

DateTime updatetime = Convert.ToDateTime(item["KSNF WebPart Update Time"]);

But I got nothing when using CAML query. Yes, I use interal name of "KSNF_x0020_WebPart_x0020_Update_x0020_Time" in my CAML query, and it returns empty. I use SharePoint Manager to check this field and see that its internal static name is "KSNF_x0020_WebPart_x0020_Update_". It's trimmed to 32-character!

So SharePoint only allows 32-character in field's internal (static) name? I created a simply console application to test that:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;

public partial class Program
{
 static void Main(string[] args)
 {
     string siteName = "http://localhost";
     string listName = "Shared Documents";
     string fieldName = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345";

     Console.WriteLine("Adding SPField to a SharePoint Document Library and a List with name of '{0}'", fieldName);
     Console.WriteLine();
     Console.WriteLine("Get SPField info from updated SharePoint Document Library:");
     TestFieldName(siteName, listName, fieldName);
     Console.WriteLine();

     listName = "Announcements";
     Console.WriteLine("Get SPField info from updated SharePoint List:");
     TestFieldName(siteName, listName, fieldName);

     Console.Read();
 }

 private static void TestFieldName(string siteName, string listName, string fieldName)
 {
     using (SPSite site = new SPSite(siteName))
     {
         using (SPWeb web = site.OpenWeb())
         {
             SPList list = web.Lists[listName];
             if (!list.Fields.ContainsField(fieldName))
             {
                 list.Fields.Add(fieldName, SPFieldType.Text, false);
                 list.Update();
             }
             if (list.Fields.ContainsField(fieldName))
             {
                 SPField field = list.Fields[fieldName];
                 Console.WriteLine("Display name:\t'{0}' {1}Static name:\t'{2}'",
                     field.Title, System.Environment.NewLine, field.StaticName);

                 list.Fields.Delete(fieldName);
                 list.Update();
             }
         }
     }
 }
}

The result is:

Adding SPField to a SharePoint Document Library and a List with name of
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345'


Get SPField info from updated SharePoint Document Library:
Display name: 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345'
Static name: 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345'


Get SPField info from updated SharePoint List:
Display name: 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345'
Static name: 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdef'

Apparently the 32-character limit for internal field name only applies to SharePoint lists. There's no such limit for SharePoint document libraries.

Uploading File To SharePoint Document Library

Uploading a file to SharePoint 2007 (WSS 3.0 or MOSS 2007) Document Library is very simple. Also Content Types and template files can be associated with a Document library. so we can create a new item using template file. Following code demos how to upload a file to SharePoint Document library from local file system and from the template file stored in SharePoint:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Microsoft.SharePoint;

class Program
{
    static void Main(string[] args)
    {
        UploadFileToDocumentLibrary("http://localhost", "Shared Documents", @"c:\Test.doc");
        InsertByTemplateFile("http://localhost", "Shared Documents", "Document");

        Console.WriteLine("Done!");
        Console.Read();
    }

    static void UploadFileToDocumentLibrary(string siteName, string listName, string fileName)
    {
        FileStream stream = File.OpenRead(fileName);
        byte[] content = new byte[stream.Length];

        stream.Read(content, 0, (int)stream.Length);
        stream.Close();

        using (SPSite site = new SPSite(siteName))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPDocumentLibrary lib = web.Lists[listName] as SPDocumentLibrary;

                //string docUrl = lib.RootFolder.Url + "/testupload.docx";
                //SPFile newDoc = lib.RootFolder.Files.Add(docUrl, content, true);
                SPFile newDoc = lib.RootFolder.Files.Add("testUpload.doc", content, true);

                SPListItem item = newDoc.Item;
                item["Title"] = "Test uploading file";
                item.Update();

            }
        }
    }

    static void InsertByTemplateFile(string siteName, string listName, string contentTypeName)
    {
        using (SPSite site = new SPSite(siteName))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPDocumentLibrary lib = web.Lists[listName] as SPDocumentLibrary;
                SPContentType cType = lib.ContentTypes[contentTypeName];

                SPFile cTypeTemplateFile = web.GetFile(cType.DocumentTemplateUrl);
                byte[] content = cTypeTemplateFile.OpenBinary();

                SPFile newDoc = lib.RootFolder.Files.Add("testTemplate.doc", content, true);

                SPListItem item = newDoc.Item;
                item["Title"] = "Test inserting by template file";
                item["ContentTypeId"] = cType.Id;
                item.Update();
            }
        }
    }
}

Preserve System Fields When Copying SharePoint List Item

In previous post I explored the SharePoint List Item "Modified" field, which is considered as a system field. It will be exact the system time when the List item is created or updated. Sometimes we want to keep the original values of those fields when copying over List items, then we need to specifically assign those values. Following code snippet demos how to o preserve the original Created By, Created, Modified By and Modified fields:

        SPList origList = web.Lists.TryGetList("MyList");
        SPList newList = web.Lists.TryGetList("BackupList");
        foreach (SPListItem origItem in origList.Items)
        {
            SPListItem newItem = newList.Items.Add();
            newItem["Author"]   = origItem["Author"];   // Created By
            newItem["Created"]  = origItem["Created"];  // Created
            newItem["Editor"]   = origItem["Editor"];   // Modified By
            newItem["Modified"] = origItem["Modified"]; // Modified

            // Populate other fields

            newItem.Update();
        }

More On SharePoint Modified Field

I discussed the SharePoint "Modified" and "Last Modified" fields in my previous post. During the test I have an interesting finding on how update a list item will affect the "Modified" field. That's where this post is coming from.

In SharePoint there're two methods you can call to update a list item (SPListItem): SystemUpdate and Update. Following are their MSDN description(http://msdn.microsoft.com/en-us/library/ms438065.aspx):

• SystemUpdate: Updates the database with changes that are made to the list item without changing the Modified or Modified By fields.
• Update: Updates the database with changes that are made to the list item. (Overrides SPItem.Update().)

Obviously SystemUpdate method won't update the "Modified" field. What about the "Last Modified" field? Let's do a quick test:

public partial class Program
{
static void Main(string[] args)
{
   string siteName = "http://localhost";
   string listName = "Documents";
   string itemInfo = string.Empty;

   itemInfo = TestItemLastModifiedTime(siteName, listName, ExecutCommand.None);
   Console.WriteLine("--Orignal--" + System.Environment.NewLine + itemInfo);
   Console.WriteLine();

   itemInfo = TestItemLastModifiedTime(siteName, listName, ExecutCommand.SystemUpdate);
   Console.WriteLine("--After SystemUpdate--" + System.Environment.NewLine + itemInfo);
   Console.WriteLine();
   System.Threading.Thread.Sleep(2000);

   itemInfo = TestItemLastModifiedTime(siteName, listName, ExecutCommand.Update);
   Console.WriteLine("--After Update--" + System.Environment.NewLine + itemInfo);
   Console.WriteLine();

   Console.Read();
}

enum ExecutCommand
{
   None,
   SystemUpdate,
   Update
}

private static string TestItemLastModifiedTime(string siteName, string listName, ExecutCommand command)
{
   using (SPSite site = new SPSite(siteName))
   {
       using (SPWeb web = site.OpenWeb())
       {
           SPList list = web.Lists[listName];
           SPListItem item = list.Items[0];
           switch (command)
           {
               case ExecutCommand.None:
                   return GetModifiedInfo(item);
               case ExecutCommand.SystemUpdate:
                   item["Title"] = item.Title.Trim();
                   item.SystemUpdate(true);
                   break;
               case ExecutCommand.Update:
                   item["Title"] = item.Title.Trim();
                   if (item.File.CheckOutStatus == SPFile.SPCheckOutStatus.None)
                       item.File.CheckOut();
                   item.Update();
                   break;
               default:
                   return string.Empty;
           }
           return GetModifiedInfo(item);
       }
   }
}

private static string GetModifiedInfo(SPListItem item)
{
   DateTime modified = Convert.ToDateTime(item["Modified"]);
   DateTime lastModified = Convert.ToDateTime(item["Last_x0020_Modified"]);
   string newline = System.Environment.NewLine;
   string msg = string.Format("SPListItem Modified: \t\t{0}{1}SPListItem LastModified: \t{2}",
           modified.ToString("yyyy-MM-dd HH:mm:ss"), newline, lastModified.ToString("yyyy-MM-dd HH:mm:ss"));
   return msg;
}
}

The result:

--Orignal--
SPListItem Modified:            2009-07-02 11:33:20
SPListItem LastModified:        2009-07-02 11:33:20

--After SystemUpdate--
SPListItem Modified:            2009-07-02 11:33:20
SPListItem LastModified:        2009-07-02 11:33:20

--After Update--
SPListItem Modified:            2009-07-03 21:15:16
SPListItem LastModified:        2009-07-02 11:33:20

It clearly shows that both SystemUpdate and Update Methods don't have any impact on "Last Modified" field.

All SharePoint data, including all version data, are stored in AllUserData table in the SharePoint backend content database. It looks like that both "Modified" and "Last Modified" time are from "tp_Modified" column in AllUserData table: "Modified" from the latest version while "Last Modified" from the last major version. You can verify this by checking the content database:

SELECT tp_Modified, tp_UIVersionString FROM AllUserData
WHERE tp_DirName = 'Documents' AND tp_LeafName = 'MyDocument1'
ORDER BY tp_Modified DESC

--return:
tp_Modified   tp_UIVersionString
2009-07-02 11:33:20.000  5.1
2009-07-03 21:15:16.000  5.0
2009-06-15 17:33:59.000  4.1

SharePoint Modified Field And Last Modified Field

We know there's a "Modified" field in SharePoint that records the last modified time of a SPListItem. This field is viewable and you can add it to any SharePoint views. You will see the time of "Modified" field is change when you do an update on the list item.

There's another hidden "Modified" field, also known as "Last Modified" field with internal name of "Last_x0020_Modified". What's difference? Following are their official description from MSDN:

• Modified: Identifies a field that contains the last modified date and time information that is associated with the specified SharePoint Foundation object.
• Last_x0020_Modified: Identifies a field that contains version control information for the last modified version of the specified SharePoint Foundation list object.

The description looks not that clear. Which one should we use in our work? Let's examine them in details. First look at their field definition in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\fields\fieldswss.xml:

   <Field ID="{28cf69c5-fa48-462a-b5cd-27b6f9d2bd5f}"
     Name="Modified"
     SourceID="http://schemas.microsoft.com/sharepoint/v3"
     StaticName="Modified"
     Group="_Hidden"
     ColName="tp_Modified"
     RowOrdinal="0"
     ReadOnly="TRUE"
     Type="DateTime"
     DisplayName="$Resources:core,Modified;"
     StorageTZ="TRUE">
 </Field>
 <Field ID="{173f76c8-aebd-446a-9bc9-769a2bd2c18f}"
     Name="Last_x0020_Modified"
     SourceID="http://schemas.microsoft.com/sharepoint/v3"
     StaticName="Last_x0020_Modified"
     Group="_Hidden"
     ReadOnly="TRUE"
     Hidden="TRUE"
     DisplayName="$Resources:core,Modified;"
     Type="Lookup"
     List="Docs"
     FieldRef="ID"
     ShowField="TimeLastModified"
     Format="TRUE"
     JoinColName="DoclibRowId"
     JoinRowOrdinal="0"
     JoinType="INNER">
 </Field>

Using SharePoint Manager we can see the raw "Modified" and "Last Modified" value (XML) stored in a list item:

    <?xml version="1.0" encoding="utf-16"?>
 <z:row xmlns:z="#RowsetSchema"
     ows_Modified="2009-06-15 11:23:13"
     ows_Last_x0020_Modified="23;#2009-06-15 11:23:13"
     ... />

As we can see "Modified" field is of DateTime type and real DateTime is stored in SharePoint content database; on the other hand, "Last_x0020_Modified" field is a lookup column pointing to "Docs" list's "TimeLastModified" column.

With further investigation, I found that this "Docs" list and the "TimeLastModified" field do not really exist. They are virtual and the values are determinted at run time. I tried to figure out what SharePoint exactully is doing to obtain this lookup value. So I did a quick test on these two fields:

    public partial class Program
 {
     static void Main(string[] args)
     {
         string siteName = "http://localhost";
         string listName = "Documents";
         using (SPSite site = new SPSite(siteName))
         {
             using (SPWeb web = site.OpenWeb())
             {
                 SPList list = web.Lists[listName];
                 DateTime modifiedTime, lastModifiedTime;

                 SPQuery query = new SPQuery();
                 query.ViewAttributes = "Scope='Recursive'";
                 query.ViewFields = "<FieldRef Name='Modified' Nullable='TRUE'/>"
                     +"<FieldRef Name='Last_x0020_Modified' Nullable='TRUE'/>";

                 SPListItemCollection items = list.GetItems(query);
                 modifiedTime = Convert.ToDateTime(items[0]["Modified"]);
                 lastModifiedTime = Convert.ToDateTime(items[0]["Last_x0020_Modified"]);
                 Console.WriteLine("Modified: " + lastModifiedTime.ToString()
                     + " Last Modified: " + lastModifiedTime.ToString());
             }
         }
         Console.Read();
     }
 }

The code is simple. I use CAML query to get the "Modified" and "Last Modified" data without any filter. I saw two quries sent to SharePoint content database for such CAML query. The first one is:

 exec proc_GetListFields '2E1D8267-FBA0-4995-8CBB-08E747FB54D7','750DB8DD-A6AA-49B5-9D23-EB6E4B95EAD7'

Notice two GUIDs passing in this query do not match the ID of "Modified" or "Last Modified". The query returns:

    <FieldRef Name="ContentTypeId" />
 <FieldRef Name="Title" ColName="nvarchar1" />
 <FieldRef Name="_ModerationComments" ColName="ntext1" />
 <FieldRef Name="File_x0020_Type" ColName="nvarchar2" />
 <FieldRef Name="Name" ColName="nvarchar3" />
 <FieldRef Name="EMail" ColName="nvarchar4" />
 <FieldRef Name="Notes" ColName="ntext2" />
 <FieldRef Name="SipAddress" ColName="nvarchar5" />
 <FieldRef Name="Locale" ColName="int1" />
 <FieldRef Name="CalendarType" ColName="int2" />
 <FieldRef Name="AdjustHijriDays" ColName="int3" />
 <FieldRef Name="TimeZone" ColName="int4" />
 <FieldRef Name="Time24" ColName="bit1" />
 <FieldRef Name="AltCalendarType" ColName="int5" />
 <FieldRef Name="CalendarViewOptions" ColName="int6" />
 <FieldRef Name="WorkDays" ColName="int7" />
 <FieldRef Name="WorkDayStartHour" ColName="int8" />
 <FieldRef Name="WorkDayEndHour" ColName="int9" />
 <FieldRef Name="IsSiteAdmin" ColName="bit2" />
 <FieldRef Name="Deleted" ColName="bit3" />
 <FieldRef Name="Picture" ColName="nvarchar6" ColName2="nvarchar7" />
 <FieldRef Name="Department" ColName="nvarchar8" />
 <FieldRef Name="JobTitle" ColName="nvarchar9" />
 <FieldRef Name="IsActive" ColName="bit4" />

The second query sent to database is massive:

exec sp_executesql N' SELECT TOP 2147483648 t2.[tp_Created] AS c3c8,t1.[Type] AS c0,t1.[TimeLastModified] AS c9,t3.[tp_ID] AS c10c5,CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N''/'' + t1.LeafName END AS c11,t1.[ScopeId] AS c16,UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_Version],t1.[Id] AS c15,t2.[nvarchar5] AS c3c7,t3.[nvarchar1] AS c10c4,UserData.[tp_HasCopyDestinations],UserData.[tp_ModerationStatus],UserData.[tp_Level],t2.[nvarchar1] AS c3c4,t2.[nvarchar4] AS c3c6,t3.[nvarchar4] AS c10c6,t3.[tp_Created] AS c10c8,t1.[MetaInfo] AS c14,t1.[LeafName] AS c2,UserData.[tp_Modified],UserData.[nvarchar3],t2.[tp_ID] AS c3c5,t3.[nvarchar5] AS c10c7,UserData.[tp_ID],t1.[ProgId] AS
c13,UserData.[tp_CopySource],t1.[TimeCreated] AS c1,UserData.[tp_Editor],t1.[IsCheckoutToLocal] AS c12 FROM UserData INNER MERGE JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1 AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName AND t1.LeafName = UserData.tp_LeafName AND t1.Level = UserData.tp_Level AND (UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL OR (UserData.tp_DraftOwnerId <>@IU AND 1=0 )) OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR 1=1 )) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU )) AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) ) AND t2.[tp_CalculatedVersion] = 0 AND t2.[tp_DeleteTransactionId] = 0x AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t3 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t3.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t3.[tp_RowOrdinal] = 0 AND ( (t3.tp_IsCurrent = 1) ) AND t3.[tp_CalculatedVersion] = 0 AND t3.[tp_DeleteTransactionId] = 0x AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L4) WHERE (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR ( UserData.tp_Level = 2 AND UserData.tp_DraftOwnerId IS NOT NULL OR UserData.tp_Level = 1 AND UserData.tp_DraftOwnerId IS NULL ) AND ( UserData.tp_CheckoutUserId IS NULL OR UserData.tp_CheckoutUserId <> @IU)) AND UserData.tp_SiteId=@L2 AND (UserData.tp_DirName=@DN) AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L2 AND (t1.DirName=@DN)) ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER) ',N'@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@DN nvarchar(260)',@L0='00000000-0000-0000-0000-000000000000',@L2='3640A558-D026-4ABF-BB7F-549ECA727EEC',@IU=3,@L3='750DB8DD-A6AA-49B5-9D23-EB6E4B95EAD7',@L4='EE1ADF17-47DB-4065-A8DC-8638E6903484',@DN=N'Documents'

I have to admit all that is just too magical for me to understand what's under the hood. So go with reflector, but I found it's even more complex than such a database query when I traced the code behind a CAML query from the SharePoint dll. Okay I gave up my path here. What I can do is to verify when the value of these two fields are different.

What I found is that all change on a list item conducted through UI would affect its "Modified" field immediately. Updating a list item by code is a different story. I will cover that in next post.

It looks like the "Last Modified" field is used internally by SharePoint itself. We should use "Modified" field in our logic to trace a list item's last modified time in general.