How to: Create a PSI Extension to Read Custom Fields in the RDB

  • Article

Applies to: Office 2010 | Project 2010 | Project Server 2010 | SharePoint Server 2010

In this article
Developing the ListProjects PSI Extension
Installing the ListProjects PSI Extension
Testing the ListProjects PSI Extension

Using a Project Server Interface (PSI) extension that queries the Reporting database (RDB) in Microsoft Project Server 2010 is the simplest way to list all projects that have a specified value of a custom field. The code example in this article uses a custom stored procedure in the RDB and returns a ProjectDataSet that contains a row in the Project table for each project that satisfies the condition. The ListProjects PSI extension can be useful, for example, to list all published projects for a certain program in an organization.

Following are the general steps for creating a PSI extension that lists projects with a specified custom field value:

  1. Create custom field data to be used during development of the stored procedure and the PSI extension. The example uses a text lookup table and a project text custom field (see Procedure 1).

  2. Create a stored procedure named ListProjectsInProgram in the RDB that lists the GUIDs for projects with the specified custom field value (see Procedure 2). A stored procedure is sometimes referred to as a SPROC.

  3. Create a PSI extension named ListProjects that calls the ListProjectsInProgram stored procedure (see Procedure 3).

  4. Create a method in the PSI extension that uses the ReadProject method in the Project service of the PSI to get a ProjectDataSet for each project GUID that is returned by the stored procedure (see Procedure 4).

  5. Install the ListProjects PSI extension (see Procedure 5).

  6. Develop a simple application that tests the PSI extension, by using the Windows Communication Foundation (WCF) interface (see Procedure 6).

This article does not describe all of the steps that are necessary to create and install a PSI extension. For an introduction to developing PSI extensions, see Creating a PSI Extension for Project Server 2010. For more information about custom field data in the RDB, see Custom Fields and the Reporting Database.

For a sample application that uses the ListProjects PSI extension, see Walkthrough: Creating a Project Server Web Part with a JS Grid.

Procedure 1. To create custom field data for the PSI extension

  1. In Project Web App, create a text lookup table that the custom field will use. For example, create a lookup table named Optics Program, which has the values Assembly, Coatings, Design, Machining, and Optical Fabrication.

  2. Create a project text custom field named Optics Program that uses the lookup table that you just created. Do not choose a default value, and do not require that the custom field contain data.

  3. Restart Microsoft Project Professional 2010, open the Project Server account, create several test projects, and then choose a value for the Optics Program custom field in the Project Information dialog box for each project. For example, create three projects that have the same Optics Program value, and two projects that have a different value. Save and publish the projects.

When you publish the test projects, the Report Data Service in Project Server adds the Optics Program column to the MSP_EpmProject_UserView view in the RDB. If you had used a task custom field, the custom field data would be added to MSP_EpmTask_UserView. It is much easier to query an RDB view for custom field data than to create multiple JOIN statements for the RDB tables, or to use the many methods it would require to get the same data from the Project service, LookupTable service, and CustomFields service in the PSI.

After the custom field data exists for testing several projects, you can create and test the ListProjectsInProgram stored procedure.

Procedure 2. To create the ListProjectsInProgram stored procedure

  1. In Microsoft SQL Server Management Studio on the Project Server computer, browse to the RDB, expand the Programmability\Stored Procedures node, and then right-click to create a stored procedure.

  2. To query MSP_EpmProject_UserView for projects with a specified value for the Optics Program custom field, copy the following code into the stored procedure. The name of the stored procedure is ListProjectsInProgram. The data type of the @cfValue parameter is nvarchar(4000), because that is the SQL data type of text custom fields.

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE ProjectServer_Reporting
GO

CREATE PROCEDURE dbo.ListProjectsInProgram 
    --Parameter: Custom field value
      @cfValue nvarchar(4000) 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ProjectUID, ProjectName
    FROM MSP_EpmProject_UserView
    WHERE [Optics Program] = @cfValue
END
GO

    Although ProjectUID is the only field that is necessary to return, the stored procedure also returns the ProjectName field to help in verifying correct values in tests.

  3. Run the query (press F5) to create the stored procedure.

  4. Refresh the Object Explorer pane in SQL Server Management Studio, right-click dbo.ListProjectsInProgram in the Stored Procedures folder, and then click Execute Stored Procedure.

  5. In the Execute Procedure dialog box, type a value for the @cfValue parameter, and then click OK. For example, type Optical Fabrication in the Value column.

SQL Server Management Studio creates a query that uses the ListProjectsInProgram stored procedure, and then runs the query. In the Results pane, the query returns the ProjectUID field and the ProjectName field of all published projects that contain Optical Fabrication in the Optics Program custom field.

After you verify that the stored procedure works correctly, you can develop the ListProjects PSI extension.

Developing the ListProjects PSI Extension

The WCF service contract for the ListProjects PSI extension is defined in the IListProjects interface. The extension has one public method, named GetProjectsWithCustomFieldValue, which uses five parameters. The parameters specify the SQL Server and RDB name, the name of the stored procedure and the parameter name for the custom field value, and the value of the custom field itself.

The ListProjectsInProgram stored procedure includes the custom field name, which is not available as a parameter. To use a different custom field, you must write a different stored procedure. However, the ListProjects extension can specify which stored procedure to call.

Procedure 3. To define the IListProjects interface

  1. In Microsoft Visual Studio 2010, create a WCF service library project named ListProjects.

    Note

    The target framework for the project must be the .NET Framework 3.5.

  2. Delete the App.config file, rename the IService1.cs file to be IListProjects.cs, and then rename the Service1.cs file to be ListProjects.cs. Click Yes in each dialog box that asks whether you want to rename all references to the code element.

  3. Select the ListProjects namespace name in the IListProjects.cs file, right-click the name, and then click Refactor. Change the name to another value, for example, Microsoft.SDK.Project.Samples.ProjectCF. Visual Studio also changes the default namespace name in the ListProjects project properties.

  4. Add the wcf.Project.cs proxy file for the Project service to the project. The GetProjectsWithCustomFieldValue method in the ListProjects PSI extension returns a ProjectDataSet object, which is defined in the SvcProject namespace in the proxy file.

    The wcf.Project.cs file is installed with the Project 2010 SDK, in the Documentation\Intellisense\WCF\Source subdirectory. For more information, see the section Adding a PSI Proxy File in Prerequisites for WCF-Based Code Samples.

  5. Delete the generic code in the IListProjects.cs file, and then copy in the following code. The WCF service contract is IListProjects, and the operation contract is the GetProjectsWithCustomFieldValue method that has five parameters.

    using System;
using System.ServiceModel;

namespace Microsoft.SDK.Project.Samples.ProjectCF
{
    [ServiceContract]
    internal interface IListProjects
    {
        [OperationContract]
        SvcProject.ProjectDataSet GetProjectsWithCustomFieldValue(
            string sqlServer,   // Name of SQL Server instance.
            string rdbName,     // Reporting database name.
            string sproc,       // Stored procedure name.
            string sprocParam,  // Exact name of the parameter in the stored procedure.
            string cfValue      // Custom field value.
            );
    }
}

After you define the IListProjects interface, you can implement the ListProjects class.

The GetProjectsWithCustomFieldValue method calls the ListProjectsInProgram stored procedure, stores the returned data in a simple ProjectListDataSet object that contains one Projects table, and then uses each project GUID to call the ReadProject method in the Project service of the PSI. GetProjectsWithCustomFieldValue then imports the temporary ProjectDataSet that is returned from ReadProject to the final ProjectDataSet for the returned value.

Because the GetProjectsWithCustomFieldValue method returns a ProjectDataSet object, the solution includes the wcf.Project.cs file for the Project service proxy. The SetClientEndpoint method programmatically sets the WCF endpoint for the ProjectClient object, rather than requiring additional modification of the web.config file for Project Web App. By using the HttpContext object that is set in the ListProjects constructor, the GetServiceUri method gets the URI of the ListProjects service, for use in the SetClientEndpoint method.

Procedure 4. To implement the ListProjects class

  1. Add the following references, which are used for DataSet operations and getting the HTTP context:

    • System.Data.DataSetExtensions

    • System.Web

  2. Open the ListProjects.cs file, delete all the content, and then copy in the following code. The ListProjects class implements the IListProjects interface. The class constructor sets the context variable to the current HTTP context, which contains information that can be used to get the Project Web App URL.

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using System.Security.Principal;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web;

namespace Microsoft.SDK.Project.Samples.ProjectCF
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    internal class ListProjects : IListProjects
    {
        private static SvcProject.ProjectClient projectClient;
        private static HttpContext context;

        public ListProjects()
        {
            context = HttpContext.Current;
        }
    }
}

  3. Create a region for supplementary methods, for example the Helpers region. The GetAdapter method creates a SQLDataAdapter object to call the ListProjectsInProgram stored procedure.

    #region Helpers

private static SqlDataAdapter GetAdapter(string sqlServer, string rdbName, 
    string sproc, string sprocParam, string cfValue)
{
    string connection = string.Format("Data Source={0};Database={1};Integrated Security=SSPI",
        sqlServer, rdbName);
    SqlConnection cn = new SqlConnection(connection);

    SqlCommand command = new SqlCommand(sproc, cn);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter param = new SqlParameter(sprocParam, SqlDbType.NVarChar, 4000);
    param.Value = cfValue;
    command.Parameters.Add(param);

   return new SqlDataAdapter(command);
}

#endregion

  4. Add a method in the Helpers region that gets the URI of the ListProjects.svc service activation file. The GetServiceUri method gets the Request.URL object (which is actually of type Uri) from the HTTP context. If you simply used the statement, requestUri = context.Request.Url;, the value of requestUri would be https://localhost/_vti/bin/PSI/ListProjects.svc, which is missing the name of the Project Web App instance in the address.

    static Uri GetServiceUri()
{
    var requestUri = context.Request.Url;
    int portNum = 80;
    var portString = requestUri.GetComponents(UriComponents.Port, UriFormat.SafeUnescaped);

    if (!string.IsNullOrEmpty(portString))
    {
        portNum = int.Parse(portString);
    }

    // Build the URI of the extension service. 
    // Following are values of the UriBuilder parameters:
    //     Scheme (typically): http or https
    //     Host: ServerName
    //     Port: when the port value is 80, it does not show in the URL
    //     PathValue: /ProjectServerName/_vti_bin/PSI/ListProjects.svc
    var uriBuilder = new UriBuilder(
        requestUri.GetComponents(UriComponents.Scheme, UriFormat.SafeUnescaped),
        requestUri.GetComponents(UriComponents.Host, UriFormat.SafeUnescaped),
        portNum,
        context.Request.RawUrl);

    return uriBuilder.Uri;
}

  5. Add the SetClientEndpoint method in the Helpers region, to initialize the projectClient class variable. The endpoint is set programmatically, to avoid making additional changes to the web.config file for Project Web App.

    private static void SetClientEndpoint()
{
    // The maximum size constant must match the MAXSIZE value in the App.config 
    // file of the calling application.
    const int MAXSIZE = 500000000;
    const string PSI_SHARED = "_vti_bin/PSI/";
    const string ROUTER_SERVICE = "_vti_bin/PSI/ProjectServer.svc";

    Uri serviceUri = GetServiceUri();

    string serviceUrl = serviceUri.ToString();

    int indexOfPsi = serviceUrl.IndexOf(PSI_SHARED);
    string pwaUrl = serviceUrl.Remove(indexOfPsi);
    string routerService = pwaUrl + ROUTER_SERVICE;

    BasicHttpBinding binding = null;

    if (serviceUri.Scheme.Equals(Uri.UriSchemeHttps))
    {
        // Create binding for HTTPS.
        binding = new BasicHttpBinding(BasicHttpSecurityMode.Transport);
    }
    else
    {
        // Create binding for HTTP.
        binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);
    }

    binding.Name = "basicHttpConf";
    binding.AllowCookies = true;
    binding.MessageEncoding = WSMessageEncoding.Text;

    binding.OpenTimeout = TimeSpan.FromHours(1);
    binding.ReceiveTimeout = TimeSpan.FromHours(1);
    binding.SendTimeout = TimeSpan.FromHours(1);

    // If the TransferMode is buffered, the MaxBufferSize and 
    // MaxReceived MessageSize must be the same value.
    binding.TransferMode = TransferMode.Buffered;
    binding.MaxBufferSize = MAXSIZE;
    binding.MaxReceivedMessageSize = MAXSIZE;
    binding.ReaderQuotas.MaxArrayLength = MAXSIZE;
    binding.ReaderQuotas.MaxNameTableCharCount = MAXSIZE;

    binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
    binding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.Ntlm;
    binding.Security.Transport.Realm = "";

    // The endpoint address is the ProjectServer.svc router for all public PSI calls.
    EndpointAddress address = new EndpointAddress(routerService);

    projectClient = new SvcProject.ProjectClient(binding, address);
    projectClient.ChannelFactory.Credentials.Windows.AllowedImpersonationLevel
        = TokenImpersonationLevel.Impersonation;
    projectClient.ChannelFactory.Credentials.Windows.AllowNtlm = true;
}

  6. Implement the GetProjectsWithCustomFieldValue method, which is defined in the IListProjects interface. The GetProjectsWithCustomFieldValue method calls the ListProjectsInProgram stored procedure and saves the data in the Projects table of the ProjectListDataSet. It then calls the ReadProject method in the Project service of the PSI, with the GUID of each project in the ProjectListDataSet.Projects table. Finally, the GetProjectsWithCustomFieldValue method imports each ProjectDataSet.ProjectDataTable row from the call to ReadProject into the ProjectDataSet object to be returned.

    public SvcProject.ProjectDataSet GetProjectsWithCustomFieldValue(
    string sqlServer,   // Name of SQL Server instance.
    string rdbName,     // Reporting database name.
    string sproc,       // Stored procedure name.
    string sprocParam,  // Exact name of the parameter in the stored procedure.
    string cfValue )    // Custom field value.
{
    const string PROJECTS_DATATABLE = "Projects";
    const string PROJECTLIST_DATASET = "ProjectListDataSet";

    // Initialize the projectClient variable.
    SetClientEndpoint();

    DataSet projectList = new DataSet(PROJECTLIST_DATASET);

    // Get the list of projects that have the specified custom field value.
    SqlDataAdapter adapter = GetAdapter(sqlServer, rdbName, sproc, sprocParam, cfValue);
    adapter.Fill(projectList, PROJECTS_DATATABLE);

    SvcProject.ProjectDataSet projectDs = new SvcProject.ProjectDataSet();
    SvcProject.ProjectDataSet tempProjectDs = new SvcProject.ProjectDataSet();

    int numRows = projectList.Tables[PROJECTS_DATATABLE].Rows.Count;
    Guid projectUid = Guid.Empty;

    for (int i = 0; i < numRows; i++)
    {
        // Get a temporary ProjectDataSet for each project.
        projectUid = projectList.Tables[PROJECTS_DATATABLE].Rows[i].Field<Guid>(0);
        tempProjectDs = projectClient.ReadProject(projectUid, SvcProject.DataStoreEnum.PublishedStore);

        // Import the ProjectDataTable row to the final ProjectDataSet that will be returned.
        SvcProject.ProjectDataSet.ProjectRow projectRow =
            (SvcProject.ProjectDataSet.ProjectRow)tempProjectDs.Project.Rows[0];
        projectDs.Project.ImportRow(projectRow);
    }
    return projectDs;
}

After you compile the solution, you can install the ListProjects PSI extension.

Installing the ListProjects PSI Extension

Installation of the ListProjects service requires installing the ListProjects.dll assembly in the global assembly cache, creating a service description file, and modifying the web.config file for Project Web App.

Procedure 5. To install the ListProjects extension

  1. Install the ListProjects.dll assembly. For example, start a Visual Studio Command Prompt window as an administrator, and then run the following command:

    gacutil.exe /if ListProjects.dll

  2. Create the ListProjects.svc file in the [Program Files]\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\PSI directory. Set the values of the service attribute as described for the installation procedure in Creating a PSI Extension for Project Server 2010. In the following code, the service attribute is all on one line.

    <%@ServiceHost language="C#" 
    service="Microsoft.SDK.Project.Samples.ProjectCF.ListProjects, ListProjects, Version=1.0.0.0,
        Culture=neutral, PublicKeyToken=f7af26ae4254aba8" 
%>

  3. Modify the web.config file in the same directory. Add the following system.serviceModel element as a child of the configuration element.

    Note

    If you have installed other PSI extensions, add only the service element for Microsoft.SDK.Project.Samples.ProjectCF.ListProjects.

      <system.serviceModel>
    <diagnostics>
      <messageLogging
           logEntireMessage="true"
           logMalformedMessages="true"
           logMessagesAtServiceLevel="true"
           logMessagesAtTransportLevel="true"
           maxMessagesToLog="3000"
           maxSizeOfMessageToLog="4194304"/>
    </diagnostics>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>

    <bindings>
      <basicHttpBinding>
        <binding name="extensionBasicHttpConf"
                 closeTimeout="00:01:00"
                 openTimeout="00:01:00"
                 receiveTimeout="00:10:00"
                 sendTimeout="00:01:00"
                 allowCookies="true"
                 maxBufferSize="4194304"
                 maxReceivedMessageSize="500000000"
                 messageEncoding="Text"
                 transferMode="StreamedResponse">
          <security mode="TransportCredentialOnly">
            <transport clientCredentialType="Ntlm" proxyCredentialType="Ntlm" realm="" />
          </security>
        </binding>
        <binding name="mexHttpBinding">
          <security mode="TransportCredentialOnly">
            <transport clientCredentialType="Ntlm" proxyCredentialType="Ntlm" realm="" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>

    <behaviors>
      <serviceBehaviors>
        <behavior name="PSIExtensionServiceBehavior">
          <serviceDebug includeExceptionDetailInFaults="true" />
          <serviceMetadata httpGetEnabled="true" />
        </behavior>
      </serviceBehaviors>
    </behaviors>

    <services>
      <service name="Microsoft.SDK.Project.Samples.ProjectCF.ListProjects"
               behaviorConfiguration="PSIExtensionServiceBehavior">
        <endpoint address=""
                  binding="basicHttpBinding"
                  bindingConfiguration="extensionBasicHttpConf"
                  contract="Microsoft.SDK.Project.Samples.ProjectCF.IListProjects" />
        <endpoint address="mex"
                  binding="basicHttpBinding"
                  bindingConfiguration="mexHttpBinding"
                  name="mex"
                  contract="IMetadataExchange" />
      </service>
    </services>
  </system.serviceModel>

After the ListProjects service is installed, you can test the service.

Testing the ListProjects PSI Extension

You can quickly test whether the ListProjects service is correctly installed by using Internet Explorer. To test whether the PSI extension behaves as expected, you must create an application that uses the ListProjects service.

Procedure 6. To test the ListProjects extension

  1. Open the service description file in Internet Explorer. For example, open https://localhost/pwa/_vti_bin/PSI/ListProjects.svc. You should see the ListProjects Service page. If you get an error, recheck the installation steps in Procedure 5. In particular, check for the correct namespace name and assembly name in ListProjects.svc, and check the service name and contract in the web.config file.

  2. Create a simple console application that uses the ListProjects service. For a detailed step-by-step procedure for a very similar test application, see Creating a PSI Extension for Project Server 2010.

    1. In Visual Studio, create a console application named Test_ListProjects.

    2. Add a reference to the ProjectServerServices.dll proxy assembly (or add the wcf.Project.cs proxy file), to enable creating a ProjectDataSet object from the Project service.

    3. Add the following references:

      • System.Runtime.Serialization

      • System.ServiceModel

      • System.Data.DataSetExtensions

    4. Set a service reference to the ListProjects service. For example, name the reference SvcListProjects.

      If you are using the Test_ListProjects application in the Project 2010 SDK download, expand the Service References node in Solution Explorer, right-click SvcListProjects, and then click Update Service Reference.

    5. Configure the SvcListProjects.ListProjectsClient object by using an App.config file. To a standard App.config file for Project Server services, add endpoint elements for the ListProjects service, as follows:

          <client>
      <endpoint address="https://localhost/pwa/_vti_bin/PSI/ListProjects.svc"
        behaviorConfiguration="basicHttpBehavior" binding="basicHttpBinding"
        bindingConfiguration="basicHttpConf" contract="SvcListProjects.IListProjects"
        name="basicHttp_ListProjectsService" />
      <endpoint address="https://localhost/pwa/_vti_bin/psi/ListProjects.svc"
        binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_IListProjects"
        contract="SvcListProjects.IListProjects" name="BasicHttpBinding_IListProjects" />
    </client>

    6. Delete all code in the Program.cs file, and copy in the following code. Change the constant values to match your installation.

      using System;
using System.Text;
using System.Windows.Forms;

namespace Test_ListProjects
{
    class Program
    {
        // Change the constant values to match your Project Server installation and
        // custom field name.
        private const string SQL_SERVER = "SERVER_NAME";
        private const string RDB_NAME = "ProjectServer_Reporting";
        private const string SPROC = "ListProjectsInProgram";
        private const string SPROC_PARAM = "@cfValue";

        private const string CF_NAME = "Optics Program";

        static void Main(string[] args)
        {
            string cfValue = string.Empty;
            if (args[0].ToLower() == "/value" && args[1] != null) cfValue = args[1];

            SvcListProjects.ListProjectsClient listProjectsClient = 
                new SvcListProjects.ListProjectsClient("basicHttp_ListProjectsService");

            SvcProject.ProjectDataSet projectDs = new SvcProject.ProjectDataSet();

            projectDs = listProjectsClient.GetProjectsWithCustomFieldValue(
                SQL_SERVER, RDB_NAME, SPROC, SPROC_PARAM, cfValue);

            int numProjects = projectDs.Project.Rows.Count;
            Console.WriteLine(@"Number of projects where [{0}] = ""{1}"": {2}",
                CF_NAME, cfValue, numProjects.ToString());

            Guid projectUid = Guid.Empty;
            string projectName = string.Empty;

            for (int i = 0; i < numProjects; i++)
            {
                projectUid = projectDs.Project[i].PROJ_UID;
                projectName = projectDs.Project[i].PROJ_NAME;
                Console.WriteLine("\t{0}\t{1}", projectUid.ToString(), projectName);
            }

            Console.Write("\nPress any key to exit: ");
            Console.ReadKey(true);

            listProjectsClient.Close();
        }
    }
}

    7. Open the Test_ListProjects Properties page, click the Debug tab, and then add an argument and custom field value for the command-line arguments. For example, type /value "Optical Fabrication".

    8. Compile and run the Test_ListProjects application.

    The console should show output that is similar to the following:

    Number of projects where [Optics Program] = "Optical Fabrication": 3
        66238e47-4f9e-4231-8272-036c4f855c2a    Wideangle Eyepiece 2
        1ea5652f-4573-4922-9838-393e85534727    Wideangle Eyepiece 3
        44397626-3f2e-4dbb-8500-4a4588f05bdc    Wideangle Eyepiece 1

Press any key to exit:

After you verify that the ListProjects PSI extension works correctly, you can create other applications or components that use it. For example, see Walkthrough: Creating a Project Server Web Part with a JS Grid.

See Also

Tasks

Creating a PSI Extension for Project Server 2010

Walkthrough: Creating a Project Server Web Part with a JS Grid

Concepts

Custom Fields and the Reporting Database

Prerequisites for WCF-Based Code Samples