SharePoint: Changing comments of document versions in code

During one SharePoint migration project I had problem with document versions comments. I needed some way to set these in my code. As I didn’t found any normal way to do it through SharePoint API I worked out something I call dirty hack. But it works.

The Idea

As API seemed more and more hopeless in my context I checked out what’s going on in database and I discovered that I can set those version comments also after importing all the data. My hack affected only two tables in SharePoint database and after solving some mysteries I got everything work as expected.

Database

The first thing we will look at is SharePoint database. We are interested in two tables: AllDocs and AllDocVersions. Version fields are UIVersion in first table and Version in second table. Both tables have field called CheckinComment – this is the fields we have to change.

Make sure you don’t edit SharePoint lists or items through browser when using this solution – you may overwrite the changes you made through code.

Version Format

Let’s look at the versions for a moment. We don’t see nice version numbers like 0.1, 1.0, 2.2 in these tables. Instead we have some integers like 1, 512, 1026. There is little trick how to compute original versions to integers. You have to use the following equation:

version integer = major version * 512 + minor version

Version Class

Next thing is class called Version. I wrote about it blog entry Using Version Class. As Version is sealed class we are not able to extend it. But we can always use extension methods to visually add new methods to classes. Now let’s add new extension method for Version class that returns Sharepoint version integer from current version.

public static class VersionExtender
{
    public static int GetSharePointDocVersion(this Version ver)
    {
        return ver.Major * 512 + ver.Minor;
    }
}

Changing the comments

Now we are ready to change the comments. As an example I give a method that is very compact so it easier for you to take it and try it out. Of course it is possible to optimize and refactor this method many ways. You are free to do it.

The method takes three arguments: versionString, comment, and guid. versionString is version label of document we want to change, comment – of course – is comment for list item version and guid is GUID of list item.

void SaveComment(string versionString, string comment, Guid guid)
{
    Version version = new Version(versionString);
    Int32 uiVersion = version.GetSharePointDocVersion();
 
 
    SqlConnection cn = new SqlConnection("SP ConnStr");
    SqlCommand cmd;
    string cmdTxt;
 
    cn.Open();
 
    cmd = cn.CreateCommand();
    cmdTxt = "update alldocs set CheckinComment=@Comment Where ";
    cmdTxt += "UIVersion=@Version and cast(id as varchar(100))=@id";
    cmd.CommandText = cmdTxt;
    cmd.CommandType = CommandType.Text;
 
    cmd.Parameters.AddWithValue("@Comment", comment);
    cmd.Parameters.AddWithValue("@Version", uiVersion);
    cmd.Parameters.AddWithValue("@id", guid.ToString());
 
    cmd.ExecuteNonQuery();
 
    cmd = cn.CreateCommand();
    cmdTxt = "update alldocversions set CheckinComment=@Comment ";
    cmdTxt += "Where Version=@Version and ";
    cmdTxt += "cast(id as varchar(100))=@id";
    cmd.CommandText = cmdtxt;
    cmd.CommandType = CommandType.Text;
 
    cmd.Parameters.AddWithValue("@Comment", comment);
    cmd.Parameters.AddWithValue("@Version", uiVersion);
    cmd.Parameters.AddWithValue("@id", guid.ToString());
 
    cmd.ExecuteNonQuery();
 
    cmd.Dispose();
    cn.Dispose();
}

Okay, now you should be able to edit comments of document versions through code. If you know some better way how to do it, please let me know.