Home > CPS, Uncategorized, work > Getting Project Server database information via C# / How to run PowerShell command in C#

Getting Project Server database information via C# / How to run PowerShell command in C#

For a long time now I have had a few scenario’s where it would be helpful for me to know what the database server and database names are of a Project Server instance. The PSI does not provide this information but there is a PowerShell command that will give you the information.

Get-SPProjectWebInstance –url <URL>

This gives me all the info a want about the instance.

image

So now I know I can get the info i need i just need a way of getting this within c# code.

Helpfully you can use System.Automation to execute PowerShell scripts within .NET

We start with a simple method that will execute PowerShell commands

private string RunScript(string scriptText)
        {
            string addWss = "Add-PSSnapin microsoft.sharepoint.powershell";
            Runspace runspace = RunspaceFactory.CreateRunspace();
            runspace.Open();
            Pipeline pipeline = runspace.CreatePipeline();
            pipeline.Commands.AddScript(addWss);

            pipeline.Commands.AddScript(scriptText);
            pipeline.Commands.Add("Out-String");
            var results = pipeline.Invoke();

            runspace.Close();

            StringBuilder stringBuilder = new StringBuilder();
            foreach (PSObject obj in results)
            {
                stringBuilder.AppendLine(obj.ToString());
            }

            return stringBuilder.ToString();
        }

The above code needs

using System.Management.Automation;
using System.Management.Automation.Runspaces;

NOTE: You’ll need to add a reference to the version of PowerShell you have installed in your GAC to the Visual Studio project file. To do so, you’ll open the project file as a text file and add the following line into the <ItemGroup> section:

<Reference Include=”System.Management.Automation” />

I was able to cheat and “browse” to the assembly using the following path

C:\Windows\assembly\GAC_MSIL\System.Management.Automation\1.0.0.0__31bf3856ad364e35\System.Management.Automation.dll

Now that we have the helper method I can actually get the info I want.

string answer = RunScript("Get-SPProjectWebInstance -url http://vm641/pwa2");
string reportingDatabaseServer = string.Empty;
string reportingDatabaseName = string.Empty;

var output = answer.Split(new string[] {Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries).Cast<string>();

reportingDatabaseServer = (from x in output where x.StartsWith("ReportingServer") select x).First().ToString();
reportingDatabaseName = (from x in output where x.StartsWith("ReportingDatabase") select x).First().ToString();

reportingDatabaseServer = reportingDatabaseServer.Replace("ReportingServer", "").Replace(":", "").Trim();
reportingDatabaseName = reportingDatabaseName.Replace("ReportingDatabase", "").Replace(":", "").Trim();

Bingo I have the name of the reporting server and the database name.

I needed this months ago to auto configure a PSI extension that was written but i need it now to auto configure a feature when it is deployed.

I’ll wrap all the above code into a single class that accepts the instance URL in its constructor and then expose properties with the information.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Management.Automation.Runspaces;
using System.Management.Automation;
using System.Text;

public class ProjectWebInstanceInformation
{
    #region Fields and Properties
    public string ReportingServer { get; private set; }
    public string ReportingDatabase { get; private set; }
    #endregion

    #region Constructor
    protected ProjectWebInstanceInformation()
    {
    }

    public ProjectWebInstanceInformation(string Url)
    {
        try
        {
            string answer = RunScript(string.Format("Get-SPProjectWebInstance -url {0}", Url));
            string reportingDatabaseServer = string.Empty;
            string reportingDatabaseName = string.Empty;

            var output = answer.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Cast<string>();

            ReportingServer = (from x in output where x.StartsWith("ReportingServer") select x).First().ToString().Replace("ReportingServer", "").Replace(":", "").Trim();
            ReportingDatabase = (from x in output where x.StartsWith("ReportingDatabase") select x).First().ToString().Replace("ReportingDatabase", "").Replace(":", "").Trim();
        }
        catch { }

    }

    #endregion

    #region Methods
    private string RunScript(string scriptText)
    {
        string addWss = "Add-PSSnapin microsoft.sharepoint.powershell";
        Runspace runspace = RunspaceFactory.CreateRunspace();
        runspace.Open();
        Pipeline pipeline = runspace.CreatePipeline();
        pipeline.Commands.AddScript(addWss);

        pipeline.Commands.AddScript(scriptText);
        pipeline.Commands.Add("Out-String");
        var results = pipeline.Invoke();

        runspace.Close();

        StringBuilder stringBuilder = new StringBuilder();
        foreach (PSObject obj in results)
        {
            stringBuilder.AppendLine(obj.ToString());
        }

        return stringBuilder.ToString();
    }
    #endregion
}
Advertisements
  1. August 7, 2012 at 6:48 pm

    Nice post. I used to be checking constantly this weblog and I’m impressed! Very useful info specially the closing phase 🙂 I take care of such info much. I used to be seeking this particular information for a long time. Thanks and best of luck.

  1. July 31, 2012 at 12:57 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: