C#连接MySQL数据库

目录

一、引用MySql.Data.dll文件

二、连接、关闭数据库

三、数据库增删改查

附：完整代码

一、引用MySql.Data.dll文件

1、创建C#窗体应用程序，解决方案资源管理器中找到“引用”，右键，选择添加引用。

2、在MySQL的安装目录下找到MySql.Data.dll文件，选择添加 ，再点击确定。

注：如果程序可能多人互传，MySql.Data.dll文件建议复制到项目文件夹内，防止在其他电脑上找不到该文件路径。

二、连接、关闭数据库

1、引入命名空间

using MySql.Data.MySqlClient;

2、定义连接字段

设置端口号、用户密码、数据库名等

string sql_ip = "127.0.0.1";//IPstring sql_port = "3306"; //端口号string sql_user = "root";//用户名string sql_pw = "power123";//密码string sql_name = "my_data"; //数据库名string sql_con_state = "off";//连接状态

3、连接、关闭数据库

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using MySql.Data.MySqlClient;//数据库namespace WindowsFormsApplication1{public partial class Form1 : Form{public Form1(){InitializeComponent();}string sql_ip = "127.0.0.1";//IPstring sql_port = "3306"; //端口号string sql_user = "root";//用户名string sql_pw = "power123";//密码string sql_name = "my_data"; //数据库名string sql_con_state = "off";//连接状态MySqlConnection sql_Test;/// 
/// 连接数据库/// 
/// public int connect_sql(){string ConnectionString = "server = " + sql_ip + "; userid = " + sql_user + "; password = " + sql_pw + "; database =" + sql_name + "; persistsecurityinfo = True;SslMode=None ;Charset=utf8;"; // string ConnectionString = "server = " + this.sql_ip.Text + "; userid = "+this.sql_user.Text+"; password = "+ this.sql_pw.Text + "; database ="+ this.sql_name.Text + "; persistsecurityinfo = True;";sql_Test = new MySqlConnection(ConnectionString);try{sql_Test.Open();Console.WriteLine("数据库连接成功");//打印信息sql_con_state = "on";}catch (MySqlException ex){Console.WriteLine(ex.Message);//打印错误信息sql_con_state = "off";return -1;}return 0;}/// 
/// 关闭数据库/// 
/// public void close_sql(){sql_con_state = "off";sql_Test.Close(); }}}

三、数据库增删改查

1、增加

/// 
/// 增加/// 
/// 表名 /// 内容/// 值/// public int insert_data(string table_name, string content, string value){string sql;sql = "insert into " + table_name + " (" + content + ") value (" + value + ")";if (content == "" || value == "" || table_name == ""){Console.WriteLine("语法错误 "+ sql);//打印错误信息return -1;}if (sql_con_state != "on"){Console.WriteLine("数据库未连接！");//打印信息return -2;}int res;MySqlCommand cmd = new MySqlCommand(sql, sql_Test);res = cmd.ExecuteNonQuery();return res;}

2、删除

/// 
/// 删除/// 
/// 表名/// 条件/// public int del_data(string table_name, string condition){string sql;sql = "delete from " + table_name + " where " + condition;if (table_name == "" || condition == ""){Console.WriteLine("语法错误 "+ sql);//打印错误信息return -1;}if (sql_con_state != "on"){Console.WriteLine("数据库未连接！");//打印信息return -2;}int res;MySqlCommand cmd = new MySqlCommand(sql, sql_Test);res = cmd.ExecuteNonQuery();return res;}

3、修改

/// 
/// 修改/// 
/// 表名/// 内容/// 条件/// public int update_data( string table_name, string content, string condition){string sql;sql = "update " + table_name + " set " + content + " where " + condition;if (content == "" || table_name == "" || condition == ""){Console.WriteLine("语法错误 "+ sql);//打印错误信息return -1;}if (sql_con_state != "on"){Console.WriteLine("数据库未连接！");//打印信息return -2;}int res;MySqlCommand cmd = new MySqlCommand(sql, sql_Test);res = cmd.ExecuteNonQuery();return res;}

4、查询

/// 
/// 查询/// 
/// 表名/// 内容/// 条件/// public DataTable db_select(string table_name, string content, string condition){string sql;DataTable dt = new DataTable();if (sql_con_state == "on"){if (condition == ""){sql = "select " + content + " from " + table_name; //查找全部if (content == "" || table_name == "" ){Console.WriteLine("语法错误 " + sql);//打印错误信息 }}else{sql = "select " + content + " from " + table_name + " where " + condition;//按照条件查找if (content == "" || table_name == "" || condition == ""){Console.WriteLine("语法错误 " + sql);//打印错误信息 }}MySqlCommand cmd = new MySqlCommand(sql, sql_Test);MySqlDataAdapter mda = new MySqlDataAdapter(cmd); //获取结果集mda.Fill(dt);}else{Console.WriteLine("数据库未连接！");//打印信息}return dt;}

附：完整代码

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using MySql.Data.MySqlClient;//数据库namespace WindowsFormsApplication1{public partial class Form1 : Form{public Form1(){InitializeComponent();}string sql_ip = "127.0.0.1";//IPstring sql_port = "3306"; //端口号string sql_user = "root";//用户名string sql_pw = "power123";//密码string sql_name = "my_data"; //数据库名string sql_con_state = "off";//连接状态MySqlConnection sql_Test;/// 
/// 连接数据库/// 
/// public int connect_sql(){string ConnectionString = "server = " + sql_ip + "; userid = " + sql_user + "; password = " + sql_pw + "; database =" + sql_name + "; persistsecurityinfo = True;SslMode=None ;Charset=utf8;"; // string ConnectionString = "server = " + this.sql_ip.Text + "; userid = "+this.sql_user.Text+"; password = "+ this.sql_pw.Text + "; database ="+ this.sql_name.Text + "; persistsecurityinfo = True;";sql_Test = new MySqlConnection(ConnectionString);try{sql_Test.Open();Console.WriteLine("数据库连接成功");//打印信息sql_con_state = "on";}catch (MySqlException ex){Console.WriteLine(ex.Message);//打印错误信息sql_con_state = "off";return -1;}return 0;}/// 
/// 关闭数据库/// 
/// public void close_sql(){sql_con_state = "off";sql_Test.Close(); }/// 
/// 增加/// 
/// 表名 /// 内容/// 值/// public int insert_data(string table_name, string content, string value){string sql;sql = "insert into " + table_name + " (" + content + ") value (" + value + ")";if (content == "" || value == "" || table_name == ""){Console.WriteLine("语法错误 "+ sql);//打印错误信息return -1;}if (sql_con_state != "on"){Console.WriteLine("数据库未连接！");//打印信息return -2;}int res;MySqlCommand cmd = new MySqlCommand(sql, sql_Test);res = cmd.ExecuteNonQuery();return res;}/// 
/// 删除/// 
/// 表名/// 条件/// public int del_data(string table_name, string condition){string sql;sql = "delete from " + table_name + " where " + condition;if (table_name == "" || condition == ""){Console.WriteLine("语法错误 "+ sql);//打印错误信息return -1;}if (sql_con_state != "on"){Console.WriteLine("数据库未连接！");//打印信息return -2;}int res;MySqlCommand cmd = new MySqlCommand(sql, sql_Test);res = cmd.ExecuteNonQuery();return res;}/// 
/// 修改/// 
/// 表名/// 内容/// 条件/// public int update_data( string table_name, string content, string condition){string sql;sql = "update " + table_name + " set " + content + " where " + condition;if (content == "" || table_name == "" || condition == ""){Console.WriteLine("语法错误 "+ sql);//打印错误信息return -1;}if (sql_con_state != "on"){Console.WriteLine("数据库未连接！");//打印信息return -2;}int res;MySqlCommand cmd = new MySqlCommand(sql, sql_Test);res = cmd.ExecuteNonQuery();return res;}/// 
/// 查询/// 
/// 表名/// 内容/// 条件/// public DataTable db_select(string table_name, string content, string condition){string sql;DataTable dt = new DataTable();if (sql_con_state == "on"){if (condition == ""){sql = "select " + content + " from " + table_name; //查找全部if (content == "" || table_name == "" ){Console.WriteLine("语法错误 " + sql);//打印错误信息 }}else{sql = "select " + content + " from " + table_name + " where " + condition;//按照条件查找if (content == "" || table_name == "" || condition == ""){Console.WriteLine("语法错误 " + sql);//打印错误信息 }}MySqlCommand cmd = new MySqlCommand(sql, sql_Test);MySqlDataAdapter mda = new MySqlDataAdapter(cmd); //获取结果集mda.Fill(dt);}else{Console.WriteLine("数据库未连接！");//打印信息}return dt;}}}