3

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();
}
Improve this question
2
  • Why does it have to be in a command prompt? The Process class can run commands directly, you do not need to use cmd in between the two. Commented Jun 14, 2010 at 5:54
  • @ jrista, Anyway the problem is in string value. Need to resolve that. Commented Jun 14, 2010 at 6:05

2 Answers 2

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

hint: if you haven't included the mysql bin directory into your path (I would recommend to do that) you should replace the "mysql_binary" variable with the full path to your mysql.exe
I tried this solution as well, but the process started still keeps on giving me the help documentation excerpt indicating the command was not valid. MySQL doesn't seem to take it as input, it still tries to execute the command before that input is supplied. I ended up simply creating a .bat with the full MySQL command in it and executing this from my .NET code.
1

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"));
Improve this answer

7 Comments

The script might be doing cross-database DDL operations, so you'll need it to run it from mysql.exe directly.
That sort of thing works fine from ODBC against MS SQL Server; all of my MySQL work has been from Linux, where again, that sort of thing works fine. Usually all I ever need is a "using XXX" in the middle of a command stream, and then to include the schema prefix if I'm joining tables across databases. If you're saying that won't work for MySQL running on Windows via ODBC, I guess I'll have to defer to your experience.
@W. Craig Trader, I tried your above code. The command prompt of Mysql opens. And that's it, there is no result. I cannot see any changes in database. Actually that file will have to update the values in database. That's not happening.
@srk, you still need to send your commands to mysql by writing them to SW. If your DDL/DML is in AAR12.sql, then you'd need to open that, and loop through it, writing commands to SW.
@srk, I updated my answer to include better ways to handle string quoting, escaping, and formatting.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.