使用SSH.NET库连接到MySQL从.NET [英] Connection to MySQL from .NET using SSH.NET Library
本文介绍了使用SSH.NET库连接到MySQL从.NET的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
![在这里输入的形象描述](\"http://i.stack.imgur.com/aNctR.jpg\")
我开发一个网页（ASP.NET/ C＃）的查询（MySQL的）数据库，通过SSH远程服务器上。我使用这两个库（使用mysql-connector-净6.9.7）和（Renci.SshNet.dll）。
我可以通过SSH连接远程服务器上使用MySQL工作台访问MySQL数据库：结果
使用 portal.RemoteServer.edu:22 ， RemoteServerUsername 和结果
RemoteServerPassword
下面是我的C＃code，它不会从客户表中返回远程服务器上的任何数据：
使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用的System.Web;
使用System.Web.UI程序;
使用System.Web.UI.WebControls;
使用System.Data这;
使用System.Configuration;
使用MySql.Data.MySqlClient;
使用Renci.SshNet;
使用Renci.SshNet.Common;
命名空间WebApplication1
{
公共部分类公司：页
{
保护无效的Page_Load（对象发件人，EventArgs的发送）
{
康涅狄格州的MySqlConnection = NULL;
SshClient客户端= NULL;
ForwardedPortLocal端口= NULL; MySqlConnectionStringBuilder connBuilder =新MySqlConnectionStringBuilder（）;
connBuilder.AllowBatch = TRUE;
connBuilder.Server =127.0.0.1;
connBuilder.Port = 3306;
connBuilder.UserID =LocalHostUserID;
connBuilder.Password =LocalHostPassword;
connBuilder.Database =数据库名; ConnectionInfo conInfo =新ConnectionInfo（portal.RemoteServer.edu，RemoteServerUsername，新PasswordAuthenticationMethod（RemoteServerUsername，RemoteServerPassword））; 使用（客户端=新SshClient（conInfo））
{
尝试
{
端口=新ForwardedPortLocal（127.0.0.1，0，127.0.0.1，22）;
client.Connect（）;
client.AddForwardedPort（端口）;
port.Start（）;
康恩=新的MySqlConnection（connBuilder.ConnectionString）;
conn.Open（）;
conn.ChangeDatabase（connBuilder.Database）; 使用（CMD的MySqlCommand =新的MySqlCommand（SELECT * FROM DatabaseName.Clients LIMIT 10））
{
使用（MySqlDataAdapter的SDA =新MySqlDataAdapter的（））
{
cmd.Connection =康恩;
sda.SelectCommand = CMD;
使用（数据表DT =新的DataTable（））
{
sda.Fill（DT）;
GridView1.DataSource = DT;
GridView1.DataBind（）;
}
}
}
}
赶上（例外前）{}
}
}
}
}
code。通过 @马丁Prikryl 从应答应用的建议，但它仍然没有工作了。
使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用的System.Web;
使用System.Web.UI程序;
使用System.Web.UI.WebControls;
使用System.Data这;
使用System.Configuration;
使用MySql.Data.MySqlClient;
使用Renci.SshNet;
使用Renci.SshNet.Common;
命名空间WebApplication1
{
公共部分类公司：页
{
保护无效的Page_Load（对象发件人，EventArgs的发送）
{
康涅狄格州的MySqlConnection = NULL;
SshClient客户端= NULL;
ForwardedPortLocal端口= NULL; MySqlConnectionStringBuilder connBuilder =新MySqlConnectionStringBuilder（）;
connBuilder.AllowBatch = TRUE;
connBuilder.Server =127.0.0.1;
connBuilder.Port = 3306;
connBuilder.UserID =remoteUsername;
connBuilder.Password =remotePassword;
connBuilder.Database =数据库名称; ConnectionInfo conInfo =新ConnectionInfo（portal.remoteserver.edu，22，remoteUsername，新PasswordAuthenticationMethod（remoteUsername，remotePassword））; 使用（客户端=新SshClient（conInfo））
{
尝试
{
端口=新ForwardedPortLocal（0，127.0.0.1，3306）;
client.Connect（）;
client.AddForwardedPort（端口）;
port.Start（）;
connBuilder.Port = port.BoundPort;
康恩=新的MySqlConnection（connBuilder.ConnectionString）;
conn.Open（）;
conn.ChangeDatabase（connBuilder.Database）; 使用（CMD的MySqlCommand =新的MySqlCommand（SELECT * FROM DatabaseName.Clients LIMIT 10））
{
使用（MySqlDataAdapter的SDA =新MySqlDataAdapter的（））
{
cmd.Connection =康恩;
sda.SelectCommand = CMD;
使用（数据表DT =新的DataTable（））
{
sda.Fill（DT）;
GridView1.DataSource = DT;
GridView1.DataBind（）;
}
}
}
}
赶上（例外前）{}
}
}
}
}
解决方案
大部分低于code是自我解释。不过我已经把要紧的意见。我能够连接到MySQL数据库与下面的code。我从这里和MySQL连接器，用于.NET 使用SSH文库。
使用（VAR的客户=新SshClient（SSH服务器ID，sshuser，sshpassword））//建立ssh连接到服务器的MySQL在哪儿托管
{
client.Connect（）;
如果（client.IsConnected）
{
VAR portForwarded =新ForwardedPortLocal（127.0.0.1，3306，127.0.0.1，3306）;
client.AddForwardedPort（portForwarded）;
portForwarded.Start（）;
使用（CON的MySqlConnection =新的MySqlConnection（SERVER = 127.0.0.1; PORT = 3306; UID = SomeUser的; PASSWORD = somepass; DATABASE = DBNAME））
{
使用（COM的MySqlCommand =新的MySqlCommand（SELECT * FROM城市，CON））
{
com.CommandType = CommandType.CommandText;
DataSet的DS =新的DataSet（）;
MySqlDataAdapter的DA =新MySqlDataAdapter的（COM）;
da.Fill（DS）;
的foreach（DataRow的卓尔在ds.Tables [0] .Rows）
{
Console.WriteLine（从MySQL：+卓尔[1]的ToString（））;
}
}
}
client.Disconnect（）;
}
其他
{
Console.WriteLine（客户端无法到达......）;
}
}
I am developing a web page (ASP.NET/ C#) that queries (MySQL) database on a remote server over SSH. I am using those two libraries (mysql-connector-net-6.9.7) and (Renci.SshNet.dll).
I can access MySQL database using MySQL Workbench on the remote server over SSH connection:
"portal.RemoteServer.edu:22" using "RemoteServerUsername" and
"RemoteServerPassword".
Here is my C# code, which doesn't return any data from Clients table on the remote server:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using Renci.SshNet;
using Renci.SshNet.Common;
namespace WebApplication1
{
public partial class About : Page
{
protected void Page_Load(object sender, EventArgs e)
{
MySqlConnection conn = null;
SshClient client = null;
ForwardedPortLocal port = null;
MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
connBuilder.AllowBatch = true;
connBuilder.Server = "127.0.0.1";
connBuilder.Port = 3306;
connBuilder.UserID = "LocalHostUserID";
connBuilder.Password = "LocalHostPassword";
connBuilder.Database = "DatabaseName";
ConnectionInfo conInfo = new ConnectionInfo("portal.RemoteServer.edu", "RemoteServerUsername", new PasswordAuthenticationMethod("RemoteServerUsername", "RemoteServerPassword"));
using (client = new SshClient(conInfo))
{
try
{
port = new ForwardedPortLocal("127.0.0.1", 0, "127.0.0.1", 22);
client.Connect();
client.AddForwardedPort(port);
port.Start();
conn = new MySqlConnection(connBuilder.ConnectionString);
conn.Open();
conn.ChangeDatabase(connBuilder.Database);
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM DatabaseName.Clients LIMIT 10"))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = conn;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
catch (Exception ex) {}
}
}
}
}
Code after applying the suggestion from the answer by @Martin Prikryl, but it is still not working.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using Renci.SshNet;
using Renci.SshNet.Common;
namespace WebApplication1
{
public partial class About : Page
{
protected void Page_Load(object sender, EventArgs e)
{
MySqlConnection conn = null;
SshClient client = null;
ForwardedPortLocal port = null;
MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
connBuilder.AllowBatch = true;
connBuilder.Server = "127.0.0.1";
connBuilder.Port = 3306;
connBuilder.UserID = "remoteUsername";
connBuilder.Password = "remotePassword";
connBuilder.Database = "databasename";
ConnectionInfo conInfo = new ConnectionInfo("portal.remoteserver.edu", 22, "remoteUsername", new PasswordAuthenticationMethod("remoteUsername", "remotePassword"));
using (client = new SshClient(conInfo))
{
try
{
port = new ForwardedPortLocal(0, "127.0.0.1", 3306);
client.Connect();
client.AddForwardedPort(port);
port.Start();
connBuilder.Port = port.BoundPort;
conn = new MySqlConnection(connBuilder.ConnectionString);
conn.Open();
conn.ChangeDatabase(connBuilder.Database);
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM DatabaseName.Clients LIMIT 10"))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = conn;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
catch (Exception ex) {}
}
}
}
}
解决方案
Most of the code below is self explanatory. Still I have put the needful comments. I was able to connect to the MySql database with the code below. I had used SSH library from here and MySql connector for .NET.
using(var client = new SshClient("ssh server id", "sshuser", "sshpassword")) // establishing ssh connection to server where MySql is hosted
{
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...");
}
}
这篇关于使用SSH.NET库连接到MySQL从.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
