Execute MySql Query using PowerShell in C#

Question

I'm new to PowerShell. Is there a way in C# to call mysql.exe and execute a query using Powershell. I want to execute the query and covert the result into a csv file.

.\mysql.exe -C -B --host=amazonaws -P 3306 --user=user --password=pass --database=MyDB --execute="SELECT * FROM MyTable LIMIT 5" | Out-File "C:\output.sql" 

using (PowerShell PowerShellInstance = PowerShell.Create())
{        
  ????
  PowerShellInstance.Invoke();
}

Thanks

Why mix C# and Powershell together? You can connect to a MySql database from C# [easily enough](https://stackoverflow.com/q/21618015). — vonPryz, Oct 11 '17 at 11:14
Use the proper [API](https://dev.mysql.com/downloads/connector/net/) instead of running a commandline program in PowerShell in C#. — Ansgar Wiechers, Oct 11 '17 at 11:33
@vonPryz our current solution has a well defined data access layer using C# but for this specific task they want it to have a separate console to run the command then using powershell covert to csv. I was using Process.Start(new ProcessStartInfo) for cmd/mysql.exe but later they asked to covert the result using Powershell and I got stuck. — DrkDev, Oct 11 '17 at 15:15

score 0 · Answer 1 · answered Oct 16 '17 at 17:29

Of course connecting to MySQL database using C# it's easier but they want it to have a powershell script

 private static void GenerateNewInputFile(
        string server, 
        string user, 
        string password, 
        int port, 
        string database,
        string query,
        string mysqlLocation,
        string outputFilePath)
    {
        using (var powershell = PowerShell.Create())
        {
            var sb = new StringBuilder();
            sb.Append($"$server = \"{server}\"\n");
            sb.Append($"$user = \"{user}\"\n");
            sb.Append($"$password = \"{password}\"\n");
            sb.Append($"$port = {port}\n");
            sb.Append($"$db = \"{database}\"\n");
            sb.Append($"$query = \"{query}\"\n");
            sb.Append($"$mysql = \"{mysqlLocation}\"\n");
            sb.Append("$params = \"-C\",\"-B\",\"-h$server\",\"-P$port\",\"-u$user\",\"-p$password\",$db,\"-e$query\"\n");
            sb.Append($"&  $mysql @params | Out-File \"{outputFilePath}\"\n");

            powershell.AddScript(sb.ToString());

            powershell.Invoke();
        }

    }

Execute MySql Query using PowerShell in C#

1 Answers1