C#连接MySQL数据库

目录

一、引用MySql.Data.dll文件

二、连接、关闭数据库

三、数据库增删改查

附：完整代码

一、引用MySql.Data.dll文件

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

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

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

二、连接、关闭数据库

引入命名空间

using MySql.Data.MySqlClient;

定义连接字段

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

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";  //连接状态

连接、关闭数据库

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";  //IP        string 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;        }

附：完整代码

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";  //IP        string 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;        }    }}

来源地址：https://blog.csdn.net/DXuexue/article/details/130154394