Skip to content

Instantly share code, notes, and snippets.

@chilversc
Created May 14, 2013 12:47
Show Gist options
  • Save chilversc/5575617 to your computer and use it in GitHub Desktop.
Save chilversc/5575617 to your computer and use it in GitHub Desktop.
Connection to MySql from .net over an SSH tunnel, using http://nuget.org/packages/SSH.NET/
void Test()
{
var ci = new ConnectionInfo ("remoteserver", "remoteuser", new PasswordAuthenticationMethod ("remoteuser", "password"));
var cs = new MySqlConnectionStringBuilder ();
cs.AllowBatch = true;
cs.Server = "127.0.0.1";
cs.Database = "database";
cs.UserID = "dbuser";
cs.Password = "dbpassword";
using (var tunnel = new SshTunnel (ci, 3306)) {
cs.Port = checked ((uint) tunnel.LocalPort);
using (var connection = new MySqlConnection (cs.GetConnectionString (true)))
using (var cmd = connection.CreateCommand()) {
connection.Open ();
cmd.CommandText = "SELECT * FROM foo LIMIT 10";
var dt = new DataTable ();
var da = new MySqlDataAdapter (cmd);
da.Fill (dt);
dt.Dump ();
}
}
}
class SshTunnel : IDisposable
{
private SshClient client;
private ForwardedPortLocal port;
private int localPort;
public SshTunnel (ConnectionInfo connectionInfo, uint remotePort)
{
try {
client = new SshClient (connectionInfo);
port = new ForwardedPortLocal ("127.0.0.1", 0, "127.0.0.1", remotePort);
client.ErrorOccurred += (s, args) => args.Dump ();
port.Exception += (s, args) => args.Dump ();
port.RequestReceived += (s, args) => args.Dump ();
client.Connect ();
client.AddForwardedPort(port);
port.Start();
// Hack to allow dynamic local ports, ForwardedPortLocal should expose _listener.LocalEndpoint
var listener = (TcpListener) typeof (ForwardedPortLocal).GetField ("_listener", BindingFlags.Instance | BindingFlags.NonPublic).GetValue (port);
localPort = ((System.Net.IPEndPoint) listener.LocalEndpoint).Port;
} catch {
Dispose ();
throw;
}
}
public int LocalPort { get { return localPort; } }
public void Dispose ()
{
if (port != null)
port.Dispose ();
if (client != null)
client.Dispose ();
}
}
@dreamer-at
Copy link

Severity Code Description Project File Line Suppression State
Error CS1061 'DataTable' does not contain a definition for 'Dump' and no extension method 'Dump' accepting a first argument of type 'DataTable' could be found (are you missing a using directive or an assembly reference?) ConsoleApp1 D:\TestProjAlstron\ConsoleApp1\ConsoleApp1\Program.cs 44 Active

@ruxo
Copy link

ruxo commented Sep 4, 2018

@Talasbaev This could be a script for LinqPad, which support Dump command on all objects.

@fredmoro
Copy link

fredmoro commented Apr 22, 2020

If You don' want to bother with LinqPad, use someting like this:
args.Exception.ToString(); instead of args.Dump()

@chilversc
Copy link
Author

Yup, dump was from LinqPad just to test the connection worked. The useful part is creating a tunnel and the connection to MySQL, what you do with the correction/ query result after that is up to you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment