How to Connect to a Remote MySQL Database from .NET Using SSH.NET Library and Retrieve Data?

Connecting to MySQL from .NET Using SSH.NET Library

Understanding the Objective

The goal is to establish a connection between a web page (.NET/C#) and a remote MySQL database over an SSH connection.

Code Analysis and Troubleshooting

Your provided code successfully connects to MySQL Workbench over SSH using the correct credentials and port configuration. However, it fails to retrieve data from the remote server.

Revised Approach

The following code addresses the issue and allows you to connect to the remote MySQL database and retrieve data:

<code class="csharp">using(var client = new SshClient("ssh server id", "sshuser", "sshpassword"))
{
    client.Connect();
    if (client.IsConnected)
    {
        var portForwarded = new ForwardedPortLocal("127.0.0.1", 3306, "127.0.0.1", 3306);
        client.AddForwardedPort(portForwarded);
        portForwarded.Start();
        using (MySqlConnection con = new MySqlConnection("SERVER=127.0.0.1;PORT=3306;UID=someuser;PASSWORD=somepass;DATABASE=Dbname"))
        {
            using (MySqlCommand com = new MySqlCommand("SELECT * FROM cities", con))
            {
                com.CommandType = CommandType.CommandText;
                DataSet ds = new DataSet();
                MySqlDataAdapter da = new MySqlDataAdapter(com);
                da.Fill(ds);
                foreach (DataRow drow in ds.Tables[0].Rows)
                {
                    Console.WriteLine("From MySql: " + drow[1].ToString());
                }
            }
        }
        client.Disconnect();
    }
    else
    {
        Console.WriteLine("Client cannot be reached...");
    }
}</code>

Explanation

• This code establishes an SSH connection to the remote server.
• It then forwards a port (for this example, port 3306) to the local machine, enabling communication with the MySQL database.
• A MySqlConnection object is created using the forwarded port and the appropriate database credentials.
• A MySqlCommand object is executed to retrieve data from the specified table.
• The retrieved data is then displayed in the console.

The above is the detailed content of How to Connect to a Remote MySQL Database from .NET Using SSH.NET Library and Retrieve Data?. For more information, please follow other related articles on the PHP Chinese website!