赞
踩
安装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。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。