Creating a Managed Code PDS Extension for Project Server 2003

Article
07/11/2006

Grant Bolitho
Microsoft Consulting Services
Microsoft New Zealand

February 2004

Applies to:
Microsoft® Office Project Server 2003

**Summary: **Microsoft Office Project Server 2003 uses the Project Data Service (PDS) to provide an extensible architecture. This is ideally suited to enterprise systems where centralized project data must be available to a wide range of clients. This article describes how to build a PDS extension using Microsoft Visual Studio .NET and supplies a base class that makes the building of custom PDS extensions easy. It includes guidance on best practices to follow when extending Project Server. (25 printed pages)

Download pj11ManagedCodePDSExtension.exe.

The download includes the source code for a sample PDS extension, test project, and a registration project.

Extending Project Server
A Simple .NET PDS Extension
Enhancing an Extension with Database Access
Case Study: A Stored Procedure Wrapper
Steps to Write a New PDS Extension Class
Steps to Implement a Method
Steps to Implement a Database Method
Installing a .NET PDS Extension
Making Extensions Easy to Develop
Accessing the PDS from a Client
Best Practices for Project Server Extensions
Conclusion
Additional Resources

Extending Project Server

Microsoft® Office Project Server 2003 is a project management system designed for use in a wide range of organizations, from small companies to large enterprises. It provides a centralized Microsoft SQL Server™-based repository for the storage of all project data. The Project Server architecture allows the traditional thick client and the new Web-based interface to access the same project data.

Most organizations want to be able to customize Project Server to some degree in order to achieve greater integration with their existing financial and reporting systems.

The Project Data Service (PDS) is the API for Project Server. PDS extensions provide the developer with a good mechanism to extend and enhance Project Server functionality. Extensions can easily be written to access and update the existing Project Server database, access new databases, or communicate with other systems and applications using Web services. The flexibility is enormous.

What is the Project Data Service?

Project Server provides standard mechanisms for client applications to read and write project data. This interface is a Web service. Clients communicate via SOAP calls to the PDS over HTTP (or HTTPS); the PDS uses XML to package requests to the server and to return replies to the client. This allows one interface to easily handle any data structure required. You can think of the PDS as being an extensible API for Project Server. The Project Server 2003 PDS Reference (PDSReference.exe) is available from the Microsoft Download Center. It includes programming concepts and descriptions of the built-in methods.

Because you may want more than the built-in functionality, the PDS provides an architecture for custom extensions. The PDS Reference includes the topic Writing a PDS Extender that describe this process, with a PDS Extender template written in Microsoft Visual Basic® 6.0.

When a client application makes a request, the PDS checks whether the request is a built-in method. If so, Project Server executes the method and sends the resulting data in the reply to the client.

If the request is not recognized as a built-in method, then the PDS searches each extension in the order it was registered, for a method name match. Project Server executes the first extender method that matches the request, and sends a reply as if the method were built in. Other PDS extensions with the same method name are ignored. Failure to locate a method name match returns a Request Invalid (error 2) to the client in the STATUS element of the reply message.

PDS extensions are implemented as COM objects that have a standard entry method and parameter list.

Accessing Project Server Data

The Project Server 2003 database can be accessed directly using ADO and an appropriate SQL Server connection string. This access method is not recommended as a best practice for a number of reasons:

Changes to data could be made that would interfere with client access or corrupt the database.
Direct database access does not sufficiently abstract the data storage from the client view. A client that relies on direct database access will not be compatible with future versions of Project Server.
ADO and SQL Server communicate using a network sockets library that uses port 1433. Most security-conscious organizations deem it an unacceptable security risk to open port 1433 on the firewall. They are normally only prepared to open ports 80 and 443 (SSL).

Using a PDS extension is the recommended practice because:

Data extraction and processing complexity can be hidden from all clients.
PDS uses HTTP over port 80 (or SSL over port 443, if that is configured) for data transfer. It is unlikely that any changes will need to be made to firewall rules.
It is easy to add a new method to a PDS extension and make these available to all clients.
The security implementation can be hidden.

Functionality Required in a PDS Extension

To access the PDS, a client sends an HTTP request with a packet formatted as follows:

<Request>
   <MethodName>
      -- More data for the request goes here --
   </MethodName>
</Request>

PDS extensions for Project Server 2003 expose one public method called XMLRequestEx. The PDS Reference explains the parameters in detail, and also explains the Microsoft Project Server 2002-style public method XMLRequest, which Project Server 2003 still supports.

public string XMLRequestEx(string sXML, string sPDSInfoEx, 
                           ref short nHandled)

Data in the parameters include the sXML string that contains the XML packet and the sPDSInfoEx string, which is parsed to extract the user name, security cookie or HTTP request, and other data necessary to make a request. The PDS calls XMLRequestEX when it is trying to establish the location and availability of a specified method name.

XMLRequestEX inspects the XML packet to decide if it will process the specified method name. If the correct method is located, then it can be executed, and any additional data in the request packet can be processed.

One XMLRequestEX method is required for every PDS extension class. Each XMLRequestEX can process any number of PDS methods by using a case statement.

Upon completion, the PDS requires the extender method to return an XML reply packet that is formatted in the following way:

<Reply>
   <HRESULT></HRESULT>
   <STATUS></STATUS>
   -- More data elements for the reply --
</Reply>

If an error occurs in the method, then this must be signaled by setting the STATUS element to a non-zero value. Status error code values are documented in the Error Codes topic of the PDS Reference. It is recommended that you use an existing error value if the error exactly matches the meaning of a documented error; otherwise, assign new custom error codes with a value of 10000 or more.

If the requested method does not exist in the class, then XMLRequestEX must return REQUEST_NOT_HANDLED in the nHandled parameter. This causes the PDS to search in the next class for the list of registered PDS extensions. Up to 200 extensions can be registered for use in Project Server 2003, 100 of them for the legacy 2002-style extensions and 100 for the 2003-style extensions.

Why Build a .NET PDS Extension?

In the past, Project Server extensions were written using Microsoft Visual Basic 6.0. Many organizations now choose Microsoft Visual Studio® .NET as the preferred development environment.

Developing PDS extensions with .NET offers a number of advantages:

The Microsoft .NET Framework offers a very rich set of classes that can be used to manipulate databases and XML data, and communicate via Web services to other systems.
Class inheritance makes it possible to build a base class that encapsulates and hides all the PDS extension plumbing. This makes it easier than ever to build custom PDS extensions.
Other managed code assemblies can be easily called.
The Visual Studio .NET development environment is highly productive.

A Simple .NET PDS Extension

Writing PDS extensions in Microsoft Visual C#® .NET or Visual Basic .NET is easy when using the Extender base class that is provided with this article. The code in these examples is written in C#; Visual Basic .NET provides equivalent functionality and could also be used.

The sample code below shows a method called Ping, which returns a simple string to the client. The Ping method models the same functionality as the PDS Extender sample in PDS Reference, which is written using Visual Basic 6.0. You will find that the Visual C# .NET version is significantly simpler and easier to write and understand, since it uses a base class that implements many of the details. Some comments are added that do not appear in the code sample file, and are explained more fully in the following sections in this article.

For a discussion of COM and .NET interoperability, see Introduction to COM Interop.

The following sample PDS extension is in the General.cs file of the PdsExtension project. The #region and #endregion directives mark blocks of code that you can expand and collapse in the Visual Studio code editor.

using System;
using System.Runtime.InteropServices;
// Other using directives go here; see the sample code 

namespace Microsoft.ProjectServer.Extension
{
   /* The declaration in [brackets] below is an attribute that enables
    * interoperability between COM and .NET objects
   */
   [Guid("EAA49654-65E3-4BC5-BCC7-E465264323D5"),
   ComVisible(true),
   ClassInterfaceAttribute(ClassInterfaceType.AutoDual)]

   // The General class derives from the Extender class.
   public class General : Extender
   {
      #region Constructors
      public General()  // The class constructor is empty here.
      {
      }
      #endregion

      #region XMLRequestEx Method
      [ComVisible(true)]
      public string XMLRequestEx(string sXML, string sPDSInfoEx, 
                                 ref short nHandled)
      {
         if (IsRequestError(sXML, sPDSInfoEx, ref nHandled))
            return ReplyInvalidRequestError();

         switch (Command) 
         {
            case "GeneralPing":
               return Reply("GENERAL PING WORKED!");
            // Other cases go here
            default:
               return ReplyUnknownRequestError(ref nHandled);
         }
      }
      #endregion
      // Other private methods go here
   }
}

Let's examine the sample code in more detail:

The sample class is called General and is derived from the Extender class. The Extender class will be discussed in more detail later. The General class is attributed with the following:

A GUID is used for the COM interface registration. This GUID can be generated with a Microsoft utility called Uuidgen. It can also be generated using .NET classes, for example:
```
string GuidString = System.Guid.NewGuid().ToString();
```
ComVisible(true) makes the managed class visible to COM (this attribute is not actually required since it is available by default).
ClassInterfaceAttribute(ClassInterfaceType.AutoDual) indicates that an AutoDual interface will be generated and exposed to COM. This ensures that the .NET object behaves just like a COM object generated by Visual Basic 6.0.

The General class requires a reference to System.Runtime.InteropServices because the .NET object is also being exposed as a COM object.

The one and only required public method is XMLRequestEX(). It must be defined exactly as shown (except of course for the optional white space).

   public string XMLRequestEx(string sXML, string sPDSInfoEx, 
                              ref short nHandled)

The parameters are:

sXML – The full XML request packet including the request and method name elements.
sPDSInfoEx – This is an XML string that has the following syntax:
```
<PDSExtensionXML>
    <UserName></UserName>
    <UserGUID></UserGUID>
    <UserID></UserID>
    <PDSConnect></PDSConnect> 
    <BasePath></BasePath>
    <DBType></DBType>
    <SOAPRequestCookie></SOAPRequestCookie>    
    <HTTPRequestCreate></HTTPRequestCreate>    
</PDSExtensionXML>
```
Following are descriptions of the elements in the sPDSInfoEx string:

*UserName   *Required. Name of the currently logged-on user.

*UserGUID   *Required. Global unique identifier of the currently logged-on user.

*UserID   *Required. ID of the currently logged-on user.

*PDSConnect   *Required. Connection string used by the PDS for the Project Server database.

*BasePath   *Required. Name of the Project Server virtual directory; for example:

   http://MyServer/ProjectServer

*DBType   *Required. The database type is always set to 1, which is for SQL Server.

*SOAPRequestCookie   *Optional. Only included if the request is made via SOAP.

*HTTPRequestCreate   *Optional. Only included if the request is made via HTTP POST (the request uses PDSRequest.asp).
nHandled – Used to signal the PDS how the method has been handled in the call to XMLRequest. There are two predefined values; REQUEST_HANDLED which is used if the correct method was found and executed, or REQUEST_NOT_HANDLED if further searching is required by Project Server for the correct extension method. If this is the last PDS extension in the registration chain, then setting Handled to REQUEST_NOT_HANDLED causes a RequestUnknown (STATUS = 1) to be returned to the client.

The following sample shows how a GeneralPing method would be called in an XML request packet:

<Request>
   <GeneralPing>
   </GeneralPing>
</Request>

The GeneralPing method element could be shortened to <GeneralPing /> if no child elements were required.

The Reply() method in the base class returns the response from GeneralPing. Reply() packages the message with the required HRESULT and STATUS elements. The response is:

<Reply>
   <HRESULT>0</HRESULT>
   <STATUS>0</STATUS>
   <GeneralPing>GENERAL PING WORKED!</GeneralPing>
</Reply>

The .NET PDS Extension Base Class

The Extender base class in the PDS Extension project was designed to meet the following requirements:

Make it as easy as possible to create a custom PDS extension.
Minimize the amount of code required for a custom PDS extension.
Simplify the parsing of an XML request packet and extraction of additional data.
Simplify the creation of an XML reply packet and the incorporation of result data.
Make error handling and reply status management transparent wherever possible.

Now that you see how easy it is to create a new PDS extension, let's have a look at what is really going on in the base class.

Handling Request Properties

The Extender base class method IsRequestError() does extensive integrity checking of the incoming XML request packet and prepares the base class for access to the additional data. IsRequestError() returns true if any error occurs, and then ReplyInvalidRequestError() packages an appropriate error number and message in an XML reply packet for return to the client. This greatly simplifies the request preparation.

The Command property of the Extender class****contains the method name that was specified in the request message. You can use a switch statement to provide as many method implementations as desired in your PDS extension class that is derived from Extender.

The default case for the switch statement calls ReplyUnknownRequestError() which sets the handled value to REQUEST_NOT_HANDLED for return to the PDS. The PDS then looks for another PDS extension to call.

If your Project Server customization is complex and contains many methods, then you might consider breaking the functionality into two or more PDS extension classes. Each PDS extension maps to one .NET class. This means that you can package two or more PDS extensions in one assembly. Each class must be separately registered.

A typical request that has additional data would look like this:

<Request>
   <Test>
      <ProjectID>1</ProjectID>
      <ResourceID>2</ResourceID>
      <Task Name='Meeting' Duration='50' />
      <Resources>
         <Resource>Matthew</Resource>
         <Resource>Sally</Resource>
         <Resource>Wendy</Resource>
      </Resources>      
   </Test>
</Request>

Request data can be accessed with one of the overloaded RequestProperty() methods. A RequestProperty() method can be called any time and in any order after IsRequestError() is called.

The ResourceID value in the request example could be extracted using the RequestProperty*(string Name)* method, which would return the string "2".

string text = RequestProperty("ResourceID");

The Task attribute values can be extracted using the RequestProperty(string Name, int Index, string Attribute) method. A request for the Name attribute would return the string "Meeting", and a request for the Duration attribute would return the string "50".

string text = RequestProperty("Task", 1, "Name");
string text = RequestProperty("Task", 1, "Duration");

A specific Resource can be selected with an XPath expression in the Name attribute of the RequestProperty(string Name, int Index) method. The following call would return the string "Sally".

string text = RequestProperty("Resources/Resource", 2);

A list can be traversed using a For loop. The following code returns a list of last names.

for (int Index = 1; Index <= RequestPropertyCount("Resources/Resource"); 
     Index++)
{   
string text = RequestProperty("Resources/Resource", Index, "Name");
   ProcessData(text);
}

Strings are always returned by calls to RequestProperty(). A string can be converted to a specific data type using static methods of the Convert class.

int ResourceId = Convert.ToInt32(RequestProperty("ResourceID"));

Remember that Convert will throw an exception if the client passes bad data. You should catch all exceptions with try/catch blocks, and either handle them or use ReplyError() to gracefully return error information.

The PDS Thread Locale

The PDS thread runs in the context of an account that depends on the version of Internet Information Services (IIS).

IIS 5.x: The account is that of the user who makes the PDS call, and IIS uses Integrated Windows Authentication (formerly called NTLM, or Windows NT Challenge/Response authentication).
IIS 6.x: Uses the Network Service security account for the Project Server application pool. To see the account, open the IIS Manager, expand the Application Pools node, right-click on the node MSPS2003AppPool, and select Properties. In the MSPS2003AppPool Properties dialog, click the Identity tab.

If this account does not have a specific profile on the server, it will use the locale settings from the default profile. The default profile may have a locale setting that is different from your own account.

Locale differences can exist between the development environment, where the extension runs under the developer's account profile, and the Project Server PDS environment where the extension runs under the account noted. Conversions for dates, times, and some data types may give unexpected results with differences in locale.

If you have trouble with different locale dates, check the date formats in the registry under HKEY_USERS\.DEFAULT\Control Panel\International. Change sShortDate and sTimeFormat as required.

Handling Malformed XML Data in a Property

Occasionally, you may need to send data to the server that will corrupt the XML request packet. Examples can include some URL strings and data such as HTML, XML, or XSL. In this situation, you should package the malformed data as CDATA as shown in the packet below.

<Request>
   <Test>
      <Data><![CDATA[</malformed!</malformed</malformed & worse>]]></Data>
   </Test>
</Request>

Calling UnwrapCdata() will extract the CDATA from the request:

string text = UnwrapCdata(RequestProperty("Data"));

This call returns the following string:

</malformed!</malformed</malformed & worse>

Data can also be wrapped as CDATA in the reply using a call to WrapCdata(), as follows:

return Reply(WrapCdata(Connect));

Generating Reply Properties

Reply properties offer a convenient way to package data for return to the client.

The Reply() method is the simplest method that can be used to return data to the client where the only data to be returned takes the form of a simple string. This can be used for XML data generated by an XmlWriter object or a database stored procedure that uses FOR XML RAW | AUTO | EXPLICIT. Calling Reply() assumes there are no errors and generates the appropriate HRESULT and STATUS values.

Reply ("Reply text");

More complex reply structures can be built using ReplyStart(), ReplyProperty(), and ReplyEnd();.

The following code. . .

ReplyStart(0);
ReplyProperty("ProjectID", "3");
ReplyProperty("Name", "Matthew");
return ReplyEnd();

. . .generates the following XML reply packet:

<Reply>
   <HRESULT>0</HRESULT>
   <STATUS>0</STATUS>
   <ProjectID>3</ProjectID>
   <Name>Matthew</Name>
</Reply>

Enhancing an Extension with Database Access

Most PDS extensions access the Project Server database table at some stage, so a database helper class is provided to make this task easier.

The Database class in the PdsExtension project makes provision for connection-based transaction processing so that multiple stored procedures can be called as part of one transaction.

The connection string supplied by XMLRequest is not in the correct format for the .NET SqlConnection class. OpenConnection() parses and converts the XMLRequest format into a form that is suitable for this class.

Commands can be executed that have been defined as literal data manipulation language (DML) or data definition language (DDL) text, or as a specified stored procedure name.

Stored procedure parameters can be defined and loaded with values.

A range of different patterns exist for return data including scalar values, data BLOBs and XML data using FOR XML RAW | AUTO | EXPLICIT. Data can also be returned from a stored procedure using OUT parameters.

The Database class also provides functions such as ToInteger() that cast results from ExecuteScalarProcedure() and ExecuteScalarQuery() to the desired data type.

Case Study: A Stored Procedure Wrapper

This case study describes a PDS extension method that calls a generic database stored procedure. There are two examples: Sample 1 shows a simple stored procedure with no additional parameters, and Sample 2 shows how to call a stored procedure with a parameter.

Sample 1: Simple Stored Procedure

Sample 1 calls a stored procedure to display all the Project Server groups to which each project resource belongs.

The request is:

<Request>
   <XmlStoredProcedure>
      <Name>PdsResourceMembershipOfGroups</Name>
   </XmlStoredProcedure> 
</Request>

This stored procedure passes no parameters. The PDS extension code that handles this call is in the General class in the PdsExtension project. In XMLRequestEx(), the following case statement handles the request:

   case "XmlStoredProcedure":
      return StoredProcedure(PDSConnect, true);

The StoredProcedure() method returns the result of calling the specified stored procedure.

private string StoredProcedure(string Connect, bool IsXml)
{
   try
   {
      Database data = new Database(Connect);
      string name = RequestProperty("Name");
         
      for (int parameter = 1; 
parameter <= RequestPropertyCount("Parameters//Parameter"); 
parameter++)
      {
         string parameterName = RequestProperty("Parameters//Parameter", 
            parameter, "Name");
         string parameterValue = RequestProperty("Parameters//Parameter", 
            parameter, "Value");

         if (parameterName.Length > 0)
            data.Parameter(parameterName, SqlDbType.VarChar, 255, 
               parameterValue);
      }
      string result = "";

      if (IsXml)
         result = data.ExecuteXmlProcedure(name);
      else
         result = data.ExecuteDataProcedure(name);
   
      data.CloseConnection();
      return Reply(result);
   }
   catch (Exception Ex)
   {
      return ReplyError(1, Ex.Message);
   }
}

The PdsResourceMembershipOfGroups stored procedure is:

CREATE PROCEDURE [dbo].[PdsResourceMembershipOfGroups] 
AS
-- 1. First level of the hierarchy.
SELECT     
   1 AS Tag, 
   NULL AS Parent,
   ResourceId  [Resource!1!Id],
   ResourceName [Resource!1!Name],
   NULL AS [GroupName!2!Name]
FROM
   PdsResourceGroup
UNION 
-- 2. Second level of the hierarchy.
SELECT    
   2 As Tag,
   1 As Parent,
   ResourceId,
   ResourceName,
   GroupName
FROM 
   PdsResourceGroup
ORDER BY 
   [Resource!1!Id], [GroupName!2!Name]
FOR XML EXPLICIT

The preceding stored procedure calls the PdsResourceGroup view which is defined as:

CREATE VIEW dbo.PdsResourceGroup
AS
SELECT dbo.MSP_WEB_RESOURCES.WRES_ID AS ResourceId, dbo.MSP_WEB_RESOURCES.RES_NAME AS ResourceName, 
dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME AS GroupName
FROM dbo.MSP_WEB_RESOURCES INNER JOIN
dbo.MSP_WEB_SECURITY_GROUP_MEMBERS ON 
dbo.MSP_WEB_RESOURCES.WRES_GUID = dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID INNER JOIN
dbo.MSP_WEB_SECURITY_GROUPS ON 
dbo.MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = dbo.MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID

This demonstrates how to build customized views of Project Server data. Full database schema details are available in the file PjSvrDB.htm in the \DOCS folder on the Project Server installation CD, or in the [Program Files]\Microsoft Office Project Server 2003\Help\1033 folder (1033 is the locale ID [LCID] for U.S. English; the folder for localized versions will vary, for example, the LCID for Japanese is 1041). The MSDN article Microsoft Project Server Data Reporting includes examples for the Microsoft Project Server 2002 database; the examples also work for Project Server 2003.

The reply from the PDS extension for the Sample 1 request is:

<Reply>
   <HRESULT>0</HRESULT>
   <STATUS>0</STATUS>
   <XmlStoredProcedure>
      <Resource Id="101" Name="Test">
         <GroupName Name="Administrators"/>
         <GroupName Name="Project Managers"/>
         <GroupName Name="Team Members"/>
      </Resource>
      <Resource Id="102" Name="Test2">
         <GroupName Name="Project Managers"/>
      </Resource>
</XmlStoredProcedure>
</Reply>

Sample 2: Stored Procedure with Parameters

Sample 2 demonstrates passing a parameter that returns the resource names in a specific group.

<Request>
   <XmlStoredProcedure>
      <Name>PdsGroupUsage</Name>
      <Parameters>
         <Parameter Name='@GroupName' Value="Project Managers" />
      </Parameters>
   </XmlStoredProcedure> 
</Request>

The stored procedure is:

CREATE PROCEDURE [dbo].[PdsGroupUsage] 
@GroupName NVARCHAR(100)
AS
SELECT ResourceId, ResourceName
FROM 
   PdsResourceGroup as [Group]
WHERE
   GroupName = @GroupName
FOR XML AUTO

The reply from the PDS extension for the Sample 2 request is:

<Reply>
   <HRESULT>0</HRESULT>
   <STATUS>0</STATUS>
   <XmlStoredProcedure>
      <Group ResourceId="101" ResourceName="Test"/>
      <Group ResourceId="102 ResourceName="Test2"/>
   </XmlStoredProcedure>
</Reply>

In the Sample 2 request the method call to PdsGroupUsage was achieved with a call to XmlStoredProcedure. We can also create a specific PDS method call for this stored procedure. The extension code is in the General class in the PdsExtension project:

   case "GroupUsage":
         return GroupUsage(Connect);

and

private string GroupUsage(string Connect)
      {
         try
         {
            Database data = new Database(Connect);
            string group = RequestProperty("Group");
         
            data.ParameterClear();
            data.Parameter("@GroupName", SqlDbType.VarChar, 255, group);
            string result = data.ExecuteXmlProcedure("PdsGroupUsage");
            
            data.CloseConnection();
            return Reply(result);
         }
         catch (Exception Ex)
         {
            return ReplyError(1, Ex.Message);
         }
      }

The stored procedure and the reply message remain the same, for the general XmlStoredProcedure() or the specific GroupUsage() PDS extension methods.

All stored procedures must have EXECUTE permission set for the MSProjectServerRole:

GRANT  EXECUTE  ON [dbo].[YourStoredProcedureName]  TO [MSProjectServerRole]

All views must also have the correct permissions set:

GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[ YourViewName]  TO [MSProjectServerRole]

At this stage the data type for ALL parameters must be VARCHAR(255). You are invited to extend the General class to handle any data type including NVARCHAR types for internationalized calls. An enhanced call might look something like:

<Parameters>
   <Parameter Name='@GroupName' Type='NVARCHAR' Size='100' Value="Project Managers" />
</Parameters>

Additional database tables can be added to the Project Server database. They will then share the same connection.

Databases other than the Project Server can be accessed by supplying different and appropriate connection strings.

Steps to Write a New PDS Extension Class

Follow these steps to create a new PDS extension class. For an example, see the General.cs file in the PdsExtension project.

Create a new Visual Studio Solution, and add a Visual C# Class Library project. Specify the required references (the project's References folder must contain at least the default System, System.Data, and System.XML) and declare a namespace.
Add a reference to the PdsExtension.dll. Right-click the References folder, click Add Reference, click the Projects tab, and click Browse.
Add the following declarations, along with other reference declarations you may require.
```
using System.Runtime.InteropServices
using Microsoft.ProjectServer.Extension
```
Note The Microsoft.ProjectServer.Extension declaration does not work unless you have added the PdsExtension reference in Step 2.
Create a strong naming key with the SN command utility. Use the syntax SN –k Strong.Snk. Add a reference to this in the AssemblyInfo.cs file:
```
[assembly: AssemblyKeyFile(@"Strong.snk")]
```
Set the version number in the solution AssemblyInfo.cs file:
```
[assembly: AssemblyVersion("1.0.0.0")]
```
Create a class of the desired name that inherits from the Extender class:
```
public class MyClass : Extender
```
Add a default constructor that doesn't take any parameters:
```
public MyClass()
{
}
```
Decorate the class with the required attributes for COM interoperability. Don't forget to generate a new GUID with uuidgen.exe for each new class. For example:
```
[Guid("EAA49654-65E3-4BC5-BCC7-E465264323D5"),
ComVisible(true), 
ClassInterfaceAttribute(ClassInterfaceType.AutoDual)]
```
Add the XMLRequestEx() method using the required parameter layout.
Add the method calls that validate the request and provide error handling.
Add the method handling cases for all the commands that the extension handles. Don't forget the default case and its call to ReplyUnknownRequestError().
Implement the code for each method.

Steps to Implement a Method

The switch (Command) statement in XMLRequestEX() is the recommended way to call a specific method. Pass any parameters that are necessary. For methods that call the database, this will include the connect string. Other methods might require the user name.

Follow these steps for each method:

Instantiate any objects necessary, for example:
```
Database data = new Database(Connect); 
```
Use RequestProperty() variants to extract additional data from the XML request string.
Perform the work necessary in the method. If the method accesses a database then follow the instructions in the next section and return here when finished.

(See Steps to implement a database method, if necessary.)
Use the Reply family of methods and functions (ReplyStart(), ReplyText(), Reply(), ReplyProperty(), and ReplyEnd() ) to package the Reply packet for later dispatch to the client.
Use try/catch exception handling to capture any unexpected errors that can occur in the method. Make sure to catch and handle any generic exceptions of type Exception.

You can use specific exceptions that are derived from Exception if finer exception handling is required. If you are using multiple catch brackets then don't forget to make the Exception class the last catch.

Exception handlers should use a call to ReplyError() to package the error. ReplyError() returns a number for STATUS and returns additional text encapsulated in an Error element. This is a useful mechanism for returning the Message property of an exception, and provides the client with a useful error message.

Steps to Implement a Database Method

Follow these steps in addition to the steps in the previous section to implement database access. See the Database.cs file in the PdsExtension project for an example.

Create the database object. The constructor receives the connection string as a parameter. The overloaded OpenConnection() method can be used within the constructor(s) if desired. Decide if transactions are required.
Clear the parameter list with a call to ParameterClear() before each call to a stored procedure. This must be done unless a call to the same or different stored procedure is repeated with EXACTLY the same parameter names, types, sizes and values.
Define the parameters as required. Remember that for the current implementation only VARCHAR(255) parameter types can be used.
Execute the required SQL command.
Close the database using CloseConnection(). An overload exists that allows the connection to be closed with an explicit COMMIT or ROLLBACK.

Multiple database calls can be made using steps 2 to 4 if required. Don't forget to clear the parameter list using ParameterClear().

To use connection based transaction processing, follow these steps:

Use the Database class constructor that has a second parameter for transactional control. Set it to true.
```
     Database data = new Database(Connect, true);
```
Call as many stored procedures as required (two or more).
To commit the transactions, call CloseConnection() or CloseConnection(true).
To roll back the transactions, call CloseConnection(false). This would be done in the case where an exception is thrown.

Installing a .NET PDS Extension

In order to work correctly a .NET PDS extension must:

Have the correct XmlRequestEx() signature.
Have the correct class and method level attributes for COM Interop. The Guid must be unique for each extension class.
The assembly must be strongly named but does NOT need to be placed in the global assembly cache (GAC). However, you can easily put the assembly in the GAC, with the following method:

On the Project Server computer, open the Run dialog on the Start menu, type assembly, and then click OK; that opens the GAC, which has the default address C:\WINDOWS\assembly.

Drag the assembly.dll from another Windows Explorer window to the GAC, and you will see the Global Assembly Name, Version, Public Key Token, and other values of the assembly if they were specified.

The steps to install a .NET PDS extension are:

Stop IIS using the following command:
```
iisreset /STOP
```
Copy the extension module DLL into a binary directory. It is recommended that %PROGRAM%BIN is used where %PROGRAM% is the Project Server directory (probably C:\Program Files\Microsoft Office Project Server 2003\). Repeat this step for any other assemblies.
Register the assembly using the .NET assembly registration tool RegAsm. Call using the syntax such as the following:
```
     RegAsm "%PROGRAM%BIN\PdsExtension.dll" /codebase
```
RegAsm adds information about the class to the system registry so COM clients can use the .NET class transparently. The /codebase option creates a Codebase entry in the registry, which specifies the file path for an assembly that is not installed in the global assembly cache. This is the reason why the assembly must be strongly named. Repeat this step for any other assemblies that contain classes that are going to be registered as PDS extensions.
Call the PdsRegister tool that is provided in the download with this article. This creates the necessary registry entry to register the PDS extension with the Project Server PDS. Call using a syntax such as:
```
     PdsRegister Microsoft.ProjectServer.Extension.General
```
If the class is already registered with the PDS, then the registration is skipped. Repeat this step for any other classes that are to be registered as PDS extensions.
Start IIS using the following command:
```
iisreset /START
```

Installation may involve other scripts such as the creation of SQL Server tables, views and stored procedures. It is recommended that all installation steps are scripted. The samples supplied with this article demonstrate a suitable way of doing this.

Don't forget to rerun the install script each time an extension is changed and compiled in Visual Studio .NET.

Making Extensions Easy to Develop

Developing and debugging extensions via a Web page is not easy, but a sample tool called PsxDirect, provided with this article, can make developing and debugging extensions more efficient and much simpler. It replaces the Web client and PDS server layers to allow direct debugging access to the extension class. It provides the XML message and the Project Server database connection string.

The tool allows a request message to be selected from a list and sent to the extension. The request and reply messages are displayed in text boxes, as shown in the following figure.

Figure 1. Request and reply messages in PsxDirect tool

A developer can debug the PDS extension using normal debugging techniques.

There are a few caveats:

The connection string contains server-specific names and password in a form such as:
```
Provider=SQLOLEDB.1;Password=;
User ID=;Initial Catalog=ProjectServer; 
Data Source=trout;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Application Name=PDS; 
Workstation ID=TROUT;Use Encryption for Data=False; 
Tag with column collation when possible=False
```
This must be changed in PdsDirect for each installation of Project Server. The User ID must be the SQL Server user for the Project Server installation, for example, MSProjectServerUser. To check for the correct User ID, open SQL Server Enterprise Manager, expand the Databases node and the Project Server database node that you will use, and then click Users.
The location of request and reply directories is hard coded.
To add new request files in the Requests folder, the XML document file name must be in the following format: General.[filename].xml.
The login account of the developer defines the locale for the thread that executes the calls to the extension. This could change (see The PDS thread locale) when the extension runs on the server.

If you have any problems with "locked" DLLs, you can do an IISReset.

In the Requests directory, the General.GroupUsage.xml and General.GroupUsageDirect.xml files both use the GroupUsage method implemented in General.cs of the PdsExtension project. The General.ResourceMembership.xml file uses the StoredProcedure method that is implemented in the PdsExtension sample. The General.Connection.xml file requests a Connection method, which is not implemented in the sample. For that case, the reply is "Unknown request".

Accessing the PDS from a Client

The Project Server PDS can be accessed by any application that can communicate via HTTP. The PDS Reference download provides the sample tools PDSTest and PDSTest.NET. They are test tools that enable you to send predefined XML requests to the PDS.

Best Practices for Project Server Extensions

There are best practices to follow when developing a Microsoft Office Project Server 2003 extension that is written with the .NET Framework:

DON'T modify any Project Server database objects (tables, views, stored procedures, and so on), Web pages or configuration files.
You can read Project Server tables and extract data with relative ease. Take extreme care when modifying or creating new data in the Project Server tables. This is definitely not recommended unless you fully understand the implications of doing so. THIS IS DONE AT YOUR OWN RISK.
Add your own objects (tables, views, stored procedures, and so on) to the Project Server database and use the MSProjectServerRole in the security permissions. Use a distinctive prefix on the names of all the database objects.
Script the installation of all the extensions you write. This includes extension DLLs, SQL Server objects, configuration files, and Web pages and resources.
PDS extension code can throw runtime exceptions in many situations. Make sure all your code is protected with try/catch blocks so that a sensible reply message can be returned to the client.
Use the extended error numbers (10000 and above) for your own errors.

PDS Callbacks

The PDS Extender template in the PDS Reference download includes an example of a callback in the PDS. For example, a method such as GeneralPing could in turn execute a call to other PDS methods, and include their replies within the GeneralPing reply. For more information, see the PDS Reference.

Project Server Security Object

For more information on use of the Project Server security object, see the topic Writing a PDS Extender in the PDS Reference.

Conclusion

The ideas in and samples provided with this article show how to build a Project Server PDS extension with Visual Studio .NET. The sample project PsxDirect has classes that derive from the base Extender class in the PdsExtension project. The PdsRegister utility provides a quick way to register PDS extensions with Project Server. All of the projects include source code. There are also example requests and replies.

This article described how to implement and extend the base class functions for a wide variety of applications. The .NET Framework and class inheritance make the development of PDS extensions easier and more flexible with Visual Studio .NET.

Additional Resources

Project Server 2003 PDS Reference is a download with HTML Help topics that include all of the built-in PDS methods and the Service for Enterprise Data Maintenance, developing a PDS extension with Visual Basic 6, Project Server security architecture, programmatic logon, using SOAP to call PDS methods, and error codes. The sample code includes the PDS Test, PDS Test.NET, and PDS Extender applications.
Introduction to COM Interop includes a discussion of COM and .NET interoperability.
Microsoft Project Server Data Reporting includes examples for the Microsoft Project Server 2002 database; the examples also work for Project Server 2003.