How to get error from "SQLCMD" through c#?

Question

Running SqlCmd utility using C# this way :

// Calls the sqlcmd                
ProcessStartInfo info = new ProcessStartInfo(
  "sqlcmd", 
 @" -S VDSS218 -i D:\Ravi\Blank_Database_Creation_script.sql");

info.UseShellExecute = false;
info.CreateNoWindow = true;
info.WindowStyle = ProcessWindowStyle.Hidden;
info.RedirectStandardOutput = true;

Process proc = new Process();

proc.StartInfo = info;
proc.Start();

Now, If any error occurred in script while running from C# then how can fetch that SQL Exception in C#.

Answer 1

First, let's declare a class for executioning result:

  public sealed class ExecutionSqlCmdResult {
    public  ExecutionSqlCmdResult(string stdOut, string stdErr, int exitCode)
      : base() {

      Out = string.IsNullOrWhiteSpace(stdOut) ? "" : stdOut;
      Error = string.IsNullOrWhiteSpace(stdErr) ? "" : stdErr;
      ExitCode = exitCode;
    }

    public string Out {
      get;
    }

    public string Error {
      get;
    }

    public int ExitCode {
      get;
    }
  }

Then we can put

  public static ExecutionSqlCmdResult ExecuteSqlCmd(string command) {
    ProcessStartInfo sqlCmdInfo = new ProcessStartInfo() {
      UseShellExecute = false,
      CreateNoWindow = true,
      WindowStyle = ProcessWindowStyle.Hidden,
      RedirectStandardError = true,
      RedirectStandardOutput = true,
      Arguments = command,
      FileName = "sqlcmd",
      StandardErrorEncoding = Encoding.UTF8,
      StandardOutputEncoding = Encoding.UTF8,
    };

    using (Process sqlCmdProcess = new Process()) {
      sqlCmdProcess.StartInfo = sqlCmdInfo;
      sqlCmdProcess.Start();

      StringBuilder sbOut = new StringBuilder();
      StringBuilder sbErr = new StringBuilder();

      sqlCmdProcess.OutputDataReceived += (sender, e) => {
        if (e.Data != null)
          sbOut.Append(e.Data);
      };

      sqlCmdProcess.ErrorDataReceived += (sender, e) => {
        if (e.Data != null)
          sbErr.Append(e.Data);
      };

      sqlCmdProcess.BeginErrorReadLine();
      sqlCmdProcess.BeginOutputReadLine();

      sqlCmdProcess.WaitForExit();

      return new ExecutionSqlCmdResult(sbOut.ToString(), sbErr.ToString(), sqlCmdProcess.ExitCode);
    }
  }

Usage

var result = ExecuteSqlCmd(@" -S VDSS218 -i D:\Ravi\Blank_Database_Creation_script.sql");

//TODO: inspect result.Out, result.Error and result.ExitCode