6

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();
           }
Improve this question
1
  • You probably should use library to forward remote to local port and use that in your connection string. Commented May 30, 2012 at 19:21

2 Answers 2

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This works perfectly, ty very much. Although, is it neccesary to open it each time? Can I open only once?
6
+100

You will need to setup port forwarding with your SSH connection...

Basically make a SSH connection that listens on port 3306 on localhost and forwards that to your MySQL DB server...

In the connections string to your MySQL DB you need to change the part server=... to server=localhost while your SSH connection needs to go to your MySQL DB server's IP.

IF you can't forward port 3306 you can use any port you want BUT you need change the additionally the port= part of the connection string... see here for examples on connection strings with a different port... the SSH connection still targets 3306 but listens on a different port on localhost in this case...

Here are some working examples with source code (although not with SSH.NET since I don't use SSH.NET but this should nonetheless get you started IMO):

Improve this answer

15 Comments

hi, thank you for your try but I already tried all of this. Please read my question all the way. I wrote about port forwarding
I just need to combine SSH.NET as I am passing a SSH connection
@denonth you can't use any SSH connection... the SSH connection MUST be setup as a port forwarding connection AND you need to change your MySQL connection string - otherwise it won't work.
@denonth IF you can't forward port 3306 you can use any port you want BUT you need change the additionally the port= part of the connection string...
I have tried your app on the first link. I can't get authentication, I even wrote the post at the bottow of that page. I put a local port as 3307 as I am using a 3306 but I can't get auth. Btw. somone told me that I can't have a local port on 3306 that will have local database and forwarded port on 3307 as this 2 won't work together. I need a local db and server one. I don't wont to use php as I need fast saving every 5-7 sec
|

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.