How do I search Projects using PSI?

C

cwmenke

My goal is to return projects matching a value in the Project or in
some of the project's Enterprise Custom Fields (only the Project and
ProjectCustomFields tables).

So far, the only way I can think of accomplishing this is:

Read the list of projects using ReadProjectList();
For every row in the list of projects
{
Read the entire project
Check the fields in that project
}

Could someone point me in the right direction?
 
C

Chris Boyd

Hey,

That is the only way to do it with the PSI. You can use the following as an
example on how to search for values of enterprise custom fields for projects:

http://blogs.msdn.com/project_progr...s-based-off-a-project-custom-field-value.aspx

Another way to do it, would be to read the reporting database. If you need
the infromation via a web service. You can write a PSI extension that reads
it from the RDB.

--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/
 
C

cwmenke

Hey,

That is the only way to do it with the PSI. You can use the following as an
example on how to search for values of enterprise custom fields for projects:

http://blogs.msdn.com/project_programmability/archive/2007/12/10/publ...

Another way to do it, would be to read the reporting database. If you need
the infromation via a web service. You can write a PSI extension that reads
it from the RDB.

--
Chris Boyd
MS Project
Program Manager

Blog:http://blogs.msdn.com/project_programmability/








- Show quoted text -

Thanks Chris. I think that writing a PSI extension is probably the
best bet at this point since reading all of the projects is extremely
slow and inefficient.
 
K

Kit

Thanks Chris. I think that writing a PSI extension is probably the
best bet at this point since reading all of the projects is extremely
slow and inefficient.- Hide quoted text -

- Show quoted text -

Is there an example somewhere that shows how to query the RDB in c#?
 
K

Kit

Is there an example somewhere that shows how to query the RDB in c#?- Hidequoted text -

- Show quoted text -

Actually, I think I figured out my own question. I somehow didn't
realize it was simply just querying the db.
http://blogs.msdn.com/project_progr...tensions-local-custom-fields-custom-code.aspx

In my opinion, it is very strange that there isn't a PSI for search. I
figured nearly everyone would have a use for that. I also can't
understand how a filtering parameter (http://msdn2.microsoft.com/en-us/
library/ms453399.aspx) isn't applied to the Project PSI.
 
K

Kit

Chris,
I was hoping you could give me some more information regarding searching
projects on MSP 2007 using C#.
- It seems the existing PSI methods for accessing projects are not
sufficient for searching the projects.
- I do not want to access the database directly and would like a higher
level of abstraction. (I would like to avoid code breaking when a
hotfix/update is released)

When you said "write a PSI extension that reads it from the RDB" did you
mean query the RDB directly and return those results through the PSI?
What would your advice be to search projects?

At this point, I am considering syncing all of the project data with a
sharepoint list.

Thanks,
Kit
 
C

Christina Odom

Chris,
I was hoping you could give me some more information regarding searching
projects on MSP 2007 using C#.
- It seems the existing PSI methods for accessing projects are not
sufficient for searching the projects.
- I do not want to access the database directly and would like a higher
level of abstraction. (I would like to avoid code breaking when a
hotfix/update is released)

When you said "write a PSI extension that reads it from the RDB" did you
mean query the RDB directly and return those results through the PSI?
What would your advice be to search projects?

At this point, I am considering syncing all of the project data with a
sharepoint list.

Thanks,
Kit







- Show quoted text -

Kit --

Unfortunately, your answer is in your original post. To my knowledge,
there isn't functionality in the Project web service will apply XML
filters and return a result based off of all projects in a datastore.
(I can understand why since custom fields is its own web service)
Its going to have to be a O(n) solution for now.

-Christy
 
K

Kit

Kit --

Unfortunately, your answer is in your original post.  To my knowledge,
there isn't functionality in the Project web service will apply XML
filters and return a result based off of all projects in a datastore.
(I can understand why since custom fields is its own web service)
Its going to have to be a O(n) solution for now.

-Christy- Hide quoted text -

- Show quoted text -

Thanks Christy for the reply.

Another question I have is:
What methods are used behind the scenes in a "Project Center" webpart?
 
C

Christina Odom

Thanks Christy for the reply.

Another question I have is:
What methods are used behind the scenes in a "Project Center" webpart?

No problem.
As for the Project Center webpart, Chris may be better qualified to
explain that one. However alot of the MOPS webparts come from
Microsoft.Office.Project.Server.PWA.dll. Just a piece of advice, if
the solution you're implementing is getting to the point where you
have to reverse engineer PWA webparts, you may be going down the wrong
path (but if you're just curious of how Microsoft implemented stuff to
figure out a 'better practice', I can't fault ya there!).
 
K

Kit

No problem.
As for the Project Center webpart, Chris may be better qualified to
explain that one.    However alot of the MOPS webparts come from
Microsoft.Office.Project.Server.PWA.dll.  Just a piece of advice, if
the solution you're implementing is getting to the point where you
have to reverse engineer PWA webparts, you may be going down the wrong
path (but if you're just curious of how Microsoft implemented stuff to
figure out a 'better practice', I can't fault ya there!).- Hide quoted text -

- Show quoted text -

I completely agree. I was hoping I would get some advice from Chris
for that exact reason. It just seems as if there is no way to get back
specific project information quickly (unless there is something I'm
missing which is more than likely).
 
C

Chris Boyd

Christina is correct. A lot of our web parts use a private PWA web service.
This is not a public interface and can chnge without notice during a patch,
service pack update or version upgrade.

There is no other way through the PSI to query for all projects that have a
partiiculr field set to a value. The only why to workaround is to create a
PSI extension and query the RDB. The RDB is a supported interface to develop
against and app should not be broken from any updates. However, I would not
recommand developing a PSI extension against the draft, publish or archive
store, as there schema may change without notice.

--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/
 
K

Kit

Christina is correct. A lot of our web parts use a private PWA web service..
This is not a public interface and can chnge without notice during a patch,
service pack update or version upgrade.

There is no other way through the PSI to query for all projects that have a
partiiculr field set to a value. The only why to workaround is to create a
PSI extension and query the RDB. The RDB is a supported interface to develop
against and app should not be broken from any updates. However, I would not
recommand developing a PSI extension against the draft, publish or archive
store, as there schema may change without notice.

--
Chris Boyd
MS Project
Program Manager

Blog:http://blogs.msdn.com/project_programmability/






- Show quoted text -

Alright. Thanks Chris.
 
A

albupp

Here's what I come up with. The code's adapted from code I found in the
ProjTool sample app that ships w/ the Office Project SDK:

<code>
public static ProjTool.ProjectWebSvc.ProjectDataSet FindProject(
java.lang.String name,
boolean getDetails)
{
Console.WriteLine("finding ms project, named: {0}", name);
ProjTool.ProjectWebSvc.ProjectDataSet rv = null;

ProjTool.ProjectWebSvc.ProjectDataSet list = new
ProjTool.ProjectWebSvc.ProjectDataSet();
ProjTool.ProjectWebSvc.Project project = new
ProjTool.ProjectWebSvc.Project();
project.Url = mspPrjSvcUrl;
project.Credentials = GetCredentials();

list.Merge(project.ReadProjectStatus(
Guid.Get_Empty(),
ProjTool.ProjectWebSvc.DataStoreEnum.WorkingStore,
name,
(int) Project.ProjectType.Project);

ProjTool.ProjectWebSvc.ProjectDataSet.ProjectRow prjRow =
list.Project().Item[0];

if (prjRow.Get_Table().DataSet.HasErrors)
{
Console.WriteLine("error found getting project named, {0}: {1}", name,
prjRow.RowError);
}
try
{
// to see if it exists, try getting the project's creation date...
DateTime d = prjRow.CREATED_DATE;

if (getDetails)
{
rv = project.ReadProject(prjRow.PROJ_UID,
ProjTool.ProjectWebSvc.DataStoreEnum.WorkingStore);
}
else
{
rv = list;
}
}
catch(Exception ex)
{
if (ex.toString().contains("DBNull"))
{
Console.WriteLine("Project named, {0}, not found on Project Server:
{1}", name, project.Url);
}
else
{
Console.WriteLine("exception caught: {0}", ex.getMessage());
}
rv = null;
}
return rv;
}
</code>
 
A

albupp

Sorry there were some compile errors in the code in the previous post. The
following compiles properly:

<code>
public static ProjectDataSet FindProject(
NetworkCredential credentials,
string mspPrjSvcUrl,
string name,
bool getDetails)
{
Console.WriteLine("finding ms project, named: {0}", name);
ProjectDataSet rv = null;

ProjectDataSet list = new ProjectDataSet();
Project project = new Project();
project.Url = mspPrjSvcUrl;
project.Credentials = credentials;

list.Merge(project.ReadProjectStatus(
Guid.Empty,
DataStoreEnum.WorkingStore,
name,
(int) PSLibrary.Project.ProjectType.Project));

ProjectDataSet.ProjectRow prjRow = list.Project[0];
if (prjRow.Table.DataSet.HasErrors) {
Console.WriteLine("error found getting project named, {0}: {1}", name,
prjRow.RowError);
}
try {
// to see if it exists, try getting the project's creation date...
DateTime d = prjRow.CREATED_DATE;

if (getDetails) {
rv = project.ReadProject(prjRow.PROJ_UID, DataStoreEnum.WorkingStore);
}
else {
rv = list;
}
}
catch(Exception ex) {
if (ex.ToString() == "DBNull") {
Console.WriteLine("Project named, {0}, not found on Project Server:
{1}", name, project.Url);
}
else {
Console.WriteLine("exception caught: {0}", ex.Message);
}
rv = null;
}
return rv;
}
</code>
 
K

Kit

Sorry there were some compile errors in the code in the previous post. The
following compiles properly:

<code>
public static ProjectDataSet FindProject(
  NetworkCredential credentials,
  string mspPrjSvcUrl,
  string name,
  bool getDetails)
{
  Console.WriteLine("finding ms project, named: {0}", name);
  ProjectDataSet rv = null;

  ProjectDataSet list = new ProjectDataSet();
  Project project = new Project();
  project.Url = mspPrjSvcUrl;
  project.Credentials = credentials;

  list.Merge(project.ReadProjectStatus(
    Guid.Empty,
    DataStoreEnum.WorkingStore,
    name,
    (int) PSLibrary.Project.ProjectType.Project));

    ProjectDataSet.ProjectRow prjRow = list.Project[0];
    if (prjRow.Table.DataSet.HasErrors) {
    Console.WriteLine("error found getting project named, {0}: {1}", name,
prjRow.RowError);
  }
  try {
    // to see if it exists, try getting the project's creation date...
    DateTime d = prjRow.CREATED_DATE;

    if (getDetails) {
      rv = project.ReadProject(prjRow.PROJ_UID, DataStoreEnum.WorkingStore);
    }
    else {
      rv = list;
    }
  }
  catch(Exception ex) {
    if (ex.ToString() == "DBNull") {
      Console.WriteLine("Project named, {0}, not found on Project Server:
{1}", name, project.Url);
    }
    else {
      Console.WriteLine("exception caught: {0}", ex.Message);
    }
    rv = null;
  }
  return rv;}

</code>

Thank you for the reply. Unfortunately, with this method has the same
problem as my solution: it only searches the project _name_.

I have the requirement to search for values in the Enterprise Custom
fields for the projects.
 
A

albupp

Thank you for the reply. Unfortunately, with this method has the same
problem as my solution: it only searches the project _name_.

I have the requirement to search for values in the Enterprise Custom
fields for the projects.

I see, sorry I missed that requirement. As I understand it, and I'm hardly
an expert, you'll have to retrieve the CustomFields that you're interested
in. You can use the ProjTool's CustomFieldWebSvc.CustomFields class for
that. Get their Guids, found in the MD_PROP_NAME field.

For task, these Guids can then be matched to the Guid found in the
ProjectDataSet.TaskCustomFieldsDataTable's rows. The
TaskCustomFieldsRow.MD_PROP_UID property holds the associated CustomField
Guid. Once you've found the CustomField that you're looking for, you can
inspect its value and compare it to your selection criteria. When a value
matches your criteria, get the Project Guid from the
TaskCustomFieldsRow.PROJ_UID property.

Hope this helps.
 
K

Kit

I see, sorry I missed that requirement.  As I understand it, and I'm hardly
an expert, you'll have to retrieve the CustomFields that you're interested
in.  You can use the ProjTool's CustomFieldWebSvc.CustomFields class for
that.  Get their Guids, found in the MD_PROP_NAME field.

For task, these Guids can then be matched to the Guid found in the
ProjectDataSet.TaskCustomFieldsDataTable's rows.  The
TaskCustomFieldsRow.MD_PROP_UID property holds the associated CustomField
Guid.  Once you've found the CustomField that you're looking for, you can
inspect its value and compare it to your selection criteria.  When a value
matches your criteria, get the Project Guid from the
TaskCustomFieldsRow.PROJ_UID property.

Hope this helps.

That was exactly my thinking as well.

However, this single statement is very slow because it is returning
_all_ of the details for that one project.

Below is a working example of what I was talking about. Project is the
proxy I generated using wsdl. I wouldn't keep all of the project data
like in the example below; just the field values I needed from
ProjectDataSet.Project and ProjectDataSet.ProjectCustomFields.

using System;
using System.Collections.Generic;
using System.Text;

// added using statements
using System.Net;

namespace psiReadAllProjects
{
class Program
{
private static Project _project;
private static NetworkCredential _cred;

static void Main(string[] args)
{
_cred = new NetworkCredential("username", "password",
"domain");
_project = new Project();
_project.Credentials = _cred;

List<Guid> projectGuids = new List<Guid>();

ProjectDataSet projectList = _project.ReadProjectList();
foreach (ProjectDataSet.ProjectRow row in
projectList.Project)
{
projectGuids.Add(row.PROJ_UID);
}

List<ProjectDataSet> projectData = new
List<ProjectDataSet>();
foreach (Guid uid in projectGuids)
{
projectData.Add(_project.ReadProject(uid,
DataStoreEnum.PublishedStore));
}

Console.ReadKey();
}
}
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top