一、引用MySql.Data.dll文件
1、创建C#窗体应用程序，解决方案资源管理器中找到“引用”，右键，选择添加引用。
2023-11-03T00:57:37.png
2、在MySQL的安装目录下找到MySql.Data.dll文件，选择添加 ，再点击确定。

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

二、连接、关闭数据库
1、引入命名空间

using MySql.Data.MySqlClient;

2、定义连接字段
设置端口号、用户密码、数据库名等

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

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";  //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;
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns></returns>
        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;
        }
 
        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        public void close_sql()
        {
            sql_con_state = "off";
            sql_Test.Close();
           
        }
    }
}

三、数据库增删改查
1、增加

/// 

/// 增加
/// </summary>
/// <param name="table_name">表名</param> 
/// <param name="content">内容</param>
/// <param name="value">值</param>
/// <returns></returns>
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、删除

/// 

/// 删除
/// </summary>
/// <param name="table_name">表名</param>
/// <param name="condition">条件</param>
/// <returns></returns>
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、修改

/// 

/// 修改
/// </summary>
/// <param name="table_name">表名</param>
/// <param name="content">内容</param>
/// <param name="condition">条件</param>
/// <returns></returns>
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、查询

/// 

/// 查询
/// </summary>
/// <param name="table_name">表名</param>
/// <param name="content">内容</param>
/// <param name="condition">条件</param>
/// <returns></returns>
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;
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns></returns>
        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;
        }
 
        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        public void close_sql()
        {
            sql_con_state = "off";
            sql_Test.Close();
           
        }
 
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="table_name">表名</param> 
        /// <param name="content">内容</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        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;
        }
 
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        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;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="content">内容</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        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;
        }
 
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="content">内容</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        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;
        }
    }
}

标签: none

评论已关闭