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.