Project Managers PDS Extender

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary

This article describes how to use the Project Managers PDS Extender sample application for Microsoft® Project Server.

Introduction

This article describes how to use the Project Managers Project Data Service (PDS) Extender sample application for Microsoft Project Server. It also describes the steps for setting up the application and describes the important features of the Project Managers PDS Extender, including parsing a request and retrieving data from the Microsoft Project Server database. For a general explanation of how PDS Extenders work, see the Writing a Project Data Service Extender article in the Microsoft Project Software Development Kit (SDK).

The Project Managers PDS Extender

The Project Managers PDS Extender retrieves portfolio data from the Microsoft Project Server database. The extender implements a custom method named ProjectManagerData, which looks up the project managers who are assigned to a particular project. No such method exists as part of the PDS API. You can call the ProjectStatus method to find out which projects are assigned to a particular project manager, but the reverse is not possible. Because the ProjectManagerData method is implemented within a PDS Extender, it appears to the client as if the PDS itself supports this functionality. Thus, the client can reuse existing code to format requests to the PDS and parse returned responses for data.

Installing and Registering the Extender

ProjectManager.vbp is a Microsoft Visual Basic® project containing code that implements the Project Managers PDS Extender. Building the ProjectManager.vbp project creates a COM DLL named ProjectManager.dll. After copying the DLL, you need to do two things: register the extender as a COM component and register the extender with Microsoft Project Server.

To register the extender as a COM component, open a command prompt window, go to the directory containing ProjectManager.dll, and type the following command:

  REGSVR32 ProjectManager.dll

This command registers the component with a ProgID of ProjectManager.Class1.

To register the extender with Microsoft Project Server, locate the following registry key:

  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\10.0\MS Project\ _

       WebClient Server\ProjectServer]

Create the following string-value key:

  "PDSExtension1"="ProjectManager.Class1"

You should first examine the registry for an existing extender named PDSExtension1 so that you don't accidentally unregister it. If the extender PDSExtension1 exists, rename the key by changing the 1 to a 2 or to any other integer value up to 100.

Note that if you modify and replace an existing PDS Extender on the Microsoft Project Server computer, you will need to restart Internet Services to overwrite the DLL.

Parsing the Request

The PDS can call a PDS Extender when the extender implements a public function named XMLRequest, which has the following signature in Visual Basic:

  Public Function XMLRequest( _

      ByVal sXML As String, _

      ByVal sUser As String, _

      ByVal sConnect As String, _

      ByVal lDBType As Long, _

      ByRef nHandled As Integer) _

      As String

The primary parameter is sXML, which contains the XML request string. For the ProjectManagerData method, this request takes the following form:

  <Request>

      <ProjectManagerData>

          <Project>

              <ProjectID>1</ProjectID>

          </Project>

          <Project>

              <ProjectID>2</ProjectID>

          </Project>

      </ProjectManagerData>

  </Request>

The XMLRequest function first checks to see if the XML is valid by using the Microsoft XML Parser (MSXML):

  'Checking if sXML variable is empty.

  'If yes then error message is created...

  If Len(sXML) = 0 Then

      sXMLReply = "<Error>Request string is empty</Error>"

      m_lStatus = STATUS_INVALID_REQUEST

      GoTo PrepareReply

  End If

  

  'Checking if sXML variable is valid XML.

  'If not, then error message is created...

  Set oXMLDocument = New MSXML2.DOMDocument

  oXMLDocument.loadXML sXML

  If oXMLDocument.parseError.errorCode <> 0 Then

      sXMLReply = "<Error>XML Parse error</Error>"

      m_lStatus = STATUS_INVALID_REQUEST

      GoTo PrepareReply

  End If

If the XML request is valid, XMLRequest looks for the <Request> node. Otherwise, it returns a user-defined Status code indicating an invalid request.

  Select Case sNodeName

      'Found the <Request> node

      Case "Request"

          Dim oNode As MSXML2.IXMLDOMNode

          Dim oChildNode As MSXML2.IXMLDOMNode

          For Each oNode In oXMLRoot.childNodes

              sNodeName = oNode.nodeName

              result = oNode.hasChildNodes()

              'There is a child node, now it has to see if it recognizes

              ' it. This loop is the essence of the PDS extension -

              ' add code to recognize the method(s) here...

              Select Case sNodeName

                  'Recognizing child node <ProjectManagerData>

                  Case "ProjectManagerData"

                      sXMLReply = ProjectManagerData(oNode)

                      nHandled = REQUEST_HANDLED

                  'If STATUS_UNKNOWN_REQUEST then assumption is that

                  ' another registered extension can handle the request

                  Case Else

                      m_lStatus = STATUS_UNKNOWN_REQUEST

              End Select

          'If successful in recognizing first xml command then it tries

          ' to recognize all commands

          Next oNode

      Case Else

          m_lStatus = STATUS_UNKNOWN_REQUEST

  End Select

If, for any reason, XMLRequest does not recognize the request, the returned Status code indicates the error. Additionally, XMLRequest sets the nHandled parameter to 0, which notifies the PDS that another extender may be able to handle the request.

Parsing the Method Call

Once the XMLRequest function determines that the request is a call to the ProjectManagerData method, it calls the ProjectManagerData helper function to further parse the request and retrieve the requested data. The ProjectManagerData function has the following signature:

  Function ProjectManagerData( _

      ByVal oNode As MSXML2.IXMLDOMNode) _

      As String

ProjectManagerData receives the XML node whose parent is the <ProjectManagerData> tag. From here, the function looks for the Project ID, by examining the child nodes:

  result = oNode.hasChildNodes()

If the function fails to find the Project ID, PROJECT_ID_TAG_NOT_FOUND is returned. Otherwise, ProjectManagerData continues parsing the request:

  Dim oChildNode As MSXML2.IXMLDOMNode

  For Each oNode In oNode.childNodes

      sNodeName = oNode.nodeName

      . . . examine sNodeName . . .

  Next oNode

The first child node should be named <Project>. If it isn't, ProjectManagerData stops parsing and returns PROJECT_TAG_NOT_FOUND.

If ProjectManagerData does find a <Project> node, it examines the child of that node:

  sNodeName = oNode.firstChild.nodeName

At this point, two checks are made. If the <ProjectID> tag is not found, ProjectManagerData stops parsing and returns PROJECT_ID_TAG_NOT_FOUND. If the Project ID itself is invalid, ProjectManagerData stops parsing and returns PROJECT_ID_NOT_FOUND. Otherwise, we now have a Project ID that we can use to query the Microsoft Project Server database. ProjectManagerData starts building the reply:

  Proj_ID(Count) = oNode.Text

  sXMLReply = sXMLReply & "<ProjectInfo><ProjectID>" & oNode.Text & "</ProjectID>"

  Count = Count + 1

The Count variable has been initialized to 0. Count allows the function to track the number of requested Project IDs. Now we can do the database lookup by calling the following code:

  Dim RES_NAME(10) As String

  SQLCommand = EncodeSQLScript(Count - 1)

  Status = ExecuteSQL(RES_NAME, Counter, SQLCommand)

The RES_NAME variable is an array of resource names, containing each project manager requested (up to 10 project managers). After the query is executed, ProjectManagerData formats the reply:

  While Counter > -1

      sXMLReply = sXMLReply & "<ProjectManager>" & RES_NAME(Counter) & "</ProjectManager>"

      Counter = Counter - 1

  Wend

The Database Query

The database query is found in the EncodeSQLScript function. This function has the following signature:

  Private Function EncodeSQLScript( _

      ByVal Count As Integer) _

      As String

The Count parameter is an index for a global variable named Proj_ID.

  Dim Proj_ID(10) As Integer

By creating an array of Project IDs, the function can return the project managers for up to 10 projects.

The query itself joins data from several tables: MSP_WEB_RESOURCES, MSP_WEB_ASSIGNMENTS, and MSP_WEB_PROJECTS. By joining on a Resource ID, the query determines the names of the project managers. By further joining and filtering for a particular Project ID, the query extracts the project managers for that requested project.

  SQLCommand = "select  distinct( r.RES_NAME ) " & _

      "from (MSP_WEB_RESOURCES r inner join MSP_WEB_ASSIGNMENTS " & _

      "a on r.WRES_ID = a.WRES_ID_MGR) " & _

      "inner join MSP_WEB_PROJECTS p " & _

      "on a.WPROJ_ID = p.WPROJ_ID " & _

      "Where p.Proj_ID = " & Proj_ID(Count)

The query returns a distinct result, so as to avoid duplicates. The query can return multiple project managers, if applicable.

Making Use of Database Connection Information

In addition to the XML request itself, the XMLRequest function also uses the database connection information that the PDS passed to it. In the extender, this information is stored in two global variables. The first variable is the database connection string:

  'Set database access information as global variables

  m_sDBConnect = sConnect

The second variable is the database type:

  Public Enum DatabaseTypeEnum

      dtNotSet = -1

      dtSQLServer = 0

      dtAccess = 1

      dtOracle = 2

  End Enum 

  Private m_eDatabaseType As DatabaseTypeEnum

  m_eDatabaseType = lDBType

This information is used to create a connection to the Microsoft Project Server database before the first lookup is done in ProjectManagerData, which calls the Create function:

  Private Function Create()

      If m_oConnection Is Nothing Then

          Set m_oConnection = New ADODB.Connection

          m_oConnection.Mode = adModeReadWrite

          ' Validate the database type

          Select Case m_eDatabaseType

              Case dtAccess

                  m_oConnection.Open m_sDBConnect

              Case dtSQLServer, dtOracle

                  If m_sDBUser = "" Then

                      m_oConnection.Open m_sDBConnect

                  Else

                      m_oConnection.Open m_sDBConnect, _

                          m_sDBUser, m_sDBPassword

                  End If

              Case Else

                  Exit Function

          End Select

      End If

  End Function

The Project Managers Client Page

A client application calling a PDS Extender method uses the Simple Object Access Protocol (SOAP), a lightweight, XML-based protocol for exchanging information. You need to install the SOAP Toolkit 2.0 (Service Pack 2) to enable your application to call the PDS. More information on SOAP and downloading the SOAP Toolkit can be found at the SOAP Developer Center on MSDN.

A client application invokes PDS method calls as described by a Web Services Description Language (WSDL) document. This file is named PDS.wsdl and is located in the virtual directory root of your Microsoft Project Server installation. The WSDL file describes the SoapXMLRequest method, which is how client applications send their XML requests to the PDS.

The ProjectManagers.asp page can be added to Microsoft Project Web Access to demonstrate using the Project Managers PDS Extender. To use the ProjectManagers.asp page, copy it to your Microsoft Project Server installation as follows:

  1. Create a folder named Custom under the ProjectServer virtual root (for example, C:\Program Files\Microsoft Project Server\IIS Virtual Root).
  1. Copy PDSExtender.asp to the Custom folder.
  1. Open PDSExtender.asp in a text editor, and modify the URLs to point to your Microsoft Project Server by replacing occurrences of myserver with the name of your server.

Next, add the client page to Microsoft Project Web Access:

  1. Log on to Microsoft Project Web Access as a user with Admin privileges.
  1. In the top link bar, click Admin.
  1. In the side pane, click Manage organization.
  1. In the side pane under Organization options, click Menus.
  1. On the Menus page, click Add Custom Menu.
  1. In the Add Custom Menu dialog box, click Add a top level menu.
  1. In the Menu Name dialog box, type the menu name (PDS Extender), and then click OK.
  1. Find the row that contains the top-level menu item that you just created, and select that row.
  1. Click Add Custom Menu.
  1. In the Add Custom Menu dialog box, click Add a Submenu.
  1. In the Submenu Name dialog box, type the name for the submenu.
  1. In the Submenu URL dialog box, type the URL for the submenu, replacing myserver with the name of your server (http://myserver/projectserver/custom/PDSExtender.asp), and then click OK.
  1. Click Save Changes.

To see your changes, log off Microsoft Project Web Access and then log on again. The menu item should be displayed in the top link bar before the Help menu item.

The client page creates a SoapClient object on the server, which needs access to the WSDL document for the PDS. You need to allow anonymous access to the PDS.wsdl and PDS.wsml files by using the Internet Services Manager. For more information on SOAP and Web server security, see the Building Secure Web Services with Microsoft SOAP Toolkit 2.0 article on MSDN.

The client page displays a drop-down list of projects that you have access to. Selecting a project in the list and clicking the GetProjectManagerNames button displays the project manager for that particular project.