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);        }        ///         /// 连接        /// 
        ///         ///         private void btnConn_Click(object sender, EventArgs e)        {                        //判断连接状态            if (conn.State != ConnectionState.Open)            {                conn.Open();                tbText.Text = strConn;                label4.Text = "";                label4.Text = "连接成功";            }        }        ///         /// 查询        /// 
        ///         ///         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();        }        ///         /// 添加        /// 
        ///         ///         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();        }        ///         /// 修改        /// 
        ///         ///         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();        }        ///         /// 删除        /// 
        ///         ///         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 = "用户不存在";                    }                }            }        }        ///         /// 判断连接        /// 
        ///         private bool CkeckConn()        {            if (conn.State ==ConnectionState.Open)            {                return true;            }            else            {                return false;            }        }        ///         /// 列表更新        /// 
        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();        }        ///         /// 退出        /// 
        ///         ///         private void btn_Exit_Click(object sender, EventArgs e)        {            this.Close();        }        ///         /// 关闭        /// 
        ///         ///         private void frm_main_FormClosing(object sender, FormClosingEventArgs e)        {            if (conn !=null )            {                if (conn.State != ConnectionState.Closed)                {                    conn.Close();                }            }            if (dr !=null)            {                dr.Close();            }        }    }}