Using the MySql Command Line from C#/.NET

We have added two new data access providers to the upcoming SoapBox Server 2007 release. We now support PostgreSql and MySql as well as Microsoft SQL and Oracle. The code for these databases has been in our server and test libraries for a couple of months and this last week it was time to add them into our post installation configuration wizard. We strive to make our setup process as simple as possible. You'll notice many improvements over the 2005 wizard. We have better auto-configuration and fewer wizard screens.

One of our core philosphies here at Coversant is to make all of our software as easy to use as possible. Aside from testing, we spend more time doing this than any other R&D effort. Why is easy to use software so important? Well, the easier our software is to use the more you will like it and the less support we have to provide. We cut costs, you are happier. It's win/win. :)

Adding the automated setup process for these new data access layers was supposed to be very straightforward. In our configuration utility it's all abstracted so I just implement a few classes, the DB guy gives me the scripts to run and I call the appropriate tool (mysql.exe, pgsql.exe, osql.exe, etc), which we ship with our server for your convenience. However, there is one big feature missing from the mysql command line tool. There is no way to specify a file to use as input! But, it does take standard input. So, typically, if i were running the tool from the command line I would do something like: 'mysql -uroot -p < "c:\myscript.sql"'. If you're command line saavy you will know this redirects the file c:\myscript.sql as standard input into the mysql command line tool. I thought I could do this in .NET using command line arguments passed to the System.Process.ProcessStartInfo class. Well, I was wrong. It doesn't work (at least I couldn't figure out how to give it a file as stdin -- the "<" didn't work).

So, what's the answer? Simple. Set the nifty RedirectStandardInput property and read the file into the Process.InputStream. Code follows:

private void ExecuteSQLScript(string databaseName, string user, string password, string command, string filename, string server)
        {
            using (System.Diagnostics.Process p = new System.Diagnostics.Process())
            {
#if LINUX
            p.StartInfo.WorkingDirectory = System.IO.Path.GetFullPath(this.InstallOptions.InstallDirectory);
            p.StartInfo.FileName = "mysql";
#else
                //grab the path from our installation options
                p.StartInfo.WorkingDirectory = System.IO.Path.GetFullPath(System.IO.Path.Combine(this.InstallOptions.InstallDirectory, BaseScriptDirectory));
                p.StartInfo.FileName = System.IO.Path.Combine(p.StartInfo.WorkingDirectory, "mysql.exe");
#endif
                
                //set all the startup options
                p.StartInfo.CreateNoWindow = true;
                p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
                p.StartInfo.UseShellExecute = false;
                p.StartInfo.RedirectStandardOutput = true;
                p.StartInfo.RedirectStandardError = true;

                //build the arguments for mysql
                StringBuilder args = new StringBuilder();

                if (!string.IsNullOrEmpty(password))
                    args.AppendFormat("-p{0} ", password);

                if (!string.IsNullOrEmpty(server))
                    args.AppendFormat("-h{0} ", server);

                if (!string.IsNullOrEmpty(databaseName))
                    args.AppendFormat("-D{0} ", databaseName);

                if (!string.IsNullOrEmpty(user))
                    args.AppendFormat("-u{0} ", user);

                if (!string.IsNullOrEmpty(command))
                    args.AppendFormat("-e\"{0}\" ", command);
                else if (!string.IsNullOrEmpty(filename))
                    p.StartInfo.RedirectStandardInput = true;

                p.StartInfo.Arguments = args.ToString();

                WTrace.TraceInfo("Run Script", this.GetType(), "Executing: '{0}' with args '{1}' in working dir '{2}'", p.StartInfo.FileName, p.StartInfo.Arguments, p.StartInfo.WorkingDirectory);


                //start up the process, and handle the redirected stdin and stdout -- send them to our trace lib
                p.ErrorDataReceived += new System.Diagnostics.DataReceivedEventHandler(p_ErrorDataReceived);
                p.OutputDataReceived += new System.Diagnostics.DataReceivedEventHandler(p_OutputDataReceived);
                try
                {
                    p.Start();
                    p.BeginErrorReadLine();
                    p.BeginOutputReadLine();

                    //read in the script file if one was specified and give it to stdin
                    if (null != filename)
                    {
                        using (FileStream f = File.OpenRead(filename))
                        {
                            using (StreamReader reader = new StreamReader(f))
                            {
                                //we could do this one line at a time to be safe memory wise, but we know our scripts are small
                                p.StandardInput.WriteLine(reader.ReadToEnd());
                            }
                        }

                        //tell mysql we want to exit, otherwise the process will hang
                        p.StandardInput.WriteLine("exit");
                    }

                    p.WaitForExit();
                }
                finally
                {
                    p.ErrorDataReceived -= new System.Diagnostics.DataReceivedEventHandler(p_ErrorDataReceived);
                    p.OutputDataReceived -= new System.Diagnostics.DataReceivedEventHandler(p_OutputDataReceived);
                }

                if (p.ExitCode != 0)
                    throw new MySqlException("SQL Command failed.", p.StartInfo.Arguments, "", "");
            }
        }

        void p_ErrorDataReceived(object sender, System.Diagnostics.DataReceivedEventArgs e)
        {
            if (!string.IsNullOrEmpty(e.Data))
                WTrace.TraceInfo("Run Script", this.GetType(), "StdErr: {0}", e.Data);
        }

        void p_OutputDataReceived(object sender, System.Diagnostics.DataReceivedEventArgs e)
        {
            if (!string.IsNullOrEmpty(e.Data))
                WTrace.TraceInfo("Run Script", this.GetType(), "StdOut: {0}", e.Data);
        }

Wow, that's a lot of code. Ok, so I probably didn't have to paste that much of it, but I wanted you to get an idea of the full extent of the method, and I think being able to run mysql scripts is useful.

When all is said and done you now have two screens for installing to a MySql database: 1) enter username, password, and hostname 2) choose an existing or new database name. From there the installer figures everything else out automatically.