Steve Lineberry

I just got back from the SharePoint Conference in Las Vegas a few weeks ago. One of the things that I learned about and am excited about is in SharePoint 2010 you can have custom activities that show up in the colleague tracker for My Sites. In SharePoint 2007, you were stuck with the out of the box (OOTB) activities such as: birthday, blog, profile attribute change, etc. When we rolled out our My Sites at work, we had two other lists that we wanted to add to people’s colleague tracker when a colleague of theirs added something to these lists. I was able to figure out a solution but I will warn you ahead of time, it’s a little hacky. Not only is the ootb Microsoft code for blog activities hacky, my solution sits on top of that and is more hacky than their code. This is all because Microsoft marked Internal several objects I wanted to use.

There are two parts to this solution. Part 1 is an eventreceiver that will add items to a user’s activity feed. Part 1 adds items the same way that blogs are added to the activity feed. Part 2 extends the colleague tracker webpart so that these new items in the activity feed don’t say blog beside them, and will actually say what you want them to say.

So for part 1 I used Reflector to see the code for the OOTB eventreceiver, Microsoft.Office.Server.UserProfiles.BlogListEventReceiver. Examining this code revealed that MS is calling some stored procedures in the SharedServices database to add items to the activity feed. There were two objects in this class that were marked Internal that I needed to figure out a way around. These objects allowed sql code to be executed on the SharedServices database. Since I couldn’t use these, the main hack involved hard coding the sharedservices database name into the event receiver. If someone knows how to get the name of the sharedservices database through the api, let me know, that would be a much better solution.

My solution below uses the author column to determine the user which this activity is for. Our solution at work required us to use a custom column. But if all you need is for the activity to be for the person that is adding the item and the list you are wanting to use is a blog, then you might just want to use the OOTB Microsoft.Office.Server.UserProfiles.BlogListEventReceiver.

Here’s the part 1 code or you can download it here:

using System.Data.SqlClient;
using System.Data;
using Microsoft.SharePoint;
using System;
using System.IO;
using System.Xml;
using System.Globalization;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Administration;

namespace EventHandlers
{
    public class AddListChangesToUserProfile : SPItemEventReceiver
    {
        // Methods

        public override void ItemAdded(SPItemEventProperties properties)
        {
            if (properties == null) return;
            if (properties.ListItemId <= 0) return;

            using (SPSite site = new SPSite(properties.SiteId))
            {
                ServerContext serverContext = ServerContext.GetContext(site);
                UserProfile userProfile = new UserProfileManager(serverContext).GetUserProfile(properties.UserLoginName);
                using (SPWeb web = properties.OpenWeb())
                {
                    SPList list = web.Lists[properties.ListId];
                    string title = list.GetItemById(properties.ListItemId).Title;
                    AddNewEntry(serverContext, userProfile.RecordId, title, string.Format(CultureInfo.InvariantCulture, GetFormUrl(list, PAGETYPE.PAGE_DISPLAYFORM) + "?ID={0}", new object[] { properties.ListItemId }));
                }
            }

            base.ItemAdded(properties);
        }

        public override void ItemDeleting(SPItemEventProperties properties)
        {
            if (properties == null) return;
            if (properties.ListItemId <= 0) return;

            using (SPSite site = new SPSite(properties.SiteId))
            {
                using (SPWeb web = properties.OpenWeb())
                {
                    SPList list = web.Lists[properties.ListId];
                    SPListItem item = list.GetItemById(properties.ListItemId);
                    string title = item.Title;
                    SPUser user = GetUserFromItemValue(web, item["Author"]);

                    ServerContext serverContext = ServerContext.GetContext(site);
                    UserProfile userProfile = new UserProfileManager(serverContext).GetUserProfile(user.LoginName);

                    RemoveEntry(serverContext, userProfile.RecordId, title, string.Format(CultureInfo.InvariantCulture, GetFormUrl(list, PAGETYPE.PAGE_DISPLAYFORM) + "?ID={0}", new object[] { properties.ListItemId }));
                }
            }
        }

        public override void ItemUpdating(SPItemEventProperties properties)
        {
            this.ItemDeleting(properties);
            base.ItemUpdating(properties);
        }

        public override void ItemUpdated(SPItemEventProperties properties)
        {
            this.ItemAdded(properties);
            base.ItemUpdated(properties);
        }

        public static string MakeXml(string subject, string permaLink)
        {
            StringWriter w = new StringWriter(CultureInfo.InvariantCulture);
            XmlTextWriter writer2 = new XmlTextWriter(w);
            writer2.WriteStartDocument();
            writer2.WriteStartElement("WebLog");
            writer2.WriteElementString("Title", subject);
            writer2.WriteElementString("PermaLink", permaLink);
            writer2.WriteEndElement();
            writer2.WriteEndDocument();
            writer2.Close();
            return w.ToString();
        }

        public static void AddNewEntry(ServerContext context, long userID, string subject, string permaLink)
        {
            SqlCommand command = new SqlCommand("dbo.Profile_AddWebLogEvent");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@RecordId", SqlDbType.BigInt).Value = userID;
            command.Parameters.Add("@Data", SqlDbType.Variant).Value = MakeXml(subject, permaLink);
            //try
            //{
                ExecuteNonQuery(command);
            //}
            //catch (SqlException exception)
            //{

            //}
        }

        public static void ExecuteNonQuery(SqlCommand command)
        {
            SPWebService service = SPFarm.Local.Services.GetValue<SPWebService>();
            SPDatabaseServiceInstance defaultDatabaseInstance = service.DefaultDatabaseInstance;
            string databaseServer = SPHttpUtility.NoEncode(defaultDatabaseInstance.NormalizedDataSource);

            string connectionString = "Data Source=" + databaseServer + ";Initial Catalog=SharedServices_DB;User ID=uid;Password=pwd;";

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = connectionString;
            command.Connection = connection;

            using (connection)
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Dispose();
            }
        }

        public static void RemoveEntry(ServerContext context, long userID, string subject, string permaLink)
        {
            SqlCommand command = new SqlCommand("dbo.Profile_RemoveWebLogEvent");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@RecordId", SqlDbType.BigInt).Value = userID;
            command.Parameters.Add("@Data", SqlDbType.Variant).Value = MakeXml(subject, permaLink);
            try
            {
                ExecuteNonQuery(command);
            }
            catch (SqlException exception)
            {

            }
        }

        public static string GetFormUrl(SPList list, PAGETYPE type)
        {
            using (SPWeb web = list.ParentWeb)
            {
                string Url = web.Url + "/" + list.Forms[type].Url;
                Url = Url.Replace(" ", "%20");
                return Url;
            }
        }

        public static SPUser GetUserFromItemValue(SPWeb web, object columnVal)
        {
            SPUser user = null;
            try
            {
                string[] userarr = columnVal.ToString().Split('#');
                using (SPSite site = web.Site)
                {
                    using (SPWeb rootWeb = site.RootWeb)
                    {
                        user = rootWeb.AllUsers.GetByID(Convert.ToInt32(userarr[0].Replace(";", string.Empty)));
                    }
                }
            }
            catch { }
            return user;
        }
    }
}

So part 2 involved extending the colleague tracker webpart, Microsoft.SharePoint.Portal.WebControls.ContactLinksMicroView, and overriding the method GenerateHtmlOneRowForOneItem and doing some find and replace magic. From what I understand, the GenerateHtmlOneRowForOneItem method returns all the html output for one colleague in the colleague tracker. Since a colleague could have a blog entry and any of our other custom activities, it made the find and replace a little more difficult. Basically, the code searchs in the html for part of the url to one of the custom activities. Once it finds that location, it finds the string “Blog:” that is the first instance before that location and replaces it with the name of the activity.

My solution only replaces the text “Blog:” with the appropriate name. If you wanted to extend my solution you can also add some additional find and replace magic to change the icon as well. My solution includes two different custom activities, List and Comments. You can add more as you see fit.

Here’s the code for part 2 or you can download it here:

using System;
using System.ComponentModel;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebPartPages;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Text;

namespace WebParts
{
	/// <summary>
	/// Description for PrintPage.
	/// </summary>
	[DefaultProperty("Text"),
        ToolboxData("<{0}:ContactLinksMicroView runat=server></{0}:ContactLinksMicroView>"),
		XmlRoot(Namespace="WebParts")]
    public class ContactLinksMicroView : Microsoft.SharePoint.Portal.WebControls.ContactLinksMicroView
	{	
		#region Variables
        private const string LIST_URL = "/mysite/Lists/MyList/";
        private const string COMMENT_URL = "/mysite/Lists/MyComments/";
        private const string BLOG = "Blog:";
        private const string LIST = "List:";
        private const string COMMENT = "Comment:";

        private string _listUrl = LIST_URL;
        private string _commentUrl = COMMENT_URL;
		#endregion

		#region Properties
        [Browsable(true),
        Category("Administration"),
        DefaultValue(LIST_URL),
        WebPartStorage(Storage.Personal),
        FriendlyName("List URL"),
        Description("URL to the List")]
        public string ListUrl
        {
            get
            {
                return _listUrl;
            }

            set
            {
                _listUrl = value;
            }
        }

        [Browsable(true),
        Category("Administration"),
        DefaultValue(COMMENT_URL),
        WebPartStorage(Storage.Personal),
        FriendlyName("Comments URL"),
        Description("URL to the Comments")]
        public string CommentUrl
        {
            get
            {
                return _commentUrl;
            }

            set
            {
                _commentUrl = value;
            }
        }

        [WebPartStorage(Storage.None)]
        public override string TitleUrl
        {
            get
            {
                return string.Empty;
            }
            set
            {

            }
        }

		#endregion	

		public ContactLinksMicroView()
		{
            this.ChromeType = PartChromeType.None;
		}

        protected override void GenerateHtmlOneRowForOneItem(DataRow objectDataRow, System.Text.StringBuilder sbRenderRowHtml, int rowID, string strStyleClass, int iIndexOfItemInDataSet, int iIndexOfItemInGroup)
        {
            StringBuilder temp = new StringBuilder();
            base.GenerateHtmlOneRowForOneItem(objectDataRow, temp, rowID, strStyleClass, iIndexOfItemInDataSet, iIndexOfItemInGroup);

            string temp2 = temp.ToString();
            temp2 = FixHtml(temp2, LIST, ListUrl);
            temp2 = FixHtml(temp2, COMMENT, CommentUrl);
            sbRenderRowHtml.Append(temp2);
        }

        private string FixHtml(string html, string title, string url)
        {
            if (html.IndexOf(url) < 0) return html;

            string needsToBeSearched = html;
            string alreadySearched = string.Empty;

            while (needsToBeSearched.Length > 0)
            {
                Split(ref needsToBeSearched, ref alreadySearched, url);
                Split(ref needsToBeSearched, ref alreadySearched, BLOG);

                if (alreadySearched.StartsWith(BLOG))
                {
                    alreadySearched = title + alreadySearched.Substring(BLOG.Length);
                }
            }

            return alreadySearched;
        }

        private void Split(ref string needsToBeSearched, ref string alreadySearched, string search)
        {
            int pos = needsToBeSearched.LastIndexOf(search);
            if (pos < 0)
            {
                alreadySearched = needsToBeSearched + alreadySearched;
                needsToBeSearched = string.Empty;
                return;
            }
            alreadySearched = needsToBeSearched.Substring(pos) + alreadySearched;
            needsToBeSearched = needsToBeSearched.Substring(0, pos);
        }
	}
}

Now that we have a working colleague tracker webpart that displays the correct text for our activity, I needed to replace the current colleague tracker webpart on everyone’s My Site with our custom one. This is a pretty straightforward feature but if you want me to post the feature code, leave a comment below.

Also, don’t forget to also attach either my eventreceiver above or the OOTB Microsoft.Office.Server.UserProfiles.BlogListEventReceiver eventreceiver to the list you want to show up as a custom activity. This can be done a few different ways. I ended up creating a webservice where I input the url to the list, and the name of the eventreceiver and it attached them.

As I mentioned in Part 1, I decided to store each site collection in its own database for better managability on both of our SharePoint farms. This post is Part 2 and hopes to answer the question: “What happens when someone deletes a site collection?” In a normal sharepoint environment that only has a few content databases, this is not a big deal. But in our environment it’s a much bigger deal because each site collection has its own database and that database name includes the site collection url. Our web service from Part 1 helps make sure we don’t put a site into a database that is left over from a deleted site collection, but it’s our event receivers that do the real clean up.

We have two event receivers for this. One for SiteDeleting (Before the site collection is deleted) and the other for SiteDeleted (After the site collection is deleted). SiteDeleting will check if the site collection being deleted is in a database which will be empty after the site collection is deleted. If this is the case, then we set the database status in sharepoint to offline (disabled). SiteDeleted will get a list of all content databases that are empty and remove them from SharePoint. It also sets the database to single user and then drops it.

Below is the code or click this link to download:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SharePoint.Utilities;
using System.Web;

public class DeleteDatabaseOnSiteDeleted : SPWebEventReceiver
{
    public override void SiteDeleting(SPWebEventProperties properties)
    {
        base.SiteDeleting(properties);
        using (SPSite site = properties.Web.Site)
        {
            //Before the site collection is deleted
            //set status to disabled if the currently being deleted site collection
            //is the last one in this database
            if (site.ContentDatabase.CurrentSiteCount == 1)
            {
                site.ContentDatabase.Status = SPObjectStatus.Disabled;
                site.ContentDatabase.Update();
            }
        }
    }

    public override void SiteDeleted(SPWebEventProperties properties)
    {
        base.SiteDeleted(properties);

        Uri url = new Uri(properties.FullUrl);
        string currUrl = properties.FullUrl;
        string basepath = currUrl.Substring(0, currUrl.IndexOf(url.Host) + url.Host.Length);

        SPWebApplication webApp = SPWebApplication.Lookup(new Uri(basepath));
        List<SPContentDatabase> emtpyDbs = new List<SPContentDatabase>();

        //After the site collection is deleted find all databases that 
        //don't have any sites in them
        foreach (SPContentDatabase database in webApp.ContentDatabases)
        {
            if (database.CurrentSiteCount <= 0)
            {
                emtpyDbs.Add(database);
            }
        }

        foreach (SPContentDatabase database in emtpyDbs)
        {
            RemoveAndDeleteDb(database);
        }
    }

    private void RemoveAndDeleteDb(SPContentDatabase db)
    {
        string dbName = db.Name;

        if (db.CurrentSiteCount != 0) return;

        //Remove database from SharePoint
        db.WebApplication.ContentDatabases.Delete(db.Id);

        //Get database server name
        SPWebService service = SPFarm.Local.Services.GetValue<SPWebService>();
        SPDatabaseServiceInstance defaultDatabaseInstance = service.DefaultDatabaseInstance;
        string databaseServer = SPHttpUtility.NoEncode(defaultDatabaseInstance.NormalizedDataSource);

        //Build connection string
        string connectionString = "Data Source=" + databaseServer + ";Initial Catalog=Master;User ID=uid;Password=pwd;";

        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = connectionString;
        string sql;

        //Force database to close other connections
        sql = "ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
        SqlCommand command1 = new SqlCommand(sql, connection);
        command1.CommandType = CommandType.Text;

        //Remove database from sql server
        sql = "DROP DATABASE [" + dbName + "]";
        SqlCommand command2 = new SqlCommand(sql, connection);
        command2.CommandType = CommandType.Text;

        using (connection)
        {
            connection.Open();
            command1.ExecuteScalar();
            command2.ExecuteScalar();
        }
    }
}

As with Part 1, the main issue with this implementation is concurrency. You probably shouldn’t be creating and deleting a site collection at the same time. Since the chances in our environment for this are very slim, we’ve never had any issues. Also, since the database is dropped, any changes made since the last backup are lost. It might be a better solution to call a stored procedure during SiteDeleted which backs up the database first, and then sets it to single user and drops it.

Of course for this solution to work, you’ll need to write a feature that adds the eventreceivers to all existing site collections and any new site collections. I haven’t included that code here because it’s pretty straightforward, but if anyone is interested, leave a comment and I’ll see if I can get it posted.

————————————
Update 11/06/2009: I’ve created a Part 3 which includes the feature code.