C#连接MySQL

一、环境搭配

         安装MySQL，引用MySql.Data.DLL文件，这个MySql.Data.DLL文件在你安装Mysql的时候已经下载好给你的了。

        把它复制到项目的Debug目录下，然后引用即可。

二、连接使用步骤

        声明对象，有连接对象，语句执行对象，结果读取对象，在这我们要先引用一下MysqlClient。

 

//连接对象
        MySqlConnection conn=null;
       
        //语句执行对象
        MySqlCommand comm=null;
        //语句执行结果数据对象
        MySqlDataReader dr = null;

        连接数据库

 conn = new MySqlConnection("Database = stu;Server = localhost;Port = 3306;Password = 123456;UserID = root;charset = utf8mb4");

        sql语句命令对象

comm = new MySqlCommand("select * from user",conn);

        执行语句获取数据

 dr = comm.ExecuteReader(); /*查询*/
 //dr = comm.ExecuteNonQuery();  /*增删改*/
            while (dr.Read())
            {               
                tbText.Text += dr.GetString("对应表字段名称") + "----" + dr.GetString("password");
                tbText.Text += "\r";
            }
            dr.Close();
            conn.Close();

        注意使用完，我们要关闭掉连接资源。如果连接失败，可能是版本不对，活动平台要修改成x86的平台。

三、功能代码实现

         首先创建一个数据库，随便写入几条数据。

CREATE TABLE `user` (
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into user values('jack','sss');
insert into user values('123','123');

        布局 有一个CheckedListBox控件，方便勾选删除。

        代码

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;
using MySql.Data;
namespace MysqlText
{
    public partial class frm_main : Form
    {
        //连接对象
        MySqlConnection conn=null;      
        //语句执行对象
        MySqlCommand comm=null;
        //语句执行结果数据对象
        MySqlDataReader dr = null;
        string strConn = "";
        public frm_main()
        {
            InitializeComponent();
            strConn = "Database = stu;Server = localhost;Port = 3306;Password = 123456;UserID = root;charset = utf8mb4";
            conn = new MySqlConnection(strConn);
        }

        /// <summary>
        /// 连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnConn_Click(object sender, EventArgs e)
        {            
            //判断连接状态
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
                tbText.Text = strConn;
                label4.Text = "";
                label4.Text = "连接成功";
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSel_Click(object sender, EventArgs e)
        {
            //判断连接状态
            if (!CkeckConn())
            {
                MessageBox.Show("请连接数据库");
                return;
            }
            comm = new MySqlCommand("select * from user", conn);
            tbText.Text = "";
            dr = comm.ExecuteReader(); /*查询*/
            while (dr.Read())
            {
                tbText.Text += dr.GetString("username") + "----" + dr.GetString("password");
                tbText.Text += "\r\n";
            }
            dr.Close();
            ckLBoxsRefresh();
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnAdd_Click(object sender, EventArgs e)
        {
            //判断连接状态
            if (!CkeckConn())
            {
                MessageBox.Show("请连接数据库");
                return;
            }
            label4.Text = "";
            //先判断用户是否已注册
            if (tbUser.Text =="" || tbPW.Text=="")
            {
                label4.Text = "请完善信息";
                return;
            }
            comm = new MySqlCommand("select * from user where username = '" + tbUser.Text + "'", conn);
            dr = comm.ExecuteReader();
            if (dr.Read())
            {
                label4.Text = "已存在用户" + tbUser.Text;
            }
            else
            {
                dr.Close();
                int num = 0;
                comm = new MySqlCommand("insert into user values('" + tbUser.Text + "','" + tbPW.Text + "')", conn);               
                num = comm.ExecuteNonQuery();
                if (num > 0)
                {
                    label4.Text = "已添加用户" + tbUser.Text;
                    ckLBoxsRefresh();
                    tbText.Text = "";

                }
                else
                {
                    label4.Text = "添加失败";
                }
            }
            dr.Close();
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUp_Click(object sender, EventArgs e)
        {
            //判断连接状态
            if (!CkeckConn())
            {
                MessageBox.Show("请连接数据库");
                return;
            }
            label4.Text = "";
            //先判断用户是否已注册
            comm = new MySqlCommand("select * from user where username = '" + tbUpUser.Text + "'", conn);
            dr = comm.ExecuteReader();
            if (dr.Read())
            {
                dr.Close();
                int num = 0;
                comm = new MySqlCommand("update user set  password = '" + tbUpPW.Text + "'where username = '" + tbUpUser.Text + "'", conn);
                num = comm.ExecuteNonQuery();
                if (num > 0)
                {
                    label4.Text = "已修改用户" + tbUpUser.Text + "密码";
                    tbText.Text = "";
                }
                else
                {
                    label4.Text = "修改失败";
                }
            }
            else
            {
                label4.Text = "用户不存在";
            }
            dr.Close();
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnDel_Click(object sender, EventArgs e)
        {
            //判断连接状态
            if (!CkeckConn())
            {
                MessageBox.Show("请连接数据库");
                return;
            }
            label4.Text = "";
            //查找选中
            for (int i = 0; i < ckLBoxs.Items.Count; i++)
            {
                if (ckLBoxs.GetItemChecked(i))
                {                   
                    int num = 0;
                    comm = new MySqlCommand("delete from user where username = '" + ckLBoxs.Items[i].ToString() + "'", conn);

                    num = comm.ExecuteNonQuery();
                    if (num > 0)
                    {
                        label4.Text += "已删除用户" + ckLBoxs.Items[i].ToString()+"\t";
                        ckLBoxsRefresh();
                        tbText.Text = "";
                    }
                    else
                    {
                        label4.Text = "用户不存在";
                    }
                }
            }
        }
        /// <summary>
        /// 判断连接
        /// </summary>
        /// <returns></returns>
        private bool CkeckConn()
        {
            if (conn.State ==ConnectionState.Open)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 列表更新
        /// </summary>
        private void ckLBoxsRefresh()
        {
            //判断连接状态
            if (!CkeckConn())
            {
                MessageBox.Show("请连接数据库");
                return;
            }
            comm = new MySqlCommand("select * from user", conn);
            dr = comm.ExecuteReader();
            ckLBoxs.Items.Clear();
            int num = 0;
            while (dr.Read())
            {
                ckLBoxs.Items.Add(dr.GetString(0));
                num++;
            }
            label4.Text = "";
            label4.Text = "已更新数据" + num.ToString() + "条。";
            dr.Close();
        }
        /// <summary>
        /// 退出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Exit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        /// <summary>
        /// 关闭
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frm_main_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (conn !=null )
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            if (dr !=null)
            {
                dr.Close();
            }
        }
    }
}

 四、效果

        连接

        查询

        添加

        修改

        删除

C#连接mysql以及CRUD的实现就这样，如有什么问题或者交流可以留言或私信me。