Boman.Biz

“What you must learn is that these rules are no different than the rules of a computer system...some of them can can be bent. Others...can be broken.”

When developing software for Project Server 2010, occasionally we need to present information to the user that can only be sourced from the Project Server Reporting database.  Actually getting the data out of the database is a fairly simple exercise, once you know which one it is! 

The class that Project Server uses to manage this sort of thing is the PsiServiceApplication that lives in the Microsoft.Office.Project.Server.Administration assembly.  Unfortunately all the classes in this assembly are marked as internal and sealed to prevent unauthorised use – i.e. Us. 

So in situations like this, its sometimes necessary to use Reflection to bend the rules a little

Enjoy!

/// <summary>
/// Gets the reporting database based on a PWA URL.
/// </summary>
/// <param name="vstrPWAUrl">The PWA URL.</param>
/// <returns>
/// SPDatabase object representing the required Reporting database or Null if not found
/// </returns>
/// <history>
///     <change user="JBoman" date="10/08/2011">Initial version.</change>
/// </history>
public SPDatabase GetReportingDatabase(string vstrPWAUrl)
{
    Assembly assPSA = Assembly.Load("Microsoft.Office.Project.Server.Administration, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c");
    Type typePsiServiceApplication = assPSA.GetType("Microsoft.Office.Project.Server.Administration.PsiServiceApplication");

    MethodInfo minfGetServiceAppByIUrl = typePsiServiceApplication.GetMethod("GetServiceAppByUrl", BindingFlags.NonPublic | BindingFlags.Static);
    object objPsiServiceApplication = minfGetServiceAppByIUrl.Invoke(null, new object[] { vstrPWAUrl });

    if (objPsiServiceApplication != null)
    {
        MethodInfo minfGetSiteList = typePsiServiceApplication.GetMethod("GetSiteList", BindingFlags.NonPublic | BindingFlags.Instance);
        object objSiteList = minfGetSiteList.Invoke(objPsiServiceApplication, new object[] { Microsoft.SharePoint.Administration.SPUrlZone.Default });
        if (objSiteList != null)
        {
            IEnumerable colSiteList = (IEnumerable)objSiteList;
            IEnumerator enumSiteList = colSiteList.GetEnumerator();
            while (enumSiteList.MoveNext())
            {
                KeyValuePair<string, SPPersistedObject> kvpSite = (KeyValuePair<string, SPPersistedObject>)enumSiteList.Current;
                if (kvpSite.Key.ToLower() == vstrPWAUrl.ToLower())
                {
                    Type typeProjectSite = assPSA.GetType("Microsoft.Office.Project.Server.Administration.ProjectSite");
                    PropertyInfo pinfReportingDatabase = typeProjectSite.GetProperty("ReportingDatabase", BindingFlags.Instance | BindingFlags.Public);
                    if (pinfReportingDatabase != null)
                    {
                        object objReportingDatabase = pinfReportingDatabase.GetValue(kvpSite.Value, null);
                        if (objReportingDatabase != null)
                        {
                            return (SPDatabase)objReportingDatabase;
                        }
                    }
                }
            }
        }
    }
    return null;
}