Getting Notes from Project Server 2003
I got an email asking about retrieving notes from a Project. Simple enough through the UI. But how about programmatically? And oh yeah, iin Project Sever 2003 and I want to do it server-side. After pondering this I came up with two possibilities,
1) Use OLE automation to the Project client (last resort choice for server side)
2) Go straight to the database.
The email said they had looked in the MSP_TEXT_FIELDS table and found that the information max’d out at 255 because the column is a varchar(255). Not mentioned there but also another very real problem is that notes are RTF. If you get the data from MSP_TEXT_FIELDS you lose all the formatting. A quick trip to pjdb.htm solved my problem. Within pjdb.htm is a piece of VB script code called getRTF to read the RTF text field from an MPP file. Some quick connection string changes and I had a solution. But I don’t want to have VB script, I want C#, I want to be able to rewrite this to go into a PDS extension if I want. I wrote a sample, and here are the specifics. I eliminated the UI and error checking for brevity sake and will leave adding that as an exercise to the reader.
Let’s start with the query. Suppose we are looking for notes for the tasks, the query would be as follows:
cmd.CommandText =
"SELECT TASK_RTF_NOTES" +
" FROM MSP_TASKS" +
" WHERE proj_id = @proj_id AND " +
" task_uid = @entity_id";
First off is we need to go to the MSP_TASKS table. The column we are after is TASK_RTF_NOTES. Then the where clause just specifies the project and task ids. Coding wise for the database access we end up with:
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
"SELECT " + RTFColumn(table) +
" FROM " + table +
" WHERE proj_id = @proj_id AND " +
UIDColumn(table) + " = @entity_id";
cmd.Connection = _conn;
cmd.Parameters.Add("@proj_id", SqlDbType.Int).Value =
Convert.ToInt32(proj_id);
cmd.Parameters.Add("@entity_id", SqlDbType.Int).Value =
Convert.ToInt32(entity_id);
byte[] rtf = (byte[])cmd.ExecuteScalar();
We build up the command string, add the parameters, and finally execute a scalar (one result) command. Notice though what is retuned, a byte array. RTF data is save as binary information. Finally if you want to display this say in the Rich Text Box, it only accepts streams for loading. Let’s manipulate the byte array into a memory stream:
MemoryStream rtfStream = new System.IO.MemoryStream();
rtfStream.Write(rtf, 0, rtf.GetLength(0));
rtfStream.Position = 0;
return rtfStream;
Our final code for GetNotes looks like the following:
private MemoryStream GetNotes(string proj_id, string table, string entity_id)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText =
"SELECT " + RTFColumn(table) +
" FROM " + table +
" WHERE proj_id = @proj_id AND " +
UIDColumn(table) + " = @entity_id";
cmd.Connection = _conn;
cmd.Parameters.Add("@proj_id", SqlDbType.Int).Value =
Convert.ToInt32(proj_id);
cmd.Parameters.Add("@entity_id", SqlDbType.Int).Value =
Convert.ToInt32(entity_id);
byte[] rtf = (byte[])cmd.ExecuteScalar();
MemoryStream rtfStream = new System.IO.MemoryStream();
rtfStream.Write(rtf, 0, rtf.GetLength(0));
rtfStream.Position = 0;
return rtfStream;
}
In my sample I did make things a little more flexible so you can read notes from other tables. I used the following utility functions to allow for more flexibility:
private string UIDColumn(string Table)
{
switch (Table)
{
case "MSP_TASKS":
return "task_uid";
case "MSP_RESOURCES":
return "res_uid";
case "MSP_ASSIGNMENTS":
return "assn_uid";
default:
throw (new Exception(String.Format("Unknown Table: {0}", Table)));
}
}
private string RTFColumn(string Table)
{
switch (Table)
{
case "MSP_TASKS":
return "TASK_RTF_NOTES";
case "MSP_RESOURCES":
return "RES_RTF_NOTES";
case "MSP_ASSIGNMENTS":
return "ASSN_RTF_NOTES";
default:
throw (new Exception(String.Format("Unknown Table: {0}", Table)));
}
}
Finally, you got that data into a Rick Text Box, and made a few changes. What do you want to do next, save it of course.
private void SaveNotes(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_id = @proj_id AND " + UIDColumn(table) + " = @entity_id";
cmd.Connection = _conn;
cmd.Parameters.Add("@rtf", SqlDbType.Image, rtf.GetLength(0)).Value = rtf;
cmd.Parameters.Add("@proj_id", SqlDbType.Int).Value =
Convert.ToInt32(proj_id);
cmd.Parameters.Add("@entity_id", SqlDbType.Int).Value =
Convert.ToInt32(entity_id);
cmd.ExecuteNonQuery();
}
I won’t go through all the details of the code, but I will point out one thing. The data type for the TASK_RTF_NOTES column in ADO.Net SQL is SqlDbType.Image, not binary. Take this add database connection information, wrap it in a library then you can call it from wherever you need.
Comments
Anonymous
August 28, 2006
The inevitable follow on question to Getting Notes from Project Server 2003  when combined with ...Anonymous
November 15, 2006
Thanks for these instructions. I've ran into a little snag though: reading out existing notes and updating them is a breeze, but I fail to make an initial notes assignment stick. E.g. I fill the TASK_RTF_NOTES column in MSP_TASKS with the code provided above, then open the project in the project client. Then, the notes appear to be lost in nivrana.. the project client doesn't show them, and upon saving the project again, the TASK_RTF_NOTES column is set back to NULL: Have you managed to get this scenario to work as well?Anonymous
August 28, 2007
Why doesn't Microsoft just save the notes in plain text format to begin with, or at least make that an option? I have no need for RTF in the notes, I just want to read and write the notes. You can export the notes to plain text if you do a selective export of the database, but then there are other pproblems. If you do full export, then you only get RTF, and if you do a search on the internet you see that this is a huge problem for people. Just put the notes in text, geeeze.....