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.