首先需要安装MySQL Connector Net 6.8.3
然后在项目中引用MySQL Connector,如下图所示
C#连接MySQL的操作类代码如下:
public class MySQLHelper { private string db_host = "localhost"; //数据库服务器 private string db_port = "3306"; //数据库端口号 private string db_user = "root"; //数据库用户 private string db_pass = "123456"; //数据库密码 private string db_name = "myblog"; //数据库名 private MySqlConnection dbconn; public MySQLHelper() { //do nothing } ~MySQLHelper() { this.CloseDBconn(); } //连接数据库 public void OpenDBconn() { this.dbconn = new MySqlConnection(); this.dbconn.ConnectionString = "server=" + this.db_host + ";uid=" + this.db_user + ";pwd=" + this.db_pass + ";database=" + this.db_name + ";"; if (this.db_port.Length > 0) { this.dbconn.ConnectionString += ";port=" + this.db_port; } try { this.dbconn.Open(); } catch (MySqlException ex) { string message = "无法连接数据库: "+ ex.Message; string caption = "系统提示"; MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(message, caption, buttons); Environment.Exit(0); } } //关闭数据库 public void CloseDBconn() { if (this.dbconn != null) { this.dbconn.Close(); } } //查询数据 public MySqlDataReader query(string sqltext) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = this.dbconn; cmd.CommandType = CommandType.Text; cmd.CommandText = sqltext; MySqlDataReader reader = cmd.ExecuteReader(); return reader; } //更新数据 public int execute(string sqltext) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = this.dbconn; cmd.CommandType = CommandType.Text; cmd.CommandText = sqltext; int result = cmd.ExecuteNonQuery(); return result; } }
使用方法(查询数据):
this.dbhelper = new MySQLHelper(); this.dbhelper.OpenDBconn(); string sqltext = "s.e.l.e.c.t * from blog w.h.e.r.e id=123 order by id asc limit 0,1"; MySqlDataReader reader = this.dbhelper.query(sqltext); if (reader.HasRows == false) { this.logtext = "\r\n" + DateTime.Now.ToString() + ", 内容处理完成。"; textBox1.AppendText(this.logtext); textBox1.ScrollToCaret(); return; } else { while (reader.Read() == true) { this.curr_id = Convert.ToInt32(reader["id"]); this.curr_title = reader["title"].ToString(); this.curr_fileext = reader["fileext"].ToString(); this.curr_filesize = Convert.ToDouble(reader["filesize"]) * 1024; } //do something more } reader.Close(); this.dbhelper.CloseDBconn();
使用方法(更新数据):
this.dbhelper.OpenDBconn(); string sqlchan = "u.p.d.a.t.e blog set isfixed=1 w.h.e.r.e id=123"; this.dbhelper.execute(sqlchan); this.dbhelper.CloseDBconn();