Friday, June 20, 2008

Moving non-file items in SharePoint lists, the nasty way

Oh lord it's been a while since my last post. Busy six months, that's just about all I've got to say in my defense.

Cutting to the chase, moving items to different folders within SharePoint lists, using code, has proved difficult. While there's a CopyTo function in the SPListItem class, there's no MoveTo, MoveUrl or similar. To add to the frustration, CopyTo fails horribly when the File member of the SPListItem isn't set (in other words: for all non-file items).

Two solutions come to mind. The less intrusive one is to create a new item, and copy all the fields from the old one. That would make for an awful lot of code, though, as you'd not only have to make sure all fields and metadata were preserved, but any alerts, previous version links and other tagalongs as well. My way, the poke-you-in-the-back-with-a-pitchfork-style intrusive one, does it all quickly and painlessly. I have to admit that I haven't looked into the consequences on search engine cache, nor have I paid much attention to Update calls on the list to do the actual data changes, but the approach isn't as dirty as I make it sound. Feel free to give it a try, and submit any suggestions / questions you might have.

public static void MoveListItemToFolder(SPListItem item, SPFolder destinationFolder)
{
    if (!item.DoesUserHavePermissions(SPBasePermissions.Open) ||
       ((item.ParentList.RootFolder == destinationFolder &&
         !destinationFolder.ParentWeb.DoesUserHavePermissions(SPBasePermissions.AddListItems)) ||
        (item.ParentList.RootFolder != destinationFolder &&
         !destinationFolder.Item.DoesUserHavePermissions(SPBasePermissions.AddListItems)))
        )
    {
        throw new Exception("Permission denied");
    }

    // Remove the site's url from the destination url
    string destDir = SPUrlUtility.CombineUrl(destinationFolder.ParentWeb.ServerRelativeUrl, destinationFolder.Url);
    // Remove any leading/trailing forward slashes
    destDir = destDir.Trim('/');
    
    using(SqlConnection conn = new SqlConnection(item.ParentList.ParentWeb.Site.ContentDatabase.DatabaseConnectionString))
    {
        conn.Open();
        SqlTransaction transaction = conn.BeginTransaction();

        try
        {
            SqlCommand command = conn.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "update AllUserData set tp_DirName = @NewDir where tp_ListId = @ListId and tp_ID = @ItemId; select tp_LeafName from AllUserData where tp_ListId = @ListId and tp_ID = @ItemId";
            command.Parameters.Add("@NewDir", System.Data.SqlDbType.NVarChar, 256);
            command.Parameters["@NewDir"].Value = destDir;
            command.Parameters.Add("@ListId", System.Data.SqlDbType.UniqueIdentifier);
            command.Parameters["@ListId"].Value = item.ParentList.ID;
            command.Parameters.Add("@ItemId", System.Data.SqlDbType.Int);
            command.Parameters["@ItemId"].Value = item.ID;
            string leafName;
            using (SqlDataReader reader = command.ExecuteReader())
            {
                reader.Read();
                leafName = reader["tp_LeafName"as string;
            }

            command = conn.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "update AllDocs set DirName = @NewDir where ListId = @ListId and LeafName = @LeafName";
            command.Parameters.Add("@NewDir", System.Data.SqlDbType.NVarChar, 256);
            command.Parameters["@NewDir"].Value = destDir;
            command.Parameters.Add("@ListId", System.Data.SqlDbType.UniqueIdentifier);
            command.Parameters["@ListId"].Value = item.ParentList.ID;
            command.Parameters.Add("@LeafName", System.Data.SqlDbType.NVarChar, 128);
            command.Parameters["@LeafName"].Value = leafName;
            command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch
        {
            try
            {
                transaction.Rollback();
            }
            catch
            {}
        }
        finally
        {
            transaction.Dispose();
        }
    }
}


The usage should be pretty obvious given the function arguments. What actually happens though, simply put, is that two tables in the SharePoint databases are updated with the new target folder url. This is done using the connection string from the connection string associated with the current site collection, and enforced by a transaction which would either successfully make the update, or change nothing at all. You might want to change the access verifications, or throw an exception in case of update issues. I'll leave that to whoever wants to adapt this.