Project Server Blogs

Unlike other methods in the PSI (e.g. ReadResources()), the ReadProject() and ReadProjectEntities() methods do not accept an xmlFilter parameter to pre-filter the data returned by the PSI. Normally, your only option would be to use a foreach or while loop. However, the DataTable class used by the PSI offers the Select() method an alternative solution. Another option is to use LINQ, which Microsoft introduced in the .NET Framework 3.5.

In order to implement the LINQ code in this post, you will need a copy of Visual Studio 2008. If you are implementing this code on a server, you will need to download and install a copy of the .NET Framework 3.5. If you only have Visual Studio 2003/2005 or your organization will not deploy the .NET Framework 3.5 to your servers, you will be limited to using the DataTable.Select() sample.

To start, I’ve created a test project consisting of six tasks.

You will notice that two of these tasks are named Test Task. My objective is to retrieve the project from the PSI and return only these two tasks first using Select() and then using LINQ.

Note that for this code I assume that you have added a Web Reference to the Project web service called ProjectPSI. Remember that this is only one way of doing things, and that this code was created for demonstration purposes. As such, it’s not really fully developed with everything it ought to have for a production environment (e.g. error checking and more intelligent filters).

Here’s the complete code for the TaskFilter class:

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Net;

using System.Text;

namespace PSH

{

    public class TaskFilter

    {

        public static List<string> SelectFilterTaskNames(Guid projectGuid, string fieldName, string filter)

        {

            List<string> _filteredNames = new List<string>();

            // Set up PSI Reference

            ProjectPSI.Project _projectPsi = CreatePsiReference();

            // Retrieve Project Data

            ProjectPSI.ProjectDataSet _projectDs = _projectPsi.ReadProject(projectGuid, ProjectPSI.DataStoreEnum.PublishedStore);

            // Filter Project Data

            DataRow[] _returnedRows = _projectDs.Task.Select(filter);

            // Read filtered data into return collection

            foreach (DataRow _row in _returnedRows)

            {

                _filteredNames.Add(_row[fieldName].ToString());

            }

            // Remember to dispose of the PSI objects!

            _projectPsi.Dispose();

            _projectDs.Dispose();

            return _filteredNames;

        }

        public static List<string> LinqFilterTaskNames(Guid projectGuid, string fieldName, string filterValue)

        {

            List<string> _filteredNames = new List<string>();

            // Set up PSI Reference

            ProjectPSI.Project _projectPsi = CreatePsiReference();

            // Retrieve Project Data

            ProjectPSI.ProjectDataSet _projectDs = _projectPsi.ReadProjectEntities(projectGuid, 2, ProjectPSI.DataStoreEnum.PublishedStore);

            // Filter using LINQ

            var _query = from r in _projectDs.Task.AsEnumerable()

                        where r.Field<string>(fieldName) == filterValue

                        select r.Field<string>(fieldName);            

            // Read filtered data into return collection

            foreach (var _row in _query)

            {

                _filteredNames.Add(_row);

            }

            // Remember to dispose of the PSI objects!

            _projectPsi.Dispose();

            _projectDs.Dispose();

            return _filteredNames;

        }

        private static ProjectPSI.Project CreatePsiReference()

        {

            ProjectPSI.Project _projPsi = new ProjectPSI.Project();

            _projPsi.Credentials = CredentialCache.DefaultCredentials;

            _projPsi.Url = "http://epm2007demo/Litware/_vti_bin/PSI/Project.asmx";

            return _projPsi;

        }

    }

}

Using DataTable.Select() to Filter Task Data

The SelectFilterTaskNames() method (lines 12-36) is actually pretty simple. My implementation takes three parameters:

• a Guid named projectGuid, which represents the projectUid of the individual project you wish to retrieve data for and is used by the ReadProject() method
• a string named fieldName, which represents the ProjectDataSet.TaskRow column you wish to use for filtering and is used in several places
• a string named filter, which is used for the filterExpression parameter of the DataTable.Select() method

t into the code.

First, on line 14, I create a generic List of type string called _filteredNames, which I use to hold the data that this method will return to its caller. Since I’m only planning on using the methods in this class to filter and retrieve task names, this is sufficient. However, if you are going to use this code to do other things, you really should either develop a more intelligent filter parser or be prepared to write a lot of method overloads.

List<string> _filteredNames = new List<string>();

Next, on lines 16-20, I call a private method named CreatePsiReference() to create my instance of the ProjectPSI.Project() proxy class. I then retrieve the project data from the Published store by using ReadProject(). This code will also work with the ReadProjectEntities() method, as demonstrated in the LINQ sample.

// Set up PSI Reference

ProjectPSI.Project _projectPsi = CreatePsiReference();

// Retrieve Project Data

ProjectPSI.ProjectDataSet _projectDs = _projectPsi.ReadProject(projectGuid, ProjectPSI.DataStoreEnum.PublishedStore);

The code for the CreatePsiReference() method is pretty simple – all it does is instantiate the proxy class, set up the credentials, and set its URL. You can get into more complicated things here like impersonation and so forth, but I’m not including that in this sample. However, if you would like to see a sample that does include impersonation, please leave a comment!

private static ProjectPSI.Project CreatePsiReference()

{

    ProjectPSI.Project _projPsi = new ProjectPSI.Project();

    _projPsi.Credentials = CredentialCache.DefaultCredentials;

    _projPsi.Url = "http://epm2007demo/Litware/_vti_bin/PSI/Project.asmx";

    return _projPsi;

}

Lines 22-29 are the meat of this sample. On line 23, I take the filter parameter that’s being passed into this method and use it for the filterExpression parameter of the DataTable.Select() method. Then, the foreach loop iterates through the returned array of DataRow objects, removes the data contained in the column denoted by the fieldName parameter, and inserts it into the previously created _filteredNames generic list collection.

// Filter Project Data

DataRow[] _returnedRows = _projectDs.Task.Select(filter);

// Read filtered data into return collection

foreach (DataRow _row in _returnedRows)

{

    _filteredNames.Add(_row[fieldName].ToString());

}

The crucial takeaway from this sample is that the DataTable.Select() method returns an array of regular DataRow objects. If you plan to take these rows and add them into another ProjectDataSet.TaskDataTable object, you will need to cast them back into ProjectDataSet.TaskRow objects.

To end the method, I clean up my PSI objects and return the List<string> object.

// Remember to dispose of the PSI objects!

_projectPsi.Dispose();

_projectDs.Dispose();

return _filteredNames;

That’s it! Pretty simple, right? Now, I’ll show you how to use LINQ to do the same thing.

Using LINQ to Filter Task Data

The LinqFilterTaskNames() method is very similar to the SelectFilterTaskNames() method that we previously examined. However, there are some very important differences. The first one comes on line 46, where I use the ReadProjectEntities() method instead of the ReadProject() method. There’s no particular reason for this other than my desire to show you the syntax of both methods. Notice the addition of the ProjectEntityType parameter, which is explained in the documentation for the ReadProjectEntities() method.

// Retrieve Project Data

ProjectPSI.ProjectDataSet _projectDs = _projectPsi.ReadProjectEntities(projectGuid, 2, ProjectPSI.DataStoreEnum.PublishedStore);

The other major difference is that this time around we are going to use LINQ instead of DataTable.Select(). This change comes on lines 46-51.

// Filter using LINQ

var _query = from r in _projectDs.Task.AsEnumerable()

             where r.Field<string>(fieldName) == filterValue

             select r.Field<string>(fieldName);            

If you’ve never used LINQ before, you’re in for a treat – it really does make life a lot easier. Unfortunately, LINQ uses many new features of C# 3.0 and VB.NET 9.0 that make it rather complicated to explain in brief. However, Microsoft has an excellent LINQ primer on MSDN. The ADO.NET team also has an excellent introductory post on querying DataSet objects using LINQ, which is what I am doing in this sample.

Here’s the console app I used to test the application – it’s pretty straightforward, so I won’t go through it.