title: Calling powershell from C# for running SQL Script
body: <p>I am trying to run the powershell scripts which executes sql scripts from C#.</p>
  
  <p>If I run the powershell scripts from powershell command prompt it works but it doesn't work when I try to run it from C#</p>
  
  <p>Here is the function I am using for running powershell scripts</p>
  
  <pre><code>public static void RunPowershell(string fileName, string functionName, Dictionary&lt;string, string&gt; parameters)
      {
          string fullPath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), fileName);
          string script = File.ReadAllText(fullPath);
  
          try
          {
              using (Runspace runspace = RunspaceFactory.CreateRunspace())
              {
                  runspace.Open();
                  PowerShell instance = PowerShell.Create();
                  instance.Runspace = runspace;
  
                  instance.AddScript(script);
  
                  instance.Invoke();
  
                  instance.Commands.Clear();
                  var command = instance.AddCommand(functionName);
                  if (null != parameters)
                  {
                      foreach (var parameter in parameters)
                      {
                          command.AddParameter(parameter.Key, parameter.Value);
                      }
                  }
                  var result = instance.Invoke();
              }
          }
          catch(Exception e)
          {
              MessageBox.Show(e.Message);
          }
      }
  </code></pre>
  
  <p>and my powershell script is(Test2.ps1):</p>
  
  <pre><code>    function RunScript(){
      Import-Module "SQLPS" -DisableNameChecking
      Invoke-Sqlcmd -inputfile ".\TestSQL.sql" -ServerInstance . -Verbose
  }
  
  function ShowMessage(){
      [System.Windows.Forms.MessageBox]::Show("Hi from Powershell !")
  }
  </code></pre>
  
  <p>TestSQL.sql looks like this:</p>
  
  <pre><code>    USE TEST
  GO
  INSERT INTO Employee VALUES('1', 'abc')
  </code></pre>
  
  <p>It Shows the Message Box when I try:</p>
  
  <pre><code>PowershellHelper.RunPowershell("Test2.ps1", "ShowMessage", null);
  </code></pre>
  
  <p>But doesn't insert into the table when I try:</p>
  
  <pre><code>PowershellHelper.RunPowershell("Test2.ps1", "RunScript", null);
  </code></pre>