Home > Paul Busby, 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
}

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/07/31/getting-project-server-database-information-via-c-how-to-run-powershell-command-in-c/

Chris Stretton
Paul is a an expert SharePoint and Project Server developer and is responsible for designing and implementing custom solutions on client systems using the latest SharePoint and .NET technologies.
Paul has extensive experience with SharePoint systems across all sizes of implementation, ranging from small to large farms and has an excellent understanding of all the elements of SharePoint.

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

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: