SSH connection to MySQL using SSH.NET library

Question

I was given a link to use this library for connection through SSH connection to a MySQL database with C#.

Here is the LINK to the library as a lot of you will find this interesting as you don't need to open a SSH channel manually.

This is a very detailed library and has a lot of features but what I want to do is pretty simple. I just want to open a SSH channel each time I want to write to the database.

While I was searching through the source that is provided I came up to this part of the code which can be found in TestSshCommand.cs and I think I can use this part for my connections but I need your help to get it work as I have my connection string I don't know how to connect all of it.

Code:

public void Test_Execute_SingleCommand()
        {
            using (var client = new SshClient(Resources.HOST, Resources.USERNAME, Resources.PASSWORD))
            {
                client.Connect();
                var result = ExecuteTestCommand(client);
                client.Disconnect();

                Assert.IsTrue(result);
            }

My code for connectiong to non SSH channel is:

con.ConnectionString = ConfigurationManager.ConnectionStrings["Con2"].ConnectionString;
            con.Open();
            cmd = new MySqlCommand("SELECT COUNT(*) FROM " + ConfigSettings.ReadSetting("main"), con);

     //...... more of the code

So how can I combine this code to the above one so it can first open a SSH channel and then execute my query?

Do I need to put my code inside connect and disconnect functions?

One more think I have add .dll file inside references and I must say that none of the imports are working. So can someone please download the library and try this simple code to see if references are working on your project and so that everyone ( which are a lot ) can have a working solution for their future projects?

EDIT:

This is my code that I was trying to do later on. My SSH connection works now only need to combine those two to get it work.

Problem is that I need to have one database locally on my pc and need to connect to the server. So port forwarding won't work as somone says on the forum that I can't port forward while mysql already have port for running

using (var client = new SshClient("cpanel****", "******", "******"))
          {
            client.Connect();
            con = new MySqlConnection(GetConnectionString());
            con.Open();
            cmd = new MySqlCommand(String.Format("insert into {0} values (null, ?Parname , ?Parname2, ?Parname3, ?Parname4, ?Parname5, ?Parname6, ?Parname7);", ConfigSettings.ReadSetting("main")), con);
            cmd.Parameters.Add("?Parname", MySqlDbType.Double).Value = Math.Round(deciLat, 5);
            // ... more parameters
            cmd.ExecuteNonQuery();
            client.Disconnect();
           }

Answer 1

You simply need to use a different local port for the tunnel than 3306 which is being used by your MySQL instance.

Example below with port 8001 being used instead:

using (var client = new SshClient("ssh_hostname", "ssh_username", "ssh_password"))
{
  client.Connect();
  var tunnel = new ForwardedPortLocal("127.0.0.1", 8001, "127.0.0.1", 3306);
  client.AddForwardedPort(tunnel);
  tunnel.Start();
  using (var DB = new DBContext())
  {
    //MySQL interaction here!
    scores = DB.Table
      .Where(x => !String.IsNullOrEmpty(x.SomeField))
      .ToList();
  }
  tunnel.Stop();
}

And then make sure in your connection string you have the server 127.0.0.1 and the port 8001

server=127.0.0.1;port=8001

This will connect anything going to 127.0.0.1:8001 to ssh_hostname:3306.