Problem, executing commands in cmd using c#

Question

I need to execute the below command in command prompt.

C:\MySQL\MySQL Server 5.0\bin>mysql -uroot -ppassword < d:/admindb/aar.sql

When i do this manually in cmd, i am getting my results.

Now i am trying to do this programatically, to execute it in cmd from c# code.

I am using the below code to do it. I am not getting any errors and Result !!!

When i debug, i get the value of string commandLine as below,

"\"C:\\MySQL\\MySQL Server 5.0\\bin\\\" -uroot -ppassword > \"D:/admindb/AAR12.sql"

I guess the problem is with this string, passed to cmd. How to solve this ??.

public void Execute()
{
    string commandLine = "\"" + MySqlCommandPath + "\"" + " -u" + DbUid + " -p" + DbPwd + " > " + "\"" + Path.Combine(Path_Backup, FileName_Backup + ExcID + ".sql");
    System.Diagnostics.ProcessStartInfo PSI = new System.Diagnostics.ProcessStartInfo("cmd.exe");
    PSI.RedirectStandardInput = true;
    PSI.RedirectStandardOutput = true;
    PSI.RedirectStandardError = true;
    PSI.UseShellExecute = false;
    System.Diagnostics.Process p = System.Diagnostics.Process.Start(PSI);
    System.IO.StreamWriter SW = p.StandardInput;
    System.IO.StreamReader SR = p.StandardOutput;
    SW.WriteLine(commandLine);
    SW.Close();
}

Answer 1

I used a more object oriented approch for the same task.

I read the file using a StreamReader and use console redirection to directly injecting the text to the mysql exe file. Works like a charm.

Update: Here is the code, it's VB not C# but should be easy to translate. I use this code to create an empty db for some unit tests. The advantage over just starting mysql from the commandline with an file as input is that

a) I create an exception if something fails
b) You could easily use this code to control mysql without needing a real file.
c) In a TestProject if the Exception is thrown you get the error output from mysql directly in your TestProtocol.

Please note that UserName, UserName, Password, Schema are Shared Properties in my class, you can just replace them with strings or modify the function call.

Public Shared Sub SetupDatabase()

    ' basic configuration
    Dim infile As String = "..\..\..\MyProject\initial_db.sql"
    Dim mysql_binary As String = "mysql.exe"
    Dim mysql_args As String = String.Format("-h {0} -u{1} -p{2} {3}", HostName, UserName, Password, Schema)

    ' Creates a StreamReader from infile
    Dim reader As New StreamReader(infile)

    ' Create the process
    Dim p As New Process()
    p.StartInfo.FileName = mysql_binary
    p.StartInfo.Arguments = mysql_args

    ' Redirect input/output/stderr
    p.StartInfo.RedirectStandardOutput = True
    p.StartInfo.RedirectStandardError = True
    p.StartInfo.RedirectStandardInput = True
    p.StartInfo.UseShellExecute = False
    p.StartInfo.CreateNoWindow = True
    p.StartInfo.WindowStyle = ProcessWindowStyle.Hidden

    ' start the process
    p.Start()

    ' read from infile and pass it to mysql
    Do While reader.EndOfStream = False
        p.StandardInput.WriteLine(reader.ReadLine)
    Loop

    ' close stdin
    p.StandardInput.Close()

    ' read from stdout / stderr
    Dim stdout As String = p.StandardOutput.ReadToEnd()
    Dim stderr As String = p.StandardError.ReadToEnd()

    ' wait for mysql to stop
    p.WaitForExit()

    ' if exitcode != 0 we raise an exception and include the output from stderr
    If p.ExitCode <> 0 Then
        Throw New Exception("Initial DB Setup failed with Exitcode " & p.ExitCode & ":" & vbNewLine & stderr)
    End If

    p.Close()
    reader.Close()

End Sub

Answer 2

Presumably you're shelling out to cmd.exe because you want to do I/O redirection. Why not just set up the file handles yourself, and then call MySQL.exe directly?

public void Execute() {

  Process p = new Process();
  p.StartInfo.FileName = @"C:\MySQL\MySQL Server 5.0\bin\mysql.exe";
  p.StartInfo.Arguments = String.Format( "-u{0} -p{1}", user, password );
  p.StartInfo.UseShellExecute = false;
  p.StartInfo.RedirectStandardInput = true;
  p.StartInfo.RedirectStandardOutput = true;
  p.StartInfo.RedirectStandardError = true;

  p.Start();

  System.IO.StreamWriter SW = p.StandardInput;
  System.IO.StreamReader SR = p.StandardOutput;

  /* Send data to MySQL and capture results */

  SW.Close();
}

You might want/need to set the WorkingDirectory property too...

Of course, the real question is, why aren't you using ODBC or some higher-level API to talk to MySQL instead of trying to run mysql.exe yourself? I could see using this to run mysqladmin.exe, but all of the functionality of mysql.exe is available from ODBC.

If you really want to push that horrible commandline at cmd.exe, then you should @try the @-quoting syntax for strings -- it allows you to avoid escaping backslashes, and you use "" to represent " in the string. Your string would end up defined like this:

@"""C:\MySQL\MySQL Server 5.0\bin\mysql.exe"" -uroot -password < ""D:/admindb/AAR12.SQL"""

A better way to write your string concatenation would be to use String.Format(), as follows:

String commandLine = String.Format( @"""{0}"" -u{1} -p{2} < ""{3}""", MySqlCommandPath, DbUid, DbPwd, Path.Combine(Path_Backup, FileName_Backup + ExcID + ".sql"));