Getting Notes from Project Server 2007

The inevitable follow on question to Getting Notes from Project Server 2003  when combined with Modifying RTF Notes is, “What about Project Server 2007?” In Modifying RTF Notes I said that the RTF Notes fields are read only in the PSI. The code I’m about to post is unsupported. Ok, everything I put out here is unsupported, but this is really really unsupported. In other words, the product group recommendation is to use the PSI and the reporting database for your integration, don’t go to the project stores (draft, published, and archive databases). The project store schemas are unpublished and are subject to change without warning. If you deploy what I’m going to show you and a Project Server hotfix breaks your code… you can't say you weren't warned J. Buuuuuut there is no other way to accomplish this so I’m going to show you it anyway.

The code is essentially the same and the 2003 code, with a few minor modifications. I’ve marked the change areas with big comments for your viewing pleasure. The biggest thing you have to take into account that the ids are now all Guids.

private MemoryStream GetNotes2007(string proj_id, string table,

                                    string entity_id)

{

    SqlCommand cmd = new SqlCommand();

    cmd.CommandText =

       "SELECT " + RTFColumn(table) +

       " FROM " + table +

       " WHERE proj_uid = @proj_id AND " + // **** 2007 Change ****

       UIDColumn(table) + " = @entity_id";

    cmd.Connection = _conn;

    cmd.Parameters.Add("@proj_id", SqlDbType.UniqueIdentifier).Value =

        new Guid(proj_id); // **** 2007 Change ****

    cmd.Parameters.Add("@entity_id", SqlDbType.UniqueIdentifier).Value =

        new Guid(entity_id); // **** 2007 Change ****

    byte[] rtf = (byte[])cmd.ExecuteScalar();

    MemoryStream rtfStream = new System.IO.MemoryStream();

    rtfStream.Write(rtf, 0, rtf.GetLength(0));

    rtfStream.Position = 0;

    return rtfStream;

}

private void SaveNotes2007(MemoryStream rtfStream, string proj_id,

                            string table, string entity_id)

{

    byte[] rtf = rtfStream.ToArray();

    SqlCommand cmd = new SqlCommand();

    cmd.CommandText =

       "UPDATE " + table +

       " SET " + RTFColumn(table) + " = @rtf " +

       "WHERE proj_uid = @proj_id AND " + UIDColumn(table) +

       " = @entity_id"; // **** 2007 Change ****

    cmd.Connection = _conn;

    cmd.Parameters.Add("@rtf", SqlDbType.Image, rtf.GetLength(0)).Value =

        rtf;

    cmd.Parameters.Add("@proj_id", SqlDbType.UniqueIdentifier).Value =

        new Guid(proj_id); // **** 2007 Change ****

    cmd.Parameters.Add("@entity_id", SqlDbType.UniqueIdentifier).Value =

        new Guid(entity_id); // **** 2007 Change ****

    cmd.ExecuteNonQuery();

}

Other than the Guid change there is nothing else apparent that changed in the code. But under the covers there is a big one… which database do you go against???? Theoretically you could read from any of the three project stores (archive, draft, published) with no consequences. But in practical application it only makes sense to write to the draft store. Draft is the only place where you can check out a project and insure its integrity while you’re writing data where you shouldn’t be ;). Writing RTF information should fall into the same data lifecycle you practice when using the PSI:

· Checkout

· Modify Data

· Save

· Check-in

· Publish

Again I can’t stress enough to be very aware of what you are doing if you use this code or any other code that goes to the database. The project store databases are not designed to be touched externally in this release.